Calculate Percentages the Right Way in Excel (% Change & Amount after % increase)

Calculate Percentages the Right Way in Excel (% Change & Amount after % increase)


Today, let’s take a look at calculating percentages in Excel. We’re gonna take a look
at the formula you need to calculate percentage change and we’re gonna do it in a way that’s gonna help you remember it. If you’re working as a financial
analyst creating reports, this is a formula you need to get right. We’re also gonna take a look
at how we can do calculations that involve percentage
increase or decrease. So for example, we have a price. We want to increase price by 15%. What’s gonna be our end price? Let’s get to it. First off, let’s take a look at calculating percentage change. Assume your boss gives you an Excel file that contains actual sales
data and budget sales. Your task is to calculate
percentage change. Let’s take this to the board. So you have actual and you have budget. To calculate the difference, you’re gonna do actual minus
budget divided by budget. An alternate way of writing this formula is actual divided by budget
minus budget divided by budget, which turns into actual
divided by budget minus one. Now it’s all clear, right? So our formula is actual
divided by budget minus one. Double click on the bottom right side and send the formula down. (light percussive music) Now let’s move on to calculating percentage
increase or decrease. Our starting point is in column A. We want to increase the price by the percentage we see in column B. And if it’s minus, we want to decrease it. Let’s take this to the board. So we have our price. To calculate the price after we add the increase
or decrease to it, we’re gonna add this with price multiplied by that percentage. An alternate way of writing this is to factor out the price, so it’s price times one
plus the percentage. So that’s another way
of writing the formula. So in Excel, this would be starting price multiplied by one plus percentage change, close bracket, press enter. So if my starting price was 100, I increased price by 10%, I get 110. If this was 5% instead, I get 105. In Excel, there’s one thing
you have to be aware of and that’s how you input your percentages. One method to input percentages
is to add it while you type. So if I come here and type in 20 followed by the percentage
sign and press enter, the cell is automatically
formatted as a percentage. Another option is to
input it as a decimal, so 0.05 translates to 5%. So if I was gonna pull this formula down, we can see it adds up correctly as 5%. Now I can change this to
percentage and I get 5%. Now another thing you can do is to format your cell as percentage first before you input your full number. But don’t do it the other way around. So for example, here,
if I was gonna input 30 and then I decide, well,
this should be a percentage and I click to format
this as a percentage, I get the wrong number. So if this was already
formatted as a percentage, I go in and input 30, then it works fine. Okay, so let’s just
pull this one down too. So that’s it. Just remember, for percentage change, it’s new divided by old minus one or end divided by start minus one. For percentage increase,
we have the formula your value multiplied by one
plus the percentage change. And if it’s a percentage decrease, it’s your value times one
minus the percentage change. But if you input your
percentage as negative in Excel, you can use the same formula. Just drag it down. So the next time you’re asked to calculate the monthly or yearly change,
you know how to do it. If you like this video,
click that thumbs up. And if you want to
improve your Excel skills, consider subscribing to this challenge. (mellow music)

