# SMALL function in Excel

> SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.

Source: https://spreadsheetpractice.com/functions/small · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Every current version
- **Category:** [Statistical functions](https://spreadsheetpractice.com/functions/category/stats)
- **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.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Runner | Time (min) |  |  | Check | Result |
| 2 | Ana | 24.6 |  |  | Fastest | 22.9 |
| 3 | Raj | 22.9 |  |  | Second fastest | 23.4 |
| 4 | Lee | 27.3 |  |  | Slowest | 27.3 |
| 5 | Kim | 23.4 |  |  |  |  |
| 6 | Mo | 25.1 |  |  |  |  |
| 7 | Ivy | 26 |  |  |  |  |

## 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.

## Related functions

- [LARGE](https://spreadsheetpractice.com/functions/large): LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
- [MIN](https://spreadsheetpractice.com/functions/min): MIN returns the smallest number from a list of values or ranges.
- [MINIFS](https://spreadsheetpractice.com/functions/minifs): MINIFS returns the smallest value among the rows that meet one or more conditions: the cheapest quote for a part, the earliest due date in a project, the lowest score above zero.
- [SORT](https://spreadsheetpractice.com/functions/sort): SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.

## 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.

## Practice

A graded SMALL exercise ("Three lowest bids") is on [the SMALL page](https://spreadsheetpractice.com/functions/small). It is checked instantly, then re-checked on changed data.

Lessons that use SMALL:

- [Lesson 34: RANK, LARGE & SMALL](https://spreadsheetpractice.com/learn/rank-large-small) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
