The SMALL function in Excel helps you identify specific smallest values in your dataset, like the second-smallest or fifth-smallest number. Whether you’re analyzing sales performance, tracking inventory levels, or evaluating test scores, this function provides a systematic way to find these values. Let’s explore how to use the SMALL function effectively in Excel 2025.
Find the Second-Smallest Value in Your Dataset
The most basic application of the SMALL function involves finding a specific smallest value in your range. Here’s how to find the second-smallest value:
- Open your Excel spreadsheet and select the destination cell
- Type the following formula:
=SMALL(array,k)
- Replace “array” with your data range
- Enter “2” as the k value
- Press Enter to calculate
Example:
Data Range |
Formula |
Result |
---|---|---|
15, 8, 12, 4, 9 |
=SMALL(A1:A5,2) |
8 |

💡 Pro Tip: Always ensure your data range doesn’t include headers or text values to avoid errors.
How to Use SMALL Function for Multiple Rankings
To find multiple smallest values simultaneously:
- Create a helper column with sequential numbers (1,2,3…)
- Enter the SMALL formula in your first destination cell
- Drag the formula down to generate multiple rankings
Example setup:
Rank |
Data |
Formula |
Result |
---|---|---|---|
1 |
15,8,12,4,9 |
=SMALL($B$1:$B$5,A1) |
4 |
2 |
=SMALL($B$1:$B$5,A2) |
8 | |
3 |
=SMALL($B$1:$B$5,A3) |
9 |
Combine SMALL with Other Excel Functions
Enhance the SMALL function’s capabilities by combining it with other functions:
Using SMALL with IF
=SMALL(IF(range>0,range),k)
This formula finds the kth smallest value while excluding zeros.

Dynamic K Values with ROW
=SMALL(range,ROW(A1))
This creates an automatically incrementing k value when copied down.

INDEX-MATCH Combination
=INDEX(names,MATCH(SMALL(values,k),values,0))
This retrieves the name associated with the kth smallest value.

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.
Get Started
Real-World Applications of SMALL Function
- Inventory Management
- Track lowest stock levels
- Identify products near reorder points
- Monitor minimum par levels
- Sales Analysis
- Find lowest performing products
- Identify bottom sales territories
- Analyze minimum transaction values
- Academic Performance
- Calculate lowest test scores
- Determine bottom percentile rankings
- Track minimum passing grades
SMALL Function Syntax and Parameters
Understanding the function’s components:
Parameter |
Required |
Description |
---|---|---|
Array |
Yes |
The range containing your numbers |
K |
Yes |
The position from smallest (1=smallest) |
Limitations:
- K must be positive
- K cannot exceed array size
- Array must contain numeric values
Common SMALL Function Examples
Finding Bottom 3 Scores
=SMALL(A1:A10,1) for lowest

=SMALL(A1:A10,2) for second lowest

=SMALL(A1:A10,3) for third lowest

Excluding Zero Values
=SMALL(IF(A1:A10<>0,A1:A10),1)

Working with Decimals
The SMALL function handles decimals automatically, maintaining full precision in calculations.
Next Steps
Now that you understand how to use the SMALL function effectively, start analyzing your own datasets to find specific smallest values. Remember to adjust the k value based on your needs.
Ready to supercharge your Excel analysis? Connect your spreadsheets to live data sources with Coefficient. Get started now and transform your Excel workflows.