How to Quickly Create Percentage Change Formulas with a Macro - Excel Campus
23

How to Quickly Create Percentage Change Formulas with a Macro

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

Percentage Change Formula Creator Macro for Excel

The image above is an animated screencast.  Click here to view it in your web browser.

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

Typical Percentage Change Formula in Excel

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)

Percentage Change Formula with IFERROR for Divide by Zerror Errors

The formula above would return a zero if the denomiator (A1/old value) is zero.

This formula can also be used to calculate 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.

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…

  1. The macro uses the InputBox method, and asks you to select the cells that contain the new and old values.
  2. 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.

Add the Percentage Change Formula Macro to your Personal Macro Workbook

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 copy/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.

Add Custom Ribbon Button to Run the Macro on Any Workbook

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! 🙂

Percentage Change Formula Creator Macro for Excel

Checkout my article and video series on the Personal Macro Workbook for details on how to set this up and create custom ribbon buttons to run your macros.

Other Options for the Percentage Change Macro

The possibilities are pretty much endless for this macro.  You can change it to fit your needs.  Here are a few things we could change:

  • Change the value_if_error value from 0 (zero) to another value.
  • Remove the IFERROR function completely.
  • Warn the user before putting the formula in the active cell.
  • Copy the formula to the clipboard instead of putting it in the active cell.
  • Format the active cell with a percentage format.
    Code: ActiveCell.NumberFormat = “0.0%”

What other changes would you like to make?  Please leave a comment below with your suggestions or questions.  Thanks! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 23 comments
Roy - December 29, 2016

This was a great lesson for me to understand some of the differences between execution of Excel’s formulas/functions and VBA code. i.e. worksheet vs module

I added some code to the module to format the ActiveCell to “percent”. This was nice because the formatting is also applied after any copy and paste action.

Feeling all proud, I then added more code to change the interior cell colorindex when a “divide-by-zero” event occurred. But quickly learned that this VBA code is only applied once to the ActiveCell where the formula was written. Subsequent coping and pasting of the newly written formula doesn’t evaluate the cell’s value and change the colorindex because this intended feature is left behind in the VBA code of the macro.

I guess one could wrap the IfError() function by another =If() function so the color evaluation occurs in the worksheet’s formula.(??) Adding a way to highlight divide by zero events.

” ‘ ADDING FEATURES to EC – Percent_Change_Formula() macro
ActiveCell.Style = “percent”
If ActiveCell.Value = “0” Then
ActiveCell.Interior.ColorIndex = “3”
End If

Reply
    Jon Acampora - January 4, 2017

    Hey Roy,
    Great question! To fully automate the coloring of the cell I would recommend applying conditional formatting to the macro. When the formula is copied, the conditional formatting will travel with it and be applied to new cells.

    You can use the macro recorder to get the code to apply the conditional formatting. I hope that helps. Let me know if you have any questions.

    Reply
Paul - October 18, 2016

Hello, Jon!
Thanx for the great job you’re doing! I make my first steps in VBA learning with your lessons and they are just what is needed 🙂
Relating this post I have one question – is it possible (I’m sure it is somehow:) to make only one messagebox with two input lines for the new and old values?
Thanks again!

Reply
    Jon Acampora - October 22, 2016

    Thanks for the nice feedback Paul!

    Great question! It is not possible using the InputBox. The InputBox function only allows for one input at a time. You would need to create a UserForm if you want to have to input boxes on the same form. I hope that helps!

    Reply
Paul - July 13, 2016

Hi Jon

You should really be dividing by the Absolute ABS() value of the divisor to get a true movement in all cases. You can get odd results if your divisor is negative to start with (speaking from past experience).
Cheers Paul.

Reply
Carlo - July 13, 2016

Please, explain how to get an absolute cell format. I see that in your example you just clicked on the cell without showing how that cell became an absolute one.
thank you

Reply
    Jon Acampora - July 13, 2016

    Hi Carlo,
    Great question! The InputBox method allows us to select a range when we use the type:=8 for the type parameter.

    The InputBox will always show and return the selected range as an absolute reference.

    However, we can change this to a relative reference in the code using the Address property. The following lines of code do this.

    sNew = rNew.Address(False, False)
    sOld = rOld.Address(False, False)

    The first two parameters of the Address property are RowAbsoulte and ColumnAbsolute. We can set these to False for relative references, or True for absolute references. You can also use mixed referencing here and set the Row to relative and column to absolute, or vice-versa.

    So even though the screencast video shows $C$2 in the InputBox, the outcome in the formula is C2.

    I added an example file that you can download at the top of the article to checkout the code and test it. I recommend putting a breakpoint at the top of the code and stepping through each line to see how it works.

    Let me know if you have any questions. Thanks again!

    Reply
