How to Use the SMALL Function in Google Sheets

How to Use SMALL Function in Google Sheets
How to Use SMALL Function in Google Sheets – Sheetaki

You can use the SMALL function in Google Sheets to find out the nth smallest value within a cell range.

In cases where you need to return the lowest value in a numeric dataset, surely, the MIN function can do the job. But let’s say you want to return the second, third, fourth, etc., smallest value. How can you do that? Well, it’s actually easy with the help of the SMALL function.

Let’s relate it to a simple scenario.

For example, you have a spreadsheet that contains this set of numbers: 2, 8, 4, 15, 10, 13. If you want to display the third lowest value in this dataset, you can use the SMALL function, specifying the cell range where they are placed in, and rank from the smallest value of the number you want to return, which is 3. This will then give you “8” as the result.

You can use the SMALL function in a wide range of situations. But of course, you need to learn its syntax first to use it effectively. And that’s what you’re about to find out in this article. So, let’s get started!

The Anatomy of the SMALL Function

The SMALL function in Google Sheets is among the simplest to write. Here’s how you should write it:

=SMALL(data, n)
Let’s break down the components to understand what each of them means:
  • = the equal sign is the first character we need to type in to initiate any function or formula in Google Sheets.
  • SMALL() this is our SMALL function.
  • data is the first required parameter of the SMALL function. It holds the array or cell range of the dataset you want the function to operate on.
  • n is the parameter that will hold the rank of the element you want to return. For instance, setting the n parameter to ‘3’ will cause the function to return the third smallest element within the given dataset.

The SMALL function only requires two parameters for it to work, as you can see above. So, it should be easy to remember and implement it to your spreadsheet.

A Real Example of Using the SMALL Function

At this point, I’ll show you how the SMALL function can be utilized in a real setting.

Let’s consider this scenario.

Below is a spreadsheet that contains the coldest temperature readings ever recorded in several states:

Example dataset for using the SMALL function in Google Sheets

If we want to find out the lowest temperature in the given dataset, we can just write a simple formula with the MIN function:

Using the MIN function to find out the lowest value

For some reasons, let’s say we also want to display the second, third, and fourth lowest temperature readings. Since the MIN function is not enough to achieve this, we’ll need to use the SMALL function instead to carry out this task.

Using the SMALL function to find out the nth smallest value in Google Sheets

As you can see in the example, Google Sheets was able to display the second up to the fourth lowest temperature readings thanks to the SMALL function. Isn’t that cool?

If you want to have a copy of the example spreadsheet, go ahead and click the link below:



How to Use SMALL Function in Google Sheets

Let’s put the SMALL function to the test, this time with a simple activity. We’ll use the example dataset in the previous section, so ensure that you already have a copy of the spreadsheet earlier.

  1. First, open your copy of the spreadsheet. Within it, you should see this dataset:
    Example spreadsheet for the SMALL function in Google Sheets
  2. Our objective is to supply the remaining fields with their respective values. We will display the second, third, and fourth lowest temperature values in the cells provided using the SMALL function.
  3. Let’s start by finding out the second lowest temperature. Select cell E3 in your spreadsheet.
    Select cell E3
  4. Next, initiate the SMALL function by typing in ‘=SMALL(‘. You should see a tooltip box appear as you type this function.
    Initiate the SMALL function
  5. This time, we need to specify the values for the parameters of SMALL. For the data parameter, set cell range B2:B10 as the value. As for the n parameter, since we’re looking for the second lowest temperature, indicate ‘2’ as the value.
    Specify the parameters of the SMALL function
  6. Finalize the function by typing in close parenthesis ‘)’. Afterward, hit the Enter key on your keyboard. Your spreadsheet should update into this:
    Result of using the SMALL function in Google Sheets
  7. Great job! You just used the SMALL function to determine the second lowest temperature in the dataset. Now, find out the values of the remaining fields using the same function. Just change the n parameter accordingly to determine the third and fourth lowest temperatures:
    • For the third lowest temperature, let n be ‘3’
    • For the fourth lowest temperature, let n be ‘4’

    If you’ve typed the function properly, you should see the following changes in your spreadsheet:
    Using the SMALL function in Google Sheets



Frequently Asked Questions (FAQ)

  1. Why does my SMALL function return a #NUM! error?
    The reason why you get this error is probably that you’re trying to input a value in the n parameter that is less than or outside the range of the selected cell range. Keep in mind that the n parameter can only hold a value that is within the range of your dataset.
  2. Can I use the SMALL function to return the nth largest element instead?
    Although it’s possible, it’s not actually the ideal function to use in this case. If you want to return the nth largest element in a dataset, use the LARGE function in Google Sheets instead.


There you go! That’s all there is to know about the SMALL function in Google Sheets. This function is just one of the many useful Google Sheets functions out there. Do you want to learn more? Check out our other articles in Google Sheets.

You can also subscribe to our newsletter to be updated with the latest about Google Sheets.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like