Compare Columns in Excel and Highlight Similarities and Differences

Compare Two Columns in Excel

There are multiple reasons why you would like to compare columns in Excel. Though you can always compare manually this might get extremely difficult when dealing with and computing large data sets.

In Excel, you can either get the result of the comparison in the default ‘true‘ and ‘false‘ output or a custom output, meaning like anything ‘match’, ‘success’, ‘identical’, etc.

Also read: The Ultimate Guide to Sorting Data in Excel?

Compare Columns in Excel – A Basic Example

Let’s start with a simple example and compare columns in Excel where the row is the same. This becomes an easy start for even those who have never worked with formulas.

Default Output:

Compare Columns in Excel
Comparing columns on same row – Default
  • To perform comparison as shown in the above image, apply the simple formula:
=A{n}=B{n}

where A{n} signifies the cell location and n=row number.

This default method of comparison gives output in the default value only – “true” or “false”

Custom Output:

To get a “Custom Output” from the comparison, we need to use the if function:

=IF(logical_test, [value_if_true], [value_if_false])

where:

A logical test can be anything A=B, A<B, A>B, etc.
[value_if_true] can be set to anything like “Same”, “Correct”, “t”, etc.

[value_if_false] can be set to anything like “Different”, “Incorrect”, “f”, etc.

To understand better, consider our below example of comparison of two columns:

 Comparing columns on same row - Custom in Excel

Compare Columns in Excel
Comparing columns on the same row – Custom

Compare Two Columns and Highlight Matching Data

  • Go to ‘Home’ inside an excel workbook.
  • Select the cells which you want to compare and highlight by left-clicking and dragging to select.
Conditional Formatting in Excel
Conditional Formatting
  • Click on ‘Conditional Formatting‘ option which will further open a drop-down menu
  • In the menu go with ‘Highlight Cell Rules‘.
  • Then again from the option menu, select ‘Duplicate Values
Highlighting duplicate cells in Excel Compare Columns in Excel
Highlighting duplicate cells
  • After selecting ‘Duplicate Values‘ you will be served with a pop-up menu
  • Here you can select from two options: ‘Duplicate’ or ‘Unique’
    where:

    Duplicate: targets the values (data) which are duplicate
    Unique: when this option is selected, it targets the values (data) which are unique.
  • There is also an option to select the color for text and also for cell borders.
Highlighting duplicate cells with green text in Excel
Highlighting duplicate cells with green text
  • Click OK to Highlight the duplicated cell values as shown in the below example image.
Result for duplicate and higlighted cells
  • To Highlight the cells which have ‘Unique Values‘:
  • Under Conditional Formatting, Go to ‘Highlight Cell Rules’ to reveal a drop-down menu
  • From the menu click on ‘Duplicate Values’ to reveal a pop-up menu
highlight unique cell values
  • From the menu select ‘Unique’ – under Format cells that contain [as depicted in above image]
  • Click on OK to apply the highlighting.

Conclusion

And that’s it from our side. I hope you have learned well how to Compare columns in Excel and to highlight them. For more tutorials, stay tuned to QuickExcel!