How to remove VAT from a price in Excel

Q: How do I get the VAT amount in the price?

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

Formula (Excel and Google Sheets)

=B2/(1+$F$1)

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

Result on the example: one result per row in C2, filled down to C6
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

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.

=B2/(1+$F$1)

Result100

B2$120.00
The value in B2.
$F$120%
The value in F1.
1+$F$11.2
The + operator adds.
B2/(1+$F$1100
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =B2/(1+$F$1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=B2*(1-$F$1)	$96.00, $47.99, $288.00…Different result	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Remove VAT from a price

In C2, work out the price without VAT, using the rate in F1, then fill down to C6.

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

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.