Calculate percent change between two values

Why it's built this way

• →The core of the formula is (new minus old) divided by old — the change expressed as a fraction of where it started, which is what "percent change" means; format the cell as a percentage rather than multiplying by 100 so negative changes still read correctly.
• →The IF check runs before the division, not after — testing old_value for zero and returning an empty string short-circuits the formula before it ever attempts to divide by zero.

The fine print

• →Without the IF guard, a zero (or blank, which Excel treats as zero here) old_value produces #DIV/0! — the guard exists specifically for that case.
• →The result is a raw decimal (0.15, -0.4); it needs percentage number formatting to display as 15% or -40% — formatting the cell as text would just show the decimal.
• →A negative old_value flips the sign in a way that reads confusingly (going from -50 to 50 computes as -200%) — worth a second look wherever the "old" figure can itself be negative.

Part by part

=IF({{old_value}}=0, "",

The guard. Percent change from zero is undefined, and without this the formula returns #DIV/0! in exactly the case a report most wants to show something.

({{new_value}}-{{old_value}})

The absolute change. Parentheses are load-bearing — without them the division binds first and the result is meaningless.

/{{old_value}}

Divides by the ORIGINAL value, not the new one. This is the direction that makes the result a change relative to where you started.

Common mistakes

• =(C2-B2)/C2

  Dividing by the new value gives a different number that is easy to mistake for the right one. Going from 100 to 150 is a 50% increase, and this returns 33%.

  =(C2-B2)/B2
• =C2-B2/B2

  Without parentheses, division happens before subtraction, so this computes C2 minus 1. The result is a plausible-looking number with no relationship to percent change.

• =IFERROR((C2-B2)/B2, 0)

  Turns a division by zero into a zero, which asserts no change when there was no baseline at all. Blank is the honest output, which is why this recipe tests for zero explicitly rather than catching the error.

Questions