One particularly useful element of Microsoft Excel 2013 Advanced is the Function, with this version containing numerous new Functions as well as improved existing Functions from prior Excel versions. The Results of Functions in Excel can vary depending upon the computer platform running Excel. A Help facility is included in Excel and is useful for finding Functions according to category as well as reading their descriptions. In addition to Functions, Excel also utilizes Arguments which are themselves used within Functions. Arguments can be expressed in multiple ways, most typically Actual Values, individual Cell References and Ranges.
Watch the free video here, transcripts for the entire video follow:
Do you need to learn Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.
Video transcripts:
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, and 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. And 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. And 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’s 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. And 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. And 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 all ready. 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.
And 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 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. And then 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.
And 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 all ready 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. And 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.
