Join Dennis Taylor for an in-depth discussion in this video Using other common functions, part of Excel 2016 Essential Training.
- Built into Excel is a huge library of over 450 functions. We're looking at a worksheet called Functions in our workbook 03 - Creating Formulas and Functions. If you go to the Formulas tab in the ribbon and slide over one of the entries here you can see, there are different kinds of functions and as we slide over this one or that one we can see a pop-up tip reminding us or explaining to us what it does and there's a lot of them as I suggested, Math & Trig. Now, with the wrong mindset here you could easily get overwhelmed.
Here's one on inverse hyperbolic cosines you think you'll need that one? I kind of doubt it, unless you're in the scientific arena and of course there are some valuable tools and functions in here. If you work in finance, there certainly are some financial functions here, quite a few of them about 50 of them or so out here. A capsule description of a function, what is it? It's a shortcut for a formula. Now, each one has to be taken on its own merits and you'll see tons of options here for using these. Under More Functions, you'll see a category called Statistical, and that's pretty huge as well.
In this worksheet we've got some information over in column J, maybe this is a description of things we might want to do with some of the data we've got here. Now we don't have a lot of data here, but you could easily imagine a list like this being huge. And here's the word Rank too, let's take a look at that idea if we want to rank these salaries, in other words compare this one with all the others, as you might guess there's a function called Rank. Now the question sometimes comes up, how do you know if there's a function? Well, sometimes you don't.
Now you may have overhead somebody use the word or the phrase, or maybe you just happened to see it up there one day as you were looking around at different functions. As you type a letter after the equal sign, all functions that begin with that letter appear in this list and sometimes just by looking at it you get ideas about functions out there. Here's one called Rank. Now, interestingly enough this is an odd one in the sense that here's one called Rank Average and there's the description, here's one called Rank EQ and there's one leftover from a prior version, simply r a n k, which you don't see in the list until you happen to type it and there's a little note there, we won't go into this right now, but this function says, in effect, I've got a value right here, comma, and I want to compare with all the other values in a list.
Now sometimes in formulas it's handy to select an entire column, I'll just click column F and I'll press enter. So, that's the eighth highest entry that's what the Rank function does. It says looking in this list, this is the eighth highest we can drag this down the column and we can see, because it's a relatively short list, that it's correct, that's the highest value right there. Here's the second highest. If two of these values are the same, if we use the simple function Rank, we'll see that they share the ranking so I'm going to make these two salaries here be identical for the moment, I'll do that, we see they share the number five position, but if you look in the list here you don't see a number six, so in effect they are occupying the fifth and sixth positions.
There are two other variations on the Rank function you might want to take a look at. Over here are some other ideas, count data cells. Well how many people do we have? Well that's pretty easy, but there is a function that counts the cells that have data, =counta. Now this counts all cells that have data not just numeric data, so how many of these cells here have data? That's probably not that useful, but this will tell us, it's 72. Now that counts numerical cells. What we wanted to count just the number cells? =count And if I were to highlight these cells of course a lot of those do not contain numbers, but some do.
Only 48 of them contain numbers. Now you could say in this case that's just trivia, and I'd sort of agree with you, but sometimes that's important to know that. Median, on the other hand, is something that's valuable it's similar to Average but certainly not the same Median says let's take the middle value in a list and if the number of entries is even, it will average the middle two. So, =median. Now, once again, how do you know there's a function called Median? Well, it's a good guess let's say, Excel has a lot of statistical capability but if you happen to be going to the Formulas tab, clicking More Functions, go to Statistical, you're gonna see Median out there.
And if you don't find it, what's your other guess? You will find it here, and there's a description of it too. Sometimes of course, you'll just have a sense as we did in this case, there's a Median out there. Left parenthesis, what's the median of the new salaries here? We could click column F, or just these cells here, that's the median value in this list. Now, second largest, I didn't think I needed this and I've only needed it a few times over the years, but I happened to discover it about 15 years ago, came in really handy a couple of times.
Is there a function for second largest, and what would it be? I can tell you what it is, but how would you discover it? Is it Math & Trig? Well you might see it this way, out here, it's not under Math & Trig, maybe it's under Statistical. Sometimes you just don't know where to look. Now there is an option here, you could for example over here, Insert Function, and actually type in second highest, second largest, and click Go and see what happens.
Now it alerts you to a bunch of functions here maybe more than you'd like to explore as it turns out in this case, it is called Large so I'll just cancel out of here, so =large. Now again, I'm not saying necessarily that's something that everybody needs it just points out how valuable certain functions can be you just don't know all the time where to look for them. In the example here, I'm looking here and I want to know the second largest so I'll put in the number two. Three for the third largest, and so on. And as you might guess, there's a similar function that does the opposite and it's called Small.
In this case we want to know the second largest salary it doesn't tell us where it is, but we can easily see it in the short list, there it is, it's Maria Perez in row 11 that's the second highest entry there. STD, standard deviation, but you wouldn't know that necessarily either, and once again, how do we know this, how would you guess it? You would probably imagine that if Excel has median, if it has averages, it's probably got standard deviation. If you're looking, you'd find it under Statistical Functions but even if you start typing, no doubt it begins with st, and right away you see a group here and maybe more than you would have expected stdev p, s, and if you are about to use this, you probably have a sense of which one is the one that you need.
And by the way, sometimes what you'll do once you find the one, is just tab it into place, like that. Refer to the salaries this way, and if you know what standard deviation means you're getting an answer that makes some sense. It gives a weighted average to the differences between these salaries and the average. So, lots of functions out there, a ton of them, again, don't spend too much time just spending idle time looking for a function but the more you work with Excel, the more you begin to have a sense of what some of these are and keep in mind that some of you will never look at Math & Trig, some of you will look at that a lot, depending upon where you work and what you do.
Lots of other functions, when you have that extra minute maybe check out this one, check out that one. Over 450 functions in Excel, and they're absolutely indispensable for many Excel users.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros