If you want to pull the smallest, second smallest, or third smallest number out of a list, the SMALL function is what you’re looking for.
It’s the function you reach for when MIN only gives you the single lowest value and you need to go a few places past that.
In this article, I’ll show you how to use the SMALL function in Excel, from grabbing one value to spilling the bottom few numbers, sorting a list, and pulling the lowest values for just one category.
Follow along with the example file
SMALL Function Examples.xlsx
What Does the SMALL Function Do in Excel?
SMALL returns the kth smallest value from a range of numbers. You tell it which range to look at and which position from the bottom you want, and it hands back that value.
Think of it as MIN with a dial. MIN always gives you the single lowest number. SMALL lets you ask for the lowest, the second lowest, the third lowest, and so on, just by changing one number in the formula.
Syntax of the SMALL Function
=SMALL(array, k)
Arguments
- array – the range or array of numbers you want to pull a value from.
- k – the position from the bottom you want. Use 1 for the smallest, 2 for the second smallest, and so on.
A few things worth knowing before we get into the examples:
- If k is less than or equal to 0, or larger than the count of numbers in the range, SMALL returns the
#NUM!error. - If the range is empty, SMALL also returns the
#NUM!error. - SMALL only looks at numbers. It skips blank cells, text, and logical values (TRUE/FALSE) on its own, so you don’t have to clean the list first.
Example 1: Finding the Smallest Value with SMALL
Let’s start with the simplest case. Below is a list of race finish times in seconds for eight runners, and I want to pull out the single lowest time.
Here is the formula:
=SMALL(B2:B9,1)
The k value here is 1, so SMALL returns the smallest number in the range, which is the fastest time.
Pro Tip: When k is 1, SMALL gives you the exact same answer as MIN. Use MIN when you only ever want the single lowest value, and switch to SMALL the moment you need the second or third lowest.
Example 2: Finding the Second or Third Smallest Value
Now let’s go one step past the minimum. Here are the same eight runners and their finish times, and this time I want the second fastest time.
Here is the formula:
=SMALL(B2:B9,2)
Since k is 2, SMALL skips the lowest value and returns the next one up. To get the third smallest, you’d just change the k to 3.
Example 3: Getting the Bottom 3 Values as a Spilling List
Typing a separate formula for the smallest, second smallest, and third smallest gets old fast. If you have Excel 365, Excel 2024, Excel 2021, or Excel for the web, you can get all three at once with a single formula that spills.
Here are the same finish times for the eight runners.
Here is the formula:
=SMALL(B2:B9,SEQUENCE(3))
Instead of a single k, I’m feeding SMALL a small array of positions. The SEQUENCE function builds the list 1, 2, 3, so SMALL returns the smallest, second smallest, and third smallest in one go.
The result spills down into the cells below on its own. You don’t press Ctrl+Shift+Enter and you don’t drag anything down. Want the bottom five instead? Change the 3 to a 5.
Pro Tip: Make sure the cells below your formula are empty. If anything is in the way of where the result wants to spill, Excel shows a #SPILL! error instead of the values.
Follow along with the example file
SMALL Function Examples.xlsx
Example 4: Adding Up the Bottom N Values
Sometimes you don’t want to see the lowest values, you just want their total. A common case is scoring where you drop the highest results and keep the lowest few.
Below are quiz scores for eight students, and I want to add up the three lowest scores.
Here is the formula:
=SUM(SMALL(B2:B9,{1;2;3}))
The {1;2;3} inside SMALL asks for the three smallest scores at once, and SUM adds them together into a single total.
This is an array constant, so you type the curly braces yourself. To add up the bottom five, you’d write {1;2;3;4;5} instead.
Example 5: Making SMALL Ignore Errors in the Range
SMALL happily skips blanks and text, but an error cell is different. If any cell in the range holds an error like #N/A or #DIV/0!, SMALL passes that error straight through and your formula breaks.
Below are daily sensor readings, and one cell has an #N/A error where the sensor failed. I still want the lowest valid reading.
Here is the formula:
=MIN(IFERROR(B2:B9,""))
IFERROR swaps every error for an empty string, and since SMALL and MIN ignore text, the bad cell drops out of the calculation. For the smallest value, MIN reads cleaner than SMALL with a k of 1.
For the second smallest while ignoring errors, use =SMALL(IFERROR(B2:B9,""),2). Change the 2 to 3 for the third smallest.
Both this formula and the MIN formula above work normally in Excel 365, Excel 2021 and later. In older versions, confirm both with Ctrl+Shift+Enter. If you forget, the MIN formula quietly returns 4.2 instead of 3.5.
Example 6: Sorting a List from Smallest to Largest with SMALL
You can use SMALL to sort a whole column of numbers in ascending order, without touching the Sort button. Below are the finish times again, and I want them laid out from fastest to slowest in a new column.
Enter this SMALL formula in D2:
=SMALL(B2:B9,SEQUENCE(ROWS(B2:B9)))
ROWS counts the eight rows in B2:B9. SEQUENCE builds ranks 1 through 8, and SMALL returns the finish times from smallest to largest, spilling into D2:D9.
For the same sorted list with a shorter formula, use SORT in E2:
=SORT(B2:B9)
Pro Tip: Use SORT for a plain sort. The SMALL version helps you see how an array of ranks works.
Important: The SMALL version needs numbers in every cell of B2:B9. Blanks or text make the final ranks return #NUM!. Both formulas use this fixed range, so extend it if you add data.
These formulas need Excel 365, Excel 2021 or later, or Excel for the web.
Example 7: Finding the Smallest Value for One Category
Often your list has more than one group in it and you want the lowest value from just one of them. Below are delivery times in minutes along with the city each delivery went to, and I want the fastest delivery for New York only.
To get the fastest New York delivery time, enter this SMALL and FILTER formula in D2:
=SMALL(FILTER(B2:B10,A2:A10="New York"),1)
FILTER first pulls out only the delivery times for New York, then SMALL grabs the lowest one from that shorter list. Change the 1 to a 2 and you get the second fastest New York delivery.
If you only need the minimum, MINIFS is the shorter option. Enter this formula in G2:
=MINIFS(B2:B10,A2:A10,"New York")
If no New York rows match, MINIFS returns 0, while this SMALL and FILTER formula returns #CALC!.
FILTER needs Excel 365, Excel 2024, Excel 2021, or Excel for the web. MINIFS also works in Excel 2019. On versions without either function, you’d use an array formula like =MIN(IF(A2:A10="New York",B2:B10)) confirmed with Ctrl+Shift+Enter instead.
A Few Things to Keep in Mind
- SMALL ignores blanks and text, but not errors. Blank cells and text values are skipped automatically. A
#N/Aor#DIV/0!in the range breaks the result, so wrap the range in IFERROR when the data might be messy. - Duplicates count as separate values. If two cells both hold the lowest number, SMALL with k=1 and k=2 both return that same number. It doesn’t collapse duplicates into one.
- Watch the k value. If k is 0, negative, or bigger than the count of numbers in the range, you get the
#NUM!error. - Spilling formulas need Excel 365, Excel 2024, Excel 2021, or Excel for the web. The SEQUENCE and FILTER examples above use dynamic arrays. On older versions, use the per-value or Ctrl+Shift+Enter alternatives shown alongside them.
That’s how the SMALL function works in Excel, from grabbing a single low value to spilling the bottom few, summing them, sorting a list, and pulling the lowest value for one category.
It’s a small function, but once you start reaching past MIN, it earns a spot in your toolkit. I hope you found this tutorial helpful.
Other Excel Articles You May Also Like:
