Compare Columns & Highlight Duplicates or Unique Values

Here is how you can compare multiple columns in Excel & change the background color for duplicate values.

Assume you have a list of values in columns A & B.

You want to compare these two column values & highlight the duplicates.

  1. Select values in Column A & B that you want to compare.
  2. Click Conditional Formatting -> New Rule
  3. Choose ‘Format only unique or duplicate values’
  4. Select ‘Duplicate’ or ‘Unique’ in ‘Edit rule Description’
  5. Choose a color to highlight the duplicate values.
  6. Click Ok.

Excel will highlight the duplicate or unique values according to the option you choose in step 4.

The above example shows how to compare two columns. But, you can choose any number of columns/rows or range of values in a Excel Worksheet & then perform the same sequence of steps to find the duplicate values.


Leave a Reply