How to Quickly Create Percentage Change Formulas with a Macro
Feb,04,2017
Bottom line: Percentage change formulas require a lot of typing to create a simple calculation. This macro will create the entire formula for you, including the IFERROR function.
Skill level: Intermediate
Download the File
Download the Excel file to follow along.
Percentage Change Formula Creator Macro (19.0 KB)The Percentage Change Formula Explained
To calculate the percentage change between two numbers we typically write a formula like the following:
=(C2-B2)/B2
Whenever I am creating this formula I always think, “new minus old, divided by old”. Or:
=(new value – old value) / old value
If we want to handle/prevent any divide by zeros (#DIV/0!), then we can wrap the formula in the IFERROR function.
=IFERROR((C2-B2)/B2,0)
The formula above would return a zero if the denomiator (A1/old value) is zero.
This formula can also be used tocalculate discount percentages, so you can use it when you are shopping.
discount % = (new discounted price – old regular price) / old regular price
Simplified Formula
The formula can also be simplified to:
=(new value / old value) – 1
This will return the same result as the other formula. The formula you use is a matter of personal preference, or your boss’s preference. 
Thanks to XLArium for mentioning this is the comments.
Thanks to XLArium for mentioning this is the comments.
Either way, there are a ton of different uses for the percentage change formula. It’s one that should be in every Excel user’s toolbox.
The Percent Change Formula Creator Macro
The percentage change formula isn’t too terribly long, but it can take some time to type out. So I wrote this simple macro that will write the formula for us.
Here’s how it works…
- The macro uses the InputBox method, and asks you to select the cells that contain the new and old values.
- It then writes the percentage change formula, including the IFERROR function, and puts the formula in the ActiveCell.
It’s a super simple time saver for a very common formula.
Here is the VBA code. You can copy/paste it to your personal macro workbook.
Sub Percent_Change_Formula()
'Description: Creates a percentage change formula
'Source: https://www.excelcampus.com/vba/percentage-change-formulas-macro/
Dim rOld As Range
Dim rNew As Range
Dim sOld As String
Dim sNew As String
Dim sFormula As String
'End the macro on any input errors
'or if the user hits Cancel in the InputBox
On Error GoTo ErrExit
'Prompt the user to select the cells
Set rNew = Application.InputBox( _
"Select the cell that contains the NEW number", _
"Select New Cell", Type:=8)
Set rOld = Application.InputBox( _
"Select the cell that contains the OLD number", _
"Select Old Cell", Type:=8)
'Get the cell addresses for the formula - relative references
sNew = rNew.Address(False, False)
sOld = rOld.Address(False, False)
'Create the formula
sFormula = "=IFERROR((" & sNew & " - " & sOld & ")/" & sOld & ",0)"
'Create the formula in the activecell
ActiveCell.Formula = sFormula
ErrExit:
End Sub
It’s important to note that this macro will override the value or formula in the activecell with the result. We can add additional code to prevent that. More on that below.
How to Use the Macro
I put this macro in my Personal Macro Workbook, and then create a ribbon button and/or keyboard shortcut for it. You could also put it in an add-in file.
Either of these options will allow you to use the macro on any Excel file you have open. This means that you do NOT have to co7py/paste the code to each file you want to use it in. You can put it in your Personal Macro Workbook, and use it on ANY file.
Here is a screencast of the macro in action. You can see how simple it is to create the formula. All you have to do is click a few buttons and select two cells. No typing required!
End
