SMALL function
Quick answer
SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.
- Syntax
=SMALL(array, k)- Most common use
=SMALL(B2:B7,1)returns 22.9 (The smallest time, same as MIN)
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
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
Answers that use SMALL
In other languages
SMALL is K.ESIMO.MENOR in Spanish Excel and KKLEINSTE in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .