Formula for Adding VAT in Excel (Apply with Easy Steps)

It is one kind of tax paid by the buyer. This is calculated based on the price of a product, then add to the product price. In this article, we will discuss the steps of how to add VAT in Excel using a formula.

Table of Contents hide

Download Practice Workbook

What Is VAT?

Formula for Adding VAT in Excel and Its Application

📌 Step 1: Create Price List with VAT Rate and Form a Table

📌 Step 2: Calculate Corresponding VAT for Each Product

📌 Step 3: Add VAT with Selling Price

💬 Notes

Conclusion

Download Practice Workbook

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

Formula for Adding VAT.xlsx

What Is VAT?

VAT stands for Value Added Tax. This tax is paid by the user instantly when purchasing any product. This is an indirect tax, as it has no direct relation to the taxpayer’s income.

Formula for Adding VAT in Excel and Its Application

The formulas related to VAT are as follows:

VAT = VAT Rate x Original Price

Price Including VAT = Original Price + VAT Rate x Original Price

Price Excluding VAT (Original Price) = Price Including VAT / (1+VAT Rate)

Now, we will discuss how to easily and effectively use a formula for adding VAT in Excel.

Follow the steps below.

📌 Step 1: Create Price List with VAT Rate and Form a Table

In this step, we will insert data and then form a table in the dataset.

First, insert all data in the dataset.

Select all cells of the dataset.
Go to the Insert tab.
Select the Table option from the Tables group.

The Create Table window appears.
Our selected range is shown here. We can modify our selection from here.
Check My table has headers option.

Finally, press OK and look at the dataset.

As the table formed successfully, the Filter symbol is showing.

Now, go to the Table Design tab.
Uncheck Filter Button and Banded Rows options.

Our dataset is ready now.

📌 Step 2: Calculate Corresponding VAT for Each Product

In this step, we will calculate VAT to add to the price.

We put a formula on the VAT column to calculate VAT. Go to Cell E5 and put the following formula.

=[@Price]*[@[VAT Rate]]

Now, press the Enter button.

As we applied the Table feature, no need to use the Fill Handle option. Automatically VAT is calculated on the rest of the cells.

📌 Step 3: Add VAT with Selling Price

In the last step, we will add VAT with the Price in Excel.

We add VAT and Price on Cell F5. Put the following formula there.

=[@Price]+[@VAT]

Now, press the Enter button.

Finally, VAT is added to the Price.

We can apply an alternative formula to add the VAT with the Price.

You can try this formula.

=[@Price]+[@[VAT Rate]]*[@Price]

Here, we multiply Price with VAT rate and add that value with Price in the same formula. We get the same result.

You can also apply this formula.

=[@Price]*(1+[@[VAT Rate]])

Add 1 with the VAT Rate and multiply the result by the Price value.

💬 Notes

Here, we used the Excel Table feature for some reasons. If we want to insert new data, we do not need to copy the formula again and again. Press the Tab button and the table will expand and enter new data.

Conclusion

In this article, we described all the steps to add VAT with Price using Excel Formula. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.