# How to remove VAT from a price in Excel

Divide the gross price by 1 plus the VAT rate: `=B2/(1+$F$1)`. With 20% VAT, 120 becomes 100.

**Excel:** `=B2/(1+$F$1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Item | Price incl. VAT | Price excl. VAT |  | VAT rate | 0.2 |
| Printer | 120 |  |  |  |  |
| Ink set | 59.99 |  |  |  |  |
| Monitor | 360 |  |  |  |  |
| Cable | 18 |  |  |  |  |
| Laptop | 1499 |  |  |  |  |

Formula in C2, filled to C6: `=B2/(1+$F$1)`

Result: $100.00; $49.99; $300.00; $15.00; $1,249.17

## How it works

A VAT-inclusive price is the net price × 1.2. Dividing by `1+$F$1` reverses that: 120 / 1.2 = 100. The rate cell is locked with $ so every row uses it.

## Other ways

- `=B2*(1-$F$1)`: Takes 20% off the gross price, which is the common mistake. (Gives 96 for the printer instead of 100: 20% of the gross is more than the VAT in it.)

## Common mistakes

- Subtracting 20% of the gross price removes too much. VAT was added as 20% of the net price, so divide.
- Round each line to the penny if your invoices do (`=ROUND(B2/(1+$F$1),2)`); otherwise line totals and the overall total can differ by a cent.

## Questions

**How do I get the VAT amount in the price?**

Subtract the net from the gross: `=B2-B2/(1+$F$1)`.

**How do I add VAT to a net price?**

Multiply: `=C2*(1+$F$1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remove-vat-from-a-price