Table Of Contents

SMALL Function in Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

SMALL Formula Excel

Below is a SMALL function Excel formula.

Parameters of the SMALL Function in Excel

The SMALL function accepts the following parameters and arguments:

array – This is the range or array from which you want the function to return the nth smallest value.

nth_position – This is an integer that specifies the position from the value, which is the smallest, i.e., the nth position.

How to Use the SMALL Function in Excel?

Below are the steps of using the SMALL function in Excel:

First, enter the desired SMALL formula in the required cell. You will get a return value on the argument supplied.
You can manually open the spreadsheet's SMALL formula Excel dialog box and insert the logical values to attain a return value.
Consider the screenshot below to see the SMALL function option under the statistical function menu.
Click on the "SMALL" option. The SMALL formula Excel dialog box will open where you can put the argument values to obtain a return value.

Return Value

The return value will be a numeric value, which is the nth smallest value in the array. Please note that if the nth_position is a larger value than the number of values in the array, then the SMALL function will return #NUM! Error. If the supplied array is empty, then the SMALL function will be returning #NUM! Error.

Usage Notes

The SMALL function is useful when you retrieve the nth smallest value from the supplied data set.
For example, we can use the SMALL Excel function to find the first, second, or third-lowest test scores or the fastest times of a race data.
Similar to the LARGE Excel Function, the SMALL function retrieves numeric values based on their position in a supplied list when sorted by value.
Please note that Microsoft Excel uses "k" instead of "n." We are using "nth" because it is much easier to understand the SMALL formula and its works.

How to Use SMALL Function in Excel with Examples

Let us look below at some examples of the SMALL function in Excel. These examples will help you explore using the SMALL formula in Excel.

Based on the above SMALL, an Excel spreadsheet, let us consider these examples and see the SMALL function return based on the function's syntax.

Consider the below screenshots of the above SMALL in Excel examples for clear understanding.

SMALL in Excel Example #1

Using the SMALL formula in Excel =SMALL(A1:A5, 1), we get -3.5.

SMALL in Excel Example #2

Apply the SMALL formula =SMALL(A1:A5, 2) to get 4.

SMALL in Excel Example #3

Applying the SMALL in Excel here =SMALL(A1:A5, 3) to get 6.8.

SMALL in Excel Example #4

Apply the SMALL formula here =SMALL(A1:A5, 4) to get 7.

SMALL in Excel Example #5

Here, we apply the SMALL formula in Excel =SMALL(A1:A5, 5) to get 36.

SMALL in Excel Example #6

Here we use the formula to calculate the SMALL in Excel =SMALL({6, 23, 5, 2.3}, 2)

Top Applications of SMALL Function in Excel

We can use the Microsoft Excel SMALL function in Excel for various purposes and applications within the spreadsheet. Some of the common applications of SMALL Excel function spreadsheets are given below:

Highlighting the bottom values of the provided data
To get the sum of the bottom n values
Sorting numbers ascending or descending
Extracting multiple matches into several columns
Finding the lowest n values
Extracting multiple matches into several rows
Sum bottom n values with criteria

SMALL Function in Excel - Common Problem

One of the most common problems you can face while using the SMALL function is that it returns the wrong value, or the return value is nothing but an error such as #NUM! Error. This error can occur even if the supplied value of k is between 1 and the supplied array value numbers. Possibly, this can arise when you have included text representations of the number within the supplied array. The SMALL function ignores text values and only recognizes numerical values. If you face this error, convert all the array values into numeric ones.

SMALL Function Errors

If you get any error from the SMALL in Excel, then it can be any one of the following:

#NUM! – This error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! – This error occurs when the supplied n is a non-numeric value.

Things to Know about the SMALL Function in Excel

The SMALL function in Excel is the function that is responsible for returning the nth smallest value from a given set of values in the spreadsheet.
The SMALL in Excel is categorized as a Statistical Function.
When sorted, the function returns a numeric value based on its position in a supplied list of values.
If the supplied array is empty, then the SMALL in Excel will be returning #NUM! Error.
If the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array, then the SMALL in Excel will be returning #NUM! Error.
If the supplied value is non-numeric, then Excel's SMALL function will return #VALUE! Error.