How do I Compare Two Columns in MS Excel?

By Sonia Waring

Excel provides several ways to compare values in two spreadsheet columns. You can highlight cells that are the same, or write a simple formula to compare the cells column by column. Either method can give you a clear visual indication of which cells are the same, or in case of numerical values, which cells are greater than, less than or equal to any others.

Conditional Formatting to Locate Duplicate Values

Step 1

Highlight the two columns you want to compare.

Step 2

Click the "Conditional Formatting" drop-down menu in the Home tab.

Step 3

Click "Highlight Cell Rules," then "More Rules...."

Step 4

Click "Format only unique or duplicate values" and choose whether to format all duplicate or all unique values in the drop-down menu labeled "Format all:".

Step 5

Click the "Format" button and set the font, border, and fill options to change the look of the cells you specified. Click "OK" to apply the conditional formatting.

Formula to Compare Values

Step 1

Choose a blank cell in a third column to compose a comparison formula. If your data begins in cells A1 and B1, use C1 for the first formula cell.

Step 2

Type an IF formula, which begins "=IF". Consider whether you want to compare the value in A1 to the value in B1 or whether you want to indicate if they are equal, or not equal.

Step 3

Add parentheses and a simple equation to the IF formula. If you want a visual indication that B1 is greater than A1, type "(A1B1)". If you want to know whether the cells are the same, type "(A1=B1)"; different, type "(A1<>B1)".

Step 4

Add a comma after the simple equation, inside the parentheses, to tell Excel you're finished with the "if" part of the formula.

Step 5

Move on to the "then" part of the statement. IF functions contain three parts. The first part the condition -- for example, A1>B1. The second part is the cell value if the condition is met -- the "true" value, if A1 is greater than B1. The third part is the cell value if the condition isn't met -- the "false" value, if A1 isn't greater than B1. Type "Greater", with the quotation marks, after the comma -- or substitute another word, whatever you want to see to indicate the comparison. Type another comma, then "Less than", again with quotes, and close the parentheses -- or, again, substitute another word. Using the same example, the formula now reads: =IF(A1>B1,"Greater", "Less than")Press "Enter."

Step 6

Copy the formula from C1 and paste it into C2, C3 and as far down as the A1 and B1 cells you want to compare go. As you paste the formula into each cell, it will immediately display the result of the formula for the A and B cells in that row.

Step 7

Use conditional formatting, if you want, to highlight all the true or false values in column C. The "Highlight Cell Rules" option labeled "Text that contains..." option lets you set the format for the text you specify.