 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2021, 2019, 2016 and Mac versions more…

# Adding VAT to a value in Excel

Sales taxes are levied in many parts of the world. In the UK, this is known as VAT, or “Value-added Tax”, but it’s also known as sales tax, consumption tax and GST (Goods and Services Tax).

Adding VAT to values is a very common requirement, but doing this in Excel isn’t very intuitive and confuses many users.

Your first instinct to add 20% VAT to a number might be to use a formula like this:

=A2+20% Unfortunately, this doesn’t work. If you’ve read our earlier article about subtracting percentages, you already know why.

If you’re completely new to Excel and have no understanding of formulas, you can learn how to create simple formulas in our completely free Basic Skills course. I’d recommend starting there if formulas are completely new to you. Otherwise, read on for the solution.

## Percentages are actually decimal values

When you type 20% into Excel, Excel sees it as the value 0.2. Excel does this with all percentage values. 50% is 0.5, 75% is 0.75, and so on.

In the previous example, you were actually asking excel to add 0.2 to the value instead of adding 20%, resulting in 88.20 instead of the correct result, which would be 105.6.

## The solution

So, knowing how Excel treats percentages, how should I add the 20% VAT?

To get your value plus 20%, you actually want to get 120% of the original value, so one solution is to multiply the number by 120% with one of the following formulas:

=A2*120%
=A2*1.2

Because of the way Excel handles percentages, it sees 120% and 1.2 as exactly the same value.

An alternative, but more long-winded calculation, would be to calculate 20% of the number and then add it to the original number, with one of these formulas:

=A2+(A2*20%)
=A2+(A2*0.2)

This will have exactly the same result and, as in the previous example, both of these formulas mean exactly the same thing to Excel. Once you’ve written the formula you can easily fill it down to as many records as you need. AutoFilling formulas is explained in depth in our free Basic Skills course.

Our Essential Skills course continues from Basic Skills and explains Excel’s handling of percentages in greater depth, as well as many other features. I’d highly recommend it if you want to improve your Excel skills even further.  These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.

They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

## Some of the things you will learn              ## Recent Articles

### Learn Excel 2021 Essential Skills with The Smart Method

Here are just a few of the things you will learn with this book: Pages: 381ISBN: ‎978-1909253513Dimensions: 8.27 x 0.86 x 11.69 inchesWeight:2.47 pounds E-book

### Excel 2021 book and e-book tutorials

Excel 2021 Book and e-Book Tutorials Details of all our Excel 2021 Books and e-books can now be found here.

### Learn Excel 2021 Expert Skills with The Smart Method

Here are just a few of the things you will learn with this book: Pages: 641ISBN: ‎ 978-1909253520Dimensions: 8.27 x 1.45 x 11.69 inchesWeight: 4.09

### YouTube Excel Expert Skills Course Support Materials

YouTube Expert Skills free video course support materials Download the full sample file set (all files for the entire Expert Skills course) If you are

### Wholesale Order Complete

Wholesale Order Complete Thank you for your order We’ll confirm your order and the likely delivery date via e-mail within the next 24 hours. If

### 6 Responses

1. Rodolfo says:

thanks for this! I got to do it right!!!

2. Mark says:

Wow, except this formula doesn’t actually work, on account of the fact that you are adding 20% to the net value, when vat is 20% of the gross value. The two are not the same.

If VAT is charged at 20%. That means that if you have a gross value of 105.6, as in the example, the VAT would be 21.12 (20% of 105.6) and the net amount would be 84.48 (80% of 105.6).

1. Simon Smart says:

Hi Mark,

I think you might be misunderstanding what this article is trying to show – the example is not trying to calculate the VAT amount from a net value, it is simply adding the VAT to a value that has not yet had the tax applied.

It sounds like you’re talking about trying to calculate how much VAT has been added to a value that has already had VAT applied. That is not what this article covers, but that kind of calculation (using a different example) is covered in this article.

3. Stuart Campbell says:

or moreover…he is going to have a problem if he is in business since it is 20% of the net!

4. Alan Dobson says:

What about where there are 2 rates of VAT on different items on spreadsheet?

1. Mike Smart says:

Hi Alan

In this case you could add the percentage VAT rate you wanted to apply (for each item) in an adjacent column. For example, if cell A2 contained the net value and B2 contained the percentage VAT rate you could use a formula such as =A2+A2*B2 to apply the correct level of tax and display the total price.

You could, of course, get very sophisticated and have a drop-down list containing text such as “Standard”, “Zero” and “Lower” and then automatically apply the correct VAT rate – or even define your own product list and VAT categories but that would required good Excel skills. You can learn all of the skills you need to create that type of sophisticated, professional workbook in our Essential Skills and Excel Skills courses.

## Which tutorial do you need?   ### Which Excel Essential Skills version do you need to learn?

#### Excel 2016 for Mac 