Skip to content

Excel function · Statistics

Excel SMALL function

SMALL returns the k-th smallest value in a range: the three lowest quotes, the shortest delivery times, the second earliest date. k = 1 is the smallest value and equals MIN.

In Turkish Excel: KÜÇÜK

What it does

SMALL works like LARGE but counts from the smallest. =SMALL(B2:B20,2) returns the second smallest value. Empty cells and text are skipped, while 0 counts as one of the smallest values.

Put the k values 1, 2, 3… in a column and fill the formula down, and you get a list of the numbers from smallest to largest that re-sorts itself whenever the source changes. In Microsoft 365, SORT does this in a single formula.

Dates are numbers, so SMALL also finds the earliest dates. For the smallest value that meets a condition use MINIFS; to find the rank of a value, RANK.EQ.

Syntax

English Excel=SMALL(array, k)
Turkish Excel=KÜÇÜK(dizi; k)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
arrayRequiredThe range to search. Empty cells and text are skipped.
kRequiredWhich smallest value to return; 1 is the smallest. It cannot be less than 1 or more than the number of values in the range.

Examples

1The three lowest quotes and their suppliers

Column D holds 1, 2 and 3. Column E finds the quote at each position, and column F returns the supplier with INDEX and MATCH.

E2fx=SMALL($B$2:$B$6,D2)
The three lowest quotes and their suppliers: example table
ABCDEF
1SupplierQuotePositionQuoteSupplier
2Brown & Sons48,250.00146,900.00Northgate Ltd
3Northgate Ltd46,900.00247,450.50Star Market
4Harbour Trade51,300.00348,250.00Brown & Sons
5Star Market47,450.50
6Oak Builders49,800.00
  • E2 (filled down to E4)=SMALL($B$2:$B$6,D2)
  • F2 (filled down to F4)=INDEX($A$2:$A$6,MATCH(E2,$B$2:$B$6,0))

The lowest quote comes from Northgate Ltd (46,900.00), the second from Star Market and the third from Brown & Sons.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2List delivery times from shortest to longest

The source list is not sorted. With the positions 1-5 in column D, SMALL lists the times from smallest to largest; when the source changes, the sorted list follows.

E2fx=SMALL($B$2:$B$6,D2)
List delivery times from shortest to longest: example table
ABCDE
1OrderDaysPositionTime
2S-1001412
3S-1002223
4S-1003734
5S-1004345
6S-1005557
  • E2 (filled down to E6)=SMALL($B$2:$B$6,D2)

Column E reads 2, 3, 4, 5, 7.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3The three earliest application dates

Dates are numbers, so SMALL finds the earliest ones. The result cells are given a date format.

E2fx=SMALL($B$2:$B$6,D2)
The three earliest application dates: example table
ABCDE
1ApplicationDatePositionDate
2B-20114-Oct-2025129-Sep-2025
3B-2023-Oct-202523-Oct-2025
4B-20321-Oct-202538-Oct-2025
5B-20429-Sep-2025
6B-2058-Oct-2025
  • E2 (filled down to E4)=SMALL($B$2:$B$6,D2)

The first application came on 29-Sep-2025, the second on 3-Oct-2025 and the third on 8-Oct-2025.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#NUM!

Cause: k is less than 1 or larger than the number of values in the range.

Fix: Limit k with =COUNT(B2:B20); in a sorted list with spare rows, wrap the formula in IFERROR.

Smallest value is 0

Cause: Rows without data were filled with 0, and SMALL treats zero as a real value.

Fix: Leave the cell empty when there is no data, or use MINIFS to skip zeros.

Good to know

  • To keep a long sorted list without errors in the spare rows, use =IFERROR(SMALL($B$2:$B$20,D2),"").
  • For the average of the three lowest quotes in Microsoft 365, use =AVERAGE(SMALL(B2:B20,SEQUENCE(3))).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Show the three lowest quotes from the quote list in a separate table, from lowest to highest

Convert to table

Bring your quote list into the tool and type this. Nexell puts the three lowest quotes into a separate table, lowest first, and shows it in a preview first; once you approve, you download it as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I find the 3 smallest values in Excel?

Type =SMALL($B$2:$B$20,1), =SMALL($B$2:$B$20,2) and =SMALL($B$2:$B$20,3), or put the k values in a column and fill one formula down.

What is the difference between SMALL and MIN?

MIN returns only the smallest value. SMALL asks which smallest you want; =SMALL(B2:B20,1) equals MIN.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free