Use formulas and functions

# Calculations using functions

Excel provides a vast number of built-in functions to perform simple or complex calculations.

Use functions

1. Select a cell in the worksheet.

2. Select Formulas, and then select a function from a category in the Function Library group.

3. Specify data in the Function Arguments box for a range of cells.

4. Press Enter, or select OK.
By default, the resulting value appears in the selected cell, and the formula is displayed in the formula bar.

Use Insert function

1. Select a cell in the worksheet.

2. Select Formulas and then select Insert Function.

3. Type the function name in the Search for a function box, or select the category of the function.

4. Select a function, and then select OK.

5. Specify data in the Function Arguments box for a range of cells.

6. Press Enter, or select OK.
To insert a function, type equal sign = and the likely function name in the cell. A list will be visible. Select a function from the list. For example, =SUM.

### Want more?

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 and Trig. Now, with the wrong mindset here, you could easily get overwhelmed.

Here's one on inverse hyperbolic cosine.

Do you think you'll need that one? I kinda doubt it unless you're in the scientific arena, and, of course, there are some valuable tools and functions in here.

Do 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.

You may have overhead somebody use the word or the phrase, or maybe you just happen 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 a description.

Here's one called RANK EQ, and there's one left over 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 gonna 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're 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, equal 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 if we wanted to count just the number cells? Equal 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 equal 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 parentheses.

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 and Trig? Well, you might see it this way out here.

It's not under Math and 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. It turns out in this case it is called LARGE, so I'll just cancel out of here.

So equal 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 third largest, and so on.

And as you might guess, there's a similar function. Does the opposite, and it's called SMALL. This case, we wanna know the second largest salary.

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 had probably imagined that if Excel has median, if it has average, it's probably got standard deviation.

If you were 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.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits

• Stream courses from your computer or mobile device.

• Take courses for every level – beginner to advanced.

• Practice while you learn with quizzes, exercise files, and coding windows.

• Choose a plan for yourself or your entire team.