SMALL function

This article describes the formula syntax and usage of the SMALL function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.


Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.


SMALL(array, k)


  • Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.
  • K    Required. The position (from the smallest) in the array or range of data to return.


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



ShowHow do I copy an example?

    Important: Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.


Data Data
3 1
4 4
5 8
2 3
3 7
4 12
6 54
4 8
7 23
Formula Description (Result)
=SMALL(A2:A10,4) 4th smallest number in first column (4)
=SMALL(B2:B10,2) 2nd smallest number in the second column (3)
Applies to: