How to Compare Three Columns in Excel (With Example)

You can use the following basic formula to compare three columns in Excel:

=IF(AND(B2=C2,C2=D2),"Equal","Not Equal")

This particular formula compares the values in cells B2, C2, and D2.

If all of the values are equal, the formula returns Equal. Otherwise, it returns Not Equal.

The following example shows how to use this formula in practice.

Example: Compare Three Columns in Excel

Suppose we have the following dataset that shows the highest scorer on various basketball teams during three different games:

We can type the following formula into cell E2 to test if the three values in the first row are all equal:

=IF(AND(B2=C2,C2=D2),"Equal","Not Equal")

The following screenshot shows how to use this formula in practice:

Excel compare three columns

The formula returns Not Equal since the three names in the first row don’t all match.

We can then drag this formula in cell E2 down to the remaining cells in column E to test for matches in each row:

Notice that the formula only returns Equal for the rows where the cell values are equal across each of the three columns.

You can also apply conditional formatting to the rows where all three cell values are equal by highlighting the cell range E2:E11, then clicking on the Conditional Formatting button on the Home tab, then clicking Highlight Cell Rules, then clicking Equal To:

In the new window that appears, type Equal into the box and choose a fill color, then click OK:

The rows that have matching values across all three columns will now be filled with green:

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Compare Two Lists in Excel Using VLOOKUP
How to Compare Two Excel Sheets for Differences
How to Compare Dates Without Time in Excel

Featured Posts

2 Replies to “How to Compare Three Columns in Excel (With Example)”

  1. Thank you. My question is if all 3 columns have the same value, how can I reflect the name of that value. For example, if all 3 columns have “Superior” “Superior” “Superio” – I would like the value Superior displayed.
    If all 3 column have a mixture of “Superior” and “Satisfactory”, I would like the dominant value from the 3 columns displayed. So if there are 2 “Satisfactory” and 1 “Superior”, I would like “Satisfactory” to be displayed in the 4th column. Thank you.

  2. I’ve got a similar formula, but I’d like the reporting cell to return the discrepant cell ID (i.e. AA45) or at lest the value that is different than the others.
    here is my current formula =AND(EXACT(E3,L3),EXACT(L3,S3),EXACT(S3,AA3))
    any help would be greatly appreciated

Leave a Reply

Your email address will not be published. Required fields are marked *