Why does your subtraction formula return #VALUE! when you copy it down? Why does =A2-B2 work for the first row but break at row 17? Why does Excel treat "1,250" as text even though it looks like a number?
Quick Answer
To apply a difference formula in Excel, type =A2-B2 in the result cell, press Enter, then drag the fill handle down. But that only works if both columns contain real numbers — not text-formatted numbers, blanks disguised as zeros, or cells with trailing spaces. That’s where most people get tripped up.
All the Methods
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Basic subtraction (=A2-B2) | 0.2 sec | High (if data is clean) | Easy |
| SUBTRACT with VALUE() wrapper | 1.4 sec | Very High | Medium |
| Array formula with IFERROR + VALUE | 2.7 sec | Highest | Hard |
| Power Query Merge & Subtract | 4.1 sec (first run) | Very High | Medium-Hard |
Method 1 Deep Dive
Start with raw sales data. In column A (A1:A10), list names: Sarah Chen, Diego Morales, Yuki Tanaka, Maya Patel, Jamal Wright. Column B (B1:B10) has Q1 revenue: $45,200, $38,900, $52,100, $29,450, $61,300. Column C (C1:C10) holds Q2: $47,800, $42,100, $49,300, $31,600, $58,700.
Don’t just type =C2-B2 in D2 and drag. First, check formatting. Select B2:C6 → right-click → Format Cells → Number tab → confirm it says 'Number', not 'Text'. If it says 'Text', Excel treats $45,200 as a string — and =C2-B2 returns #VALUE!.
Fix it: Select B2:C6 → press Alt+H+F+M (Home → Format → Convert to Number). Then enter =C2-B2 in D2. Drag down to D6. You’ll see differences: $2,600, $3,200, -$2,800, $2,150, -$2,600.
Here’s the counterintuitive part: Even if numbers look right, hidden characters can break subtraction. Try this test: In E2, type =ISTEXT(B2). If it returns TRUE, that cell is text — no amount of formatting will fix it without VALUE().
Method 2 Deep Dive
Now imagine your source data comes from a CRM export — and column B contains entries like "45200", "38,900", and "$52100" all mixed together. Basic =C2-B2 fails every time.
Use VALUE() to force conversion. In D2, enter:=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",","")) - VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",",""))
This strips $ and commas, then converts to number. Works on B2="$45,200" and C2="47800". Copy down to D6. Result: same numeric differences, zero errors.
But here’s what most miss: VALUE() returns #VALUE! if the cell is truly blank (not ""). So wrap it: =IF(OR(B2="",C2=""),"",VALUE(...)-VALUE(...)). Better yet — use IFERROR: =IFERROR(VALUE(SUBSTITUTE(B2,"$",""))-VALUE(SUBSTITUTE(C2,"$","")),"").
Test it: Put "" in B7 and "$47,800" in C7. Without IFERROR, D7 shows #VALUE!. With it, D7 stays blank — clean and safe for reports.
Cheat Sheet
| Action | Formula / Shortcut | Notes |
|---|---|---|
| Convert text numbers to real numbers | Select range → Alt+H+F+M | Only works if text looks like numbers (no letters) |
| Safe difference with cleanup | =IFERROR(VALUE(SUBSTITUTE(B2,"$",""))-VALUE(SUBSTITUTE(C2,"$","")),"") | Paste into D2; drag down |
| Find hidden text cells | =ISTEXT(B2) | Returns TRUE if cell is text — even if it looks numeric |
| Subtract entire columns (fast) | =B2:B10-C2:C10 (Ctrl+Shift+Enter in older Excel) | In Excel 365/2021: just press Enter — spills automatically |
| Difference as % change | =(C2-B2)/B2 | Format result cell as % — and add IF(B2=0,"N/A",...) to avoid #DIV/0! |
