Hello again and welcome back to our course on Excel 2013 Advanced.
In this section we’re going to start looking at functions and in fact functions are the
first area of Excel that we’re going to look at in detail in this advanced course.
Now I think it’s true to say that functions have traditionally been one of the main strengths
of Excel since the very earliest versions.
And in Excel 2013 not only do we have a few dozen new functions but we also have some
great improvements to existing ones.
Now the number of functions in Excel 2013 is in the hundreds and I’m certainly not
going to go through all of them on this course, but I am going to start by pointing you at
a couple of places in online Help that are really going to be able to help you to find
the functions that you need.
So let’s start with this online Help entry, Excel Functions by category.
Now in there we get a categorized list of the functions.
With each of those if you click on the link, it takes you down to the functions, a list
of the functions.
With each of the items in the list, there’s a description of that function.
As I say I’m not going to go through all of these now.
We’re going to use many examples on the course.
Let’s go back up to the top of this page though.
Right at the top here, there’s a note that says Important and that’s exactly what it
is.
Some of you will be using Excel on devices with ARM processors.
So you may be running Windows RT using Excel.
In that case, the level of accuracy, the calculated results of formulas and some Excel worksheet
functions may differ slightly between a Windows PC, a 32-bit or a 64-bit PC and a Windows
RT device using ARM architecture.
Now to be fair, the differences are going to be very slight and they’re going to be
in certain specific circumstances and there is a link here that takes you through to explain
more about what those differences are.
It’s also the case, although it’s not mentioned here, that some values generated
by some Excel functions in Excel 2013 are actually slightly improved, slightly different
from ones that are generated in earlier versions of Excel.
In the process of continual improvement of functions, Microsoft have made some improvements
to a number of functions.
You may find that if you are using Excel 2013 on a device and you call a function to get
a value and if you called an earlier version of the same function in an older version of
Excel, it is possible in some specific circumstances to get slightly different values even using
the same PC.
Now these differences are slight.
They’re in very specific situations and to a large extent we can ignore them from
this point onwards.
In fact, none of the examples that we’re using on this course should show any of these
differences.
However, if in your normal use of Excel, you do find differences like this, perhaps you’ve
got a particular complex calculation that you’ve been running in an older version
of Excel for some time, you’re upgrading to Excel 2013 and suddenly the values you
get just seem to be that little bit different from how they used to be.
This could well be explained by changes to, improvements to Excel functions in Excel 2013.
That’s something to be aware of but it’s not something that I’m going to go into
any further on this course.
Now let me just point out one or two specific things about the Help facility here.
As it says there, Worksheet functions are categorized by their functionality.
If you know the category you can choose a category and find it.
There’s also a Search facility here.
As it says, Control-F brings up a Find function.
Let’s suppose you’re looking for a standard deviation function, you can start typing Standard,
S-T-A-N-D, and so on.
And if in this case it has found Stand in a standard normal cumulative distribution,
you could keep typing or you could just say well it’s going to be something like that.
Let’s try Next, Next, there we are, Standard deviation.
In addition to this categorize list of functions, if I just do a Back in Help there is a list,
Excel functions alphabetical, click on that, and that gives a full list in alphabetical
order and you can jump to a particular first letter by clicking on it in the this sort
of index here.
So you’ve got a lot of functions in Excel 2013 but they can even be supplemented.
You can either download or purchase add-ins and, of course, you can write your own functions
using VBA.
We’re not covering VBA on this course but if you want to be able to pretty much write
a function to do anything learning VBA is the way to go.
So let’s have a quick recap on a couple of syntax rules about functions.
What I have here is a little scratch workbook.
It’s got five cells with numbers in and I’m going to enter in the cell below that
column of five numbers a function to total those numbers.
Now if you have trouble with that, you probably shouldn’t really be doing this course.
But basically within a formula, we’ll begin the formula with an equal sign and then we
can specify the name of the function that we want to use.
We can select it.
There’s a number of ways of doing it but let’s suppose I know I’m just going to
add those up.
I put in the Sum function.
That’s a function you should certainly know.
Then always there are parentheses.
Even for functions where you don’t need to do anything more than know the name of
the function, you will always have parentheses; open parentheses, close parentheses.
And as soon as I type the parentheses there, I’m given a sort of hint which basically
gives me a list of arguments.
Now I’m going to talk a little bit more about arguments in a moment.
But basically in this case, you can see you’ve got a bold argument which means there must
be at least that first one, and then a comma.
Arguments are separated by commas, and then the later ones which are in brackets are then
optional.
Now I could if I wanted to in that particular function list of arguments there I could put
something like 3.0, comma, 5.2, then close the brackets, and tick to enter that function
name and there we are, 8.2.
Of course, it’s got nothing to do with the five numbers above it but you can put literal
arguments in, actual values in there.
It’s not something you’ll often do but there’s nothing to stop you doing it.
So let’s now work towards summing those five numbers.
I’m going to dwell on this a little bit because it’s important to understand some
of the rules about arguments.
Let me click back into that Sum function again, into the list of arguments, and this time
I’m going to delete the arguments that were there before and instead of those I’m going
to put in the cell references and I can always use cell references.
As I put in the cell references, note the coloring of the cell references, the coloring
also relates to the coloring on the border of each cell in the worksheet.
So I’ve got a blue C2, a red C3, and so on and I can list the cell references.
Again, tick it and now I’ve got a much more sensible use of a function.
So the function arguments can certainly be cell references.
Now let’s suppose that I really want to do is to put in a range, and a range is very
often what I will be summing.
So let me suppose I put in sum C2, as you should know, colon, C4 specifies a range.
Notice how when I do that I have the range highlighted in blue covering those three cells.
Also note the little selection handles at the four corners of that cell range.
If I hover over the bottom right hand one, I can adjust the cell range using the mouse
and you’ll see that reflected in the formula in the entry bar.
And that’s probably something that you use regularly already.
Again, when I’ve finished I’ve got a sum C2 to C6, tick in the entry bar, and now I
have the arguments expressed as a range.
Let me just demonstrate this once.
I won’t keep doing this but I think it’s useful for those of you who are using touch
to see that it works pretty much the same way.
If I switch into touch mode and then just select the cell there with the formula in
it, you can see how I get those round handles for touch mode.
Keep an eye on the formula in the entry bar.
I’m using my fingers now to change that selection and as I change the selection, you’ll
see it change in the entry bar as well and you can see the markers where my fingers are
on the screen.
So I’ve shown you there the three most common types of argument that you’re going to use
in a function, so actual values, individual cell references, ranges.
Of course, you can also have references to other sheets or even other workbooks within
a function.
Let’s look at a couple of special cases here.
Can you think of an example of a function with no arguments?
Well, here’s one, pie, tick, and that gives you the approximate value of pie.
And also one of the main syntactic rules is I don’t have a space between the name of
the function and the parentheses.
So if I type in here Equal sum, space, open brackets then I can type in something similar
to what I did before but I just get an error message because that space stops Excel 2013
from seeing that that is one of its functions, so I’ll just get an error message.
Another very important point with functions is that you can nest functions.
So, for instance, in the sum function here, I could say comma, pie and what that would
do is to say calculate the sum of cell C2 to C6 and pie.
Note I must have the parentheses with pie for it to be recognized as a function.
Tick that and that’s the sum of those five numbers and pie.
So that’s a pretty quick recap of the basics of using functions.
If you haven’t used formulas and functions a lot before, there are a couple of very useful
Help pages that you might want to work through.
One of them is Overview of formulas and that gives you some good examples of the use of
formulas, explains some of the things that I’ve done about using functions, constants,
cell references, and so on.
If you’re not comfortable with those, I think it’s a really good idea to go through
that.
Another very useful page is this one and I suppose that to some extent this page is aimed
at the opposite end of the function spectrum in terms of knowledge.
This is really meant for people who’ve used functions a lot in the past and your question
is so what’s new in Excel 2013?
Well, there is a Help page dedicated to which are the new functions in Excel 2013.
So that’s another one that’s well worth looking at.
For those of you who are really hot on functions, this is also a very important page in Excel
2013 Help as well because it lists compatibility functions.
These are functions that have actually been superseded by newer functions, better functions,
but they’re still provided in Excel 2013 for compatibility reasons.
So if you want to make sure whether any of these functions maybe you use already, there’s
a better alternative than your alternative or just to understand what’s changed then
that’s an important list for you as well.
As the reference says here, For more information about the functions that are replacing these
compatibility functions, see Statistical functions reference.
So particularly in relation to these types of statistical function, then looking at what
they’ve been replaced by is pretty important.
So that’s it on the basics of functions and what we’re going to look at in the next
section is some of the more advanced techniques for working with functions before we move
on to some case studies and applications.
So I’ll see you in the next section.