Formula to Remove the VAT in Excel

Last Updated on 27/02/2024
Reading time: < 1 minute

To remove the VAT of a price in Excel, you must consider 2 situations

1. Calculate the original price (price without the VAT)

If you have a list of prices that include the VAT, the next formula will return the price without VAT
To remove VAT (Value Added Tax) from a price using Excel, you can use a simple formula to calculate the original price before VAT

2. Calculate the amount of the VAT

This second formula will extract only the VAT amount of the price

Calculate the original price

In a cell where you want the original price without VAT, enter the following formula:

=Price with VAT/(1+Tax rate)

In our example, the formula is

=B4/(1+\$C\$1)

You divide the price of the product by 1 + VAT rate.

• For the product 1, the price without VAT is 75 😀

Extract the VAT of the price

The formula to extract only the VAT is a little bit more complex with Excel. The formula is the following:

=(Full price * Tax rate)/(1+Tax rate)

=(B4*\$C\$1)/(1+\$C\$1)

Now in column D, you have only the amount of the tax. And of course, if you subtract this amount from the price with VAT, you find the price without VAT

Price without VAT = Full Price - VAT amount

Frédéric LE GUEN

1 Comment

1. Seth
31/08/2023 @ 00:06

Sorry but it doesn't make sense to me, this works for me.

="cell of the price"-("cell of the price"/1."VAT Rate")

20% VAT
=A1-(A1/1.2)

10% VAT
=A1-(A1/1.1)

5% VAT
=A1-(A1/1.05)

Formula to Remove the VAT in Excel

Reading time: < 1 minute
Last Updated on 27/02/2024

To remove the VAT of a price in Excel, you must consider 2 situations

1. Calculate the original price (price without the VAT)

If you have a list of prices that include the VAT, the next formula will return the price without VAT
To remove VAT (Value Added Tax) from a price using Excel, you can use a simple formula to calculate the original price before VAT

2. Calculate the amount of the VAT

This second formula will extract only the VAT amount of the price

Calculate the original price

In a cell where you want the original price without VAT, enter the following formula:

=Price with VAT/(1+Tax rate)

In our example, the formula is

=B4/(1+\$C\$1)

You divide the price of the product by 1 + VAT rate.

• For the product 1, the price without VAT is 75 😀

Extract the VAT of the price

The formula to extract only the VAT is a little bit more complex with Excel. The formula is the following:

=(Full price * Tax rate)/(1+Tax rate)

=(B4*\$C\$1)/(1+\$C\$1)

Now in column D, you have only the amount of the tax. And of course, if you subtract this amount from the price with VAT, you find the price without VAT

Price without VAT = Full Price - VAT amount

1 Comment

1. Seth
31/08/2023 @ 00:06

Sorry but it doesn't make sense to me, this works for me.

="cell of the price"-("cell of the price"/1."VAT Rate")

20% VAT
=A1-(A1/1.2)

10% VAT
=A1-(A1/1.1)

5% VAT
=A1-(A1/1.05)