Every business report includes some form of “up 12% from last quarter” or “down 3% year-over-year.” Calculating percentage change sounds simple, but getting it right — especially with negative numbers, zero baselines, and compounding — trips up even experienced Excel users. Here’s how to do it correctly for business reporting.
Basic Percentage Change
The formula
=(New Value - Old Value) / Old Value
Or in Excel: =(B2-A2)/A2 — Format the result cell as Percentage.
The Scenario
Quarterly revenue report showing current vs. prior quarter with percentage change:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Q1 | Q2 | % Change |
| 2 | Sales | $142,000 | $158,000 | +11.3% |
| 3 | Marketing | $68,000 | $61,000 | -10.3% |
| 4 | Operations | $95,000 | $95,000 | 0.0% |
| 5 | Support | $0 | $12,000 | N/A |
The formula for D2:
=(C2-B2)/B2
($158,000 – $142,000) / $142,000 = 11.3%. Format as percentage with one decimal place.
Handling Division by Zero
Row 5 has a Q1 value of $0. Dividing by zero gives a #DIV/0! error. Wrap the formula to handle this:
=IF(B2=0, "N/A", (C2-B2)/B2)
If the baseline is zero, show “N/A” instead of an error. You can also use “New” to indicate this is a new line item with no prior period.
Negative baselines
If Q1 was -$5,000 (a loss) and Q2 is $10,000 (a gain), the formula gives ($10,000 – (-$5,000)) / (-$5,000) = -300%. That’s mathematically correct but misleading in a report. For departments that can swing between profit and loss, consider showing the absolute dollar change instead of a percentage.
Year-Over-Year Growth
Comparing the same month or quarter across years:
=(This Year Value - Last Year Value) / Last Year Value
Same formula, just make sure you’re comparing the same period. Q2 2025 vs Q2 2024, not Q2 2025 vs Q1 2025.
Month-Over-Month with Arrows
Add visual indicators using IF and Unicode characters:
=IF(B2=0, "N/A", IF((C2-B2)/B2>0, "▲ "&TEXT((C2-B2)/B2,"0.0%"), IF((C2-B2)/B2<0, "▼ "&TEXT((C2-B2)/B2,"0.0%"), "— 0.0%")))
Shows ▲ 11.3% for increases, ▼ -10.3% for decreases, and — 0.0% for flat. Combine with conditional formatting to color green/red.
Compound Annual Growth Rate (CAGR)
For measuring average annual growth over multiple years:
=(Ending Value / Beginning Value)^(1/Number of Years) - 1
Example: Revenue grew from $500K to $820K over 3 years. CAGR = ($820K/$500K)^(1/3) - 1 = 17.9% per year.
In Excel
=(C2/B2)^(1/D2)-1
Where B2 is the starting value, C2 is the ending value, and D2 is the number of years.
When to use CAGR vs simple % change
Simple percentage change shows what happened between two points. CAGR shows the smoothed annual rate that would get you from point A to point B. Use CAGR when presenting multi-year growth to investors or leadership — it's the standard metric.
When Formulas Aren't Enough
Percentage change calculations are simple individually, but business reporting often requires more:
- Automated period detection — the report should know which months/quarters to compare without manual selection
- Multi-level rollups — department totals, division totals, and company totals all calculated and formatted
- Charts with variance bars — visual representation of positive/negative changes with color coding
- Prior year comparison — current period, prior period, and same period last year side by side
- Board-ready formatting — branded headers, proper number formatting, conditional coloring, and print layout
A custom VBA report handles all period logic, multi-level calculations, chart generation, and professional formatting — rebuilt from fresh data with one click.
"Most likely one of the best freelancers I've had the opportunity of working with. I would highly recommend David for any complex VBA coding."
VBA Development Client · Upwork
Need Professional Financial Reports That Build Themselves?
Tell us about your reporting requirements and we'll automate the entire process.
Start a Project