Returns the k-th smallest value from a data set. SMALL finds the nth smallest value when data is sorted in ascending order. Use SMALL to find bottom values like 1st smallest, 2nd smallest, etc. Essential for finding lowest performers, identifying outliers, ranking analysis, and bottom-N analysis.
Master the fundamentals of Excel SMALL function
The SMALL function returns the k-th smallest value from a data set. SMALL sorts the data in ascending order and returns the value at position k. k=1 returns the smallest value (same as MIN), k=2 returns the 2nd smallest, etc. SMALL ignores text values, logical values, and empty cells. If k is greater than the number of values or ≤ 0, SMALL returns #NUM!. Essential for finding lowest performers, identifying lowest values, ranking analysis, bottom-N analysis, and finding outliers or lowest values.
Returns value at position k
Values sorted ascending
Only numeric values considered
Can return multiple values
Function-specific parameters
Function-specific return type
Find lowest values or performers
Rank values from smallest
Analyze bottom N values
Identify lowest outliers
Exact matching required
Returns numeric position
Handles missing text gracefully
=SMALL(array, k)Array or range of data from which you want to find the k-th smallest value.
Position from the smallest value to return (1 = smallest, 2 = 2nd smallest, etc.).
The k-th smallest value
Description: Returns the k-th smallest value from a data set
Find smallest value
Returns the smallest value. SMALL(range, 1) is equivalent to MIN(range).
Use SMALL function in VBA
' Basic SMALL in VBA
Range("C1").Value = Application.WorksheetFunction.Small(Range("A1:A10"), 1)
' Returns: Smallest value
' Find smallest value
Sub FindSmallest()
Dim smallValue As Double
smallValue = Application.WorksheetFunction.Small(Range("A1:A10"), 1)
Range("B1").Value = smallValue
End Sub
' Find bottom 5 values
Sub FindBottom5()
Dim i As Integer
Dim smallValue As Double
For i = 1 To 5
smallValue = Application.WorksheetFunction.Small(Range("A1:A100"), i)
Range("B" & i).Value = smallValue
Next i
End Sub
' Compare SMALL with MIN
Sub CompareSmallMin()
Dim smallValue As Double
Dim minValue As Double
smallValue = Application.WorksheetFunction.Small(Range("A1:A10"), 1)
minValue = Application.WorksheetFunction.Min(Range("A1:A10"))
Range("B1").Value = "SMALL(1): " & smallValue
Range("B2").Value = "MIN: " & minValue
' Should be equal
End Sub
' Find values in bottom percentile
Sub FindBottomPercentile()
Dim dataRange As Range
Dim totalCount As Long
Dim percentile As Double
Dim bottomCount As Long
Dim i As Long
Set dataRange = Range("A1:A100")
percentile = 0.1 ' Bottom 10%
totalCount = Application.WorksheetFunction.Count(dataRange)
bottomCount = Application.WorksheetFunction.RoundUp(totalCount * percentile, 0)
' Output bottom values
Range("B1").Value = "Bottom " & (percentile * 100) & "%"
For i = 1 To bottomCount
Range("B" & (i + 1)).Value = Application.WorksheetFunction.Small(dataRange, i)
Next i
End SubFind lowest values or performers
Rank values from smallest
Analyze bottom N values
Identify lowest outliers
SMALL returns #NUM!
Check: k must be 1 ≤ k ≤ COUNT(range)Solution: This occurs when k is greater than the number of numeric values, or k ≤ 0. Ensure k is between 1 and the count of values. Use COUNT(range) to verify available values.
Uncertainty about when to use SMALL vs MIN
SMALL(range, 1) = MIN(range)Solution: MIN finds smallest only. SMALL(range, 1) equals MIN(range), but SMALL can find 2nd, 3rd, etc. Use MIN for smallest only; use SMALL when you need k-th smallest.
Text values not considered
SMALL only works with numbersSolution: This is expected. SMALL only considers numeric values. Text values are ignored. If you need to rank text, convert to numbers first or use a different approach.
Need array of bottom N values
SMALL(range, {1,2,3}) returns bottom 3Solution: Use array formula: SMALL(range, {1,2,3,...,N}). Enter as array formula or use in newer Excel with automatic array expansion.