100 thoughts on “Calculate Percentages the Right Way in Excel (% Change & Amount after % increase)

  1. thank you very much
    Leila Gharani I watched your video they are very useful and help me in my duty.
    If it's possible for you please teach us ( DGHET) formula.
    thanks so much

  2. Very nice video, you are very good to simplify every problem .
    Keep posting such informative videos. Thanks for sharing !

  3. Thank you so much for very great video. It helps me lots. I have a question, related with IF function.
    my question is :
    Richard asks you to calculate the membership prices for the first quarter memberships sold in the gallery ticket office. The individual membership price is $50. Students pay a discounted price of $25.

    In cell F5, create a formula using the IF function to determine the membership price based on the following criteria:

    a. If cell D5 (the Category) is Student the price is 25. (Hint: Student must appear in quotes. Do not enter $ for the price.)

    b. All other individual members must pay 50.

    Copy the formula in cell F5 into the range F6:F13.

  4. So much informative and proper video. Keep updating these like video. I'm also getting inspired by these video, thus i've also created a youtube channel on Microsoft Excel.. Thank you..!!

  5. Excellent, as usual. You explain things clearly and concisely.
    For me, this one wasn't as useful as the others. I'm a certified high school teacher, so I know the math, and I learned spreadsheets on Supercalc 5.1 ( ever heard of that??), so the cell formulas were pretty straightforward.
    However, this brings us back to my first point: excellent, as usual. Since many people doing this work may not know the math, it's great that you cover it, just to be sure. I think people would rather learn math from you, as they need it, than suffer in my math class. LOL.
    This is really a great service you are doing. Thank you, and God bless you!

  6. Let me Share few tips.

    Tip1: Conventional way of calculating Gross Margin is (Sales-Cost) /Sales. But you can use (1-Cost/Sales) for ex (152-68)/152 = 55.3% or (1-68/152)= 55.3%. both will give you the same result.

    Tip2: You have a Cost Price lets say 20 and you want to fix the sales price which can give you 30% Gross Margin. How do we get that sales price which can give me 30%GM? Its simple.
    Cost/(1-GM%) in other words (20/1-0.3)= 28.57 is the sales price that we want to sell in order to get 30%GM

  7. Version 2010 and on (not sure about earlier) of the pivot table does this also. Even if you prefer the worksheet with formulas, it's a great way to check.

  8. I used to do the longer formula of %=(B-A)/A. Figuring out %=B/A-1 was a help. But also fun to see it as a reduction of B=A*(1+%)

  9. Hi Leila,
    Thanks for sharing nice basic concept. And hope not only excel users but other people will also be clear about these percentage confusion.
    Anyway, as an Excel guru can you please share any video on "Unique Random No Generation by formula."
    I had searched a lot and found some solution but they are not satisfactory. By Match and Large with Rand List is somehow suitable solution but I am looking for a formula which will replace RANDBETWEEN like RANDBETWEEN.UNIQ.

  10. Hello Leila Gharani Madam,
    Can you do a video on, how to make unique values of Rang("A:C") in vba ? Using Advanced Dynamic Array function we can do. Still "Unique" function is not available yet.
    If you do this, more people will get knowledge on this.
    Thanks for videos.

  11. Hello Leila. I´ve just bought your course bundle today, but I am having some issues to log in to my account, teachable doesnt recognize my email in their data base. Do you have any support email to get this problem sorted??

  12. Thanks you so much for your videos. You are a very good teacher in excel. I have learned a lot through your videos. Even if I'm french, I can understand easely what you said, because you have a very good skills in teaching. So, I'm waiting for new videos, especially about datavisualisation and making beautiful graphs. I hope you can understand me, I'm sorry for my english.

  13. Hi Ms. Leila,

    Please help me with correct formula of Growth

    if base sales is negative in value and in current sales is positive in value

  14. Do you have any videos dealing with projections, such as if a company is trying to figure out how many people to hire in order to keep staffing at where they need it to be so they can hit production goals? how they could use historical attrition data, create an attrition rate for each months of the year that would help them to predict a future staffing number needed to stay at plans by year end? For example, a Department knows they need to keep 100 people all the time to hit production goals, but they would like to know if there are certain times of the year they probably have more people leaving the company and they want to plan on hiring more then too, so that by the end of the year and all year through they remain close to the goal of 100 people. What would be the best way to present that data?

  15. For finance folks all this stuff is basics 101. But one thing that is tricky is when percentage change involves a negative number. I use the ABS function but there seems no standard way to handle that.

  16. Question: in your first example you calculated actual v budget percentages for individual entries in the row. If I wanted to calculate the overall average budget v actual between all of the entries, what would be the best way? Should I run the average of the % in the column? Or should I add all my numbers and denominators and then divide?

  17. you are really a great teacher I have never seen like you before.
    I have some confusing thing is that, I want to now how can I post all the transaction from general journal to ledger "by used one or more of Excel Functions" ?
    Thank you so much for 💓
    I'm waiting for your reply.

  18. Hello.
    Great video as always.
    Can I ask for help with profit on return rather than a straight percentage ?
    =A1/(100-B1)*100

    With A1 = 100,  B1 = 10, Profit on return formula above gives a result of 111.1111.
    This gives you full profit of B1 from the result rather than on cost. If you take 10 % from 100.

    Is there a better way, a Leila Gharani way to achieve this ?
    Thanks.(This is without cells set as %)

  19. Having seen a lot of your videos I have to congratulate you on your detailed approach to any issue that you are trying to explain. I consider myself an advanced excel user and still there are bits that I am learning from you. I see a lot of %changes as my daily job and I see people struggling. First you have to identify your starting point and then identify your terminal point. For example Budget sales is my starting point and the Actual sales my finishing point (it can easily be the other way around, depending on what you want to present). With the %chnage we are trying to find out what is the number to add on top of the budget sales (starting point) so to arrive to the actual sales (finishing point). And always the %change = (Terminal point / Starting point -1), that is the only that you have to remember so in your example is Actual/Budget-1. You might have year1 and year2 data the %change is different if the starting year is year1 and different if is year2, but always the correct formula is (Terminal point / Starting point -1). Special cases If year1 sales=120 and year2=0, then the %(year2 vs year1) = (-100%) but if year1=0 and year2=120 the %change is not defined as there is no number to multiply 0 (the starting point) and arrive to 120 (finishing point). As an analyst when I came across these cases I rather have a text saying something like "No sales in previous year" but not +100% that is totally incorrect.

  20. Graduated from statistics major and the first thing I had to learn when hired was how to calculate % growth in excel.

  21. Excellent, Leila! A lot of people – especially in the media – misuse percentages. For example "the amount of sales decreased by 200%" – impossible! Or "they increased by 300%" when a threefold increase was what happened, that should have read "they increased by 200%" (or better "to 300% of the initial figure").

  22. Thank you for all that amazing info. I have made a function with many "IFS" and I was wondering if there would be a shorter function that would give me the same result … I have to pay different percentages of taxes on different amounts; <=5,000 x 6% & <=15,000 x 11% & >15,000 > 16% ….. this function works but very long ….. would you have a better one =IF($N$15<=5000,$N$15*6%,IF($N$15<=15000,$N$15*11%,IF($N$15>15000,$N$15*16%)))

  23. Thank you for all the videos I learned a lot from you, I have one question
    How do you find the percentage increase in a negative number?
    for example:
    previous year: -3,162
    current year: 13
    your method will show: -100%

    do you recommend to use ABS function to solve the wrong result?
    BTW I have more than 100,000 data line in Excel, so I prefer to use one method that works for both positive and negative numbers

  24. Leila, for short timeframes, you can also calculate % change as ln(new/old).

    That way, if you have a sequence of events, such as daily stock market data, you can just sum the % change of a range (ie week) to get the % change in that range (ie for that week)

  25. Great video. Someone might have already mentioned this but I was always taught to mark up using P/(1-%) and down using P*(1-%) that way if you mark and and then back down you end up the same number. Using the method you describe you will not get the starting value.

  26. Hi
    Leila, thanks for the video as everyone is saying your videos are great. But i have a question now it might be my math / or some error on my part that is the problem but why can you not reverse this. So assuming that A2 is actual and budget is B2.
    =A2*(1+10%)
    would give us actual +10% (so if actual is 100, and budget is 10% more then budget would be 110
    ) now if i wanted to check that increase could use
    =A2/B2-1
    But that show 9.09% not the 10% i asked to be added to. i would have expected to see 10% here the same as i specified.

  27. I am so surprised! I was thinking about how to calculate the change in variance analysis – believe me once I opened youtube it showed the useful video – was that by chance? Leila covered every valuable tips 🙂

  28. Thank you, Leila! Be wary of margin and markup – e.g. cost= $80 and sale price = $100; the markup is 20/80 = 25% but the margin is 20/80 = 20%.

Leave a Reply

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