Hello again and welcome back to our course on Excel 2016 Advanced.
In this section we’re going to start to look at Functions and we’re going to spend
quite a bit of time looking at functions and they’re very much going to be the basis
of many of the other things that we cover later in the course.
Functions have been one of the main strengths of Excel very much since it started really.
And in Excel 2016 the number of available Functions has passed the 400 mark and I can
assure you that we’re not going through all of those on this course, but we are going
to look at quite a few of them.
And really the sheer number of Functions is one of the problems with using them in that
even if you’ve got an idea of the function you need you may not know what it’s called
and you may have trouble finding it.
So in this first section I’m going to cover a few of the basics of Functions which I’m
sure you’ll be familiar with but I’m going to try to extend those into areas that you
may not have seen or used before.
And I’m also going to talk a little bit about how to find the function that you need
and how to identify not only what it does but how to use it.
Now the first thing I’m going to point out is that there is a pretty good Help topic
on Functions in Excel 2016 and this is a good reference point to find out about Functions.
Here is that Help topic, Excel Functions by Category.
Many of the categories agree with those that I use in this section of the course.
Let’s go into a particular category.
Let’s say go into Logical Functions.
Within the list of Logical Functions some of the Functions have a little year number
under them.
That actually corresponds to a version of Excel and it shows you when that particular
function was introduced, which implies that the function was not available in any versions
of Excel prior to the one that is listed.
So for example, the IFS function, the IFS function was introduced in Excel 2016.
If you drill down into a function you get a lot of useful information.
There is usually a brief description of what the function does.
There will then be some technical details that usually include information about the
arguments, which ones are required and optional, information about data types and so on.
And then generally one or more examples of the use of the function.
Depending on the complexity and importance of the function you may have more examples,
more explanations and so on.
You also get questions such as common problems that you might get with this function and
typical sort of errors that you get, and explanations of how those errors might have occurred.
So you have a reasonable amount of information about all of the functions which can be very
useful.
But let me go back again to that first page because there are a couple of very important
things to note here.
I just pointed out about the version markers.
But below there there is a note about differences in calculated results between what you will
see on a Windows PC, 32 bit or 64 bit, and what you might see on a Windows RT device,
using A-R-M, ARM architecture.
If you’re only using a Windows PC that’s fine.
But if you are sometimes or all of the time using an ARM-type device it’s important
to be aware of some of the differences that occur when using functions.
There’s a link here to a section that tells you about those differences.
Something that isn’t mentioned here but which is also important is that some of the
Functions in Excel in recent versions have been improved or enhanced and in some cases
give what is usually more accurate results than they used to.
Most of the time you probably won’t even notice this but if you have legacy systems
based on Excel, maybe old macros that use Excel Functions, it’s possible that the
results may change from version to version when a more accurate version of a function
is introduced.
This will normally only affect accuracy of numbers to a very, very high level of precision.
But in some applications of course that can be important.
Now there are some other useful links towards the bottom of this page.
An overview of formulas, how to avoid broken formulas, how to use error checking and so
on, one or two of which we’ll come back to later in the course.
But there is also a link here to the alphabetical list of Excel Functions.
And there they are.
If you want some really interesting bedtime reading there’s a nice long list of all
of the Excel Functions in the version so far.
And a couple of other things that are well worth bearing in mind are that you can actually
get Functions from third parties.
So you can buy or in some cases get free third party Excel Functions.
And of course you can write your own Functions.
Both of these are outside the scope of this course but well worth bearing in mind if you
need a particular function and Excel 2016 doesn’t have that function.
What I’m going to do now is a very quick demonstration of the use of a simple function
and I’m going to point out some of the key features that I’m going to assume you are
already familiar with and possibly a couple of things that you’re not so familiar with.
So let’s start with a very straightforward job.
Let’s add up these four numbers.
If I want to total some numbers as I do here I can enter a formula anywhere on the sheet.
On this occasion I’m going to write it directly underneath the list of four numbers.
I start a formula with an equal sign and I’m going to use the SUM function.
So I hit the letter S and straightaway IntelliSense takes me to all of the Functions that begin
with the letter S. Now a U.
There are about ten Functions that begin S-U.
And of course the one that I want on this occasion is SUM.
Now in order to select that if I double click not only do I get the rest of the name of
the function but I get the first parenthesis and then within the parentheses I’m going
to need to enter arguments.
In the list of arguments, as you can see here, bold arguments are mandatory, so I must have
at least one number in here.
And then the non-bold arguments indicated also here by square brackets are optional
arguments.
And as you can see in the standard definition of the SUM function the arguments are separated
by commas.
Whatever the function, even if it has no arguments, you will need to use parentheses.
In those arguments I don’t have to put cell references.
I could put actual constants.
So for instance, I could put SUM(6,).
Notice now that having entered the comma what Excel 2016 is telling me is that the next
argument become mandatory.
Eight, comma, and so it continues.
Let’s put a ten in there and then close the parentheses.
When I’ve finished entering the argument I can either hit the Enter key or I can of
course use the standard enter tick mark on the Formula Bar there.
On this occasion I’m going to hit the Enter key and there I have my total of 24.
Excel here is set up so that when I hit the Enter key the cursor moves down to the cell
below the one that I’ve just entered, in this case a formula into.
Let me go back to here.
And now you can see the formula with the use of the SUM function.
If instead of using constants for the arguments I want to use cell references and let’s
suppose that I’m now going to add the values in D2, D3, D4, and D5.
I can list those cell references instead of the constants.
So click within the formula and within the arguments for function, enter that and I can
see my total here is 75.
So listing the cell references is one possibility.
The other possibility of course is to specify a range.
So instead of listing all of those cells separated by commas I can indicate a range using a colon
between the start cell in the range and the end cell in the range.
So that cell says SUM(D2:D5).
I’m not restricted to accessing and using values on this worksheet.
I have a second worksheet in this little temporary workbook.
So if instead of that I want to add up cells from a range on another worksheet I’ve still
got my SUM function there although now the arguments are empty.
If I go to Sheet 2 and select that range you can now see that the range has appeared in
the arguments of my SUM function.
If I enter that you will see now that what I have as the argument in my SUM function
is SUM(Sheet2!), well I normally say a bang.
It’s (Sheet2!E3:I3).
So that is using a range on a different sheet.
And of course there’s nothing to stop me adding the values in the cell range D2 to
D5 on this sheet to the values in the cell range E3 to I3 on Sheet 2, giving me a grand
total so far of 143.
Now partly because of the flexibility within the use of formulae in Excel there are certain
rules you need to stick to such as the fact that you don’t have spaces between function
names and parentheses.
So I’ve got a formula here with a SUM function that’s working perfectly well.
But if I click in there and put a space between the function name and the brackets, tick that,
I get an error.
It’s the sort of error that Excel is quite familiar with.
So it says “We found a typo in your formula and tried to correct it.”
It shows the correction that Excel has tried to make.
It is the correct correction in that it has removed the space there so click on Yes and
that’s fixed again.
And as I mentioned just now even if a function has no arguments you still need to put the
parentheses.
So let’s suppose I want to put the value of PI in the cell F6.
I’m going to do it using a formula =PI.
Note the screen tip there for PI.
Returns the value of Pi.
The value is shown there in the screen tip accurate to fifteen digits.
If I tick that I actually get that familiar error message.
Note the little marker in the corner there to indicate an error.
The formula contains unrecognized text.
And that gives you a link through.
Invalid name error.
So it knows there’s a problem with a name here.
Help on this error, show the calculation steps if it’s managed to do any so far, ignore
the error, edit in the formula bar, and error checking options.
Let’s say Edit in formula bar.
I know that what I need there are the empty parentheses.
Now click on tick and I have that value which is Pi to the accuracy specified by the formatting
of that cell.
The other point to bear in mind of course is that you can nest functions.
So if I wanted to add to that sum the value of Pi as well I could put a third argument
in there, SUM, and within that I could put PI with of course its parentheses.
Click on tick and y total in D6 is now that.
And again you can see that it is shown to the accuracy specified by the format for that
cell.
One other feature which you probably have noticed before and may even use already is
the use of color coding to make it easier to identify arguments on a worksheet.
In this particular case that won’t be particularly helpful but later on in some of the case studies
this color coding can really, really make life a lot easier.
If I just start to type in here those cell references again one at a time separated by
commas.
So the first one is D2.
As I type D2 you see that the cell D2 gets both a colored border and a shaded background.
And the coloring corresponds to the coloring of D2 in the list of arguments.
So if I now put D3 as the next argument you can see the red color both for the argument
D3 in the list of arguments in the formula bar there and the red shading and outline
for D3 on the worksheet.
And then of course that continues with further cells that I specify in the list of arguments.
So that can also be a very useful feature and it’s one that we can take advantage
of later on in the course.
And now just two or three points to finish off this section.
One of them is that if you have any doubts or uncertainties about the use of formulas
in Excel I refer you to this Help page.
And if you have used Functions before you may well have used some of these older Functions
that are now referred to as Compatibility Functions.
These have been replaced by more modern versions but are still available in Excel to support
various legacy systems, old macros and so on.
If you need the functionality provided by these make sure that you use the modern version
of each of these and not the originals that are listed here.
And the third and final point I’d like to make is that I pointed out to you earlier
on in the course that it seems that in these regular updates to Excel 2016 they are going
to be an opportunity for new Functions or replacement Functions to be introduced.
So keep an eye out for those.
In the next section we’re going to continue looking at Functions in general and we’re
going to look at some of the more sophisticated facilities, such as the use of the Function
Arguments Dialog.
Please join me for that.