• Skip to main content

Annielytics.com

I make data sexy

  • About
  • Services
  • Tools
  • Blog
  • Portfolio
  • Contact
  • Log In

Feb 14 2014

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 like other investment apps such as usdcoin: usdm crypto and usdcoin wallet. 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.

Learn More

You can learn more about data formatting in my Annielytics Dashboard Course, a 16-hour video course that will teach you how to put your data in stilettos and work the pole. 🙂

~~~

If you would like to learn more about Excel, check out my Excel dashboard course. 24 instructional videos, totaling 6+ hours of instruction for $95.

buy-now-button

Written by Annie Cushing · Categorized: Excel

Reader Interactions

Comments

  1. Tom Claman says

    February 14, 2014 at 4:11 pm

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

    Reply
    • Annie Cushing says

      February 14, 2014 at 6:33 pm

      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.

      Reply
  2. alorant says

    February 15, 2014 at 8:59 pm

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

    Reply
    • Annie Cushing says

      February 16, 2014 at 9:31 pm

      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.

      Reply
  3. raha says

    June 1, 2014 at 8:17 am

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

    Reply
    • Annie Cushing says

      June 1, 2014 at 3:35 pm

      No, unfortunately. 🙁

      Reply
  4. Duke Carey says

    June 19, 2014 at 2:39 pm

    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)

    Reply
    • Annie Cushing says

      June 25, 2014 at 11:07 am

      This is excellent, Duke! Thanks!

      Reply
    • Lindsay says

      April 22, 2016 at 11:02 am

      Perfect for my need. Thank you!

      Reply
      • Annie Cushing says

        April 22, 2016 at 12:31 pm

        My pleasure!

        Reply
    • SR says

      May 3, 2019 at 11:07 am

      Excel says too many arguments. For this function. I just replaced your starting and ending with my starting value and ending value. What could be the issue? I must mention that my beginning value is 0. Is that the problem?

      Reply
      • Annie Cushing says

        May 3, 2019 at 11:16 am

        Yes, that’s the problem. You can’t divide by 0. Some apps indicate the % delta as ∞. Excel gives up the ghost.

        Reply
  5. Tay says

    June 24, 2014 at 7:30 pm

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

    Reply
    • Annie Cushing says

      June 25, 2014 at 8:18 pm

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

      Reply
  6. villa says

    September 14, 2014 at 4:24 am

    How do i calculate next year expenses figures based on 3% growth in the budget using excel

    Reply
    • Annie Cushing says

      September 15, 2014 at 12:05 am

      Just multiply your current expense values by 1.03.

      Reply
  7. Mya says

    September 17, 2014 at 6:34 pm

    Thank you, this was very helpful!!!

    Reply
    • Annie Cushing says

      September 17, 2014 at 8:55 pm

      My pleasure! 🙂

      Reply
  8. Steve says

    January 6, 2015 at 3:37 pm

    How do you calculate daily percent delta over a month’s worth of data to find spikes?

    For example, I have a stock price and I want to find the days it jumped or dropped, so I can analyze those data points further.

    Reply
    • Annie Cushing says

      April 2, 2015 at 8:22 pm

      Sorry for the horrifically late response! I didn’t realize I wasn’t receiving comment alerts. :/

      But I would create a column next to the column where you have your month’s worth of data and enter the formula in the second row and double-click the bottom right corner to send it down the rest of the column.

      Reply
  9. mike says

    January 13, 2015 at 12:34 am

    Thank you so much for posting this! I found myself transferred out of a regular sales job into a position that requires more data analysis. I have never taken classes or anything regarding this subject. Are there any books or resource materials you could suggest to get me up to speed?

    Reply
  10. DK says

    February 10, 2015 at 12:56 am

    HI,

    I always thought that the calculaion was: ((Present Value – Past Value)/ Present Value). Is this incorrect?

    Reply
    • Annie Cushing says

      April 2, 2015 at 7:50 pm

      Sorry for the horrifically late response! I didn’t realize I wasn’t receiving comment alerts. :/

      But it’s just difference/original, so past value should be in the denominator. I’m on mobile right now, so I can’t double-check, but I’m 99% sure.

      Reply
  11. Chris says

    April 13, 2015 at 12:36 pm

    Hi, I’m not dealing with dollars and cents but I am comparing new data versus old data. Some of my old data are at 0 so what should I use to find the percent increase to the new data? For example: in October the number was 0 and now it is 5. If I did new/old then I have the divide by zero error. Thank you!

    Reply
    • Annie Cushing says

      July 17, 2015 at 12:06 pm

      I use an IFERROR function to put something else in there if it returns an error (which it will if you try to divide by 0). You could even put in 0 if you want.

      Reply
      • Eustace says

        August 21, 2015 at 12:34 pm

        Like Chris above I would like it to show a 100% increase – as (using Chris’s example it has gone from 0 – to a value of 5. Using the iferror function just hides any increases that have come from 0.

        Reply
        • Annie Cushing says

          August 29, 2015 at 8:34 pm

          You can use a combination of an IF and ISERROR function, e.g., =IF(ISERROR((B1-A1)/B1),1,(B1-A1)/B1). This just says, “If you calculate the % delta and it’s an error (b/c you’re dividing by 0), assign it the value of 1 (100%). If it doesn’t kick out an error, go ahead and just calculate the % delta.” Hope this helps!

          Reply
  12. Alan Nafziger says

    October 15, 2015 at 2:51 pm

    A slightly more simpler form of good to bad formula would be:
    =IFERROR(new-old)/old*SIGN(J3),0)
    This will figure the difference between the new and old numbers as a positive and negative number. Feel free to replace SIGN with ABS if you just want to deal with positive numbers.

    I’ve been asked a lot of times how to get the delta positive to be green and negative to be red…simply format the cells (right click the cell and use a custom format for the numbers) I use: [Color10]▲0.00%;[Red]▼0.00%
    Color 10 is a slightly darker green than the lime colored code of [Green]. You can change the colors to pretty much anything.

    Reply
    • Alan Nafziger says

      October 15, 2015 at 2:53 pm

      grrrr…forgot to change the entire formula..should be…
      =IFERROR(new-old)/old*SIGN(old),0)

      sorry for any issues

      Reply
      • Annie Cushing says

        October 15, 2015 at 7:26 pm

        Nice! I’ll have to give this a try. Thanks!

        Reply
  13. Carl says

    December 10, 2015 at 2:32 am

    Hello, I have the opposite problem. I have a column of percentage returns that correspond to a specific date over a years timeframe. I want to plug in a principal amount in a seperate column or table that then will show running values in terms of appreciation and or depreciation based on whether the values are negative or positive returns and finally give me a total return at the end of the year. If there is a way to then plot this data on a line chart that would be great too. Thanks for the help in advance.

    Reply
    • Annie Cushing says

      December 14, 2015 at 9:34 am

      Sorry. I can’t visualize this issue. It helps, in questions like this, to attach a Google Spreadsheet I can reference.

      Reply
  14. lili says

    June 22, 2016 at 4:47 am

    Piece of cake, thanks to you 🙂

    Reply
  15. SEO_Plus says

    September 14, 2016 at 9:20 pm

    Hi Annie – always love your work!!

    Learned that =NEW/OLD-1 also works.

    Keep up the good work!!

    Reply
    • Annie Cushing says

      September 15, 2016 at 9:41 am

      Yep, that works too! And thank you! 🙂

      Reply
  16. vedoza says

    January 7, 2017 at 3:22 am

    You can verify it at http://www.percentagechangecalculator.com/ website

    Reply
    • Annie Cushing says

      January 7, 2017 at 9:42 am

      Nice! Thanks!

      Reply
  17. Tracy says

    July 18, 2019 at 4:14 pm

    You can also add the Delta symbol in Excel by typing ALT+30.

    Reply

Leave a Reply Cancel reply

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

Copyright © 2025