How to Calculate Percent Change in Excel (Negatives Safe)

Need a copy‑pasteable Excel percent difference formula that won’t break when values are negative or cross zero? Here’s the definitive answer you can use today.

Exact formula (Old in C5, New in D5):

=(D5-C5)/ABS(C5)

Zero‑aware (numeric) variant:

=IF(C5=0, IF(D5=0, 0, NA()), (D5-C5)/ABS(C5))

Zero‑aware (text labels) variant:

=IF(C5=0, IF(D5=0, "No change", "New value only"), (D5-C5)/ABS(C5))

Symmetric “percentage difference” (directionless):

=ABS(D5-C5)/ABS((C5+D5)/2)

Tip: After entering a formula, format the result cell as Percentage and set decimals to taste (Home > Number).

Key takeaways

Use =(New−Old)/ABS(Old) to keep the sign intuitive when Old is negative; magnitude scales by the size of the Old value.
When Old = 0, percent change is undefined. Return NA() for charts or a clear label like "New value only"—but don’t divide by zero.
Crossing zero flips the business meaning: loss→profit yields a large positive %, profit→loss yields a large negative %.
For directionless comparisons, use the midpoint method (symmetric percentage difference) instead of percent change.
Always format results as Percentage; for rates, distinguish percent change from percentage points.

How the Excel percent difference formula behaves with negatives and crossing zero

Sign logic (with =(New−Old)/ABS(Old)):
- Positive result → increase or improvement relative to the Old value.
- Negative result → decrease or deterioration relative to the Old value.
Crossing zero:
- Old negative, New positive (e.g., −10 → 5) → large positive % (improvement: loss to profit).
- Old positive, New negative (e.g., 10 → −5) → large negative % (deterioration: profit to loss).
- Both negative but less negative (e.g., −20 → −10) → positive % (loss narrowed).
  Worked examples of percent change with negative numbers are widely documented in Excel tutorials.
Old = 0:
Percent change is undefined mathematically; Excel would show #DIV/0! if you divide by zero. Decide a clear policy: numeric NA() (chart‑friendly) or a text label like "New value only"—but be consistent across the report.

Quick, replicable business examples

Assume Old in C5 and New in D5. Enter the formula in E5 and format as Percentage.

Revenue grew (100 → 150): =(D5-C5)/ABS(C5) → 50%
Standard percent change (New−Old)/Old or New/Old−1 produces the same result when Old > 0, as shown in the Corporate Finance Institute’s guide to calculating percentages in Excel.
Loss narrowed (−20 → −10): =(D5-C5)/ABS(C5) → 50%
This reads as improvement because the loss got smaller.
Profit turned into loss (10 → −5): =(D5-C5)/ABS(C5) → −150%
A deterioration from profit to loss.
Loss turned into profit (−10 → 5): =(D5-C5)/ABS(C5) → 150%
A material improvement from loss to gain.
No change (100 → 100): =(D5-C5)/ABS(C5) → 0%
Old is zero (0 → 100):
Use the numeric policy: =IF(C5=0, IF(D5=0, 0, NA()), (D5-C5)/ABS(C5)) → NA()
Charts skip NA(), which is often preferable to forcing a number when the baseline is zero.

Why this works: ABS(C5) makes the denominator positive, so the sign is driven only by the numerator (New−Old). That keeps interpretations consistent when Old is negative.

For background on the standard percent change formula and visuals, see the explanations by the Corporate Finance Institute and Zebra BI.

Robust zero and error‑handling patterns

Choose one approach based on how you present results.

Catch‑all with NA() (numeric only):
```
=IFERROR((D5-C5)/ABS(C5), NA())
```
Any error—including division by zero—returns #N/A so charts omit the point.
Explicit zero policy (numeric only):
```
=IF(C5=0, IF(D5=0, 0, NA()), (D5-C5)/ABS(C5))
```
Returns 0% when both values are zero; otherwise NA() if Old is zero.
Friendly labels (text + numeric mix):
```
=IF(C5=0, IF(D5=0, "No change", "New value only"), (D5-C5)/ABS(C5))
```
Note: If you output text in some rows and numbers in others, don’t apply Percentage formatting to the entire column, or your labels may look odd. Consider separate columns for numeric calculations and human‑readable notes.

Authoritative references discuss these patterns and trade‑offs: see Ablebits’ overview of percent change variants and Microsoft Support’s guidance on IFERROR and handling error values.

Symmetric percentage difference — when you don’t want direction

Sometimes you need a directionless comparison (A vs. B regardless of which is “old” or “new”). Use the midpoint method, also called symmetric percentage difference:

=ABS(D5-C5)/ABS((C5+D5)/2)

What it tells you: how different the two values are, relative to their average magnitude.
When to use it: peer comparisons, quality checks, or any place where A→B should equal B→A in magnitude.
Guardrail: if C5+D5=0, the midpoint is zero; wrap with IF((C5+D5)=0, NA(), ABS(D5-C5)/ABS((C5+D5)/2)) to avoid an error.

A concise walkthrough of percentage difference (symmetric) and when it’s useful is available from Acuity Training.

Formatting and setup tips in Excel

Apply Percentage format: Home > Number > Percent Style, then set decimals. Microsoft Support explains percent formatting and custom formats.
Optional +/− signs: use a Custom format like +0.00%;-0.00%;0.00% so increases show a leading plus. This makes the Excel percent difference formula easier to scan in dashboards.
Fill down safely: place Old in one column and New in the next, enter the formula once, and copy it down the column.
Keep data numeric: convert text numbers to values before calculating to avoid #VALUE! errors.

Troubleshooting and common pitfalls

#DIV/0!: You divided by zero (Old = 0). Use IF or IFERROR to return NA() or a clear label instead of an error.
#VALUE!: One or both inputs are text. Clean or convert to numbers before applying the formula.
Confusing negative transitions: Using (New−Old)/Old alone can feel counter‑intuitive when Old is negative; (New−Old)/ABS(Old) tends to match business intuition better in those cases, as shown in tutorials covering negatives.
Percent change vs. percentage points: For rates like churn moving from 5% to 6%, percent change is (6%−5%)/5% = 20%, while percentage points is +1 pp. Financial modeling references emphasize this distinction so dashboards don’t overstate changes.

Testing checklist (paste‑ready scenarios)

Use a small table with Old in C, New in D, and your chosen Excel percent difference formula in E. Expected results after formatting as %:

100 → 150 → 50%
−10 → 5 → 150%
10 → −5 → −150%
−20 → −10 → 50%
100 → 100 → 0%
0 → 100 → NA() or a clear label per your zero policy

If all cases match, you’re ready to apply the formula across your KPI sheet.

References and further reading

Clear walkthrough of percent change basics in Excel by the Corporate Finance Institute: Calculate percentages and percent change in Excel (tutorial).
Worked examples for negative values: ExcelDemy on percent change with negative numbers.
Practical variants and IF/IFERROR patterns: Ablebits on calculating percent change.
Percentage formatting and custom formats: Microsoft Support — format numbers as percentages.

Next steps

Prefer to avoid manual formulas across many files? You can upload spreadsheets and compute percent changes programmatically with an AI data agent. As one option, hiData supports Excel/CSV analysis, generates charts, and can assemble presentation‑ready outputs from plain‑English instructions.