Statistical

SMALL Function in Excel

Returns the k-th smallest value in a data set.

Syntax

  • =SMALL(array, k)

Arguments

  • array (required): Range or array of values
  • k (required): Position from smallest (1=smallest)

Examples

  • =SMALL(A1:A100, 1) - Smallest value - Result: Minimum
  • =SMALL(A1:A100, 5) - 5th smallest - Result: 5th lowest

Excel SMALL function documentation

  • SMALL returns the k-th smallest value in a data set — mirror of [LARGE](/functions/large/).
  • Syntax: =SMALL(array, k). k=1 returns the minimum.
  • Use for bottom performers, lowest bids, and outlier detection from the low end.
  • Related: [MIN](/functions/min/), [LARGE](/functions/large/), [PERCENTILE](/functions/percentile/).

SMALL syntax and errors

  • array: numeric range; text ignored.
  • k: 1 = smallest; k > numeric count → #NUM!.
  • Duplicates handled like LARGE — tied values occupy consecutive k ranks.
  • Lock array with $ when building a top/bottom summary table.

Step-by-step: bottom 3 expense lines

  • Step 1 — Expenses in C2:C200.
  • Step 2 — Smallest: =SMALL(C2:C200, 1).
  • Step 3 — Build k column 1,2,3 and =SMALL($C$2:$C$200, E2).
  • Step 4 — Compare to [LARGE](/functions/large/) for range spread.
  • Step 5 — Flag outliers: =IF(C2<=SMALL(C:C,3),"Review","").

SMALL vs MIN vs QUARTILE

  • MIN — single minimum.
  • SMALL — nth smallest for ranked lists.
  • QUARTILE — division points, not nth discrete value.
  • Pair SMALL+LARGE for range width: =LARGE()-SMALL().

Worked examples to copy

  • Minimum via SMALL: =SMALL(A1:A10, 1).
  • 2nd lowest score: =SMALL(scores, 2).
  • From cell k: =SMALL(B2:B50, D1).
  • Positive min: =SMALL(FILTER(B:B,B:B>0),1).

People also ask

  • SMALL #NUM!? — k too large for numeric count in array.
  • SMALL with negatives? — Yes; most negative is smallest.
  • SMALL vs RANK? — SMALL returns value; RANK returns position of one value.
  • SMALL Google Sheets? — Same syntax.

Common errors

  • #NUM! if k > number of values or k < 1

Use cases

  • Bottom N values
  • Ranking
  • Threshold analysis

Frequently asked questions

  • How do I get the bottom 3 values? Use =SMALL(A1:A100, {1,2,3}) to return the 3 smallest values as an array. Or use =SMALL(A1:A100, SEQUENCE(3)) in newer Excel for a spilling result.
  • How do I find the smallest positive number? Combine with IF: =SMALL(IF(A1:A100>0, A1:A100), 1). In older Excel, enter as array formula with Ctrl+Shift+Enter. This ignores zeros and negatives.
  • How do I average the bottom 10%? Use =AVERAGEIF(A1:A100, "<="&PERCENTILE.INC(A1:A100, 0.1)) to average values at or below the 10th percentile. Or use SMALL in AVERAGE: =AVERAGE(SMALL(A1:A100, SEQUENCE(10))).

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SMALL

  • Bottom N values — common Statistical scenario for SMALL.
  • Ranking — common Statistical scenario for SMALL.
  • Threshold analysis — common Statistical scenario for SMALL.

SMALL in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • SMALL syntax: =SMALL(array, k)
  • array (required): Range or array of values
  • k (required): Position from smallest (1=smallest)
  • Confirm SMALL arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SMALL arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • LARGE (/functions/large/): Returns the k-th largest value in a data set.
  • MIN (/functions/min/): Returns the smallest value in a set of values.
  • RANK.EQ (/functions/rank-eq/): Returns the rank of a number in a list of numbers.

Errors to watch for

  • #NUM! if k > number of values or k < 1 — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SMALL on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SMALL worked examples to copy

  • =SMALL(A1:A100, 1) — Smallest value. Expected result: Minimum.
  • =SMALL(A1:A100, 5) — 5th smallest. Expected result: 5th lowest.

SMALL reference summary for crawlers and offline review

  • SMALL belongs to the Statistical category in Excel. Returns the k-th smallest value in a data set.
  • Full syntax: =SMALL(array, k). Open /functions/small/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if k > number of values or k < 1
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.