Laura - July 13, 2016

How is this different from “show value as % difference from…” in the pivot table calculations?

Reply
    Jon Acampora - July 13, 2016

    Hi Laura,
    Great question! It’s the same basic calculation. This is just the formula version for when you are not using pivot tables. Let me know if you have any other questions. Thanks!

    Reply
Juan - July 13, 2016

Thank you very much Jon for sharing this fantastic tip!

The formula that can be used with the General format in the cell would be:

New/Old*100-100

The result is the percent change directly.

Can this macro be applied to calculate year to date (YTD) changes and then, when a new year starts, it recalculates the change from January of the new year?

Reply
    Jon Acampora - July 13, 2016

    Nice suggestion Juan!

    In regards to your question, yes you can definitely reference/select the cell that contains the January number with the macro. This would be the “old value”.

    You would then want to anchor that cell reference to make it absolute. You can do that in the code, or go back and modify the formula to make the reference absolute. To do it in the code you would just change the Address arguments to true for the Old value.

    sOld = rOld.Address(True, True)

    The formula would then look like the following.

    =C2/$B$2-1

    or

    =(C2-$B$2)/$B$2

    In regards to changing the formula for the new year, that will depend on how your sheet is setup. Anchoring the old value cell means that reference will NOT change when you copy across/down. You might want to change the row or column absolute/relative referencing to move, as you copy the formula down or across for the new year.

    I hope that makes sense. Thanks!

    Reply
Nate - July 13, 2016

Thanks for the tip! I hate having to write out this formula each time – and every time I do I always Google “is there a formula to calculate % change in Excel?” This solves that!

One thought – do you think it would make sense to apply some error handling to keep the user from selecting more than one cell for each of the arguments?

Reply
    Jon Acampora - July 13, 2016

    Awesome! Thanks Nate 🙂

    That is a great suggestion! One easy way to handle that is to automatically resize the range to the top-left cell of the selection, if the user selects multiple cells.

    The following code will do that by reference the row and column index of the range object variables.

    sNew = rNew(1, 1).Address(False, False)
    sOld = rOld(1, 1).Address(False, False)

    If the user selects one cell or multiple cells, this will return the address for the top-left cell of the selection. Row 1, column 1 of the selection.

    Thanks again!

    Reply
James - July 13, 2016

Hi Jon
This is great as I normally have to manually enter the formula in.

I’m having issues getting the Macro to work though.

The “On Error GoTo ErrExit” is showing in red when I paste it into a Module; and I get a message saying “Sub or Function not defined”

Wondering if it’s just me it’s not working with or if I’ve copied it incorrectly…

Reply
    Jon Acampora - July 13, 2016

    Thanks James! There might have been an issue with the copy/paste from the web. I just added a download link to the file at the top of the article. So you can download the file that contains the code, and copy/paste the code from there. Check it out and let me know if you still have issues.

    Reply
      james - July 14, 2016

      works like a charm

      thank you very much for this
      article is greatly appreciated

      I always check in here for any new tips, or searching for answers to problems

      please keep up the great work you are doing!

      Reply
XLarium - July 12, 2016

Hello Jon, wouldn’t it be more efficient to use:
=C2/B2-1
Each cell reference will be used only once.

Reply
    Jon Acampora - July 13, 2016

    Yes, that’s a great alternative. I see the (new-old)/old used more often, but you are right that (new/old)-1 is a simplified version of the formula.

    The method used is probably highly dependent on how the boss wants it or does it. 🙂

    The formula in the macro can be modified for the simplified formula.

    sFormula = "=IFERROR((" & sNew & "/" & sOld & ")-1,0)"

    I will update the post as well. Thanks for the comment! 🙂

    Reply
    Jon Peltier - July 13, 2016

    I prefer =New/Old-1 myself. I also like to keep it as a fraction (percentage), without multiplying the result by 100.

    Reply
Chris Newman - July 12, 2016

This sort of automation is a must for any financial analyst. I have used something similar for a long time! One modification I would recommend is wrapping an ABS() function around the denominator. This will handle instances of going from a negative (old number) to a positive (new number)

Reply
    Jon Acampora - July 13, 2016

    Great suggestion Chris! You reminded me why I don’t use =new/old-1. 🙂
    If there are negative numbers involved then that formula can return incorrect results.

    Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x