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