Small Formula Excel

What this does

SMALL is the mirror of LARGE: it returns the kth smallest value, with k=1 equivalent to MIN. Beyond bottom-n lists, it has a second and less obvious use — combined with IF it extracts the row numbers of every match, which is the classic pre-dynamic-array way to return multiple lookup results. In modern Excel, FILTER does that far more simply, but the SMALL idiom is what you will find in older workbooks and it still works everywhere. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “small formula excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Delivery times in C2:C400: =SMALL(C2:C400, 1) is the fastest and =SMALL(C2:C400, 3) the third fastest. The worst-performing three, listed downward: =SMALL($C$2:$C$400, ROW()-1) filled down. Ignoring zeros when they mean "not measured": =SMALL(IF(C2:C400>0, C2:C400), 1). SMALL is the bottom-n counterpart to LARGE, and its IF variant is the pattern behind most pre-FILTER multiple-match formulas you will inherit. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. The short version of “small formula excel”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.

Common mistakes

• Including zeros or blanks that mean "no data", which then occupy the bottom positions and hide the real minimum.
• A k outside the range of available values, which returns #NUM!.
• Using the old SMALL-with-IF idiom for multiple matches when FILTER is available and far clearer.

Frequently asked questions