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

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you might need to calculate a product’s actual price for calculation advantages. The actual price is required to remove other costs from the price. For that reason, you need to deduct the VAT from the price. It’s easy to solve in Microsoft Excel with formulas. In this article, I am sharing with you the formula to remove VAT in excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Easy Methods to Remove VAT Using Excel Formula

In the following, I have shared 3 easy and simple formulas to remove VAT in excel.

Suppose we have a dataset of some Products, the Total Price of those products including VAT, and the VAT Percentage. Now we are going to remove VAT using formulas in excel.

Remove VAT Using Excel Formula


1. Use Divide Formula to Remove VAT in Excel

The simplest way to remove VAT is using a divide formula between the price and VAT percentage. Follow the steps below to do so-

Steps:

  • Choose a cell. Here I have selected cell (E5).
  • Apply the formula-
=D5/(1+$G$5)

Use Divide Formula to Remove VAT in Excel

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

  • There we have our exact price removing the VAT from the total price.

2. Calculate VAT and Remove from Total Price

Sometimes you might require to find the actual VAT price over the total price. After that, you can remove the VAT value from the price. In this method, we will learn how you can calculate the VAT and then deduct it from the price.

Step 1:

  • Select a cell to calculate the VAT. Here I have selected cell (E5).
  • Put the formula down-
=(D5*$H$5)/(1+$H$5)

Calculate VAT and Remove from Total Price

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

  • Now we have the VAT price for each product.

Calculate VAT and Remove from Total Price

  • Let’s remove the VAT price from the total price to achieve our desired output.

Step 2:

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

Calculate VAT and Remove from Total Price

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

  • Thus we will get the price excluding VAT in a new column.

Calculate VAT and Remove from Total Price

Read More: How to Calculate VAT from Gross Amount in Excel (2 Examples)


3. Perform ROUND Function to Remove VAT in Excel

You can also use the ROUND function as this function rounds the number from a given string. Using the ROUND function we will calculate the VAT price first and secondly, a simple deduction formula will fly us to the destination. Stay tuned.

Step 1:

  • Here I have selected cell (E5) to write the formula down-
=ROUND((D5*$H$5)/(1+$H$5),2)

Perform ROUND Function to Remove VAT in Excel

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

  • Now we have our VAT price excluded from the total price.

Perform ROUND Function to Remove VAT in Excel

Step 2:

  • Go to a different column a select a cell (F5) to apply the formula.
=D5-E5

Perform ROUND Function to Remove VAT in Excel

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

  • Here we have our result removing VAT using formulas.

Perform ROUND Function to Remove VAT in Excel


Things to Remember

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

Conclusion

In this article, I have tried to cover all the simple formula to remove VAT in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram
Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo