It’s 3:12 PM. You’re reconciling vendor invoices against your PO log. Sarah Chen from Acme Corp sent invoice #INV-8821 with $14,750.00 — but cell D7 says $14,750 and E7 says 14750. Are they the same? You’ve stared at them for 90 seconds. Your cursor hovers over F7. You type =D7=E7. It returns FALSE. You sigh. You just lost 3 minutes.
The Problem
Excel treats 14750, $14,750.00, and '14750 as completely different values — even if they represent the same number. Text formatting, leading apostrophes, extra spaces, or invisible characters break direct comparisons. Worse: Excel hides these differences until you force it to show them.
Here’s what your raw data actually looks like — not what it appears to be:
| A1 (PO Amount) | B1 (Invoice Amount) | =A1=B1? | What’s Really Stored |
|---|---|---|---|
| 14750 | $14,750.00 | ❌ FALSE | 14750 (number) |
| '14750 | 14750 | ❌ FALSE | '14750 (text) |
| 14750 | 14750 | ❌ FALSE | " 14750 " (text w/ spaces) |
| 14750.00 | 14750 | ✅ TRUE | 14750.00 & 14750 (both numbers) |
| 2024-03-15 | "2024-03-15" | ❌ FALSE | 45366 vs "2024-03-15" (date vs text) |
| Apple Inc. | apple inc. | ❌ FALSE | "Apple Inc." ≠ "apple inc." (case-sensitive) |
The Solution
Do this — not what you’ve been doing.
- Type
=EXACT(A1,B1)in C1. This checks exact character-by-character match — case-sensitive, no trimming, no coercion. Returns TRUE only if identical. - For numeric equivalence (ignore formatting), use
=A1*1=B1*1. Multiplying by 1 forces text numbers into real numbers. Works on'14750,"14750",14750, even$14,750.00— all become 14750. - To ignore case and spaces:
=TRIM(UPPER(A1))=TRIM(UPPER(B1)). Converts both to uppercase, strips leading/trailing spaces, then compares. - Press Ctrl+Enter after typing — don’t hit Enter alone. That keeps your cursor in the formula bar so you can edit without losing selection.
Now your comparison column looks clean:
| A1 | B1 | C1 Formula | Result |
|---|---|---|---|
| 14750 | $14,750.00 | =A1*1=B1*1 | ✅ TRUE |
| '14750 | 14750 | =A1*1=B1*1 | ✅ TRUE |
| 14750 | 14750 | =TRIM(UPPER(A1))=TRIM(UPPER(B1)) | ✅ TRUE |
| Apple Inc. | apple inc. | =TRIM(UPPER(A1))=TRIM(UPPER(B1)) | ✅ TRUE |
| 2024-03-15 | "2024-03-15" | =A1*1=B1*1 | ✅ TRUE |
Going Further
You’ll need more than simple equality soon. Here’s what to reach for next:
- Compare entire ranges: Use
=AND(A1:A10=B1:B10)— but enter it with Ctrl+Shift+Enter (not Enter) to make it an array formula. In Excel 365, just press Enter. - Highlight mismatches visually: Select A1:B10 → Home tab → Conditional Formatting → New Rule → “Use a formula…” → enter
=A1<>B1→ set red fill. - Detect hidden characters: Type
=LEN(A1)and=LEN(B1). If lengths differ but values look identical, there’s a space, non-breaking space (Alt+0160), or tab hiding in one cell. - Compare across sheets:
=Sheet1!A1=Sheet2!A1works — but if either sheet name has a space, wrap it:='Q3 Data'!A1='Q3 Backup'!A1.
Surprising tip: =A1-B1=0 is faster than =A1=B1 for numbers — especially with large datasets. Excel optimizes arithmetic comparisons better than logical ones.
When NOT to Use This
These comparisons fail silently — and cost time later.
- Don’t use
=A1=B1on dates stored as text."2024-03-15"and45366(Excel’s serial number for that date) will never equal — even if they display identically. - Don’t compare currency-formatted cells with
EXACT(). It sees$14,750.00as text including dollar signs and commas — so$14,750.00≠14750even if both are numbers. - Avoid
TRIM()on cells with intentional internal spaces."New York"becomes"NewYork"if you double-trim or mishandle nested functions. - Never use
=A1*1=B1*1on text that isn’t numeric."abc"*1returns#VALUE!. Wrap in IFERROR:=IFERROR(A1*1, A1)=IFERROR(B1*1, B1).
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Edit active cell formula | F2 | Faster than double-clicking — puts cursor inside cell content |
| Force recalculation | F9 | Essential when testing volatile formulas like NOW() or RAND() |
| Open Go To dialog | F5 | Type A1:B10 then Enter — selects range instantly |
| Toggle formula view | Ctrl+` (backtick) | See all formulas at once — reveals hidden text or errors instantly |
| Insert function dialog | Shift+F3 | Start typing “EXACT” — autocomplete kicks in before you finish |
