SMALL function
SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on. Use it to list the lowest bids, the fastest times or the earliest dates in order, which MIN alone can't do.
- Available in
- Every current version
- Category
- Statistical functions
- Returns
- The k-th smallest number, or #NUM! if k is less than 1 or more than the number of values.
- Google Sheets
- Also available in Google Sheets.
Syntax
=SMALL(array, k)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or list of numbers to look in. |
k | Required | Which position to return, counting from the smallest (1 = smallest). |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =SMALL(B2:B7,1) | 22.9 | The smallest time, same as MIN |
| F3 | =SMALL(B2:B7,2) | 23.4 | The second-fastest time |
| F4 | =SMALL(B2:B7,COUNT(B2:B7)) | 27.3 | k equal to the count returns the largest value |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Runner | Time (min) | Check | Result | ||
| Ana | 24.6 | Fastest | 22.9 | ||
| Raj | 22.9 | Second fastest | 23.4 | ||
| Lee | 27.3 | Slowest | 27.3 | ||
| Kim | 23.4 | ||||
| Mo | 25.1 | ||||
| Ivy | 26 |
Practice SMALL
Graded instantly, then re-checked on changed data.
Three lowest bids
Column D numbers the places 1 to 3. In
E2, return the lowest bid for place 1. Fill down to E4 so you get the three lowest bids in order.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | k is larger than the number of values, or less than 1. |
| Blank cells aren't treated as zero | SMALL ignores empty cells and text, which is usually right. Zeros do count, so a 0 placeholder will become the smallest value. |
Tips
- For dates, SMALL returns the earliest, second earliest and so on. Format the result as a date.
Questions
How do I find the second lowest value?
Use =SMALL(range,2).
How do I find the smallest value that isn't zero?
Use =MINIFS(range,range,">0"), or SMALL with k set to COUNTIF(range,0)+1.
Lessons that use SMALL
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.