To calculate percentage change in Excel, the formula is =(new_value - old_value) / old_value, formatted as a percentage. The part that catches people out is a zero or negative baseline: dividing by zero gives a #DIV/0! error, so guard it with =IF(B2=0, "N/A", (C2-B2)/B2). For growth across several years use CAGR rather than averaging the yearly percentages, which overstates the result.
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.
Example: quarterly revenue comparison
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 2026 vs Q2 2025, not Q2 2026 vs Q1 2026.
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.
Common questions
How do I show percentage change from a negative number?
You generally should not. Percentage change from a negative baseline is mathematically defined but reads backwards, since a loss shrinking looks like a decline. Show the absolute change instead.
Why is my percentage change in the thousands?
The baseline is very close to zero. A move from 0.5 to 500 is a 99,900% increase and is technically correct but useless in a report. Use absolute values when the baseline is near zero.
Can I average yearly growth rates?
No, that overstates growth because it ignores compounding. Use CAGR, which calculates the constant rate that would produce the actual start and end values.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.