3 ways to calculate VAT using Excel - step by step

Did you know that over a million companies around the globe use the well-known suite of office tools, Microsoft Office 365? Moreover, one of the best known and most popular tools in the sector, Microsoft Excel, is used on a daily basis by around 1.2 million users.  

These figures demonstrate just how important this tool is today, hence many sectors often require workers to have intermediate or advanced Excel skills.

Above all, they're looking for know-how of some of the most commonly-used functions, such as calculating VAT using Excel. This is an application that any company or person is likely to need at some point. And for that reason, below we explain exactly what VAT is and how to calculate it easily using Excel.

What is VAT and why is knowing how to calculate it so important?

Before explaining how to use Excel to calculate VAT, it's vital to know what exactly Value Added Tax (VAT) is. 

VAT is a tax applied to the sale of goods and services, in all EU countries as well as the UK. Mind you, it is important to note that each country charges different rates of VAT. 

For example, in Spain, general VAT is set at 21%. However, in the same country there is a reduced VAT rate for certain products and services, such as food or books, which is set at 10%, as well as super-reduced VAT (4%), applied to basic necessities.

It should also be noted that VAT is not paid by the seller, but rather by the buyer. In other words, if a person buys a product costing €100, they have to pay €21 VAT, making the full price to be paid €121, with €100 being the product price and €21 being the VAT.

As we’re dealing with a tax that applies to a wide range of goods and services, it can be really handy to know how to calculate VAT in Excel. For example, if you are self-employed or own your own business, knowing how much VAT to add to the price of your products is fundamental when it comes to setting prices, issuing invoices or filing tax returns. 

But thanks to the advanced formulas featured in this office tool, you can easily automate VAT calculation in Excel, saving you time and reducing the chance of making mistakes.

3 ways to calculate VAT using Excel

Although Excel was devised as a simple spreadsheet aimed mainly at accounting, nowadays it is a comprehensive tool that can do just about everything. Its amazing versatility and infinite functions have made Excel a software used in almost every field, and calculating VAT in Excel is just one of the innumerable tasks you can perform.

Below, we explain three different ways to calculate VAT in Excel, so that you can choose whichever best suits your needs.  Additionally, with a little imagination, you can employ these formulas to optimise your financial management skills.

Basic VAT calculation using Excel

The easiest way to calculate VAT with Excel is with one of its formulas, which can be activated in a cell by typing the equals symbol (=). 

Let's look at an example to make things easier: if the VAT to be applied is 21% and you want to know the final price of a product or service that without VAT would be €50, then you need to calculate 21% of €50. Here's how:

1. In a cell, add the price of the product without VAT (A1), then in another cell (B1), divide the VAT sum by 100. In this case, in cell A1, type 50 and in cell B1, 21/100.

2. Then, multiply the price by the VAT/100. In other words, A1*B1. You can also multiply the price without VAT by 0.21, i.e., 50*0.21. It's important to press Enter to get the VAT result, which in this case would be 10.5.

3. Lastly, to this result you need to add the initial price of the product or service = (Initial Price *VAT) + Initial Price. So, the formula to calculate VAT using Excel would be =(A1*B1)+A1, giving you the total price including VAT. Following this example, type in =(50*0.21)+50. The final result will be 60.5.

Calculating VAT in Excel using %

The above method to calculate VAT using Excel could be described as rudimentary. However, there are other more efficient and practical ways to do the calculation. One such way is to use the percentage symbol (%).  To type it, press Shift + 5.

This method is similar to the previous one, but you just multiply the initial price by the percentage of VAT, i.e.:

• = Initial Price * VAT%: =50*21%=10.5
  
  
• To get the final price including VAT, you have to add the initial price to the result = (Initial Price * VAT%) + Initial Price: =(50*21%)+50= 60.5.

Automatically calculate VAT in Excel

The two previous ways of calculating VAT using Excel are great if you only need to perform a couple of calculations. But what if you need to calculate sums for 100s of products? Doing that one by one would be a waste of time, but luckily Excel features the necessary tools to automatically calculate prices including VAT.

To do this, for example, in column A, you have to enter all the prices excluding VAT. In the next column, in this case B, enter the VAT percentage you're going to be working with (21%). Lastly, after applying the formula that we explain below, in column C you will automatically see the final result.

So, to get the results in column C, you need to do the following:

1. Enter all prices excluding VAT (column A) and the VAT rate (column B).
   
   
2. Use the mouse to select the first set of prices excluding VAT, i.e., column A (cell A1).
   
   
3. Enter the multiply symbol (*) and press the cell where you have your VAT rate, in this case, column B (cell B1) =A1*B1 (=50*21%).
   
   
4. Press the F4 key to set the cell with the VAT rate and then press enter to get the result of that row.
   
   
5. To calculate VAT for your other prices, just copy and paste the formula to the cells below. Look closely and you’ll see that the cell with the first result should have a small dot in the lower right corner. Click on that and without releasing it, drag down.
   
   
6. The price + VAT of the other values will automatically appear.

As you can see, this not only saves you time by calculating multiple prices, but also makes it possible to adjust to potential VAT rate changes. For example, imagine that the product you're selling, which currently has 21% VAT applied to it, changes to the reduced rate of 10%. In this case, all you need to do is change the cell with the VAT rate (column B) and Excel will recalculate all of the prices automatically. Give it a go!

You'll soon find that calculating VAT in Excel is just one basic example of the potential the tool offers, because Excel features many mathematical functions of all kinds. That's why mastering Microsoft Excel is vital when it comes to being efficient at work.

Microsoft Excel, the tool that has democratised data management

It's common to read that Excel has democratised data management. And it's no wonder because, as a widely used tool, it provides the means to create databases, registries, tables and charts in a matter of seconds.

Efficient data management is a crucial task at any business, and while there may be more comprehensive or specific programs for this job, Excel's versatility makes it the go-to option for many organisations. In fact, over a million companies use it on a daily basis. 

Excel is undoubtedly the tool that everyone should learn to use, because thanks to digitalisation, it's only becoming more and more important. In this sense, having Excel know-how is increasingly necessary, both for professional development and to enhance job opportunities.

If you want to master office automation tools in high demand, Banco Santander is launching 5,000 seats for the Santander Course | Learn Excel, Canva, PowerBI or Photoshop.

This is a unique opportunity where you can choose one out of four 100% free online courses. At your own pace and from wherever you want, learn quickly one of the following programmes: 

• Excel: learn how to handle large volumes of data effortlessly, automate 95% of your daily tasks, create dynamic reports and other functions, whether you are a beginner or advanced level. 

• Canva: start creating posters, logos, brochures and social media content for your personal or professional projects without any design skills.

• Power BI: get familiar with Power BI, explore the proper use of DAX and hone your data cleansing skills with PowerQuery. In a practical and project-focused way, discover essential patterns, from consumer behaviour to sales trends or critical points in production.

• Adobe Photoshop CC: this course will equip you with the skills you need to successfully execute unique graphic design projects and the essential techniques for editing and retouching images to create compelling marketing materials.

All courses are available in Spanish, Brazilian Portuguese and English, and are supported by a professional online instructor to address your doubts and assist you with practical exercises. There is no cost for beneficiaries and it is not necessary to have a university degree or to be a Banco Santander customer.

Are you over 18 years and want to further develop your technological skills? Sign up for the Santander Course | Learn Excel, Canva, PowerBI or Photoshop and boost your professional and personal projects.