Hello again and welcome back to our course on Excel 2016 Advanced.
We’ve been looking at Functions for the last couple of sections and in this section
I’d like to look at the AutoSum Functions which you’ve probably used before but you
may not be aware of some aspects of their use that can make them a very quick and efficient
way of dealing with straightforward requirements.
Let me show you the sort of thing that I mean.
Let’s suppose that I wanted to add the numbers there in column B. Now you might be tempted
to select the numbers in column B and then insert the AutoSum function as part of the
formula in B6.
But in fact you don’t need to select the numbers first.
If you just click below the column of numbers and then go up to the Ribbon, either the Home
tab or the Formulas tab, both of them have an AutoSum button on them.
I’m on the Home tab, click on AutoSum and a formula is placed in B6.
And what Excel 2016 has done is to work out what it believes is the range of numbers that
I’m trying to sum.
If I’m happy with that all I need to do is to click on Enter and there is my total.
Now of course if instead of doing that I selected those four cells first, note that I haven’t
selected B6 in this case and clicked on AutoSum.
That still gives me the same result.
Let me Undo that.
Note that if I select B2 right down to B6, click on AutoSum it achieves the same effect
as well.
And once again let me Undo that.
And another point to bear in mind is if I select say B2 to B5 the Quick Analysis tool
also has a Totals tab.
And if I wanted to put the sum in using that rather than the AutoSum button I get the same
result and of course the formula is exactly the same as well.
Now let me Undo that and this time what I’m going to do is to select the sales from B6
to C6 and click on AutoSum.
And in this case of course it can sum both of those columns.
It puts the formula in B6 for column B and the equivalent formula in C6 for column C.
Now of course this doesn’t only work for columns.
If I wanted to deal with rows, let me click in G2.
Click on AutoSum and I get the sum from B2 to F2.
So it works on rows as well.
Let me just cancel that and delete the contents of cell F5.
What would happen if I clicked the AutoSum button while cell F5 is selected?
And the answer is it gives the sum from F2 to F4.
Where AutoSum has an option of dealing with a column or a row it always gives precedence
to columns.
So that’s an important point to remember as well.
So you might be interested to see what happens if I select B2 to F5 and click on AutoSum.
Note it’s the column totals that I get.
And what if I select B2 to G6?
I get all of the totals.
And the 150 at the bottom right here of course is the sum from B6 to F6.
Now another important point here is that Excel 2016 is pretty good at identifying entities
that aren’t part of what you’re trying to do.
So for instance, if in B1 I had a header, say these were supposed to be some sort of
month figures, let’s put a set of headers in.
If I wanted to sum B2 to B5 without making a selection that’s no problem.
Excel realizes that B1 is not part of this.
And in fact even if I select B1, so I’m selecting B1 to B5, it can cope with that
as well perfectly well.
And the other point I’d like to make here is that so far I’ve been dealing with sum
functions but of course the same is true if instead of doing the sum of those numbers
I want, for instance, a Count of those numbers.
So as you can see there the count is four.
Or in the next column let me select C1 to C5 and let me go for the Max.
And of course the max number there is eight.
And another point to remember here is that if in a specific situation or in fact if in
general you find yourself say using the Max function a lot then you always have the option
of adding that to the Quick Access Toolbar and there’s a very easy of doing that.
If I click on the AutoSum button up there on the Home tab and right click on Max and
say Add to Quick Access Toolbar it appears on the Quick Access Toolbar with that fairly
neutral sort of icon there.
Note when I hover over it it gives me a screen tip of Max.
And anywhere that I want to use the function it then just becomes very, very easy to use
it.
So that’s it.
That’s some general points about the AutoSum family of functions.
Now in the balance of the course, as I’ve said already, we’ll be doing a lot of work
with Functions and we’ll be using quite a few of these general points as well.
That’s the end of this section.
I’ll see you in the
next one.