Hi, this is Jason and this is video number two of four videos to show you how to use
basic formulas in Excel.
In the last video I gave you a rundown of for main operators, you're going to need to
use in Excel.
In this video I'm going to show you how to use functions and apply them to formulas to
make your process a whole lot easier, especially when you're dealing with a lot of numbers.
In the previous video I gave you a quick rundown of the basics of maths in Excel, so the four
symbols: plus and minus multiply and divide.
In this video I want to talk you through how to introduce functions into your formulas
to make the process a bit easier, especially when you've got a few more numbers to work
with.
So what I have here is a simple table, okay, we've got some months going down the side,
three people going across the top and these can be whatever you want them to be, but I
want to show you how to add these up quickly.
So with what you know already, based on my previous video, you would have to say something
like Equals this plus this plus this.
Now in this case that's only three values, so that's OK, but down here you've got six
and in the real world you might have say a hundred rows and you don't really want to
be adding a hundred things together manually.
So instead of this we can use what's called the AutoSum.
Now the AutoSum is just up here on the Home ribbon, right hand side.
If you've got a smaller screen like a little laptop, all you might see here is that Greek
Sigma symbol.
It may not display the word AutoSum, so just watch out for that.
But what we're going to do here is to click AutoSum.
What Excel does is it looks around to see is there any data here I can use.
It's found some directly to the left and it's offering to add that up for you.
So I notice the structure, it says Equals SUM and SUM just means produce a total.
B2 to D2 is the range that it's picked up.
So when it's talking about range, it puts the first cell, the two dots (the colon),
and then the last cell, meaning, from this cell to this cell.
So when you press Enter, that's your total.
Now once you've done one, you can take the cell here, you'll notice there's a little
block on the bottom right corner, and if I hover over that I'll get a black cross and
when I get a black cross I can either click and drag to the bottom and that will give
me all my totals, all the rows.
I'm going to quickly undo that because there's a second way you can use this, which is again
to hover over the block, get your little black cross, but double click.
That double click is a really useful one because it works out where the bottom of the table
is for you and you can imagine if you've got a hundred or a thousand rows getting Excel
to work out where the bottom is, is a lot easier.
Now, over here I'm going to do the same kind of thing.
Now, previously I clicked a blank cell, clicked AutoSum and Excel worked out which range it
thought it needed.
But if you pre select your range, so here I'm going to choose all the data here and
I can either include the blank cell as well or not, it doesn't make any difference.
When I then click AutoSum because I've already provided the data, the total goes straight
in, so it doesn't have to ask me or confirm that this is the range it wants to use because
I've already provided it.
From here I could then take the Autofill handle, this time I'm going to drag this across to
get my column totals all the way across the table.
Now let me show you two quick techniques that will make this even quicker.
So let me just wipe these out quickly.
So we're back to where we started.
So if you want to calculate all the row totals quickly, all you actually need to do is to
select all these blank cells here in the total column and click AutoSum.
The answers go straight in and this always happens when you choose two or more blank
cell to start off with.
So obviously when you're going down it recognizes that it's working with rows and it needs to
put the row totals in.
If I was to choose the three across the bottom here, the three blank cells, and again click
AutoSum, it recognizes that it's working with columns of data and it puts the totals, column
totals in for each of those.
And if you think that's quick, there's an even quicker way.
So let me just quickly clear these out again.
And the final way of working with this is to select all of your data here plus the blank
column, plus the blank row.
That's assuming you've want totals down here and across the bottom.
So with all that selected, I click AutoSum once and all of my totals here and across
the bottom are inserted.
So when you've got two dimensional ranges of data., that's a great trick to use.
OK bonus technique for you.
So let me just wipe out this row here and what I'm going to do is to convert this table
to an Excel table or at least using the Excel table format.
So the way this works is you click somewhere in your table, doesn't matter where.
Again, on the Home tab I'm going to come across to Format As Table and just choose a design
for this gallery.
Any color, any sort of configuration you like, it all works.
Now you just have to confirm that your table has headers.
That's the top row.
So leave that ticked and click OK and your whole table here is formatted.
Now there's lots of things I could say about tables
but to keep this on topic, at the top here you have Table Tools with a Design tab.
And at any stage, if you want to, you can change the design here, but the bit I want
to mention quickly is this Total Row.
If you tick this, it adds a Total row to your table and it makes totalling really easy because
all you need to do is to, say, click on the bottom here and click the little drop down
that appears and simply choose SUM, okay, SUM being Total.
And again I could just AutoFill this across to get the remaining totals.
So I hope you found this useful.
The next video goes through some other basic functions such as AVERAGE, MAX, MI and COUNT,
and all of those functions are useful and have their place, whatever level of Excel
your at.
So stay tuned and I'll see you in the next video.