The fastest way to highlight differences between two columns in Excel is Row Differences: select both columns, press Ctrl + \ (backslash), and Excel selects every cell in the second column that doesn’t match the first on the same row. Click a fill color to highlight them. For highlighting that updates automatically, use conditional formatting with the formula =$A2<>$B2. If the lists are in different orders and you want values in one column that are missing from the other, use a COUNTIF rule instead. All three are below.
First, decide what “different” means
| Your data | Use |
|---|---|
| Two columns that should match row by row (old vs new price) | Row Differences or the A2<>B2 rule |
| Two lists in any order (this month’s vs last month’s customers) | The COUNTIF rule |
| Case matters (ABC vs abc) | The EXACT rule |
Method 1: Row Differences (Ctrl + \)
- Select both columns, for example A2:B100. Start the selection in the column you want to compare against (A).
- Press Ctrl + \. Or go to Home > Find & Select > Go To Special, choose Row differences, and click OK.
- With the mismatched cells selected, click Home > Fill Color and pick a color.
This is a one-time highlight. If the data changes, run it again.
Method 2: Conditional formatting (row by row)
- Select A2:B100.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=$A2<>$B2. - Click Format, choose a fill color, and click OK twice.
Both cells in each mismatched row turn colored and update as you edit. The $ before the column letters keeps the comparison on columns A and B while the row changes. To highlight matches instead, use =$A2=$B2.
Method 3: Values missing from the other list
- Select A2:A100.
- Create a new formula rule as above with
=COUNTIF($B$2:$B$100,$A2)=0. - Pick a color and click OK.
- Repeat for B2:B100 with
=COUNTIF($A$2:$A$100,$B2)=0.
Now any value that doesn’t appear anywhere in the other column is highlighted, regardless of order. For a quicker version, select both columns and use Conditional Formatting > Highlight Cells Rules > Duplicate Values, then change Duplicate to Unique, though that also flags values repeated within one list.
Method 4: Case-sensitive comparison
Excel’s normal comparisons ignore case. For an exact match, use the conditional formatting formula =NOT(EXACT($A2,$B2)).
Add a Match/Different column
If you’d rather filter than color, add a helper column: =IF(A2=B2,"Match","Different"). Then filter that column for Different. For lists in any order: =IF(COUNTIF(B:B,A2),"Found","Missing").
Common reasons for false differences
- Extra spaces: “Smith” and “Smith ” don’t match. Clean with
=TRIM(A2). - Numbers stored as text: 100 and “100” are different. Convert with Convert to Number.
- Hidden decimals: 10.004 shows as 10.00. Compare rounded values with
=ROUND($A2,2)<>ROUND($B2,2). - Different date types: one column has real dates and the other has text.
Compare whole sheets
To compare two sheets or workbooks rather than two columns, see how to compare two sheets in Excel for matches and how to match data from two worksheets. For financial lists, see how to do a reconciliation in Excel.
Worked example: price list changes
Say column A has last month’s prices and column B has this month’s, one product per row. Select A2:B200 and press Ctrl + \ to see which prices changed, then fill them yellow. For a running check, add the conditional formatting rule from Method 2 instead, so new changes light up as soon as someone types them. In column C, =B2-A2 shows how much each price moved, and =COUNTIF(C2:C200,"<>0") counts how many changed in total.
Frequently asked questions
Does this work in Excel for the web and on a Mac?
Conditional formatting rules work in both. On a Mac, Row Differences is under Edit > Find > Go To > Special. Excel for the web doesn’t have Go To Special, so use the conditional formatting method there.
Can I compare more than two columns?
Yes. To flag rows where column C differs from A or B, use =OR($A2<>$B2,$A2<>$C2) as the rule.
Remove the highlighting
For conditional formatting, select the cells and choose Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. For Row Differences fills, select the cells and choose Fill Color > No Fill.
Summary
- Quick: select both columns, Ctrl + \, add a fill color.
- Live: conditional formatting with
=$A2<>$B2. - Any order:
=COUNTIF($B$2:$B$100,$A2)=0. - Case-sensitive:
=NOT(EXACT($A2,$B2)).
Related: How to Make a Cell Turn a Color with a Formula in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.