# How to Remove VAT Using Excel Formula (3 Simple Methods)

### Method 1 – Use Divide Formula to Remove VAT in Excel

• Select a cell. I have selected cell (E5).
• Enter the following formula:
`=D5/(1+\$G\$5)`

• Press Enter.
• Drag the “fill handle” down to fill all the cells.

• It will give the price after removing the VAT from the total price.

### Method 2 – Calculate VAT and Remove from Total Price

Step 1:

• Select a cell (E5).
• Enter the following formula:
`=(D5*\$H\$5)/(1+\$H\$5)`

• Hit the Enter button and pull the “fill handle” down to fill.

• It will output the VAT price for each product.

Step 2:

• Choose a cell (F5) and apply the subtraction formula-
`=D5-E5`

• Click Enter.
• Drag down the “fill handle” to get the output in all cells.

• You’ll get the price excluding VAT in a new column.

### Method 3 – Perform the ROUND Function to Remove VAT in Excel

Step 1:

• Select a cell (E5) and add the formula-
`=ROUND((D5*\$H\$5)/(1+\$H\$5),2)`

• Press Enter and pull down the “fill handle”.

• The VAT price will be excluded from the total price.

Step 2:

• Go to a different column a select a cell (F5) and enter the following formula:
`=D5-E5`

• Press Enter and drag the “fill handle” to fill all the cells.

• It will remove VAT.

## Things to Remember

• Don’t forget to use absolute reference (\$) inside the formulas as VAT 15% is used for every cell in the dataset.

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF