If A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the original price with =A2*1.2. For example, a net price of £100 produces £20 VAT and a £120 total.
What the 20% VAT formula calculates
VAT is calculated as a percentage of a price that excludes VAT, often called the net price. At a 20% rate, the VAT amount is one fifth of that net price. The price including VAT, sometimes called the gross price, is the net price plus the VAT amount.
For a simple worksheet, put the net price in A2. The formula =A2*20% returns the VAT amount, while =A2*1.2 returns the total including VAT. If A2 is £100, those results are £20 and £120. The second formula works because 1.2 represents the original 100% of the price plus another 20%.
Excel does not have a special VAT function. These are ordinary multiplication and division formulas, so you can adapt them to a different VAT rate or a different cell layout. The key is knowing whether the figure you start with already includes VAT.
Add 20% VAT to a net price
To show the calculation in separate columns, put the net price in A2, the VAT amount in B2, and the total in C2. With that layout, enter =A2*20% in B2. For a £100 net price, B2 returns £20.
With the same A2 net-price layout, enter =A2+B2 in C2 to add the VAT calculated in B2. A £100 net price and £20 VAT return a £120 total. Keeping the tax amount and total in separate cells makes it easier to check an invoice or show the calculation to someone else.
If you only need the final total, put the net price in A2 and use =A2*1.2. A2 set to £100 returns £120. You can copy a formula down a column to calculate totals for multiple rows: for example, a formula in C2 will adjust its reference to A3 when copied into C3.
Remove VAT from a VAT-inclusive price
A common mistake is to subtract 20% of a VAT-inclusive total. That does not correctly remove VAT, because the 20% rate is applied to the net price, not to the gross total. To recover the net price from a total that already includes 20% VAT, divide by 1.2.
With the VAT-inclusive price in A2, enter =A2/1.2 to calculate the net price. If A2 is £120, the result is £100. To calculate the VAT portion directly from the inclusive total, use =A2*20%/1.2 with the same layout. For £120, this returns £20.
The VAT portion of a price that includes 20% VAT is one sixth of the gross price, because the total consists of six equal parts: five parts net price and one part VAT. Therefore, =A2/6 also returns £20 when A2 is £120. Dividing the total by six is equivalent to multiplying it by 20% and dividing by 1.2, but the longer formula can make the reasoning clearer to someone reviewing the workbook.
Use a cell for the VAT rate
A separate rate cell is useful when a worksheet has many prices or the rate might change. Enter the numeric percentage 20% in B1 and put a net price in A2. With that layout, use =A2*$B$1 to calculate VAT. For a £100 price and B1 set to 20%, the result is £20.
The dollar signs in $B$1 make the reference absolute, so it stays fixed when you copy the formula down. For the total including VAT, with the same rate in B1 and net price in A2, use =A2*(1+$B$1). A2 equal to £100 and B1 equal to 20% return £120.
Enter the rate as 20% or as 0.2. If you enter 20 as an ordinary number, Excel treats it as twenty, or 2,000%, rather than 20%. You can format B1 as Percentage using Home > Number. A rate cell also makes it easy to inspect or update the assumption without editing every formula.
Worked examples for common price layouts
These examples use pounds for illustration, but the arithmetic is the same for any currency. Excel does not infer a VAT rate from the currency symbol: the rate is whatever the formula or rate cell specifies.
- Net price of £45: with £45 in A2,
=A2*20%returns £9 VAT. The total is £54. - Net price of £250: with £250 in A2,
=A2*1.2returns £300 including VAT. The VAT portion is £50. - VAT-inclusive price of £60: with £60 in A2,
=A2/1.2returns a £50 net price. The VAT portion is £10. - VAT-inclusive price of £18: with £18 in A2,
=A2*20%/1.2returns £3 VAT and leaves £15 net.
These examples assume the entire price is subject to the stated 20% rate. Real transactions can involve different rates, exemptions, or items that are treated differently, so a spreadsheet calculation alone does not establish the correct tax treatment.
Rounding VAT and displaying currency
Excel stores numbers with more precision than a two-decimal currency display may show. A displayed value of £4.00 could therefore have additional decimal places in the underlying calculation. This matters when you add many lines: rounding every line to pennies can produce a slightly different total from calculating VAT on the combined net amount and rounding once.
For a single line, with a net amount in A2, use =ROUND(A2*20%,2) to round the VAT amount to two decimal places. If A2 is £19.99, the unrounded VAT is £3.998 and this formula returns £4.00. If you need a rounded line total, with the net amount in A2, use =ROUND(A2*1.2,2). For £19.99, that returns £23.99.
Whether to round each line or round a total depends on the applicable invoicing rules and the method used by the organisation or accounting system. Do not assume that one rounding approach is required everywhere. To change how a value looks without changing the underlying result, select the cells and choose Home > Number, then select a currency or number format with two decimal places. Formatting is different from using ROUND: formatting changes the display, while ROUND changes the formula result.
Common formula errors and checks
If a result is twenty times larger than expected, check whether the rate was entered as 20 instead of 20%. If the result is unexpectedly small, confirm that the source cell actually contains a number and not text that looks like a price. Excel may align text differently from numbers, but alignment alone is not a reliable test; selecting the cell and checking its contents in the formula bar can help.
Another frequent issue is applying the add-VAT formula to a price that already includes VAT. Multiplying a gross price by 1.2 adds a second 20% charge. Conversely, dividing a net price by 1.2 does not add VAT. Label columns clearly as Net, VAT, and Gross, and note which source figures are VAT-inclusive.
For a quick check, calculate the net amount and VAT separately, then confirm that net plus VAT equals gross. For example, £100 plus £20 should equal £120. If you use a rate cell, confirm that it contains a percentage and that copied formulas still refer to the intended rate. Absolute references such as $B$1 help prevent the rate reference from moving when copied down.
Frequently Asked Questions
What is the Excel formula to add 20% VAT?
With the net price in A2, use =A2*1.2 for the VAT-inclusive total, or =A2*20% to return only the VAT amount.
How do I calculate the VAT part of a price that already includes VAT?
With the gross price in A2, use =A2*20%/1.2. For a £120 inclusive price, the formula returns £20 VAT.
Why can’t I just subtract 20% from a VAT-inclusive total?
Because the 20% rate is a percentage of the net amount. In a total that includes 20% VAT, the VAT portion is one sixth of the gross amount, not 20% of it.
Should I type 20 or 20% into the rate cell?
Use 20% or 0.2. A plain 20 is a value twenty times larger than 100%, so it will produce an incorrect calculation if used as the rate.
Can I use the same formula for every product or service?
The arithmetic works for any item assigned a 20% rate, but not every transaction necessarily has that rate or treatment. Check the applicable rate and rules for the transaction rather than assuming every line uses 20%.
Choosing the right formula
Start by identifying whether your source figure is before VAT or already includes VAT. For a net figure, multiply by 20% to find the tax or by 1.2 to find the gross total. For a gross figure, divide by 1.2 to recover the net amount, or use the VAT-extraction formula to find the tax portion.
For a one-off calculation, a direct formula is concise. For a reusable workbook, put the rate in a clearly labelled cell and use an absolute reference so the rate can be checked in one place. In either case, label the inputs and outputs, apply an appropriate currency format, and verify the result with a simple example. VAT rates and rules vary by jurisdiction and transaction; these formulas explain the arithmetic and are not tax advice.
Related
- Excel Accounting Format: Show Negative Numbers in Brackets
- Excel Data Analysis: How to Build a Useful Model
- Remove a Data Model from Excel: Steps and Effects
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Excel Concatenate Formula Examples for Text and CellsWith a first name in A2 and a last name in B2, join them with a space using…
- Excel Cell Protection Options: Lock Cells the Right WayExcel cell protection has three main parts: mark cells as Locked or Hidden, then use Protect Sheet to…
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.