There’s more to Excel formulas than SUM!
When I’m training on Excel, the one formula that people seem to be able to do is add cells up with SUM (often by using the AutoSum button).
The thing is, often that’s the only formula a lot of people know!
Which is a shame because Excel has over 470 built-in formulas known as Functions, and some of them are incredibly useful.
Familiarising yourself with some of these other functions is a good thing to do in Excel, because you will save loads of time.
To be honest though, quite a lot of them will not be relevant to you. My rule of thumb is there are at least 20 that everyone would use.
This “Top 20” will be different for each user, (depending on what you are using Excel for), but shown below is my Top 20 to give you an idea…
My-Top-20-Functions-Dave-GenericAt the end of this post you can download the Top 20 sheet, then you can have a look through the Functions and fill in your own.
What is the LARGE function?
As humans, we can easily look at 10 numbers and see which is the largest, 2nd largest or 3rd largest even.
But what if there are 300 numbers in a column?
Not so easy now.
We could do it, but the chances of a mistake are a lot greater and this is where the time would go.
Excel provides the MAX function which gives you the largest number in a range of cells you highlight.
But what if you want the 2nd largest, 3rd largest or even 289th largest?
LARGE (range, n)
Gives you the nth largest number in a range of cells that you highlight.
So if you want the 4th largest number in a range of cells (say, B2:B11 as in the example below), the formula would be
LARGE (B2:B11, 4)
To enter this formula, start by clicking the Insert Function button…
Type the name (or part of the name) and click Go…
(If you don’t know the name of the Function scroll through the list and work it out from the descriptions shown).
Select the LARGE function and click OK…
Highlight the range of cells you want it to operate on…
If, (as in this example) it is the 4th largest you are looking for, type a 4 into the “K” box…
Click OK and your result should appear in the cell you started in…
To help you remember this, have a look at the video demo below:
If you are interested in filling in your own Top 20 Functions, you can use the sheet below, (just download it to your machine by clicking the button shown below)
