Hello again and welcome back to our course on Excel 2016 Advanced.
In this section we’re going to continue looking at Functions in general and I want
to look first at tools to help you find the function or functions that you need.
Now let’s take a particular example, let’s suppose that I’m working on this new worksheet
here and I know that I need a standard deviation function. That’s a statistical function.
Well if you look at the Formulas tab you will see the Function Library group there and some
of the most commonly used categories of function. So you have Financial, Logical, Text, etcetera.
Note also a category there of Recently Used. And if you do tend to use a fairly restricted
set of functions that’s a really good feature because it makes it more likely that the function
you want you’ve used recently and you can merely locate it here.
If you look at the other end of the group there is a More Functions button and this
gives you access to the other categories, one of which is Statistical. And the fly out
list there lists all of the available statistical functions. Now note that with each one if
I just hover over it I’ll get a screen tip with a description for that function. And
underneath the screen tip there is a link to Help. Now unfortunately this link to Help
is not quite as good as it used to be because it basically only takes you to a general Excel
Functions Help area. And what you would need to do for the particular function that you
were looking at just now is to type in the name of the function and then you would indeed
get the help on that function. But it isn’t contextual as I believe it used to be.
So what you might do knowing that you need a statistical function is to scroll down there
and try and find the standard deviation or function that you need. But if you didn’t
find it and that will often be because you don’t know its name and you don’t particularly
want to look at the screen tip for every one of those functions there is another option
and that is to click on Insert Function and that brings up the Insert Function dialog.
Now, note that the Insert Function dialog here has this particular field which is the
category field preselected at Statistical because I was looking at the statistical functions
when I invoked this. Let me just Cancel it again.
There are a couple of other ways of invoking this dialog. One of them is that it’s the
left most command on the Formulas tab. And another way you can invoke it is with this
little FX button here on the Formula bar. So once I’ve invoked the Insert Function
dialog I have access to the same list of functions, the statistical functions, that we were looking
at just now. Now there are a couple of important differences with using this dialog. Note that
as I click on each of these functions I will see the equivalent of the screen tip information
there. If I click on Help on this function it does however take me to the actual help
on that specific function, which I think is a big advantage of using that dialog.
And the other big advantage is that if you don’t know the name of the function that
you want you can use the Search facility. So I click in that first field. I know I need
standard deviation. Let’s just try typing in Standard Deviation. In fact Standard will
probably do it but let’s try Standard Deviation. Click on Go. And here we have a list of functions
that in some way involve standard deviation. Now the very first one is STDEV. That’s
the old standard deviation function. And as you can see there from the text underneath
the list, this function is available for compatibility with Excel 2007 and earlier. And then we have
the current standard deviation functions. This is one for calculating the standard deviation
of a sample. This is one for calculating the standard deviation of a population. And then
we have various other functions related to standard deviation, some of which are compatibility
functions and some of which are not. But as you can see, the big advantage here is that
search facility. So what I’m going to do here is to calculate
the standard deviation of the numbers in cells A1 to A7 and I’m going to put the answer
there. So I’m going to use the Insert Function dialog and I do know the function that I want
to use on this occasion. Now note that when I click on OK what I see is this Function
Arguments dialog. And this is a particularly useful dialog. Not only because it will remind
you about the arguments that you need for a particular function and give you more information
about what the function does but it can also help you to enter the arguments for the function.
It’s a non-modal dialog and therefore you can not only type arguments in or type in
cell references but you can actually make selections on a worksheet to form the arguments
for the function. Now in this case the cursor is flashing within the Function Arguments
dialog in the number one field. And I could type in there the first number that is part
of the sample that I’m going to calculate the standard deviation of. I could put the
next number in number two and so on. Now in fact I can also put things like lists of numbers,
ranges and so on into those arguments. Note the little prompt on the right there that
tells me that it’s a number that’s expected. But if as in this case the numbers that I
want the standard deviation of are adjacent, quite conveniently. Then I can also select
the range of numbers just by clicking from A1, dragging down to A7. And you can see that
my first argument is A1:A7. It’s a range. If I wanted to add a second range I could
either put that in as number two or I could just put a comma after A1:A7 in number one
and put an additional range in there as well. So it’s very flexible and very helpful in
terms of quickly populating the arguments for the function.
Now the Function Arguments dialog gives me a preview of the formula result and I’ll
just click on OK. And there is my standard deviation.
And there’s one other feature that can be very useful and that’s a feature called
Formula AutoComplete. Now in order for this to work you need to make sure that one of
your options is set correctly. The option you need is on the Formulas page. It’s in
the Working with Formulas section, second option down, Formula AutoComplete. And as
the little information panel there says, Show a list of relevant functions and define names
when building cell formulas. This functionality can be enabled or disabled by using the keyboard
shortcut Alt plus down arrow when in Formula Editing mode. Now the reason for giving you
that straightforward shortcut is that this function can be both helpful and a little
bit annoying as well. So let’s suppose that I’m going to put
a formula into D4 that involves one of the amortization functions and I know that it’s
AM-something then I can type equal to start the formula and then as soon as I type A I
get a list of the functions that begin with an A. By the way, when you get these little
screen tips for this one you can actually move these little screen tips out of the way
if they’re obscuring part of what you need to see on the worksheet. And in fact it’s
the dropdown, for example, and the screen tip, etcetera that some people find rather
annoying with the Formula AutoComplete function. But let’s say I know it’s AM, oh yes it’s
one of those two. And let’s suppose I decide it’s probably the first one. So I’m going
to double click on that. And when I’ve done that not only do I get a list of the arguments
for that function but the function name itself here has a link to the Help on that function.
So I can not only check that it is the one that I want but there will normally be a full
explanation of the arguments and often an example. I could actually copy and paste some
data here and actually try this out. So it’s a very useful way of confirming
that you’ve got the right function. And then depending on the arguments as you start
to type in the values or select the values, so say there the first value I want is cost.
Let’s assume it was $2,500, comma. Not only does the next argument in the list get highlighted
but if it’s possible what the Formula AutoComplete will do will offer me options or recognize
what I’m typing in that particular argument. Now in the case of date it won’t be able
to guess what date I meant. But if I was say going to put a name in here it will be able
to react to what I was typing to help me to identify the name that I wanted to include.
But other than that I carry on entering my arguments. Note as usual that optional arguments
are in square brackets. So the last argument there is optional.
And that’s it. That’s formula AutoComplete. Now in this couple of sections I’ve either
introduced you to or recapped for you some of the key general features for using Functions
in Excel 2016. In the next section I’m going to look at the AutoSum functions because they
also have some important and rather unusual features as well that it’s well worth knowing
about. And then in subsequent sections of the course as we use some of these functions
you’ll some of the features I’ve been talking about in action. But that’s it for
this section. I’ll see you in
the next one.