The Excel MIN function, explained interactively.
MIN returns the smallest number in a range. Like MAX at the other end, its trick isn’t complexity — it’s what MIN silently ignores: text, empty cells, and booleans all get skipped without error. Useful 99% of the time, occasionally a bug waiting to bite you.
How to use MIN
- Type
=MIN(and select the range of numbers — one contiguous range or a comma-separated list of ranges and numbers. - Close the parenthesis. MIN returns the smallest numeric value — text, blanks, and booleans are ignored silently.
- For a conditional minimum (“fastest endpoint in region X”), use
MINIFS. For the Nth-smallest, useSMALL(range, N). - To include booleans or text-as-zero, swap MIN for
MINA— rare, but useful for flag-coded data.
The demo sheet has one text cell ("N/A") — watch how MIN skips it and still returns the fastest numeric response time.
| A | B | D | |
|---|---|---|---|
| 1 | Endpoint | Response (ms) | Min |
| 2 | /api/search | 142 | 74 |
| 3 | /api/users | 98 | |
| 4 | /api/orders | 215 | |
| 5 | /api/auth | 187 | |
| 6 | /api/metrics | N/A | |
| 7 | /api/uploads | 74 | |
| 8 | /api/billing | 156 |
=MIN(B2:B8). MIN ignored the text cell ("N/A" at B6) and the smallest numeric value is 74 ms (row 7, /api/uploads).MIN syntax and arguments
One required argument, up to 255 total. See Microsoft’s official MIN reference for the canonical specification.
MIN(TRUE, 5)) are coerced to 1/0. The common usage is a single range.MIN examples
Four patterns that cover almost every real-world MIN use.
Example 1: MIN for a plain minimum
The canonical case — lowest price, fastest time, smallest score.
Returns the smallest numeric value in column B. Text and blanks are silently skipped. Returns 0 if the range has no numbers — not an error, just a sometimes-surprising fallback.
Example 2: MIN with a floor to clamp results
Combine MIN with a literal to guarantee a ceiling — a classic “cap” pattern.
Returns whichever is smaller: the value in B2, or 100. Useful for capping fees, quotas, progress bars. Pair with MAX for a full clamp: =MIN(MAX(B2, 0), 100) keeps the value in [0, 100].
Example 3: MINIFS for a conditional minimum
Smallest value that also meets a criterion.
Returns the smallest C where A is Food AND B is 2026. Same multi-criteria pattern as SUMIFS — aggregate range first, then range/criteria pairs. Requires Excel 2019 / 365 or Google Sheets.
Example 4: MIN combined with INDEX/MATCH
Find not just the minimum, but the label of the row that holds it (fastest endpoint, cheapest SKU).
MIN finds the smallest value; MATCH finds its row; INDEX plucks the row’s label. INDEX + MATCH canonical use case. Ties break to the first occurrence.
Common MIN errors and fixes
Four failure modes, each with what to check and how to recover.
MIN returns 0 when your numbers are all positive
Cause: the values look like numbers but are stored as text. MIN skips text silently and, finding no real numbers, falls back to 0.
Check with =ISNUMBER(B2). Coerce via Data → Text to Columns → Finish, or multiply by 1 in a helper column. CSV and PDF imports are the usual culprits.
MIN including hidden rows
Cause: MIN does not respect filter / hidden rows. Even values hidden by a filter participate.
Use SUBTOTAL(5, B2:B100) or AGGREGATE(5, 3, B2:B100) to respect visibility. Both skip hidden rows; AGGREGATE can also skip errors.
MIN choosing the wrong cell on ties
Cause: multiple rows share the minimum value. MIN returns the value; MATCH picks the row and returns the first occurrence.
If you need the last tied row, reverse the range with SORTBY, or add a tie-breaker column (row number, timestamp) before MATCHing.
MIN counting negative numbers you meant to exclude
Cause: a negative value in the range that doesn’t belong (import artefact, flag).
Filter with MINIFS: =MINIFS(B:B, B:B, ">=0") excludes negatives. Or pre-clean the data with an IF helper column.
MIN vs MAX, MINIFS & SMALL
Four aggregators. Pick by which end of the distribution you need and whether the pick is conditional or ordinal.
| Function | Returns | Conditions | Typical use |
|---|---|---|---|
| MIN | Smallest value | None | Best price, fastest time, floor |
| MAX | Largest value | None | Top score, highest order, ceiling |
| MINIFS | Smallest matching value | One or many (AND) | Fastest response for a specific endpoint + day |
| SMALL | Nth smallest value | None | 2nd or 3rd smallest — ranking, bottom-N lists |
Rule of thumb: MIN for single-ended minima, MINIFS when a condition is involved, SMALL for the Nth rank. SMALL(range, 1) is equivalent to MIN but reads better when you also need the 2nd or 3rd smallest in nearby cells. Pair with MAX / LARGE for the symmetric top-end case.
MIN frequently asked questions
6.01Does MIN count text values or booleans?▸
No. MIN silently skips any cell whose value isn’t a number — text like "N/A", empty cells, and booleans are all ignored without error. If you need booleans coerced (TRUE = 1, FALSE = 0), useMINA instead.
6.02How do I find the minimum that matches a condition?▸
Use MINIFS: =MINIFS(C2:C100, A2:A100, "Food") returns the minimum of C where A is “Food”. Introduced in Excel 2019 / 365. In older versions, use {=MIN(IF(A2:A100="Food", C2:C100))} as an array formula (Ctrl+Shift+Enter).
6.03Why does MIN return 0 on an empty range?▸
MIN on an all-text, all-blank, or empty range falls back to 0 — not an error. If 0 would be meaningful in your data (e.g. prices or ages), wrap with =IF(COUNT(range)=0, "—", MIN(range)) to surface empty results explicitly.
6.04How do I get the second-smallest value (not just the min)?▸
Use SMALL: =SMALL(A2:A100, 2) returns the 2nd-smallest. SMALL(range, 1) is equivalent to MIN; SMALL(range, 3) gives the 3rd-smallest; and so on. Mirrors LARGE for the Nth-largest case.
6.05What’s the difference between MIN and MINA?▸
MIN ignores text and booleans. MINA treats TRUE as 1, FALSE as 0, and text as 0 — so a range with any text-as-zero values can pull the minimum all the way down to 0. Use MIN for normal numeric columns; MINA is rare and mostly exists for compatibility.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.