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

### 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**.

Tom Claman says

Glad to know the Real Name. My Management likes to refer to it as the Growth Rate.

Percent Delta works for me.

Annie Cushing 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.

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

Annie Cushing says

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 says

Does the Excellent Analytics add-in work for Excel for Mac?

درب اتوماتیک – دوربین مدار بسته

Annie Cushing says

No, unfortunately. 🙁

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)

Annie Cushing says

This is excellent, Duke! Thanks!

Lindsay says

Perfect for my need. Thank you!

Annie Cushing says

My pleasure!

SR says

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?

Annie Cushing says

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

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

Annie Cushing says

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

villa says

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

Annie Cushing says

Just multiply your current expense values by 1.03.

Mya says

Thank you, this was very helpful!!!

Annie Cushing says

My pleasure! 🙂

Steve says

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.

Annie Cushing says

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.

mike says

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?

DK says

HI,

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

Annie Cushing says

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.

Chris says

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!

Annie Cushing says

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.

Eustace says

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.

Annie Cushing says

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!

Alan Nafziger says

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.

Alan Nafziger says

grrrr…forgot to change the entire formula..should be…

=IFERROR(new-old)/old*SIGN(old),0)

sorry for any issues

Annie Cushing says

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

Carl says

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.

Annie Cushing says

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

lili says

Piece of cake, thanks to you 🙂

SEO_Plus says

Hi Annie – always love your work!!

Learned that =NEW/OLD-1 also works.

Keep up the good work!!

Annie Cushing says

Yep, that works too! And thank you! 🙂

vedoza says

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

Annie Cushing says

Nice! Thanks!