How to calculate percentages in Excel training tutorial
This is a Microsoft Excel training tutorial on how to calculate percentage in any version of Excel. Learn three cases you can use in all situations and know how to calculate percentages correctly in Excel.
Example one: how many percentage is X of Y?
Here's a simple example: the original price of a product is 149 € and it is now being sold at 79 €. How many percentage is 79 € from 149 €?
You can get this easy with new price/old price.
If the old price would be in cell A1 and new price in A2, we would go with:
=A2/A1
This will give us 0,53. Change the formatting of the cell to percentage (in the home tab, under data click the % mark) and you get 53%.
Changing formatting into percentage in Excel
So what would be the discount percentage then? We can now calculate the discount-% with:
1-0,53 or 100%-53%
This will give us a discount percentage of 47 %. We can check this with old price multiplied with 0,47:
=A1*0,47
This will give us 70. So the discount of 47 % is 70 € from 149 €. And 149 € - 70 € = 79 €
Example two: how much is X% of Y?
The original price of a product is 159 € and it is now sold with discount of 25 %. How much is the discount?
You can get this again easy with price multiplied with discount percentage.
If the price would be in cell A1 and discount percentage (marked either 25 % or 0,25) in A2, we would go with:
=A1*A2
This will give us 39,75.
Note that we don't have to divide the 25 % with 100 when the value is formatted as percentage because the real value in the cell is already 0,25.
So how much is the discount price?
159 € - 39,75 € is 119,25 €, so the discount price would be roughly about 120 €.
Example three: calculating VAT correctly of price including tax in Excel
The most important thing here is to note that you cannot calculate VAT correctly by multiplying the price with the VAT percentage (in case you are wondering, this has actually nothing to do with Excel, it's just math).
So, the correct formula to calculate the VAT from a price including tax is:
=(VAT-%*PRICE WITH VAT)/(100+VAT-%)
Let's see an example how to use the formula.
Example for calculating VAT of price including tax
A product costs 15,09 € and the price includes VAT of 21 %, how much is the VAT?
Insert 15,09 in cell A1 and the VAT percentage of 21 in cell A2 (do not format the VAT into percentage). Here is the correct formula:
=(A2*A1)/(100+A2)
Manually calculating this would be:
=(21*15,09)/(100+21)
This will give us 2,62 € and we can now quickly calculate that the price without VAT is 15,09 € - 2,62 € = 12,47 €.
If we are going to use the result in accounting, it needs to be rounded to 2 decimals with function ROUND:
= ROUND((A2*A1)/(100+A2);2)
That's it for this tutorial, you learn more of Excel in our Excel courses. The Excel Essentials training course is perfect for for beginners and intermediate levels, the Excel Advanced course is the best match for those who want to become more advanced Excel users.
Until next time!
Sign up to our newsletter
Expert advice on Excel, WordPress, SEO, Google analytics and Adwords. Sign up to our newsletter to get the latest posts directly into your inbox.
You might also enjoy these articles
This is a tutorial on how to round time in Excel. This can feel a bit tricky if you are not familiar with how Excel handles time formatted data. Now we can’t just go ahead and use the ROUND function which might feel like the obvious choice. We need a different set of tools for this, because for Excel, data formatted as time behaves in a different way.
