SMALL in Excel: Find the Nth Smallest Value with Examples

How-to basics · Updated November 20, 2026

In Excel, use the SMALL function to return the 1st, 2nd, 3rd, or any other chosen smallest number in a range: =SMALL(B2:B7,3) returns the third-smallest value. The second argument tells Excel which position to find, counting upward from the lowest value.

What the SMALL function does

SMALL is a statistical function for retrieving a value by its position in an ascending list. It does not rearrange or sort your worksheet; it calculates the requested result and returns it in the cell containing the formula. This is useful when you need a particular low value but want to leave the original data in its existing order.

For a concrete example, suppose B2:B7 contains 18, 5, 12, 5, 30, and 9. With those values in B2:B7, =SMALL(B2:B7,3) returns 9: ordered from smallest to largest, the values are 5, 5, 9, 12, 18, and 30. The repeated 5s each occupy a position, so the third item is 9.

The function has two required inputs: an array or range containing numbers, and a positive whole number that specifies the position. In the formula above, B2:B7 is the data range and 3 asks for its third-smallest numeric value. You can enter the formula in any empty cell, such as D2, and press Enter.

How to enter a SMALL formula

To find a value in your own workbook, select the cell where the answer should appear, type an equals sign followed by SMALL, and provide the range and the position separated by a comma. For example, with test scores in C2:C21 and a request for the fifth-lowest score, use =SMALL(C2:C21,5). Replace the range and 5 with the cells and rank you need.

  1. Identify the cells containing the numeric values you want to compare.
  2. Choose the rank: use 1 for the lowest value, 2 for the second-lowest, and so on.
  3. Enter the formula in a blank cell and press Enter.
  4. Check that the requested rank does not exceed the number of numeric values in the range.

You can also put the rank in a cell so it is easy to change. If the values are in B2:B7 and D2 contains 3, enter =SMALL(B2:B7,D2) in E2. With the example values 18, 5, 12, 5, 30, and 9, the result is 9. Change D2 to 1 and the result becomes 5; change it to 4 and the result becomes 12. The formula remains the same while the requested position changes.

For a growing data list, consider using a range that includes the rows where new numbers will be entered, or convert the data to an Excel table and use a structured reference. A broader range can be convenient, but it should not accidentally include unrelated numeric values, such as totals or notes elsewhere on the sheet.

Understanding rank, ties, blanks, and text

SMALL counts duplicate values separately. In the example range, 5 is both the first-smallest and second-smallest number. Therefore, asking for the second-smallest value returns 5, not the next distinct value, 9. This distinction matters when ranking scores, prices, or processing times: the function returns a position in the list of values, not a position among unique values.

Blank cells and text in a referenced range are ignored by SMALL. Numeric values stored as text are not treated as ordinary numeric entries in that reference, so they may be skipped. If a result seems to overlook a number, check whether the cell is genuinely numeric: select it and inspect its contents, or convert the value to a number before relying on the ranking. Error values in the range can cause the formula to return an error rather than a number.

Zero is a number, not a blank. It participates in the ranking and can be the smallest result. Negative numbers also count and sort below zero; for example, -4 is smaller than 0. If your range contains zeros that mean “not entered” in your particular dataset, decide whether they should count before interpreting the result. Do not assume Excel will automatically disregard them.

Finding several of the smallest values

For a short report, you can write separate formulas for each rank. With values in B2:B7, =SMALL(B2:B7,1) returns 5 and =SMALL(B2:B7,2) also returns 5 because the minimum occurs twice. =SMALL(B2:B7,3) returns 9. These formulas show why ties can produce repeated results in a list of the lowest values.

In Microsoft 365 or Excel 2021, the SEQUENCE function can supply several rank numbers at once. With the six values in B2:B7 and room for results to spill into the cells below the formula, enter =SMALL(B2:B7,SEQUENCE(3)). Excel returns the first three values in ascending order—5, 5, and 9—in three cells. The destination cells must be empty; otherwise Excel may show a spill error. If you use an Excel version without dynamic arrays, enter individual SMALL formulas with ranks 1, 2, and 3 in separate cells and fill them down.

SMALL can also return a high value when you calculate the appropriate position from the count. With six numeric entries in B2:B7, the second-largest value is the fifth-smallest, so =SMALL(B2:B7,COUNT(B2:B7)-1) returns 18. COUNT returns 6 for the example, and 6 minus 1 is 5; the fifth value in ascending order is 18. In many cases, LARGE is clearer when your goal is explicitly to find the largest values.

