How to Use the SMALL Function in Google Sheets

The main goal of the SMALL function is to provide the nth lowest value of the range. Apart from that, you can use the SMALL feature in several other ways when working with Google Sheets.

In this guide, we will discuss the SMALL function in detail, provide some examples, and show how it can be nested with some other functions. Read on to find out more.

When to use the SMALL function in Google Sheets

The primary 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 you 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 have to set those n Argument to the value you want to find.

SMALL function syntax

The SMALL function takes two arguments to complete its calculations. Below is an example of the syntax structure of the SMALL function.

=SMALL(data, n)

Let’s take a closer look at each sentence to better understand what each term means:

• = (equal sign): In Google Sheets, we always start with the equals sign when we want to perform a calculation or call a function. It tells the program to treat the text in the cell as a formula.
• SMALL: This is the function name so Google Sheets knows what calculation to do. In this case, identify the nth smallest value in a dataset.
• Data: This is the array or range containing the record that SMALL considers. The collection of values ​​contains data that can be in the form of a date, a number, a duration value, and so on. It is required that all values ​​are of the same type.
• n: This is an optional argument and represents the rank of the value you want to get, represented as a number. The rank value is a number and must be in the range from 1 to the number of values ​​in the collection.

For example, if you set n to 4, the SMALL function returns the fourth smallest element in the data collection. If you don’t use the n argument, it searches for the smallest value in the range.

How to use the SMALL function in Google Sheets

Let’s look at some practical illustrations. Check out the examples below to better understand how to use the SMALL function in Google Sheets.

Simple SMALL function

Consider the following data set. Let’s say we want to find the lowest value. Here’s how we would do it:

1. click on an empty cell, C2 in this case
2. Enter the beginning part of the formula, which is =SMALL(
3. Highlight or enter the range in which you want to find the smallest value. In our example it is A2:B11.
4. Enter a comma “,” to indicate that we are moving to the next argument.
5. Enter the n Fight. Since we are finding the smallest value, we enter 1.
6. Press Enter

Here is what the result would be:

But we could continue this pattern change the n-value to find the 2nd, 3rd, etc. lowest value. So:

Sort with SMALL

Sorting data in Google Sheets from smallest to largest is a handy but little-known use of the SMALL function. We use the SMALL function and the ROW function to sort the data. Below is the formula for the function we 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 this using absolute references (the $ markers) to indicate that these values ​​will not change for the formulas in subsequent cells.

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

Nesting of SMALL with IF

There are many other ways to nest the SMALL function, but perhaps the most useful is the IF function. Suppose we are interested in knowing the fastest time of a class 2 or worse kart driver from the above data. To do this we use an ARRAYFORMULA, SMALL and IF function as follows:

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

We used a 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 the values ​​in column C are only considered if the value in column B is 2 or more. Check out our Google Sheets IF function guide if you want to learn more about how this works.

Possible error causes for the SMALL function in Google Sheets

Alphanumeric data

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

However, columns with no numeric data will return a #NUM! error like this:

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

Out of range error

SMALL produces a #NUMBER! Error entering a number n, greater than the value of the entries in the data area.

You’ll see that we’re looking for the 12th smallest number out of a possible set of only 10. Therefore, SMALL #NUM! Mistake.

Alternative to the SMALL function in Google Sheets

The MIN function is an alternative method to find the smallest value in Google Sheets. The MIN function returns the lowest value among the given numbers and is fully automated.

In this case we provide two numbers to the MIN function and MIN returns the lower value. Its syntax is:

 =MIN(value1,value2) 

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

Just a SMALL part of your learning

Now that you understand how the SMALL function works, you’re well on your way to mastering Google Sheets. Still, there’s still a lot to learn, so make sure you keep practicing and you’ll be a pro in no time.