Easiest Way To Calculate Percent Delta in Excel

easiest way to calculate percent delta in Excel

When To Use It

A bedrock of marketing is the calculation of percent change. (Real analysts call it percent delta … just so you know.) We use these to show month-over-month (MoM) and year-over-year (YoY) changes in data, and they should be in every reporting dashboard you build. Without exception.

What is amazing to me is that Excel doesn’t have this critical function built in. So if you need to know the binomial distribution for a Bernoulli experiment, no prob, Bob! Excel’s got your back. But the percent delta between two numbers? Don’t get crazy!

How To Do It In Excel

So the first thing to remember is forget what you learned in middle school algebra. I find the easiest way to remember how to calculate percent delta is this:

(NEW – OLD)/OLD

The mnemonic device I use is new comes first because your new stuff is more important than your old stuff. Then everything else is old. Dooon’t judge.

Here’s what it looks like in Excel …

how to calculate percent delta in Excel

How To Format

To format, just choose the percent formatting from Home > Number. And if you don’t want decimals (I rarely use them unless I have values under 5%), press the Decrease Decimal button until they’re sleeping with the fishies.

format percentages in Excel

Click for larger image

How To Get The Delta Symbol

To get the delta symbol in your Excel file, simply enter a capital D, select it, then change the font to Symbol. These get mangled in pivot tables though. I’m pretty sure that’s the only time I type it out.

  • Tom Claman

    Glad to know the Real Name. My Management likes to refer to it as the Growth Rate.
    Percent Delta works for me.

    • http://www.annielytics.com/ Annie Cushing

      Yeah, I try to avoid terms like “growth” because it implies a positive direction. So if there’s a negative percent delta it sounds a bit oxymoronic to reference negative growth.

  • alorant

    Love your site and follow your posts regularly. THANKS!

    How do you get the green up and red down delta triangles? (BTW, I’m using a Mac.)

    • http://www.annielytics.com/ Annie Cushing

      Glad you’re getting something out of them. Always good to hear! I’m going to do a follow-up post on that, hopefully this week.

  • raha

    Does the Excellent Analytics add-in work for Excel for Mac?
    درب اتوماتیکدوربین مدار بسته

    • http://www.annielytics.com/ Annie Cushing

      No, unfortunately. :(

  • Duke Carey

    Thanks for the post. I work in banking and we have to be very careful about percent change. An increase in Revenue = GOOD, increase in Expenses = BAD.

    Here’s a formula that I use that deals with increases being GOOD, and it also captures divide by zero errors:

    IFERROR(ABS(Ending/Starting-1),1)*IF(Ending>Starting,1,-1)

    If increases are BAD, flip the last 2 arguments like so:

    IFERROR(ABS(Ending/Starting-1),1)*IF(Ending>Starting,-1,1)

    • http://www.annielytics.com/ Annie Cushing

      This is excellent, Duke! Thanks!

  • Tay

    I similarly thought that it was silly there was no easy way to do percent change in Excel since I use it all the time, but then I figured out a neat trick with pivot tables I thought I should share here:

    Say your row label is a bunch of consecutive days, weeks, months, etc. You can pull in the same label into the value field twice so that you end up with two columns with those values.

    Now select the cell in the second row of data in the second one of those columns, hit second click and then either show values as or value field settings and then select Percentage % and choose the number in the first row of data on the first of the value field columns.

    Now you can pull that down for the whole column….

    • http://www.annielytics.com/ Annie Cushing

      I’m going to have to play with this. Thanks!

Powered by WishList Member - Membership Software