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.

Comments

    • says

      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.

  1. alorant says

    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.)

  2. Duke Carey says

    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)

  3. Tay says

    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….

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>