Two easy methods to compare columns in Excel

Finding an easy way to compare contents of two columns in Excel and highlight the differences (or similarities) could be challenging.

Searching the internet would provide you with hundreds of different ways to accomplish this task, but many of them are complicated and/or are for comparing cells in the same row.

Here you can see two easy methods to perform the comparing in a few simple steps.

Method 1: Using Conditional Formatting

• Open your Excel spreadsheet and select the two columns you want to compare. For instance, let's say you're comparing column A and column B.

• Go to the "Home" tab on the Excel ribbon, and then click on "Conditional Formatting" in the "Styles" group.

• Hover over "Highlight Cells Rules", and then click on "Duplicate Values…"

• In the dialog box that appears, under the first drop-down list, choose "Duplicate".

• Now, you'll see that Excel highlights the matching cells in both columns.

• To highlight the differences instead, you need to select "Unique" from the first drop-down list in the dialog box.

Now, Excel will highlight the differences in both columns.

Method 2: Using an Excel Formula

Let's assume that column B has 11 rows (from row number 1 to 11).

1. In a new column (let's say column C), type the following formula: =COUNTIF($B$1:$B$11, $A1)>0. This formula compares the cells of column A with all the cells of column B one by one, from row 1 to row 11.

1. Press Enter and then drag the fill handle (the small square at the bottom-right of the cell) down to copy this formula for all cells in the column (column C). If a cell in column A exists in column B too, it will return "TRUE" under column C; otherwise, it will return "FALSE".

1. If you want to see if each cell of column B exists in column A or not, just change the formula and replace A with B and vice versa. The formula would be: =COUNTIF($A$1:$A$11, $B1)>0.

Remember to replace A, B, and C with the actual cell references in your spreadsheet.