Working with the SMALL Function effectively

close up photo of small jellyfish

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010. 

In this post, you will learn all about working with the SMALL function effectively in Microsoft Excel. We will cover the following:

  • Firstly, a summary of this function;
  • Secondly, its formula syntax;
  • Thirdly, important conditions to remember when using this function; and
  • Finally, an example to play around with it in awe of its convenience.

A small fox - represents the SMALL function.
Image by Tom Frydenlund from Pixabay.

Summary of the SMALL Function

The SMALL function yields the k-th smallest value in a dataset. Employ this function to capture values with a specific unique position in a dataset.

Syntax of the SMALL Function

SMALL(array, k)

The SMALL function syntax contains these listed arguments:

  • Array  –  Required. An array or range of numerical data for which you are looking to discover the k-th smallest value.
  • K –   Required. The position (from the smallest) in the array or data range to bring back.

A small ladybird.
Image by Myriam Zilles from Pixabay.

Remarks of the SMALL Function

Working with the SMALL function effectively requires close attention to the following conditions:

  • If array is empty, SMALL captures the #NUM! error value.
  • If k ≤ 0 or if k is greater than the amount of data points, SMALL yields the #NUM! error value.
  • If n is the number of data points in array, SMALL(array,1) corresponds to the smallest value, and SMALL(array,n) equals the largest value.


Copy the sample data from the following table, and paste it into cell A1 of a fresh Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you have to at any point, you can extend the column widths to have a complete view of the entire dataset.

Data 1Data 2
FormulaDescription (Result)Result
=SMALL(A2:A10,4)4th smallest number in first column (4)4
=SMALL(B2:B10,2)2nd smallest number in the second column (3)3

Similar Topics – Statistical Functions

Find the middle value with MEDIAN

Rank data without sorting with RANK

Return the largest value in a data set with LARGE

Tabulat blank cells with COUNTBLANK 

Determine frequency distributions with FREQUENCY 

Build analysis via regression techniques with TREND

GROWTH function

All statistical functions

Leave a Reply

%d bloggers like this: