How to use the SMALL function in Google Sheets

In this tutorial, TipsMake will discuss with you the SMALL function in detail, provide some examples, and show how it can be nested with several other functions.

When to use the SMALL function in Google Sheets?

The main use of the SMALL function is to return the cell with the lowest value after searching a range of cells. For example, if you have a list of values ​​in column A and want to get the lowest number in the list, you can use the SMALL function to find it.

You can also use the SMALL function to find the 2nd, 3rd, 4th, 5th, etc. lowest value in a range of data. You just need to set the argument n to whatever value you want to find.

Syntax of the SMALL . function

The SMALL function uses two arguments to complete its calculations. Below is an example of the syntactic structure for the SMALL function.

=SMALL(data, n)

In there:

1. = (equal sign): In Google Sheets, we always start with an equal sign whenever we want to perform a calculation or call a function. It tells the program we want it to treat the text in the cell as a formula.
2. SMALL : This is the function name so that Google Sheets knows what calculation to do. In this case, determine the nth smallest value in the data set.
3. data : This is the array or range of cells containing the data set that SMALL will look at. Set of tuples containing data that can be in the form of dates, numbers, duration values, etc. Requires all values ​​to be of the same type.
4. n : This is an optional argument and represents the rank of the value you want to get, represented as a number. The rating value is a number and must range from 1 to the total number of values ​​present in the set.

For example, if you set n to 4 , the SMALL function will find the fourth smallest element in the data set. If you don't use the n argument , it will search for the smallest value in the range.

How to use the SMALL function in Google Sheets

Check out a few practical examples below to better understand how to use the SMALL function in Google Sheets.

Simple SMALL function

Consider the data set below. Suppose you want to find the lowest value. Here's how to do it:

How to use the SMALL function in Google Sheets Picture 1

1. Click an empty cell, C2 in this case

2. Enter the first part of the formula, which is =SMALL(

3. Highlight or enter the range where you want to find the minimum value. In the example, it's A2:B11.

4. Enter a comma "," to indicate that we are moving to the next argument.

5. Enter the argument n . Since you're looking for the smallest value, enter 1 .

6. Press Enter

Here are the results:

How to use the SMALL function in Google Sheets Picture 2

We can continue this pattern but change the value of n to find the 2nd, 3rd lowest, etc…

How to use the SMALL function in Google Sheets Picture 3

Sort using SMALL

Sorting data in Google Sheets from smallest to largest is a little known but practical use of the SMALL function. We use the SMALL function and the ROW function to sort the data. Here is the formula for the function you will use:

=SMALL($B$2:$B$11, ROW()-1)

Note that the code above locks cell references while copying formulas or using autofill. It does so by using absolute references (the $ sign ) to indicate that those values ​​remain unchanged for formulas in subsequent cells.

How to use the SMALL function in Google Sheets Picture 4

This function uses the ROW function to determine the value of n. As a result, it sorts them from smallest to largest, starting with n=1 (lowest number) and going up to n=10 (largest number).

Nesting SMALL function with IF

How to use the SMALL function in Google Sheets Picture 5

There are other ways to nest SMALL functions, but perhaps the most useful is with the IF function. Let's say you are interested in finding out the fastest time of a kart driver in class 2 or below from the data above. To do this, we will use the ARRAYFORMULA, SMALL and IF functions, as follows:

=ARRAYFORMULA(SMALL(IF(B2:B9 >=2,C2:C9),1))

How to use the SMALL function in Google Sheets Picture 6

The article used Google Sheets ARRAYFORMULA to be able to search two columns at once. And the nested IF function of:

IF(B2:B9>=2,C2:C9)

This means that values ​​in column C will only be considered if the value in column B is 2 or more. Check out the IF function tutorial if you want to learn more about how it works.

Potential error cause for SMALL function in Google Sheets

Alphanumeric data

The SMALL function will ignore non-numeric data. Here is an example:

How to use the SMALL function in Google Sheets Picture 7

However, columns without numeric data will return #NUM! Error, like this:

How to use the SMALL function in Google Sheets Picture 8

This is because the SMALL function cannot handle non-numeric data.

Error out of range

SMALL will generate #NUM! Error when you enter the number n, which is greater than the value of the entries in the data range.

How to use the SMALL function in Google Sheets Picture 9

You'll see an example looking for the 12th smallest number out of a set of only 10 numbers. Therefore, SMALL gives the value #NUM! Error.

Alternative to SMALL function in Google Sheets

The MIN function is an alternative method to find the minimum value in Google Sheets. The MIN function returns the lowest value of the given numbers and is fully automatic.

In this case, the two numbers provided to the MIN and MIN function return a lower value. Its syntax is:

=MIN(value1,value2)

How to use the SMALL function in Google Sheets Picture 10

In the above example, the MIN function is used to find the smaller of two numbers. You can also use the MIN function with a range of cells instead of two cell references and it will find the lowest value. However, the MIN function can only find the lowest value, not the nth lowest value.

Now that you understand how the SMALL function works, you're one step closer to mastering Google Sheets. However, there's still a lot to learn, so make sure you keep practicing and you'll definitely turn pro in no time.

Lesley Montoya

Update 07 February 2023