Using SMALL with a condition

Sometimes you want the smallest number only among rows that meet a condition, such as the lowest sales figure for one region. In Microsoft 365 or Excel 2021, FILTER can create the qualifying list for SMALL. Suppose A2:A7 contains region names and B2:B7 contains the corresponding amounts. If the East rows have amounts 18, 12, and 9, enter =SMALL(FILTER(B2:B7,A2:A7="East"),2) to get the second-smallest East amount, 12.

This approach is useful because the condition is visible in the formula and the source data remains unchanged. However, the spelling and contents of the region cells must match the condition. If no rows match, FILTER returns an error unless you provide its optional if-empty argument; handle that possibility if the category may be absent. FILTER is available in Microsoft 365 and Excel 2021, but not in older perpetual versions such as Excel 2019.

For older Excel versions, a conditional minimum or nth-smallest calculation may require a more involved array formula or a helper column. The exact method depends on the condition and Excel version. A helper column that marks qualifying rows can be easier to audit than a dense formula, especially in a workbook maintained by other people. Avoid copying a formula from a newer version without checking that every function in it is supported by your Excel release.

SMALL compared with MIN, LARGE, and sorting

MIN returns the lowest numeric value in a range, which is equivalent to asking SMALL for rank 1. SMALL is more flexible when the question is “what is the second-lowest?” or “what is the fifth-lowest?” MIN alone does not provide that positional choice. LARGE works in the opposite direction: it returns the value at a specified position counting down from the largest.

Sorting is better when you need to see or rearrange the entire ordered dataset, inspect which records produced the values, or apply several manual checks. SMALL is better when you need a particular result in a formula without changing the row order. It returns the number only; it does not tell you which person or record owns that number. To identify the matching row, use a suitable lookup approach and take care with duplicates, because the same value can belong to more than one record.

SMALL is also not the same as finding the next distinct value. If repeated values should count only once, deduplicate or build a unique list first, then rank that list. Microsoft 365 and Excel 2021 include UNIQUE for creating unique values; versions without it may require a helper column or another method. Decide whether duplicates represent separate observations before choosing a technique.

Common errors and troubleshooting

  • #NUM! usually means the requested rank is less than 1 or larger than the number of numeric values in the range. Check the value in the second argument and confirm how many numeric entries Excel can use.
  • #VALUE! can occur when the rank argument is not a usable number, such as text entered where a numeric position is expected. Check the rank cell and any formula that supplies it.
  • An unexpected result may mean that zeros or negative numbers were included, duplicates occupy multiple positions, or values stored as text were ignored. Inspect the source cells rather than assuming the displayed values tell the whole story.
  • A spill error from a formula using SEQUENCE means Excel cannot place all returned results in the required cells. Clear the cells below the formula or move it to an open area.
  • Filtering does not change the answer because SMALL normally evaluates the range, including rows hidden by a filter. Hiding a row is not the same as excluding its value from the calculation.

When a workbook contains imported data, it can help to check a few cells directly. A value may look like a number but be stored as text, while a blank-looking cell may contain a formula returning an empty string. These details can affect other calculations even when the worksheet appears tidy. Clean or validate source data before building a ranking that people will use for decisions.

Frequently Asked Questions

What does SMALL do in Excel?
SMALL returns the numeric value at a position you specify, counting from the lowest value upward. Use rank 1 for the minimum, rank 2 for the second-smallest entry, and so on.

How do I find the second-smallest number in Excel?
Use SMALL with your data range and 2 as the rank. For values in B2:B7, the formula is =SMALL(B2:B7,2). If the minimum appears twice, the second-smallest result will be that same minimum.

Does SMALL ignore zero or duplicate values?
No. Zero is included as a numeric value, and duplicates each occupy a position. Blank cells and text in a referenced range are generally ignored, but duplicate numbers are not removed.

Why does SMALL return #NUM!?
The requested rank may be zero, negative, or greater than the number of numeric values in the range. Check the second argument and verify that your selected range contains enough usable numbers.

Can SMALL ignore filtered-out rows?
Not by itself. SMALL generally includes values from rows hidden by a filter. If the result must use only rows matching a condition, use a conditional method such as FILTER with SMALL in a supported Excel version, or create a helper range containing only eligible values.

Can SMALL return the value and its matching name?
SMALL returns only the value. A separate lookup can retrieve a related name or record, but ties require care because more than one row may contain the returned number. Decide how duplicates should be handled before selecting the lookup method.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.