Quick Tips for Calculating Percentages In Excel

Here are some techniques for calculating percentages. For these examples, we have a simple sheet of travel expenses and you are after the percentage results of prices going up or down for the total and for the individual months.

Percentage Formula 2_expense sheet

Percent increase
Let’s say you anticipate more travel the following year and want to know what the cost will be if the expenses increase by 8 percent.

Tip: Excel lets you type percentages either with a percent sign or as a decimal. For example, you can enter twenty percent as either 20% or 0.2 (with or without the leading zero). Excel will treat them both the same.

In B19, enter:
=B18*1.08
Or if you like, enter it as:
=B18*108%

The amount will be 77,555.

Percentage Formula 2_8 percent higher

Percent decrease
If you think travel expenses will decrease by 8% instead, enter this in B20:
=B18*92%
Or if you like, enter it as:
=B18*.92

The result will be 66,065.
Percentage Formula 2_8 percent lower

Maybe it’s late in the day and you don’t want to calculate in your head that 92% is an 8% decrease. You can enter the formula this way for the same result:
=B18-B18*0.08

Tip: Remember the Order of Operations are Parenthesis, Exponents, Multiplication, Division, Addition, Subtraction. (Please Excuse My Dear Aunt Sally.)

Percent amount
But what if you want to see the 8% amount itself, not the new total? The formula in B21 would be:
=B18*0.08
OR
=B18*8%

This is the formula you’d use to calculate sales tax in New York City, if B18 contained the total sale.
Percentage Formula 2_8 percent of total

While all this math is correct, you might prefer to put the percentage in its own cell, rather than hard-coding it into the formulas. That way, you can change the percentage without having to rewrite the formulas.

If we put the percentage by itself in B23, these formulas would do the trick:

Percentage Formula 2_two formulas

Percent change

Finally, we might want to calculate the percentage change between two numbers. That would give us a clear picture of whether costs were heading up (a positive percentage) or heading down (a negative percentage). In this expense sheet, we’ll calculate the percent change from one month to the next and put the formulas down column C.

The general rule to calculate a percentage change is:
=(new value-old value)/old value

Since January is the first month, it doesn’t have a percentage change. The first change will be in February, so this is the formula for C6:
=(B6-B5)/B5

By default, Excel displays this as a decimal, so click the Percent Style button  on the Home tab to format it as a percent. You’ll now see this:

Fred Pryor Seminars_Excel Percentage Formula

Tip: The keyboard shortcut for Percent Style is Ctrl + Shift + %

Now auto-fill the formula down to the bottom: roll the mouse pointer over the dot in the lower-left corner of the cell that shows -16% and double-click the mouse. That’s a faster way of filling down to the bottom of the column than dragging down.

Local Seminars Related to this Topic:

Related Advanced Excel Articles

Categories

Monthly Archives

14 Comments

  • Russ Myers says:

    Hi, I believe % change is related to the change of the original number.
    So, % change = (New Value – Old Value)/Old Value
    I teach at a local community college and sometimes have my students search for examples that we can do together. Thanks!

  • Ravi says:

    I can’t thank you enough. I’m not a big Excel fan but this will patch up what I need to pernest until I get through some MATLAB code or R. Thanks a ton.

  • Sheila Hay says:

    Thank you, explained in detail that was well understood.

  • ZAKARIYYA says:

    Landing cost 2240, saled 2264 margin 24=%?
    And excel formula pls….

  • Pat says:

    the percentage calculation is helpful, however when comparing the result i find the if statement =IF((D11/C11*30)<30,D11/C11*30,"30") result of 30 a non-numeric number and cannot be used in calculations, i've tried changing the formatting of the cell to a number so that i can add the 4 cells where the if statement was used to place a number with no luck, any suggestions?

  • Adil Butt says:

    what’s the formula if i want to get % of an amount and ad it to the TOtal

  • ejayasinghe says:

    Need to get a quick percentage calculation. There is a way of doing it by highlighting cells. I have seen it but cannot remember
    100
    25
    250
    125

    total 500. quick calculation of percentage is requited.

    • patrick says:

      If my hotel ran 45.1% this week and is down -36.3% from last year same week what’s the formula to know what I ran last year?

  • Linda Todaro says:

    I am wanting to calculate current years increase over last year, but I want to show negative in red. When using your formula I can show in red, but it will show 100% as 1.00. I prefer the red and parenthesis, but want the 1.0 to show as 100 and (.50) to show as (50%) in red. Is there a formula I can do for this?

  • njabulo says:

    How can I increase the amount by 50% (it increases every month from jan to june) jan – 500 feb-? Mar-? ….

    • Excel Tips and Tricks from Pryor.com says:

      In order to increase the amount by 50% you will take the amount and multiply it times 1.5 (=amount X 1.5). You can then copy and paste your formula down.

  • scott says:

    Nice Tips articles and comments too.

    Thanks

  • Wanita says:

    Thank you so very much for this. My Director asked me to do a spreadsheet with the price comparison between two suppliers but percentage difference. I thought how the hell do you that. I couldn’t ask anybody in the office as everybody normally asks me how do do formulas on Excell. Then I googled it and found this page. I cant wait to send my comparison to her tomorrow. Lifesaver site this one. Don’t need an excell course just ask Bob Flisser

Leave a Reply

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