## 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
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. lampu merah says:

Hei helo. Can you teach and focus on vba more often? your teaching is fabolous

always love you. i bought your course on udemy and it's really useful for my work. thanks for always update new trick.

3. sarfaraj kureshi says:

Nice video I need it, first time I see you while teaching on board….

4. Gh.Rasool Faizi says:

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

5. Tom Kossler says:

This was good. Thanks. Could you do a quick video on the best way to calculate compound annual growth rate using Excel?

6. Rahul Kumar says:

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

7. Nirmal Budhathoki says:

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.

8. Technical Portal says:

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

9. Luciano says:

Like…Thanks Leila.

10. Andrew Moss says:

Really useful guide about percentages. Thanks!

11. Faraz Shaikh says:

Lovely .. straight & clear.!!!

12. Mark Williamson says:

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!

13. srivardhan kandike says:

Love You Mam

14. DurgaSai Gogu says:

15. Prabhu Kumar says:

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

16. Ajeesh K FREE DISH AND TECH MALAYALAM says:

Very good it helped me so much… ๐๐๐๐๐๐๐๐๐๐ป๐ป๐ป

17. KarChun Chong says:

why would it be 'more' right than the first method? is the 2nd method more efficient? thanks.

18. SamT says:

Great tut

19. Ajay Verma says:

Absolutely Amazing. Love it

20. Murray HC says:

This is the only "useful" and non-time wasting channel I follow on YouTube! Really happy I found it!!

21. practicecomputing says:

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.

22. A.J. Wilkes says:

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+%)

23. Noumon Munir says:

omg yep this is what i confuse the most even to this day. nice refresher

24. Francis Allan Lim says:

very cool, elegant formula….

25. Box Lane Productions says:

thank you, is there a way to show that a name with a score has moved up or down a ranking list?

26. Sukhomoy Bhattacharya says:

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.

27. Urs Venky says:

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.

28. Praveen Singh says:

I have a question
Can we record calls live by macros
Kindly suggest

29. Sebastian Mayorga says:

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??

30. sosoboss says:

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.

31. Soul of Chogokin says:

your awesome! i learned 2 things in one day and applied those in my work….

32. DeJaBleuK8 says:

THANK YOU for this video. This is the help I needed to simplify my formulas.

33. Anubhav Gupta says:

It's a very nice trick to get result of percentage changes.
Thanks to sharing this video.

34. BHARAT ASANANI says:

Hi Ms. Leila,

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

35. Coffee Bean says:

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?

36. Useful Stuff says:

I like the way you use the whiteboard to explain algebra. What's your degree?

37. yamturbo says:

Great

38. Saif Shaikh says:

Mam why we are putting 1+%

39. Shashi Kant says:

Awesome!! ๐๐

40. Star Walker says:

Thank you

41. smith lovy says:

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.

42. Ehsan Ahmed says:

How Can I Put Percentage On Sheet?

43. JSavic says:

A-B always for the numerator.
But ABS(B) for the denominator.
Your formula doesnโt work when B is negative.

44. Marla Packer says:

Thank you! This video helped me pass an exam!

45. Noumena says:

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?

46. Giacomo Vaccari says:

Been wondering how this was calculated for a while. Thank you!

47. Aliyar Husain says:

very nice. Thanks for sharing such a grate lesson.mam

48. J G says:

You are the best

49. Melvin Gonzalez says:

Thank you. Very nicely done

50. david alejandro gutierrez granada says:

Thank you math teacher

52. AMIT KUMAR SHARMA says:

Wow

53. Gedo prince says:

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 ๐

54. MirVids says:

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

55. Krishu doley says:

I have learned so many idea from your videos.please keep teaching us.

Love you Leila. Many thanks for your video tutorial. I have improved my skill in ms excel.

57. Christos Lefkimiotis says:

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.

58. Patricia says:

59. Jorge Arturo Montes Dรญaz says:

Beautiful and smart woman…

60. Raule Estobar says:

Una vez mรกs; excellent work. Thank you, gracias

61. yes4me14 says:

thanks a lot

62. A&K On demand says:

63. Haris Khan says:

It's really amazing video and so helpful tips Thanks for sharing

64. jusjengkol says:

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

65. Nasser Farahat says:

u r so smart

Good, very useful knowledge.

67. Babak Kakavand says:

Super useful, Thanks for sharing

68. arif hidayat says:

Love it …thank you Leila

69. T Hyslop says:

This is fourth grade math, maybe third.

70. chaz kaz says:

Videos are amazing. Watched you do this in IF functions and was slightly confused. Thanks for clearing up

71. Shlomo Batsia says:

Basic indeed, but always nice to remember…

72. Andrew Muir says:

This is maths not excel

73. Jojo YZ says:

I am new here. Really nicely delivered your knowledge! Love from Pakistan ๐

You are great. Your video really helped me.

75. 505 Strat says:

Another way of calculating actual value is: (You = VeryCool)

76. txreal says:

100% cool video

77. Andrzej Andrzejos says:

Your videos are nice to watch, hear and are helpful also. Damn thats fair enough to subscribe

78. gulshan jii says:

I love you mam

79. George Reynolds says:

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

80. Janet Lamb says:

Can I ask what is the signficance of the either -1 at the begining of the formula or the end of the formula

81. Sylvie Morissette says:

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

82. Iscoal Michael says:

This particular video is one of a kind, but guess what? you are such a genius!

83. MATRIX GHOST says:

I was distracted the whole time by that angelic face

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

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

85. Mo naz says:

Is percentage change the same as percentage variation ?

86. Carlos lopez lozano says:

Thanks for the video! easy!

87. rkagi says:

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)

88. YAIR GS says:

No deberรญas explicar temas en los que no estรกs muy segura. Se nota tu inseguridad al exponer un poco de รกlgebra.

89. Ahmed Saeed says:

Good stuff. How about irregular percentage increase in n years? Is there Leila version channel for Word?

90. dark thunder says:

again and again i wont stop thanking you ๐ love your videos

91. N SundaraMoorthy says:

Fantastic.

92. Archie White says:

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.

93. Phil Wilkinson says:

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.

94. Anne Lopez says:

Amazing ๐

95. Robbie Knott says:

96. Maher Ahmed says:

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 ๐

97. Huma says:

Thank you so very much
Thanks

98. Budi Nugraha says:

how to calculate for this case.
actual 2019 = -2
target 2020 = 3
growth = ? (formula)

99. rasal sheikh says:

Tnx to share

100. George Reynolds says:

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