Percentage Change Formula in Excel

The percentage change formula in Excel is =(new-old)/old, formatted as a percentage — it's the new value minus the old value, divided by the old value. The two things that break this formula in practice are a zero baseline, which throws #DIV/0!, and a negative baseline, which produces a mathematically correct but easily misread result.

What is the percentage change formula?

Subtract the old value from the new value, divide by the old value, then format the result as a percentage — Excel's Percentage format multiplies the decimal by 100 and adds the % sign for display, it doesn't change the underlying number.

=(new_value - old_value) / old_value

Example: old = 80, new = 100
=(100-80)/80 = 0.25 → formatted as 25%

Example: old = 100, new = 80
=(80-100)/100 = -0.20 → formatted as -20%

Select the cell and press Ctrl+Shift+% to apply Percentage format quickly, or use the % button on the Home tab's Number group.

How do I handle a zero baseline without a #DIV/0! error?

Dividing by zero is undefined, so a formula with old=0 always throws #DIV/0! — wrap it in IF or IFERROR to show something meaningful instead of an error cell.

Explicit check:
=IF(B2=0, "N/A", (C2-B2)/B2)

Or, if you'd rather show a number than text:
=IF(B2=0, 0, (C2-B2)/B2)

Shorter with IFERROR (catches the #DIV/0! after the fact):
=IFERROR((C2-B2)/B2, "N/A")

Going from 0 to any positive number is technically an infinite percentage increase — there is no mathematically correct finite answer, so "N/A" or a blank is more honest than forcing a number.

How do I handle a negative baseline correctly?

The plain formula still computes without erroring on a negative baseline, but the sign of the result can be counterintuitive — dividing by a negative number flips the direction of the percentage relative to what most readers expect "improvement" to mean.

Example: a loss shrinking from -100 to -50 (an improvement)
Plain formula: =(-50 - -100) / -100 = 50 / -100 = -50%
   Reads as a 50% DECREASE — misleading, since the loss
   actually got smaller (better), not worse.

Corrected with ABS() on the denominator:
=(-50 - -100) / ABS(-100) = 50 / 100 = +50%
   Reads as a 50% INCREASE — correctly signals improvement
   when moving from a larger loss toward a smaller one.

Use ABS(old_value) in the denominator whenever the baseline can be negative — profit/loss figures, temperature changes, or any metric where negative values are meaningful, not just an error state.

How do I format the result as a percentage correctly?

Apply Percentage number formatting to the cell rather than multiplying the formula by 100 yourself — the two approaches look identical on screen but behave differently if the value feeds another formula.

Correct: =(C2-B2)/B2, then format cell as Percentage
   Underlying value stays 0.25, displays as "25%"
   Safe to reference in other formulas as a true fraction

Common mistake: =(C2-B2)/B2*100, formatted as a plain number
   Underlying value is 25, displays as "25"
   Breaks if you later apply Percentage format on top
   (shows "2500%") or reference it expecting a fraction

Keeping the underlying value as a decimal fraction (not multiplied by 100) is what makes it composable — you can sum, average, or conditionally format a percentage column without unit-conversion bugs creeping in later.

How do I show the change with an up/down arrow or color?

Combine the percentage change formula with a custom number format or conditional formatting rule to add visual direction without a separate helper column.

Custom number format (Format Cells → Custom):
+0.0%;-0.0%;0.0%
' Shows a leading + on positive changes, - on negative

Conditional formatting formula (select the percent column):
=$C2>0   → green fill (increase)
=$C2<0   → red fill (decrease)

See custom conditional formatting formulas for the $ anchoring rules behind rules like these, and running totals when you need cumulative change over a whole series rather than one period-over-period figure.

What are common mistakes with percentage change formulas?

• Dividing by the new value instead of the old: (new-old)/new gives a different, incorrect percentage — the denominator must always be the baseline (old) value
• Not guarding a zero baseline: leaves #DIV/0! errors scattered through a report
• Ignoring sign flips with a negative baseline: reading a negative-baseline percentage the same way as a positive-baseline one can invert the actual message
• Multiplying by 100 in the formula, then also applying Percentage format: produces a value 100x too large