SMALL Function in Excel: Explained

In this article, you will learn how to use the SMALL formula in Excel.

‍

What is the SMALL function in Excel?

The SMALL formula in Excel is a statistical function used to return the k-th smallest value in a data set. In other words, it can be used to find out the 1st, 2nd, 3rd (or any other position) smallest value in a range of cells in an Excel spreadsheet.

‍

When is it helpful to use the SMALL formula in Excel?

The SMALL function in Excel can be helpful in several situations, such as the below: 

• Data Analysis: The SMALL function can be used to help you glean insights from your data such as identifying the extremes of your dataset. For example, you can use it to find the 5 lowest sales values, the 3 lowest test scores, the customers generating the least revenue, and so on.
• Performance Improvement: If you have a dataset in Excel that represents some form of performance (like production numbers, sales figures, etc.), you can use the SMALL formula to identify areas where improvement is needed the most.

‍

Inserting the SMALL function in Excel

The syntax of the SMALL formula is the following:

=SMALL(array, k)

Array: The required argument array refers to the range of cells that you want to consider in your data set.

K: The required argument ‘k’ is the position from the smallest value. So, if ‘k’ is 1, the function will return the smallest value. If ‘k’ is 2, it will return the second smallest value, and so on.

‍

Note: The SMALL function will return the “#NUM!” Error if:

• The array is empty or contains no numeric values.
• k≤0 or k is greater than the number of data points.

‍

An example of using the SMALL formula in Excel can be seen below. For instance, let's say you had a bake sale and wanted to know which items performed the worst.

‍

Using the SMALL function with other Excel functions

The SMALL formula can be combined with other Excel formulas to create more complex formulas and achieve specific outcomes. For instance, suppose you wanted a threshold for the lowest acceptable sales amount in an Excel tab, consider using the formula below.

=IF(SMALL(C3:C7,1)>500, SMALL(C3:C7,1), "Sale not high enough. Stop producing.")

This function can return two things:

1. If your lowest sale is above $500 the function will return the sale value.
2. If your lowest sale is below $500 the function will return the statement “Sale not high enough. Stop producing.”.

By combining the use of the IF function, which considers a condition, and the SMALL function we have a more complicated formula that can provide deeper insight into the data in your Excel workbook.