How to calculate percentage change in Excel

Percentage change is the most common calculation in any spreadsheet — and the one people most often get subtly wrong. This guide gives you the exact increase/decrease formula, how to format the result, the negative-numbers gotcha that flips your sign, and the divide-by-zero guard. Worked example throughout.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.What percentage change is

Percentage change measures how much a value has grown or shrunk relative to where it started, expressed as a percentage of the starting value. It is the engine under almost every business metric — year-over-year growth, month-on-month movement, price changes, conversion-rate shifts. Get this one formula right and most of the others fall into place.

The key idea is that the change is always measured against the old value, not the new one. Going from 80 to 100 is a 25% increase (the 20-point gain is 25% of the original 80), but going from 100 back to 80 is a 20% decrease (the same 20-point gap, now measured against 100). That asymmetry surprises people, and it is the source of most percentage-change confusion.

2.Step 1 — Lay out old and new

Open Excel. You need two numbers: the old (starting) value and the new (ending) value. Put them in two cells. For a single comparison, put old in A2 and new in B2. For a time series, list the values down a column and compare each row with the one above it.

3.Step 2 — The formula

The one to memorise:

In C2:

=(B2-A2)/A2

For the first row, =(100-80)/80 returns 0.25 — a 25% increase. For the second row, =(150-200)/200 returns −0.25 — a 25% decrease. The sign does the work for you: positive means up, negative means down, and you never need a separate “increase or decrease?” column. The denominator is always the old value — dividing by the new one is the single most common mistake.

4.Step 3 — Format as a percentage

Excel stores 25% internally as 0.25, so right after typing the formula you will see 0.25, not 25%. Fix the display, not the number: select the cell and press Ctrl+Shift+%, or click the % button on the Home tab. The underlying value stays 0.25 — the percentage format only changes how it is shown, which matters if you reference the cell in further calculations.

If you genuinely want the number 25 (for a label or a chart axis), multiply by 100 explicitly: =(B2-A2)/A2*100 — but then leave the cell formatted as a plain number, or you will get 2500%. Pick one approach and be consistent.

5.Step 4 — Zeros and negatives

Two edge cases break the formula, and both are worth guarding from the start.

Zero starting value. If A2 is zero, the division is undefined and Excel returns #DIV/0!. Wrap it:

=IF(A2=0, "n/a", (B2-A2)/A2)

Percentage change from zero is genuinely infinite — any movement away from nothing is an infinite proportional jump — so “n/a” plus the absolute change is the honest answer.

Negative starting value. This is the subtle one. When the old value is negative — say a −50 loss turning into a +30 profit — the formula (30-(-50))/(-50) = 80/-50 = −160%. A genuine improvement reads as a negative percentage, because dividing by a negative base flips the sign. The maths is correct but the interpretation is misleading. Whenever a value can cross zero, report the absolute change (=B2-A2) instead of, or alongside, the percentage.

Worked example

A monthly sales figure (illustrative numbers), with each month compared to the one before:

The formula in the Feb row is =(1380-1200)/1200 = +15%. Note the asymmetry in action: Feb rose 15% to 1,380, then Mar fell 10% — but 1,380 minus 10% is 1,242, not back to 1,200. A 15% rise followed by a 10% fall does not cancel out, because each percentage is measured against a different base. This is why you should never add or average raw percentage changes to get a total — for multi-period growth, use a compound annual growth rate (CAGR) instead, and to track a cumulative figure use a running total in Excel.

Common mistakes

1. Dividing by the new value. The base is always the old value. (B2-A2)/B2 gives a different, wrong percentage.

2. Forgetting the percentage format. A result of 0.25 sitting in a number-formatted cell looks like a tiny decimal, not 25%. Either format as a percentage or multiply by 100 — never both.

3. Adding or averaging percentage changes. +15% then −10% is not +5% overall. Percentages compound; they don’t add. Chain the actual values instead.

4. Percentage change from a negative or zero base. Both produce nonsense. Guard zeros with IF, and for any series that can go negative, report the absolute change.

The 30-second shortcut

Percentage change is everywhere in reporting, and recalculating it across dozens of rows and metrics every period is tedious. DataHub Pro computes period-over-period change — correctly handling zeros and negatives — across every column you upload, and shows the trend alongside it. No formula to drag down.

Frequently asked questions