How to Compare Three Columns in Excel

To compare three columns in Excel, you can use a combination of IF, AND, or OR functions, or Conditional Formatting to highlight the differences. Here's how to do it.

Using IF and AND Functions

1. Open Excel and insert the data you want to compare in three adjacent columns.
2. In a new column, type a formula using the IF and AND functions to compare the values in the three columns. The formula should look like this:

=IF(AND(A1=B1, B1=C1), "Match", "Difference")

Replace A1, B1, and C1 with the appropriate cell references for your data.

3. Press Enter to apply the formula to the first row of data.
4. Click the bottom right corner of the cell with the formula and drag it down to apply the formula to the rest of the rows.

The formula will return "Match" if all three values in the row are the same, and "Difference" if they're not.

Using Conditional Formatting

1. Select the range of cells you want to compare (including all three columns).
2. Go to the Home tab and click on Conditional Formatting.
3. Select "New Rule" from the dropdown menu.
4. Choose "Use a formula to determine which cells to format" as the rule type.
5. In the "Format values where this formula is true" box, type the following formula:

=AND(A1<>B1, B1<>C1)

Replace A1, B1, and C1 with the appropriate cell references for your data.

6. Click the "Format" button and choose the formatting you want to apply to cells with differences (e.g., change the fill color).
7. Click "OK" to apply the formatting rule.

Excel will now highlight cells with differences using the formatting you chose.

Example

A       B       C       Result
100     100     100     Match
200     150     150     Difference
300     300     250     Difference
400     400     400     Match

In this example, the values in rows 1 and 4 match across all three columns, while the values in rows 2 and 3 do not. The IF and AND functions will return "Match" for rows 1 and 4 and "Difference" for rows 2 and 3. If using Conditional Formatting, the cells with differences in rows 2 and 3 will be highlighted.