Math function
Excel SMALL function
SMALL returns the nth smallest number in a range. SMALL(range,1) is the same as MIN, SMALL(range,2) is the next one up. Use it for fastest lap times, cheapest bids, or a bottom 3 list.
=SMALL(array, k)Arguments
array- The range of numbers to rank.
k- Which position you want, counting from the smallest. 1 is the lowest.
SMALL examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Runner | 5K time (min) | Age |
| 2 | Sam | 24.6 | 31 |
| 3 | Rosa | 22.15 | 27 |
| 4 | Kai | 27.8 | 45 |
| 5 | Nina | 21.9 | 38 |
| 6 | Luis | 23.4 | 29 |
Result: 21.9
The fastest time, same as MIN.
Result: 23.4
The third-fastest finisher, the last spot on the podium.
Result: 22.483333333
The average time of the three fastest finishers.
Result: #NUM!
k must be at least 1, so 0 returns #NUM!.
How to use SMALL
- Click an empty cell.
- Type =SMALL( and select the numbers.
- Type a comma and the position you want, like 3.
- Close the bracket and press Enter.
Common SMALL mistakes
k out of range
SMALL returns #NUM! if k is 0, negative, or larger than the count of numbers in the range.
Zeros sneaking in
Blank cells are skipped, but a 0 is a real value and will be the smallest. Clear placeholder zeros.
Mixing up SMALL and LARGE
SMALL counts up from the lowest. If you want "second highest", you need LARGE.
SMALL FAQ
How do I find the second smallest value in Excel?
Use =SMALL(range,2). It skips the very lowest and returns the next one.
How do I find the smallest value greater than 0?
Use =MINIFS(range,range,">0"). It ignores zeros and negatives and returns the lowest positive number.
Does SMALL ignore blank cells?
Yes. Blanks, text and TRUE/FALSE in the range are skipped. Only numbers are ranked.
Guides that use SMALL
Related functions
Updated