The Excel SMALL function returns the k-th smallest value in a data set — so you can pull the smallest, 2nd smallest, 3rd smallest, and so on, regardless of sort order.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The array or range of numeric data to evaluate. |
k | Required | The position from the smallest. 1 = smallest, 2 = 2nd smallest, and so on. |
How to use it
SMALL finds the value at a given rank counting up from the bottom. k=1 is the minimum, k=2 the next smallest, and so on.
SMALL is the mirror of LARGE, which counts down from the top. Use SMALL with ROW or SEQUENCE to build a sorted list, or to extract the bottom N values for a report.
Bottom-N totals: wrap SMALL in SUM with an array of positions — =SUM(SMALL(range,{1,2,3})) adds the three smallest values in one formula.
Try it: interactive demo
Pick a SMALL example to see the formula and its result.
Practice workbook
Frequently asked questions
How is SMALL different from MIN?
SMALL(array,1) equals MIN, but you can also ask for the 2nd, 3rd, or k-th smallest.What happens with duplicate values?
What if k is larger than the number of values?
How do I sum the smallest few values?
=SUM(SMALL(range,{1,2,3})) adds the three smallest values. This works as a normal formula in Excel 365 and as an array formula in older versions.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class