Remove VAT in Excel and Google Sheets
Choose the cell that holds your price and the VAT rate. Copy the formula into Excel or Google Sheets.
=A2/1.2=A2-A2/1.2=A2Works in Excel / Google Sheets.
Excel formulas for VAT at 20%
Assuming the price is in cell A2:
| You want | Price in A2 is | Formula |
|---|---|---|
| Net price (remove VAT) | VAT-inclusive | =A2/1.2 |
| VAT contained in the price | VAT-inclusive | =A2-A2/1.2 or =A2/6 |
| Net price, rounded to the penny | VAT-inclusive | =ROUND(A2/1.2,2) |
| VAT to add | net | =A2*0.2 |
| Gross price (add VAT) | net | =A2*1.2 |
For 5% VAT, replace 1.2 with 1.05, 0.2 with 0.05 and 6 with 21.
Using a rate cell
Put the VAT rate in its own cell so you can change it once for the whole sheet. With 20% in E1:
- Net price:
=A2/(1+$E$1) - VAT:
=A2-A2/(1+$E$1) - Rounded VAT that always adds up:
=A2-ROUND(A2/(1+$E$1),2)
Fill the formula down the column by dragging the small square at the bottom right of the cell, or by double-clicking it.
Working out VAT for a whole column
If column A holds VAT-inclusive prices from row 2 to row 100:
- In B2, enter the net formula, e.g.
=ROUND(A2/1.2,2). - In C2, enter the VAT as the difference:
=A2-B2. - Select B2:C2 and fill down to row 100.
- Total each column with
=SUM(B2:B100)and=SUM(C2:C100). Net plus VAT will match the total of column A exactly.
Working out the VAT as the difference, rather than with a separate formula, is what keeps every row adding up to the penny.
Common mistakes
- Multiplying by 0.8 to remove 20% VAT. This takes 20% off the total, which gives a net price that is too low. Always divide by 1.2.
- A rate stored as 20 instead of 20%. Then (1+E1) becomes 21. Format the cell as a percentage or divide by 100.
- Rounding only for display. Changing the number format hides decimals but doesn't remove them, so column totals can disagree with the rows. Use ROUND when the values must add up exactly.
Frequently asked questions
What is the Excel formula to remove 20% VAT?
If the VAT-inclusive price is in A2, use =A2/1.2 for the net price and =A2-A2/1.2 (or =A2/6) for the VAT.
How do I remove 5% VAT in Excel?
Use =A2/1.05 for the net price and =A2-A2/1.05 (or =A2/21) for the VAT.
Can I keep the VAT rate in its own cell?
Yes. Put the rate in a cell such as $E$1 formatted as a percentage (20%), then use =A2/(1+$E$1). The dollar signs keep the reference fixed when you fill the formula down. If the cell holds 20 rather than 20%, use =A2/(1+$E$1/100).
Why does my VAT column add up to a penny more or less than expected?
Excel keeps full precision behind the displayed value. Wrap the formula in ROUND, e.g. =ROUND(A2/1.2,2), so each line is a whole penny, and work out the VAT as the total minus the rounded net price so the two always add up.
Do the same formulas work in Google Sheets?
Yes. The formulas on this page work unchanged in Google Sheets, Excel for Mac and Windows, Excel online, LibreOffice Calc and Apple Numbers.