The Excel SMALL Function

Basic Description

The Excel SMALL function returns the k'th smallest value from an array or a range of cells containing numerical values.

The format of the function is :

SMALL( array, k )

where the function arguments are:

array-An array of numeric values that you want to return the k'th smallest of
k-The index - i.e. the function returns the k'th smallest value from the supplied array

The array argument can be supplied to the function either directly, or as a reference to a range of cells containing numeric values. If values in the supplied range of cells are text values, these values are ignored.

Small Function Examples

The following example shows the Excel Small function, being used to retrieve the 1st, 2nd, 3rd, 4th and 5th smallest values from the set of values in cells A1 - A5.

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right.

Examples of use of the Excel Small Function
Excel Small Function Results

Note that, any calls to the Small function, in which the index, k is set to 1, gives the same result as the Min function.

In the example above, the functions in cells B1 and B5 could be replaced by the Min function and the Max function, respectively.

Further information and examples of the Excel Small function are provided on the Microsoft Office website.

Small Function Error

If you get an error from the Excel Small Function, this is likely to be the #NUM! error:

Common Error
#NUM!-Occurs if the supplied k is less than 1 or greater than the number of values in the supplied array.

Also, the following problem is encountered by some users:

Common Problem

The Small function returns the wrong value, or returns the #NUM! error, even though you believe your value of k is between 1 and the number of values in the supplied array.

Possible Reason

Text values, including text representations of numbers within the supplied array, are ignored by the Small function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values (read more about Excel data types on the Excel Formatting page)


This problem can be solved by converting all array values into numeric values. To do this:

  1. Use the mouse to select the cells you want to convert (this must not span more than one column)
  2. From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
  3. Make sure the Delimited option is selected and click next
  4. Make sure all the delimiter options are unselected and then click next again
  5. You should now be offered a selection of Column Data Formats. Select General and click the Finish button
Note that the Text to Columns only converts values, it will not change the data type of a cell containing a function.