How to Use SMALL in Excel
• The SMALL function returns the nth smallest value from a dataset, making it essential for bottom-ranking analysis, percentile calculations, and identifying outliers in your data.
Key Insights
• The SMALL function returns the nth smallest value from a dataset, making it essential for bottom-ranking analysis, percentile calculations, and identifying outliers in your data.
• Combining SMALL with INDEX-MATCH creates powerful lookup formulas that extract associated data based on rank position, such as finding product names corresponding to the lowest sales figures.
• Dynamic arrays using SMALL with ROW() functions enable automatic bottom-N lists that update as your source data changes, eliminating manual formula copying.
Understanding the SMALL Function Fundamentals
The SMALL function is Excel’s tool for extracting the nth smallest value from a range of numbers. While many users reach for MIN to find the smallest value, SMALL provides granular control by letting you specify exactly which position you want—first smallest, third smallest, tenth smallest, and so on.
The syntax is straightforward: SMALL(array, k) where array is your data range and k is the position (1 for smallest, 2 for second smallest, etc.).
Here’s the most basic usage:
=SMALL(A1:A10, 1)
This returns the smallest value in the range A1:A10. It’s functionally identical to MIN(A1:A10), but SMALL’s real power emerges when you need values beyond just the minimum.
The function excels at data analysis tasks: identifying bottom performers in sales data, finding the lowest test scores, analyzing manufacturing defects, or extracting percentile values. Unlike sorting your entire dataset, SMALL gives you instant access to specific rank positions without disrupting your data structure.
Working with Basic SMALL Operations
Let’s examine practical scenarios. Suppose you have monthly sales figures in column B (B2:B20) and need to identify the three lowest sales months for review.
=SMALL(B2:B20, 1) // Lowest sales month
=SMALL(B2:B20, 2) // Second lowest
=SMALL(B2:B20, 3) // Third lowest
The key difference between SMALL and MIN becomes apparent here. MIN only returns one value—the absolute minimum. SMALL lets you analyze the entire bottom tier of your data, which is far more useful for identifying patterns or trends in underperformance.
Duplicate values deserve attention. If your dataset contains multiple instances of the same value, SMALL treats each instance as a separate position. For example, if you have values [10, 15, 15, 20, 25], SMALL(range, 2) returns 15, and SMALL(range, 3) also returns 15 because both occupy the second and third positions.
This behavior differs from ranking functions and is crucial for understanding your results. SMALL works with positions in the sorted array, not unique values.
Advanced Combinations with Other Functions
The real power of SMALL emerges when you combine it with other Excel functions. The most valuable combination is SMALL with INDEX and MATCH to retrieve associated data.
Consider a sales team spreadsheet with names in column A and sales figures in column B. To find the name of the salesperson with the second-lowest sales:
=INDEX(A2:A10, MATCH(SMALL(B2:B10, 2), B2:B10, 0))
This formula works by: (1) SMALL finds the second-lowest sales figure, (2) MATCH locates the position of that value in the sales range, (3) INDEX retrieves the corresponding name from that position.
For conditional analysis, combine SMALL with IF. To find the smallest value that meets specific criteria, you’ll need an array formula approach:
=SMALL(IF(Region="West", Sales, ""), 1)
In Excel 365 or Excel 2019+, this works as entered. In older versions, press Ctrl+Shift+Enter to create an array formula. This returns the smallest sales figure specifically from the West region.
You can also make the k value dynamic using COUNTIF. To find the median of the bottom 50% of values:
=SMALL(A1:A100, COUNTIF(A1:A100, "<="&MEDIAN(A1:A100))/2)
This calculates how many values fall at or below the median, divides by two, and uses that as the position argument.
Building Dynamic Rankings and Lists
Creating automatically updating bottom-N lists is where SMALL truly shines. Instead of writing separate formulas for each rank position, use ROW() to generate sequential position numbers.
Place this formula in cell E2 and drag down five rows:
=SMALL($C$2:$C$50, ROW(A1))
The formula uses ROW(A1) which returns 1 in the first row. When you copy down, it becomes ROW(A2) returning 2, then ROW(A3) returning 3, and so on. This creates your bottom 5 list automatically.
For a complete dashboard showing both values and corresponding labels, combine this with INDEX-MATCH:
// In column E (values):
=SMALL($C$2:$C$50, ROW(A1))
// In column D (labels):
=INDEX($B$2:$B$50, MATCH(E2, $C$2:$C$50, 0))
This creates a self-updating bottom performers list. As source data changes, your rankings automatically recalculate.
For horizontal lists (bottom 3 across columns), use COLUMN instead:
=SMALL(Sales_Range, COLUMN(A1))
Place this in the first cell and drag right. COLUMN(A1) returns 1, COLUMN(B1) returns 2, creating sequential position arguments.
Handling Errors and Edge Cases
The most common error with SMALL is #NUM!, which occurs when k exceeds the number of values in your array. If you request the 15th smallest value from a 10-item list, Excel returns #NUM!.
Wrap SMALL in IFERROR to handle this gracefully:
=IFERROR(SMALL(A1:A10, 15), "Not enough data")
This displays a custom message instead of an error, crucial for dashboards or reports shared with non-technical users.
Empty cells are ignored by SMALL, which is generally helpful. However, text values cause issues. If your range contains text, SMALL ignores those cells entirely. This can lead to unexpected results if you’re not aware of mixed data types in your range.
For datasets that might have insufficient values, make k conditional:
=IF(COUNTA(A1:A10)>=5, SMALL(A1:A10, 5), "Less than 5 values")
This checks if at least 5 values exist before attempting to retrieve the 5th smallest.
Another edge case: when working with filtered data, SMALL operates on the entire range, not just visible cells. To work with filtered data only, you need AGGREGATE function instead:
=AGGREGATE(15, 5, A1:A10, 1)
Function number 15 is SMALL, and option 5 ignores hidden rows.
Real-World Applications
In sales analysis, identifying bottom performers is as important as celebrating top achievers. Create a bottom 3 products report:
// Product names (Column A):
=INDEX(Product_Names, MATCH(SMALL(Sales_Amounts, ROW(A1)), Sales_Amounts, 0))
// Sales figures (Column B):
=SMALL(Sales_Amounts, ROW(A1))
Drag both formulas down three rows for your bottom 3 products.
Quality control applications use SMALL to identify concerning measurements. In manufacturing, finding the smallest tolerance measurements helps identify potential defects:
=SMALL(Measurements, 1) // Absolute minimum
=SMALL(Measurements, COUNTIF(Measurements, "<"&Target_Min)) // Count below threshold
Sports analytics frequently employ SMALL for timing data. To find the three slowest lap times:
=SMALL(Lap_Times, ROWS($A$1:A1))
Copy this down to automatically increment the position argument.
For financial analysis, calculating lower percentiles helps understand downside risk. The 10th percentile (bottom 10%) approximates to:
=SMALL(Returns, ROUND(COUNT(Returns)*0.1, 0))
This multiplies the count of values by 0.1 to find the position representing the 10th percentile.
The SMALL function is indispensable for bottom-tier analysis. Master it alongside LARGE (its counterpart for largest values), and you’ll have complete control over extracting ranked values from your datasets. The combination with INDEX-MATCH creates particularly powerful lookups that go far beyond basic MIN and MAX functions, enabling sophisticated analysis with relatively simple formulas.