How to Use Microsoft Excel 2013 Functions – Part 2

Excel contains numerous specialized tools and features for managing Functions, such as the Functions Library group, the Insert Function Dialogue, a Search option, an AutoComplete facility, the Tool Tip feature, Help Links, and the Function Arguments Dialogue. The Functions Library group organizes Functions according to functionality and includes links to the Insert Function Dialogue, which itself contains multiple Help commands for the user. The Search option and AutoComplete facility are useful shortcuts to finding an appropriate Function for the user’s needs. The Function Arguments Dialogue is a useful non-modal tool for learning about various Arguments.

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 look at some of the more advanced aspects of functions in Excel 2013. You should all ready be familiar with the Formulas Tab so we’re going to start with that.

Now when you’re using the Formulas Tab to insert a function one of the problems you’ve probably come across in the past is finding the function that you want. Even if you know the name of the function you won’t necessarily know which category it’s in. There are various things that can help you with this and one of the things we’re going to look at in a moment is the very first button on the left here, the Insert function button. But let’s look at the function library first and we have all these categories. I’m sure you’ve used many of these in the past. The last option More functions gives you access to additional categories: statistical, engineering, cube, and so on. Now whichever category you’re interested in and indeed whichever function you’re interested in if you hover over the function name you get a description of what that function does as well as a syntactical definition including a list of the arguments of the function. So that’s one thing that can help you to find the function that you want. And with each of these categories, with all of the buttons here right at the bottom you have Insert function which gives you access to the Insert Function Dialogue. And as I said just now that’s the one I want to look at in detail first.

Now one useful thing when you’re using the Insert Function Dialogue is that you effectively have two lots of Help. The question mark denoting the conventional Help in the Office 2013 applications, if you click on that the Help that you get relates to how to use this dialogue. In fact slightly more generally how to create a formula that uses or includes a function. So that’s the general Help on working with formulas and functions. Whereas at the bottom left of the dialogue given a selected function, let’s suppose I select this one Cube set, Help on this function gives me access to the Help specifically on the selected function. So if you’re not sure about a particular function, what it does and how to use it, you’ve got direct access to the relevant Help there.

Now in terms of finding a specific function if you know the name and you don’t really want to go around the function library on the Formulas Tab you can select the category here and look for that particular function within any one of the categories. If you choose a particular category and you want to know what a particular function does click on the function, you get a brief description here, and as I mentioned just now you also have access through to the Help on that function. So that’s one way of quickly going through the lists of available functions and finding the one that you want.

And if you’re quite a bit more unsure about the name of the function that you want or if you don’t even know whether such a function exists you can use the Search facility within the Insert Function Dialogue. This is a fairly intelligent search facility. It uses a certain amount of what might loosely be called fuzzy logic. So if I say wanted to find the median of some numbers I could click in this box, type in the word Median, and then hit the Go button and it will find the median function for me. Now that is the only specific median function in Excel 2013 but a median is a statistical average and there are various other statistical averages such as mean and mode. And the Insert Function Dialogue and the search within it are intelligent enough to offer me some of these alternatives. Now these aren’t medians, they’re arithmetic means and modes but it may well be in some situations that the term I’ve used to do the search is just something about what I’m looking for and it may be that one of these other functions is actually what I need. Now if you put in a term that it can’t exactly match but it’s got some idea of what it’s about, so let me put in the term Capital, as in financial capital. Let’s see if it can find any financial functions related to capital. Click on Go and it’s not really quite happy. It says, Please rephrase your question. But it does find some functions that are related to capital in some way. So there’s the PMT, the payment function. There’s the CUMIPMT, the cumulative interest paid function. So it knows that capital is potentially to do with finance but it doesn’t have a specific function that calculates capital or indeed uses capital specifically as one of the arguments. So if you’re a little bit uncertain it’s always worth trying the search function because I find it quite often comes up with the answer.

