How to Use Excel SMALL Function (Examples + Video)

Excel SMALL Function – Overview

Excel SMALL Function

When to use Excel SMALL Function

Excel SMALL function can be used to get the Kth smallest value from a range of cells or array. For example, you can get the 3rd smallest value from a range of cells.

What it Returns

It returns the value which is the Kth smallest value from a range of cells.

Syntax

=SMALL(array, k)

Input Arguments

  • array – the array or range of cells from which you want to fetch the kth smallest value.
  • k – the rank or position (from the bottom) of the number that you want to fetch.

Additional Notes

  • If the array is empty, SMALL returns the #NUM! error value.
  • If k ≤ 0 or if k is greater than the number of data points, SMALL returns the #NUM! error value.
  • If n is the number of data points in a range, then SMALL(array,1) returns the smallest value, and SMALL(array,n) returns the largest value.

Excel SMALL Function – Examples

Here are six example of using Excel SMALL Function:

#1 Getting the smallest value from a list

Excel Small Function - Smallest Value from a list

If you have a list of numbers (A2:A4) and you want to get the smallest number from the list, you can use the following small function: =SMALL(A2:A4,1)

Here the Kth value is 1, which would return the smallest number from the range of numbers.

#2 Getting the second smallest value from a list

Excel Small Function - Second Smallest Value from a list

To get the second smallest value from the list (A2:A4), use the following formula: =SMALL(A2:A4,2)

Since the Kth value in the formula is 2, it returns the second smallest values from the range of numbers.

#3 Using SMALL function when there are blank cells in the range

Excel Small Function - Ignores Blanks

If there are any blank cells in the range, it is ignored by the small function. For example, in the above example, while we take the range of cells as A2:A5, the blank cell is ignored by the SMALL function while returning the smallest value from this list.

#4 Using SMALL function when there are cells with Text

Excel Small Function - Ignores Text

Similar to blank cells, Excel SMALL function also ignores cells with text, alphanumeric characters, special characters, and logical values.

#5 Using SMALL function when there are cells with Duplicates

Excel Small Function - Duplicates

If there are duplicates in the range used in the Excel SMALL function, it will treat those duplicates as consecutive values. In the above example, two cells have the value 1. Now when you use SMALL function to return the smallest and second smallest value, it returns 1 in both the cases.

#6 Using SMALL function when there is an Error in the Range

Excel Small Function - Error

If there is an error in any of the cells used in the Excel SMALL function, it will return an error.

Excel SMALL Function – Video Tutorial

Related Excel Functions: