Calculating a simple percentage increase in Excel seems simple but, as usual, there’s a traps for the unwary. Here’s a smarter and more consistent way to figure out % change.
First we’ll go over some basics which you’re welcome to skip …
The formula for calculating a percentage change is simple:
(NewValue – OldValue) / OldValue
In other words, find the difference between the two values first then divide by the OldValue.
The Excel formula version can look like this in a simple table.
=([@New]-[@Old])/[@Old]
That gives a fraction (e.g 0.25 0.50 0.75 etc).
The brackets are important to ensure the subtraction is done before division – basic BODMAS/PEDMAS rules.
Here’s how that looks in Excel

All that might seem bleedingly obvious but it’s easy to make a mistake. We did ourselves by not checking a simple % increase formula until a eagle-eyed reader pointed out our ‘bludner’.
There’s a way to stop making such simple mistakes by adding your own little custom function. That not only ensures consistency (fewer typing errors) but also lets you add little enhancements to the basic formula to covering special situations.
Show as Percentage
Change the % increase cell from General or Number to Percentage. It’s the same value but presented in a more useful way.
100% increase from zero
That’s all simple math but now it gets tricky with a difference between proper arithmetic and the real world.
Excel hits a problem if you’re trying to work from an old value of Zero.
According to Excel and basic mathematics you can’t divide by Zero so the error #DIV/0 in row 6 is quite right.
But in the human world, we’d usually say that an increase from zero to a positive number is a 100% increase. It’s not good arithmetic but what people say.
(If the value goes DOWN to zero, Excel shows a 100% decrease which is what us humans expect)
A better % increase formula
Here’s two ways to fix the ‘up from zero’ problem. We use this formula consistently to avoid any #DIV/0 errors.
=IF([@Old]<=0, 1,([@New]-[@Old])/[@Old])
The IF() function catches any Zero value and inserts 1.00 (aka 100%). You can replace that with another value or text like “Infinite”. “♾️”, “N/A” or NA( ) which returns a #N/A error.
The even better way to do this is with a simple Lambda function – available in Excel 365 and Excel 2024.
=LAMBDA(Old,New,IF(Old <= 0, 1, (New - Old) / Old))
Which looks like this in the Excel name manager.
Lambda is a MUCH easier to deal with using the Advanced Formula Environment.
Now we have a “PercChange” function that not only traps the occasional zero starting or old value but also ensures a consistent formula is used throughout the workbook without typing errors.