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.
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)
- 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)
- Hit the Enter button and pull the “fill handle” down to fill.
- Now we have the VAT price for each product.
- 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
- 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.
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)
- Press the Enter button and pull down the “fill handle”.
- Now we have our VAT price excluded from the total price.
Step 2:
- Go to a different column a select a cell (F5) to apply the formula.
=D5-E5
- Press Enter and drag the “fill handle” to fill all the cells.
- Here we have our result removing VAT using formulas.
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.