SMALL Function

Returns the k-th smallest number in a set.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

SMALL(range, 1) is the smallest value (same as MIN), SMALL(range, 2) the second smallest, and so on. Tied values each take a position.

Use it for bottom-N lists, or to sort numbers with a formula.

Syntax

=SMALL(array, k) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range or array of numbers.
kYesThe position from the bottom: 1 = smallest.

Examples

Example 1 — Bottom 3 sales results

Ben and Gus tie on 48,500, so it appears twice.

E2fx=SMALL($C$2:$C$8,D2)
ABCDE
1RepRegionSalesPlaceSales
2AnaNorth62000139800
3BenSouth48500248500
4CyNorth50000348500
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=SMALL($C$2:$C$8,D2) Explain

Example 2 — Sort with a formula

With ROWS counting up as it fills down, SMALL lists the values in ascending order.

=SMALL($C$2:$C$8,ROWS($E$2:E2)) Explain

Common mistakes

  • #NUM! k is 0 or less, or bigger than the number of values.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.