How to Use the SMALL Function in Excel

This guide explains how to use SMALL Function in Excel, including its syntax, required arguments, practical examples, and common errors.

How to Use the SMALL Function in Excel screenshot

Description: The function returns the kth smallest value in a dataset.

Syntax: SMALL (array, k)

Inside:

- array : The array or range of data points you want to determine the kth smallest value.

- k: The order (from the smallest value) in the data array to be returned.

Attention:

- If array is empty -> the function returns the #NUM! Error value .

- If k ? 0 or k exceeds the number of data points in the array -> the function returns the #NUM! Error value

- where n is the number of data points we have:

+ SMALL (array, 1) -> returns the smallest value in the array.

+ SMALL (array, n) -> returns the largest value in the array.

For example:

Calculate the kth smallest value as described in the following data table:

How to Use the SMALL Function in Excel screenshot 2

- Calculate the second smallest value in array1. In a cell to calculate enter the formula : = SMALL (C6: C10,2)

How to Use the SMALL Function in Excel screenshot 3

- Press Enter -> the second smallest value in array1 is:

How to Use the SMALL Function in Excel screenshot 4

- Calculate the 3rd smallest value in array2. In a cell to calculate enter the formula: = SMALL (D6: D10,3)

How to Use the SMALL Function in Excel screenshot 5

In array2, there are 2 similar elements are 29 but the function is still arranged in 2 orders, 1 value is the 3rd and the value is 4th.

- Calculate the 6th smallest value in array1. In a cell to calculate enter the formula: = SMALL (C6: C10,6)

How to Use the SMALL Function in Excel screenshot 6

Because array1 has only 5 elements, there is no 6th value -> the function returns the #NUM! Error value .

Above are instructions and some specific examples when using the SMALL function in Excel.

Good luck!

FAQ

The SMALL function performs the calculation described in this guide. Use the correct arguments and compatible data types for reliable results.

Start with an equals sign, enter SMALL, add the required arguments inside parentheses, and confirm that cell references and separators are correct.

Common causes include missing arguments, invalid data types, incorrect cell references, unsupported values, or regional separator settings.