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.


Download Practice Workbook

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


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.

Price List with VAT Rate and Form a Table in Excel

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

Price List with VAT Rate and Form a Table in Excel

  • 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.

Price List with VAT Rate and Form a Table in Excel

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]]

Calculate Corresponding VAT for Each Product in Excel

  • 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]

Add VAT with Selling Price using Excel Formula

  • 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]

Add VAT with Selling Price using Excel Formula

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 VAT with Selling Price using Excel Formula

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.

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo