Yes, you can find differences between two Excel ranges with built-in tools. But if you’re comparing A1:A100 to B1:B100 and only using Conditional Formatting → Highlight Cells Rules, you’re missing 40% of real-world mismatches — especially when numbers are stored as text, or blanks hide as zeros.
The Problem
You get two sales reports: one from Finance (Sheet1), one from CRM (Sheet2). Your manager says “they should match.” You line them up side-by-side and scan. You spot three obvious mismatches — then send the email saying “all good except $3K in Q3.” Two days later, Finance finds a $17,850 discrepancy in the "Acme Corp" row — buried because the CRM entered "acme corp" (lowercase) and Finance used "Acme Corp", and Excel’s default comparison treats those as identical.
Here’s what your raw data actually looks like — and why visual scanning fails:
| Client Name | Amount (Finance) | Amount (CRM) | Visually Same? | Actually Same? |
|---|---|---|---|---|
| Acme Corp | $45,200 | $45,200 | ✓ | ✓ |
| BrightLabs Inc | $12,650 | 12650 | ✓ | ✗ |
| Cedar & Co. | $0 | (blank) | ✓ | ✗ |
| DynaTech Group | $8,900 | $8,900.00 | ✓ | ✓ |
| EcoSolutions Ltd | $22,100 | $22,100 | ✓ | ✓ |
| Fusion Dynamics | $37,400 | 37400 | ✓ | ✗ |
| Global Reach PLC | $51,920 | $51,920 | ✓ | ✓ |
The Solution
Forget side-by-side scrolling. Use this 4-step method — it catches text-vs-number mismatches, blank-vs-zero, and case sensitivity, all in one column:
- In cell C1, enter this formula:
=IF(EXACT(A1,B1),"✓","✗") - Drag down to cover all rows (e.g., C1:C50).
EXACT()is case-sensitive and treats numbers vs. text as different — so"12650"≠12650. - Select C1:C50, press Ctrl+H, type
✗in Find, leave Replace blank, click Find All. Excel shows every mismatch location at once. - Click any result in the Find dialog — Excel jumps straight to that row. No more guessing which row is off.
Here’s what C1:C7 looks like after applying the formula:
| Row | A (Finance) | B (CRM) | C (Result) |
|---|---|---|---|
| 1 | Acme Corp | Acme Corp | ✓ |
| 2 | $12,650 | 12650 | ✗ |
| 3 | $0 | ✗ | |
| 4 | $8,900 | $8,900.00 | ✓ |
| 5 | $22,100 | $22,100 | ✓ |
| 6 | $37,400 | 37400 | ✗ |
| 7 | $51,920 | $51,920 | ✓ |
Going Further
You’ll need variations for real work. Here are four you’ll use weekly:
- To highlight mismatches visually: Select A1:B50 → Alt + H + L → choose New Rule → Use a formula… → enter
=NOT(EXACT($A1,$B1))→ set fill color to light red. - To compare entire columns across sheets: In Sheet3!C1, use
=IF(EXACT(Sheet1!A1,Sheet2!A1),"✓","✗")— works even if Sheet2 has extra rows. - To ignore case but catch number/text mismatches: Replace
EXACT()with=AND(A1=B1, TYPE(A1)=TYPE(B1)).TYPE()returns 1 for numbers, 2 for text — so"12650"(type 2) ≠12650(type 1). - For large datasets (10k+ rows): Skip formulas. Copy both columns into Power Query → Merge Queries as New → choose Full Outer → filter for Null in either column. It’s faster and handles 500k rows without lag.
Surprising tip: If one column has dates formatted as text (e.g., "2024-03-15" vs. actual date serial 45366), EXACT() catches it instantly. Conditional Formatting won’t — it converts both to numbers first and calls them equal.
When NOT to Use This
This method breaks down in three situations — and most people don’t realize until they’ve wasted hours:
- Hidden characters: If CRM exports with trailing spaces (
"Acme Corp ") or non-breaking spaces (Alt+0160),EXACT()sees them — but you won’t. Fix: wrap withTRIM()orSUBSTITUTE(A1,CHAR(160),""). - Numbers with different decimal precision:
$8,900(formatted) vs.8900.000(stored) —EXACT()returnsFALSEeven though mathematically equal. Use=ROUND(A1,2)=ROUND(B1,2)instead. - Entire rows or multiple columns: Don’t try
EXACT(A1:E1,A2:E2)— it fails silently. Use=AND(EXACT(A1,A2),EXACT(B1,B2),EXACT(C1,C2))— or better, concatenate:=EXACT(A1&B1&C1,A2&B2&C2).
If your data includes formulas returning "" (empty string), treat those as blanks — not text. Add IF(A1="",NA(),A1) before comparison to avoid false negatives.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Find dialog | Ctrl + F | Jump to next mismatch after entering ✗ |
| Replace All mismatches with highlight | Alt + H + F + R | Starts Replace dialog directly |
| Apply bold to selected cells | Ctrl + B | Quickly mark rows needing review |
| Toggle formula view | Ctrl + ` | See all =EXACT() results at once — no more clicking each cell |
