The fastest way to spot differences between two columns

The simplest method is to create a formula in a third column that compares the values row by row. In cell C1, type =A1=B1 and press Enter. Excel returns TRUE if the values match and FALSE if they don't. Copy this formula down the entire column by clicking C1, then dragging the small square at the bottom-right corner of the cell down to the last row with data.

If you want to see the actual differences rather than just TRUE or FALSE, use =IF(A1=B1,"Match","Different") instead. This shows the word "Match" when values are identical and "Different" when they aren't, which is easier to scan visually.

For a more detailed view of what changed, use =IF(A1=B1,"",A1&" → "&B1). This leaves the cell blank if values match, but shows the old value, an arrow, and the new value if they differ — useful when you're tracking changes over time.

Key Takeaways

  • A simple formula like =A1=B1 in a helper column shows TRUE or FALSE for each row, letting you spot mismatches instantly.
  • Conditional formatting with a color rule highlights all differences at once without needing a formula column.
  • The Find & Replace dialog with regular expressions can locate specific patterns across both columns if you're searching for particular types of changes.
  • For large datasets, sorting by your TRUE/FALSE column groups all the differences together so you can review them in one section.

Using conditional formatting to highlight differences visually

If you prefer to see differences highlighted in color rather than in a separate column, conditional formatting is faster. Select both columns A and B together (click A1, hold Shift, and click the last cell in column B). Then go to the Home tab, click Conditional Formatting, and choose New Rule.

In the dialog, select "Use a formula to determine which cells to format." In the formula box, type =A1<>B1 (the <> symbol means "not equal to"). Click Format, choose a fill color like yellow or red, and click OK. Every cell that differs from its partner in the other column now shows that color.

This method works best when you want a quick visual scan without adding extra columns to your spreadsheet. It's especially useful if your data is read-only or you're presenting the comparison to someone else.

Filtering to show only the rows that don't match

Once you've added a TRUE/FALSE formula column, you can filter to show only the mismatches. Click any cell in your data range, then go to the Data tab and click Filter. Small dropdown arrows appear in the header row of each column.

Click the dropdown arrow in your formula column (the one with TRUE/FALSE values) and uncheck TRUE, leaving only FALSE checked. Now the spreadsheet displays only the rows where the two columns differ. This is much faster than scrolling through thousands of rows to find problems manually.

When you're done reviewing, click the dropdown again and select "Clear Filter" to see all rows again. The filter doesn't delete anything — it just hides rows temporarily.

Comparing columns when values are in different orders

If column A contains names in one order and column B contains the same names in a different order, a simple row-by-row comparison won't work. Instead, use =COUNTIF($B:$B,A1)>0 in column C. This checks whether each value from column A appears anywhere in column B, returning TRUE if it does and FALSE if it doesn't.

To find values that exist in column B but not in column A, reverse the formula: =COUNTIF($A:$A,B1)>0 in a column next to B. Now you can see which items are missing from each list.

This approach is slower than row-by-row comparison on large datasets because Excel has to search the entire column for each value. If you're working with more than a few hundred rows, consider sorting both columns first so the values line up, then use the simple =A1=B1 method.

Using the Go To Special feature to select all differences at once

Excel has a built-in tool called Go To Special that can select every cell that differs from its pair. First, select the range that includes both columns — for example, A1:B100. Go to the Home tab, click Find & Select (or press Ctrl+H), and choose Go To Special.

In the dialog, select "Row differences" if you want to find cells that differ within each row, or "Column differences" if you're comparing across columns. Click OK, and Excel selects every cell that doesn't match its neighbor. You can then format all of them at once — change the font color, add a background, or apply bold formatting to every difference in your data.

This method is faster than conditional formatting when you want to apply multiple formatting changes at once, but it requires you to select the exact range first, so it works best when your data is tidy and rectangular.

Comparing columns with text that might have extra spaces

Sometimes two columns look identical but don't match because one has extra spaces before or after the text. Use the TRIM function to remove leading and trailing spaces: =TRIM(A1)=TRIM(B1). This compares the actual text content, ignoring spaces.

If the text differs only in uppercase versus lowercase letters, wrap both sides in UPPER or LOWER: =UPPER(A1)=UPPER(B1). This treats "Smith" and "smith" as identical.

You can combine both: =TRIM(UPPER(A1))=TRIM(UPPER(B1)) handles both extra spaces and mixed capitalization. This is especially useful when comparing names or addresses that may have been entered inconsistently.

Comparing numeric columns with rounding differences

When comparing numbers, Excel sometimes shows them as different even though they're nearly identical — often because one column has more decimal places than the other. Use the ROUND function to compare them at the same precision: =ROUND(A1,2)=ROUND(B1,2) compares both numbers rounded to two decimal places.

If you want to find values that are close but not exact, use =ABS(A1-B1)<0.01. This returns TRUE if the difference between the two numbers is less than 0.01, which is useful when comparing measurements or calculations that might have small rounding errors.

For percentage differences, try =ABS(A1-B1)/A1<0.05, which returns TRUE if column B is within 5 percent of column A. Adjust the 0.05 to whatever tolerance you need.

Frequently Asked Questions

Can I compare two columns and delete the duplicate rows?

Yes, but only if you want to keep rows where the columns match and remove rows where they differ. Filter to show only the FALSE values (using the method in the filtering section), select all visible rows, right-click, and choose Delete Row. Then clear the filter to see the remaining data. Be careful — this permanently removes rows, so save a backup first.

What if I want to compare columns in two different Excel files?

Open both files, then arrange them side by side using the View tab's Arrange All button. Create your comparison formula in a third column in one of the files, referencing the other file by name — for example, =[OtherFile.xlsx]Sheet1!A1=B1. The formula works as long as both files remain open. If you close the other file, the formula breaks.

How do I compare columns and show which one has the larger value?

Use =IF(A1>B1,"A is larger",IF(B1>A1,"B is larger","Equal")). This returns a text label showing which column has the bigger number, or "Equal" if they're the same. Adjust the labels to match whatever you're tracking.

Can I compare columns that contain formulas instead of static values?

Yes — the comparison works the same way whether the cells contain typed values or formulas. Excel compares the results of the formulas, not the formulas themselves. If you need to compare the actual formula text instead, that requires a different approach using the FORMULATEXT function, which is available only in newer versions of Excel.