Most Excel tutorials teach =(B2-A2)/A2 as the universal way to find percentage decrease. They’re dangerously incomplete. That formula returns garbage when either value is negative—like comparing Q1 loss ($−12,500) to Q2 profit ($8,300). You’ll get −166%, which isn’t a meaningful decrease. Worse: it fails silently. No error. Just wrong logic.
Quick Answer
Use =IF(A2=0,"N/A",(B2-A2)/ABS(A2)) for mathematically sound percentage change—then apply conditional formatting to highlight decreases (red) and increases (green). This handles negatives, zeros, and text gracefully.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Basic Formula | Enter =(B2-A2)/A2 in C2, format as % |
Simple positive-to-positive comparisons (e.g., sales drop from $25K → $18K) | Fails on zero or negative starting values; mislabels direction when signs differ |
| ABS-Based Formula | Use =(B2-A2)/ABS(A2), wrap with IF for zero handling |
Real-world financials with losses, credits, or mixed-sign metrics | Still ambiguous if both values are negative (e.g., −$5K → −$3K looks like +40% decrease) |
| SIGN-Aware Formula | Apply =IF(A2=0,"N/A",(B2-A2)/A2*SIGN(A2)) to preserve directional meaning |
Accounting teams reconciling P&L line items across periods | Requires understanding of sign logic; overkill for basic dashboards |
| Custom Number Format + Formula | Enter =(B2-A2)/ABS(A2), then format cell with 0.0%_);[Red](0.0%) |
Executive summaries where visual cues matter more than precision | Formatting hides actual value—can’t sort or filter by true % change |
Method 1 Deep Dive
Let’s walk through the ABS-based approach—the one we recommend for 90% of cases. Open your workbook. In column A, enter last month’s revenue figures. In column B, enter this month’s.
| Company | Jan 2024 (A) | Feb 2024 (B) | % Change (C) |
|---|---|---|---|
| Acme Corp | $45,200 | $39,800 | =IF(A2=0,"N/A",(B2-A2)/ABS(A2)) |
| Nexus Labs | −$12,500 | $8,300 | =IF(A3=0,"N/A",(B3-A3)/ABS(A3)) |
| Vista Solutions | $0 | $14,700 | N/A |
| Orion Dynamics | −$7,200 | −$11,400 | =IF(A5=0,"N/A",(B5-A5)/ABS(A5)) |
| Terra Systems | $63,900 | $63,900 | 0.0% |
Click C2. Type =IF(A2=0,"N/A",(B2-A2)/ABS(A2)). Press Ctrl+Enter to keep focus in C2, then drag the fill handle down to C6. The beauty of this approach is that it treats magnitude consistently—whether you’re measuring profit erosion or cost reduction. Notice how Nexus Labs shows −166.4%: that’s correct. A shift from −$12,500 (loss) to +$8,300 (profit) *is* a 166.4% improvement in position relative to the original base magnitude.
Method 2 Deep Dive
The SIGN-aware method adds nuance for accountants who need directional fidelity—not just size. It answers: “Did this line item get worse or better *in context*?”
Take Orion Dynamics: from −$7,200 to −$11,400. The ABS formula says +58.3% (since |−11,400 − (−7,200)| / |−7,200| = 0.583). But in reality, their loss *grew*. So we want −58.3%. Enter SIGN:
In C5, replace the formula with =IF(A5=0,"N/A",(B5-A5)/A5*SIGN(A5)). The SIGN(A5) multiplies the result by −1 when A5 is negative—flipping the sign to reflect worsening performance. Now C5 reads −58.3%, matching intuition.
Keyboard shortcut tip: To toggle between showing formulas and values, press Alt+` (grave accent, left of 1). Use it after entering your formulas to verify logic before applying % formatting.
What makes this elegant is how it preserves business semantics. A negative percentage always means deterioration—even if the raw numbers crossed zero. That matters in variance analysis reports where stakeholders scan for red flags.
Cheat Sheet
| Task | Formula | Shortcut / Tip |
|---|---|---|
| Basic % decrease (safe) | =IF(A2=0,"N/A",(B2-A2)/ABS(A2)) |
Drag fill handle (Ctrl+D) to copy down |
| Add % format instantly | Select range → Ctrl+Shift+% | Formats to nearest whole %; add decimals via Home → Increase Decimal |
| Highlight decreases only | Home → Conditional Formatting → Highlight Cell Rules → Less Than → 0 | Set red fill + dark red text for instant visual triage |
| Handle text/blank cells | =IF(OR(A2="",B2=""),"",IF(A2=0,"N/A",(B2-A2)/ABS(A2))) |
Prevents #VALUE! errors from empty imports |
