How to calculate percentages in Excel training tutorial

By: Ilkka Mäkinen | Published: 14 June, 2016 | In: Excel

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

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.

Subscribe to our newsletter

Get the latest expert advice from our blog on Excel, WordPress, search engine optimization, Google analytics and Adwords straight to your email.

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!

By: Ilkka Mäkinen | Published: 14 June, 2016 | In: Excel

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.

Want to stop messing about and learn to use Excel efficiently?

Save your spot in our Excel Essentials course and learn how to make Excel rock!

Terms & Conditions

Conditions: Minimum number of participants is 3 persons on each course. If the course you are enrolling in does not reach the minimum number of participants it will be cancelled or postponed. We will inform enrolled participants in case there are not enough participants on a course.

Cancellations and substitutes: a) Cancellations confirmed more than 21 days prior to the date of the course are fully refundable and free of charge. Cancellations are to be made in writing to info@talentwire.eu. 

b) Cancellations made within 21 days of the date of the course may not be cancelled without incurring course charges fully.

c) Substitutes may be made at any time free of charge. TalentWire should be informed of any changes.

Pricing and invoicing: Payment for each course is to be received no later than 14 days prior to the course start date. We will send an invoice after your booking has been confirmed. Inform us of any special arrangements you require for your invoices.

All prices are as stated and are exclusive of VAT which will where appropriate be charged at the current rate.

×