So you have there a few way of finding the function that you need. Another way is to use the AutoComplete facility. For this first of all go into File Options, go to the Formulas Page, and make sure that this option is checked, Formula AutoComplete. Show a list of relevant functions and defined names when building cell formulas. This functionality can be enabled or disabled by using the keyboard shortcut Alt plus down arrow. So click on Formula AutoComplete to enable it, click on OK, and now what I’m going to do with this selected empty cell is click in the entry bar, Equals, and start typing. So A and what it comes up with is a list of the functions that begin with the letter A. Now notice that depending on which one is selected by default, in this case ABS, a little tool tip appears on the right. Returns the absolute value of a number, a number without its sign. And you can actually move that tool tip. If you hover over it, it becomes a crosshair cursor. You can put that tool tip somewhere else. One of the reasons for being able to do that is that when you’re working in a particular part of a worksheet you may well find that that tool tip is in the way. Well it’s as easy as that to move it. And of course if I type a second letter, third letter, and so on it’s going to narrow down the choice of available functions. So say I type an M next. There are only two functions that begin with A-M and again I’ve got tool tips that I can move. So that can be a very helpful way. If you’ve got a reasonable idea of what a function name is a combination of that and using the tool tip you can normally find the one that you want quite quickly.

Now once you’ve chosen the function, let’s suppose in this case that we’re going to choose the first function there, not only do we get the function with the open parentheses but we get an improved tool tip which includes both the function name and the list of arguments. And in fact if you hover over the function name in that new tool tip you notice it becomes underlined and that’s a hyperlink through to the relevant Help page.

Now as I mentioned earlier on any arguments that are in square brackets such as Basis here are optional arguments and unless you can remember exactly what the options are in a particular case it’s useful to be able to just flick through using this link here to the Help. So in the case of the function we’re looking at here the AMORDEGRC function, if you look at the optional argument there which was the last one, the basis argument, Basis optional, the year basis to be used zero or omitted. So that is that if we don’t put any value in there at all then the basis is 360 days using the NESD method. I won’t trouble you with what that means now. One means the actual year basis, three is 365 days in a year, and four is 360 days in a year, the European method, and I’ll leave you to look up exactly what each of those mean. But particularly if you’re using a function you’re not familiar with you may well need to just flip into the Help there for any optional arguments to check what the options are or indeed what happens if you omit the argument.

So that’s a pretty useful way of finding out about optional arguments or indeed any arguments using the Help facility, but there’s another really great way of doing this in Excel. Let’s go for that same function again. So I’m going to just select that function but instead of clicking on the Help I’m going to press Control and A and what I get is the Function Arguments Dialogue which lets me enter a value for each of the arguments but then will also give me an explanation of what each of the arguments means. And if like me you have trouble remembering those keyboard shortcuts if you’ve identified a function such as this one, selected it, and just got as far as the function and the first parentheses there’s an Effects button here on the left and if you click on that that also gives you the Function Arguments Dialogue.

Now the Function Arguments Dialogue is a non-modal dialogue so you can keep it open while you work on the function. And not only can you type in values such as cost, date, purchase, but you can also refer to cells or ranges within your workbook. So for instance if I wanted to pick up the cost from cell A4, I realize that A4 here is empty but if I wanted to pick up it from A4 if I just click in A4 then the cell reference there is copied straight into the cost field in the Function Arguments Dialogue. We’re going to be using the Function Arguments Dialogue a little bit later on when we start working through the case studies so I’m not going to dwell on it here, but hopefully you can get a good idea of how to use the Function Arguments Dialogue all ready but as I say more on that later.

So having looked at some of the more advanced facilities available generally for functions we’re nearly ready to start using them in some specific case studies but before we do I’d like to look at a particular range of functions that have a special status within Excel really and that’s the AutoSum functions. They’ve even got their own button here in the function library. So in the next section we’re going to take a quick look at some special things to do with AutoSum and then after that we’ll start working on our examples and case studies. So please join me in the next section.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!