Skip to content

Smart Percentage Change in Excel

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.

Understanding the new Lambda helper functions

Debug to understand and fix Excel formulas

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.