How to Use the Excel Functions
SMALL and LARGE

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

With some basic sorting, it’s easy to find the smallest and largest numbers in a spreadsheet.

But sometimes you need to put those numbers into a formula – and that can be tricky.

That’s where the functions SMALL and LARGE can help you out.

They’ll pull the smallest and largest numbers from a range—and they can help you find the second smallest, third largest, and so on.

Let’s take a look at how they work, and then check out an example of how they can help you out.

Get your FREE exercise file

SMALL and LARGE are simple functions, but to show you how useful they can be, we’ve put together an example workbook.

We’ll be using it in the rest of the article, so download it now and follow along!

Download the FREE Exercise File

Download exercise file

The SMALL function

We’ll start by finding the smallest number in a range. Open up the example workbook, and you’ll see a spreadsheet with a list of sales figures for four different regions.

Using the SMALL function, we’ll find the lowest value of sales in the list.

First, though, let’s take a look at the syntax of the function.

The syntax of the SMALL function

=SMALL(range, n)

The range is the group of numbers from which you want to find the smallest value.

n tells Excel to return the nth-smallest value. So if n is 3, you’ll get the third-smallest value. If you want the smallest value, enter 1.

Click into cell F3 and type the following formula:

=SMALL(B2:B51, 1)

small-function-one

We want the smallest number, so we’ll use an n value of 1.

Hit Enter, and you’ll get the smallest value in the list:

least-sales

If you sort the list of sales figures, you’ll see that the function correctly returned the smallest sales number.

Try finding the value for the next cell down, 5th least sales.

Kasper Langmann, Co-founder of Spreadsheeto

The LARGE function

LARGE is almost exactly the same as SMALL. It takes the same arguments, and returns the nth-most value.

Here’s the syntax:

The syntax of the LARGE function

=LARGE(range, n)

range, again, is the selection of numbers from which you want to find the largesvalue.

And n tells Excel which number you want. Setting n to 4 will give you the fourth-largest number. Setting it to 1 will give you the largest number.

In cell F5, we’ll type the following formula:

=LARGE(B2:B51, 1)

And, as expected, it returns the largest value in the Sales column:

most-sales

Use what you’ve learned to fill out the next cell, 3rd most sales.

Kasper Langmann, Co-founder of Spreadsheeto

Combining SMALL and LARGE
with other functions

Like many functions, SMALL and LARGE are the most useful when you combine them with other functions.

We’ll use them in conjunction with INDEX and MATCH to find out which regions have the most and least sales.

Here’s the formula we’ll use for getting the region with the least sales:

=INDEX(A2:A51, MATCH((SMALL(B2:B51, 1)), B2:B51, 0))

Let’s break that down one step at a time, starting with SMALL.

Kasper Langmann, Co-founder of Spreadsheeto

As we saw earlier, SMALL(B2:B51, 1) gives us the smallest number in the sales column.

Moving out one step, we have MATCH(SMALL(B2:B51, 1), B2:B51, 0).

MATCH returns the location in the range of the result of our SMALL function. In short, it tells us which row the smallest sales number is in.

Finally, we have INDEX. This function takes the row number from MATCH and looks it up in a different column—in this case, the Region column.

So =INDEX(A2:A51, MATCH((SMALL(B2:B51, 1), B2:B51, 0)) finds the row number of the smallest sales number in column B and returns the corresponding value from column A.

Hit Enter on that formula and let’s see what happens.

lowest-region

The function returns “West.” A quick look at the lowest number in the sales column shows that this is correct.

Again, feel free to sort the column to make sure this is true.

Try writing a function that shows you the region that posted the second-highest sales number in the report.

Kasper Langmann, Co-founder of Spreadsheeto

More powerful than sorting

It’s easy to find the smallest and largest values in a list with sorting.

But when you need to use those numbers in a function—or the values in the list might change regularly—the SMALL and LARGE functions will serve you better.

They may not seem like much at first. But after you’ve seen what they can do with other functions, you’ll understand that they’re really convenient to have around!