Excel VAT Formula - Add Different VAT Rates in Same Spreadsheet

Apply an Excel VAT formula with different rates

See a simple Excel VAT formula you can apply to your accounts spreadsheet. In this tutorial we will answer:

• How do I add different VAT rates into the same spreadsheet?
• How to make a formula apply to a whole column in a spreadsheet?
• How to work out how much of an amount is VAT?

If you want to follow along then you are more than welcome to download the starting exercise file. If you want the completed template, then it's at the bottom of the page.

This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everyday Excel hints and tips.

How do I add different VAT rates into the same spreadsheet?

Adding different VAT rates into the same spreadsheets is possible so long as you structure the data in the way below:

Insert a VAT Rate Column

Insert Column for VAT Rates - 1:00

First thing to do is to insert a column into which we will place the VAT rate. Right click on column C and select Insert.

Add VAT Rate as the column header.

How to add predetermined figures in Excel?

Add Predetermined Figures in Excel - 1:38

Why should you know how to add predetermined figures in Excel? The reason is that it is so easy to make mistakes. Imagine if you enter the wrong percentage or even forget to add the percentage sign. Your figures could be totally out.

To prevent that from happening you can add predetermined figures in your accounts spreadsheet. That way you can select 0%, 5%, or 20% from a drop-down list. This very same list will also prevent you from entering in any other value.

1. Select cells E6:E119. (All the cells you want to see has drop-down lists).
2. Click Data - Data Validation
3. Choose List from the Allow Drop-down list.
4. In the source box enter:0%,5%,20%. (No spaces) Then click OK.
5. Enter the VAT rates using the drop-down lists.

There you go! All you now need to do is to adjust the width of the column and finish entering all of the VAT rates.

I would also like to mention that the amounts in the tutorial are just my guess and are in no way reflective of actual VAT amounts. If you would like to know them I would suggest you look at the UK governments guidance on VAT rates on different goods and services.

Excel VAT Formula - Net

Excel VAT Formula - 4:40

Now it's time to use the VAT rate we've entered to work out how much VAT you have paid on a product. Insert a column to the right of Received and title it VAT Amt.

In the cell below enter the following VAT formula:

=E6-(E6/(1+C6))

Insert a column to the right, title it Net and enter the following to work out the Net:

=E6-F6

How to make a column apply to a whole column in a spreadsheet?

Make a Formula Apply to a Whole Column in a Spreadsheet - 5:40

Select Cells F6 and G6 then copy the formula using Auto-fill and drag down with the mouse to make a formula apply to a whole column in a spreadsheet. To make this process quicker, you can double-click on the small dot in the bottom right corner.

Apply a light green colour to cells E2 to G119.

How to work out how much of an amount is VAT?

Next thing to do is to work out how much of an amount is VAT. In this case depending on our purchases will need to determine how much money have we already paid on our purchases depending on whether the VAT amount is 5% or 20% VAT.

Insert a column to the right of the Expense column and title it VAT Rec. (This will be the column we will add up to calculate how much vat we can claim.

In cell E6 enter the following formula:

=D6-(D6/(1+C6))

Use Auto-fill to make the formula apply to the entire column.

Change the background colour of cells D2 to E119 to light blue.

Just a couple of more things to do. One is to sum up the VAT amounts and display the totals at the top. The other is to update the running total formula.

1. Select Cells A2 to I4 and press delete on the keyboard to remove the current totals.
2. Enter the following as labels:
   • In cell D2 enter Expenses
   • In cell D3 enter VAT to Reclaim
   • In cell F2 enter Gross
   • In cell F3 enter VAT to Pay
   • In cell F4 enter Net
3. Now we add in the formulas to add up all the VAT and other amounts:
   • In cell E2 enter =SUM(D6:D119)
   • In cell E3 enter =SUM(E6:E119)
   • In cell G2 enter =SUM(F6:F119)
   • In cell G3 enter =SUM(G6:G119)
   • Finally in cell G4 enter =G2-G3

Sum Different VAT Amounts - 9:15

Now the last thing is to update the running total formula as currently the formula is reference the wrong cells.

Make a Running Total in Excel - 11:24

1. Click in Cell I6 and enter the following formula:
   =SUM(F$6:F6)+SUM(D$6:D6)
   
   
2. Use Auto-fill to make the running total formula apply to the whole column.
   
3. Now to apply the alternate grey and white rows to the running total column. Change the background colour of cell I7 to grey.
4. Select Cells I6:I7
5. Click the format painter button.
   
6. Hold down Ctrl & Shift then tap the down arrow to apply the alternate row format down to cell I119.
   

Well done if you have follow the step by step instructions down to this point. As you can see creating a VAT spreadsheet that will allow multiple rates of VAT is possible.

Here's the completed file should you feel that it's all a bit much.

Other Excel Accounts Tutorials