Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Formulas to calculate sales tax

Daniel Walter Scott || VIDEO: 8 of 24

Download Exercise Files Download Completed Files

Comments

Video transcript

In this video, we're going to add our amounts that has the dollar signs. Great! Then we're going to make an option where we add something to our quote. Say, adding an 'InDesign Course'. Then we add some money to it. It's going to add them all at the bottom for our 'Subtotal', then it's going to calculate the tax, and give us our total all together. So let's go and do that now in this video.

First thing is, we're going to put in some dummy text. So over here, it's going to be 'Excel Course', and I'm going to charge you, let's say, $300. Now, at the moment it doesn't have the currency symbol, so let's change that. So, instead of selecting just this one, I want it to be for all of them, so we're going to select all of these by clicking, and dragging across all of them. And then at the top, where it says 'General', under 'Home', drop that down, there's a basic one in here called 'Currency'. You might be lucky, and it might give you the right symbol. Mine's given me Pounds. I don't want English Pounds, I want either Euros or Dollars, so you go and change it. And there is a drop down here, to these options, but if you want the full list, drop this down, all the way down the bottom where it says 'More Number Formats'. Be on currency. And you can see, here's the symbol that I want. You can drop it down, there is a bunch in here.

So, depending on where you are in the world, you'll find your option in here. I'm going to find 'English'.

And I'm going to use 'English' but 'United States'. Great! Let's click 'OK'. It's applied a currency format.

It hasn't changed the number at all, just knows that it's the dollar signs.

Next thing I like to do is, I'd like to do the 'Subtotal'. 'Subtotal' is really easy, just all of this added together. So what we'll do is, we'll put in another one. Let's say you want to do a Word course as well.

I've got a Word course, go check that out. And this one here is cheaper, I'm not sure why.

One thing we'll do is, currency is right aligned here, I want them all to be right aligned. So select it all, 'right align'ed. Just to line up with these totals down the bottom here. So our first bit of formula worked.

Just to add everything in this column, and put it here in the 'Subtotal' where we had the text.

Next, we're going to do something called 'Autosum', the most common and easiest Excel formula to do.

And it appears in lots of different panels. You can kind of see, I've got it selected down here. He's down the bottom here, nice and small. If you've got a smaller screen you might have to go to 'Formulas'. Here he is, right at the beginning. Just click 'Autosum', and it's gone up, and reached up, and says, "Would you like all of this?" And it's close.

So I'm going to say, "Kind of, I want you-- actually I'm going to drag across them all above these. There's no point having that one in there. So, just drag across all the ones you want. You can manually type it in here. Can you see, it's running from column I, along the top, 18, which is there, so I18, all the way down to I35, which is this last option here. I'm going to click return on my keyboard. And you can see there, it's added it up. It's added the dollar sign, as well as made it the right currency. Awesome Excel stuff. That's it for adding things up using 'Autosum'.

Let's calculate the 'Tax' next. So, to calculate 'Tax', click on the 'Tax'-- you might call it VAT, or GST or Sales Tax, whatever you want to call it. We're calculating some sort of tax, right? Click in this cell. And up here, in our 'Formula' bar, we're going to start with equal, '='. This is just telling-- this is really important, it tells Excel we are not typing a word, we are typing our little formula. And what I want to do is, I want to take this 'Subtotal' here. So I'm just going to click off with my mouse. You can see, it's added to there automatically. I can just type it in. And I want to times, '*' this with my tax rate. Tax rates can't be typed in as percentage in formula. It needs to be a decimal place. So it's going to be zero point something.

And you need to find out what it is for your country, or state in your country. Where I'm at, at the moment, Ireland, it's 23%. And if I hit 'return', I have that 'Tax'. And, in New Zealand it's 15, which I thought was high, went up to that from 12.5; 23 is a lot higher. And if you're in some states of America,

I know it's 7.5.

And this is, I guess it's a little bit interesting, you need to put in a little zero, '0'. '75'. If you don't, and if you put in just '75', that's going to be 75% tax. That's pretty high. So it needs to be '0'. '0.075'. Now it will give you 'Tax'. Nice!

Let's add these two together for our 'Total'. So I'm going to click in this 'Total' cell. And I'm going to-- there’s a couple of ways I can do it. I can do 'Autosum', or I can just do '=’. And I'm going to say, you…

Can you see, it picks it there, I'm going to hit plus, '+'. And I'm going to click you, and then I'm going to hit 'return'. And that’s going to give me my 'Total'. My 'Subtotal' plus my 'Tax' equals this. Happy days.

Let's see if it works. Let's say you've done this, you're going to now do my Photoshop course. I've got one of those too. But that's super awesome, and it's super expensive. Say, it's '700'. You can see, it's calculated it. Calculated the 'Tax', and has my 'Total'. Happy days.

All right, let's get into the next video. We will look at adding and removing these columns in case we have extra stuff that needs to fill it in. Let's go do that now.