How do I add multiple VAT rates into the same spreadsheet? Now if you've been following
along with this Excel accounts tutorial series, you'll know from the last tutorial
how to calculate your VAT, the net from the gross and the gross from the net.
But how do you actually apply it into a column into your account spreadsheet? This is what
we're going to do now! I'm Simon a real life Excel trainer, and this is Computer Tutoring.
So this is the spreadsheet that we're going to be using and we're going to add in these
multiple rates of VAT, now currently in the UK there are three rates of VAT due to the recent
pandemic there's the twenty percent rate, five percent rate, and zero percent rate, and we're
going to look at adding them into this spreadsheet , so I'll give you the link if you want to follow
along. Now the first thing that we're going to do is insert a column to add the multiple rates,
so I'm going to go up into right click on column C so you see here I've right clicked on column C,
and I can go down to and click on insert just there, and that will insert a column to the left.
in this one here I'm going to type in VAT rate, now what you can do is you can just type in the values
you can type in, I got 20% for doing Mr Bush's roses and 5% for gloves and 5% for weed killer,
now I just got to say that I wouldn't take this as gospel here, I would go and check in
the HMRC website so you can actually get the correct rates for whatever business that you're
looking at. Now the problem is with typing them in is it's so easy to make mistakes,
so I could for instance inadvertently type 5 in and leave the percent out when I press enter,
then in the world of Excel, Excel will think that's 500, every one represents 100 percent,
or I could just type in an erroneous amount accidentally. So what I would like to do is to know
how to add predetermined figures in Excel to drop down list in which I can select the zero, five
or twenty percent. And this is how you do it. First let me just delete these percentage values here,
and then I'm going to select all of the cells all the way down to let's say 119 here, to which I want
to apply these predetermined values then I'm going to click on the data tab here at the top,
just make sure I've got my things, so there we go, so this data tab here at the top
give that a click and then over on the right hand side there's an option here that's called
data validation, in fact if I just hover over that and zoom in
you can see it pops up data validation, and give it a click. Great so this little box here pops up the
data validation box it's getting closer you can see that, now what I can do here is I can select
lists which will allow me to put these predetermined values in here. Now I'm
interested in three values I'm going to type zero percent comma five percent comma twenty percent, so
now I can only type one of those three values into the cells I've selected on my account spreadsheet.
I click on ok and now you can see I have this little drop down list to the right hand side,
and if I click on this drop down list here I can see I can choose 0 5 or 20 percent.
So what I can do now is I can select the 20 here from Mr Bush's roses and say it was
five percent for gloves if I accidentally make a mistake and type a number in you notice, here
I've got this error message here, nice and clear and it doesn't allow me to type in an erroneous
value. I'll either retry to type in a number in which is obviously going to be wrong, cancel it
I'll choose a number from the drop down list or I can type the number in if I want to as well.
Now I'm sure you don't want to watch me putting all these figures in so I'm just going
to pause it and then we'll come back and see how we can calculate the different VAT rates. So now
you can see I've added that last one pruning there, with the percentage rate. So how do I use this in
the calculation? So let's go up to the top here I'm just going to adjust the width of this bring this
in a little narrower. So say for instance this here is my gross, let me type that in at the top
and I want to know how much was 20% of that, you know before the 20% was added, let's just
right click and go to insert, I'm going to click on here and type in here the VAT amount,
press enter here. So the calculation goes this way and if you want to know how or a little bit more
detail about how this works then I'll give you the link so you can follow along, but basically if you
just want it to work this is their calculation =E6 -(E6/(1+C6))
press enter and I can see I've got
the amount of £8.33. So fantastic now we've got the amount of VAT that we need to pay.
But that was the gross we've got the VAT amount, now we need to work out the net.
So let's insert a column here, and we're going to type in the word Net, and to work that out
it's going to be a simple formula of taking the gross minusing how much VAT was paid.
We can see that that resulted amount is £41.67. Now I hear what you're asking, you're
wondering how do I make a formula apply to a whole column in a spreadsheet? Well if you want
to make your formula apply to the whole column, you can use something that's known as autofill.
So you notice here, if I select these two because I want these two formulas to apply all the way down
to this bottom row 119, what I'm going to do is just draw your attention to this little dot in the
bottom left hand corner, sorry right hand corner, I get my left and the rights correct!
So as I hover my mouse over it you see I get a solid black cross. You need to
make sure you see that black cross, if you see a cross with arrows on it, that's a no-no, if you
see a white cross that's a no-no, you want to make sure you see the solid black cross, and when you do
you can either drag all the way down to the bottom by clicking and holding your mouse or
double click on the mouse, and notice what happens. When I've double clicked it's taken that
formula and it's applied it to the entire column. Brilliant. So next thing I want to do
is make this easier to read. I'm just going to color it so if I go up to the top here start on E2
drag all the way down to G119 like so, and then I'm going to go back to the home tab here at the top,
click on this little paint bucket drop down menu, let's have a look and see what colors I've got.
I'm gonna go for this sort of second down green green accent six lighter sixty percent, let's go,
and there we go, so I know that this part of the account the green part is the money that I need
to pay out, so you're wanting to know the answer to the question how do I know how much VAT I have
paid on a product? This is how you do it. So firstly we're going to insert another column in here
let's do that, and let's have a look here and we're going to say VAT paid,
so we know how much VAT paid, now we're going to go to row 7 because I haven't got anything in row 6
and I just want to show you it working, and then we can copy that formula down the entire
column. So let's just zoom in here get really close so you can see the formula, so again, if you need
to know more details I'll give you the link of how this works, but it's exactly the same as the other
one. So =D7-(D7/(1+C7))
There we go, so if you want to know how much
of an amount is VAT or how much of gloves was VAT, I can press enter I can see that of that £5.00,
£0.24 of it is VAT. So now I can use the auto fill to copy that formula up, and then if I go down
I want to auto fill it down or copy the formula down to the entire column, make sure I see the
black cross and give my mouse a double click and it will automatically apply that all the way down
to the bottom. So now I want to make this easier to read. I'm going to color this section blue.
So just going to highlight from D2 go all the way down to D119 there we go and then click on my
drop down list here, let's get really close and I'm going to choose the second down blue there, that's
good, excellent. Okay looking good. So the next thing I need to do is start totaling up my expenses. How
much VAT I can reclaim, how much I need to pay and maybe my gross and my net might be handy for the
VAT man. All right, so let's just get rid of these. I want to show you how I did them. So we'll
clear all of those totals. So I want to know expenses, I want to know the VAT to reclaim,
I want to know the gross, I want to know the VAT to pay and I'll have the net amount as well.
So I'm basically going to use auto sum to sum up all of the values. So start off with an equal sign
type in the word SUM and if you look very closely you can see that excel has automatically
picked out the sum, if you're doing it this way the way I like to do it, I press tab on the
keyboard and what it does is it completes the formula, puts it in capitals, and puts a nice
little bracket, it's all ready for me to select my expenses column, so let's do that, so if I select my
expenses column all the way down to 119 and then press enter to finish and I can see my expenses
are £72.95, okay so let's just do the same with the VAT amount so equals SUM tab
and then I can copy all the way down here, and I'm pretty much going to do this for each
one of these here, the same with the gross equals SUM, tab and then we just add up the gross here,
press enter to finish, and then the VAT to pay,
SUM, tab and then and drag all the way down, notice when I get to the bottom the next thing I do after
I've highlighted cell 119 or G119 I just press enter on the keyboard, I don't have to go back up
to the top it will do it for me. I do have to scroll up a little bit and then I can see
these amounts here, great. And the last thing the net, the formula will be basically taking the gross
or subtracting the VAT to pay from the gross and I've got the net amount. so there we go. That's
totaling up the VAT that you can reclaim and the VAT to pay. we're nearly done! the last thing we
need to do is update the running total formula. what do I mean? what's wrong with it? Well if I
double click here you can see that the formula is taking into account across these cells here,
and it's going to muck it up with an expense and the VAT paid. I basically want to have the running
total running on the expenses and the gross. So what I'm going to do with this is just highlight
the formula and backspace back to the bracket, I'm going to do the gross first, so click on F6
and then all I'm going to do is type a colon on the keyboard, and close the bracket, and you can
see if I come into the formula just here that it's got F6 to F6 which seems really peculiar, why would
you add up the same cell? Well that's because we're going to copy that formula down the entire column ,
and I want the first part of this formula, so basically I want this part of the formula
to remain fixed, so how do I do that?
So you remember I can use the dollar signs $, let's just get back there, if I do shift and the dollar sign
it will fix the first part of the formula, so that when I copy that formula down the entire column
then it will be F6 to F6, F6 to F7, F6 to F8 and so on, and I'll show you a bit more about that after
I've completed the formula. So now what I need to do, is I need to just add a little bit on here, so
I'm just going to add and then SUM open bracket and I want to sum the expenses one, which is
D6 type in a colon, and close that off, and I'm going to pop a little dollar $ in the front bit
there because I want to fix this part here, great. So let's just press control and enter to enter that
formula, and then what I can do is, I can double click to auto fill that down. So first things first.
Let's just explain a little bit about that dollar sign, just very briefly, I cover it in an earlier
video as well, but if you can see I got this is F6 to F6 but because of the dollar sign, when I
go to the next one this will be F6 to F7, D6 to D7 and if I go down here F6 to F8, D6 to D8. You see
the first part of the formula is fixed the second part is relative, so you can copy that formula down,
that formula will apply to the entire column there, and as you can see the running total is
working fine, you can see I've got the £50 here, I've spent the £5 here and it ends
up with £45. It absolutely looks great and the last thing I'm going to do is I'm just going to do
the white and the gray alternate rows. So I'm just going to click on, I've clicked on 45 and I'm
going to choose a gray color yeah it looks like it's matching, I highlight these two here, I click
on format painter at the top, and ctrl shift down arrow, quickest way to get to the bottom, and as you
can see it does alternate alternating rows white and grey white and grey all the way to the bottom.
Fab okay, now you know how to add predetermined figures in excel by means of drop down lists,
you can also answer the question how do I add different vat rates into the same spreadsheet?
You also know how to make a formula apply to a whole column in a spreadsheet,
and finally you also know how to work out how much of an amount is VAT.
If you've got anything out of this Excel accounts tutorial please give us a thumbs
up give me a thumbs up it means so much. Our next tutorial video will be looking at summary data .
So we've got the totals at the top here, know how much VAT to pay and our expenses etc, but we don't
know how much we got from Mr Bush or each of our customers or how much money we've made in
a particular day or month or year, and that's what we're going to look at next time! So make sure you
click on that subscribe button and click on that notifications button so you don't miss a thing!
If you're on twitter or on Instagram please reach out to us, it'll be great to hear from you!
So I'm just hoping that everybody's keeping safe, and I want to say thank you so much for
watching!