Quick Navigation
SMALL Function
Summary
The Excel SMALL function returns the k-th smallest value from a dataset, perfect for ranking analysis, percentile calculations, and identifying specific order statistics within numerical data.
Syntax
SMALL(array, k)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Number Array |
Yes | The numeric data range or array |
| k | Integer |
Yes | The position of the value to return (from smallest) |
Using the SMALL Function
SMALL identifies specific ranked values within datasets, enabling percentile analysis, performance ranking, and outlier detection without sorting data.
Common SMALL Examples
Find 3rd Smallest Score
=SMALL(A2:A20,3)Returns the 3rd lowest value from scores in A2:A20 (ignores duplicates by position)
Rank Bottom Performers
=SMALL(Sales[Amount],ROW(A1))Creates dynamic list of lowest sales amounts using ROW as k-value
Percentile Equivalent
=SMALL(Heights,ROUND(COUNT(Heights)*0.1))Returns approximate 10th percentile value from height data
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: k ≤ 0 or k > data points
Solution: Ensure 1 ≤ k ≤ COUNT(array)
#NUM!
Cause: Array contains no numeric values
Solution: Verify range contains numbers
#VALUE!
Cause: k is not a number
Solution: Use integer for k parameter
Notes
- SMALL(array,n) returns largest value (like LARGE(array,1))
- Processes duplicates by position, not unique values
- Array can be single column, row, or multi-cell range
- Use with ROW() or COLUMN() for dynamic ranking
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+