Excel MIN Without Blanks: What the Formula Ignores

If your values are in A2:A10, use =MIN(A2:A10) to find the smallest number while ignoring blank cells. Excel’s MIN function already skips empty cells and text in a referenced range, so you usually do not need a special “ignore blanks” formula.

How MIN handles blank cells

MIN returns the lowest numeric value in the cells or values you give it. When you refer to a range, Excel ignores cells that are truly empty and also ignores text entries in that range. For example, if A2:A5 contains 14, an empty cell, 9, and 21, the minimum is 9. The blank does not get treated as zero and does not affect the result.

This distinction matters because an empty cell and a numeric zero are different. A zero is a number, so MIN includes it. If a range contains 14, 0, and 9, the minimum is 0. That answer is correct according to the data; it may not be the answer you intended if zero means “not entered” in your workbook.

MIN can take a range or several separate cell references. A range is usually easier to maintain as your list grows. The result is a single number, not a filtered copy of the source data, and the original cells remain unchanged.

Check the range and the expected result

Before changing the formula, check that the reference covers the values you want to compare and excludes headings or unrelated totals. If the range includes a header, MIN normally ignores its text, but a clear data-only reference is easier to inspect. Also confirm that the cells that look blank are not actually zeros, error values, or text that resembles a number.

For a simple example, suppose A2:A7 contains 18, a blank cell, 6, a formula that displays nothing, 0, and 11. MIN ignores the blank and the text result that displays nothing, but it includes the numeric zero. The answer is therefore 0, not 6. If the zero is a legitimate measurement, that is the correct minimum. If zero is a placeholder, use a method that excludes it.

A useful first check is to click a cell that appears empty and look at the formula bar. A genuinely empty cell has no value or formula. A cell containing a formula such as one that returns an empty string is not physically empty, even though it may look the same on the worksheet. That difference can matter when you build conditions around the range.

Exclude zero only when zero is a placeholder

If the range contains positive measurements and zeros mean “no observation,” use MINIFS to require a value greater than zero. With the sample values in A2:A7, the qualifying numbers are 18, 6, and 11, so the smallest qualifying number is 6.

With values or blanks in A2:A7 and zero used as a placeholder, enter:

=MINIFS(A2:A7,A2:A7,">0")

MINIFS is available in Excel for Microsoft 365 and Excel 2019 or later. This criterion does more than remove blanks: it excludes zero and every negative number too. Use it only when the numbers you want to keep are known to be positive. If negative values are valid, this formula would wrongly discard them.

MINIFS returns 0 when no values meet its criteria. That can be indistinguishable from a genuine minimum of zero, so consider checking whether any qualifying entries exist if an empty result is possible. For a positive-only dataset, a 0 result can mean there were no positive numbers to evaluate, not necessarily that the minimum measurement was zero.

Keep negative numbers while excluding zeros

When negative numbers are valid but zero is only a placeholder, a “greater than zero” condition is not appropriate. In Microsoft 365 or Excel 2021, FILTER can first retain numeric entries that are not zero, and MIN can then find the smallest remaining value. This keeps negative numbers in the comparison.

With the mixed values in A2:A7, including possible blanks, text, formula-generated empty strings, and zero placeholders, use:

=IFERROR(MIN(FILTER(A2:A7,ISNUMBER(A2:A7)*(A2:A7<>0))),"No nonzero numbers")

If the nonzero numeric values are 18, 6, and 11, the result is 6. If the range instead contains -4, 0, and 8, the result is -4. ISNUMBER keeps numeric values and rejects text, blanks, and formulas that return an empty string; the second condition removes zero. IFERROR supplies a readable message if no qualifying number remains. FILTER is available in Microsoft 365 and Excel 2021 or later.

This formula is more explicit than ordinary MIN, but use it only when the rules for your data require those exclusions. If zero is a valid measurement, removing it would produce a misleading result. If you only need to ignore ordinary blank cells, the simpler MIN formula is clearer and less likely to hide useful data.

What about cells containing formulas that look blank?

A formula may display an empty-looking result, often because its output is an empty string. To MIN, that result is text rather than a numeric value, so MIN ignores it when it is part of a referenced range. In that common situation, a formula-filled column with some empty-string results can still be passed to MIN without those results becoming zero.

However, a formula that returns the number 0 is numeric, even if a number format or conditional formatting makes it appear blank. MIN includes that zero. If the displayed result and the underlying value seem inconsistent, select the cell and inspect the formula bar, or temporarily apply a standard number format to see what value is stored.

There is another reason to distinguish formulas that look blank: criteria functions and functions that count blanks may classify them differently from an actually empty cell. Do not assume a “not blank” test will always match the visual appearance of the sheet. When the goal is specifically to include numeric values, testing with ISNUMBER makes the rule explicit.

Common problems and ways to diagnose them

The result is zero: Look for a real numeric zero in the range. A zero can also be hidden by formatting. If zero is a placeholder, choose a zero-excluding formula rather than expecting MIN to treat it as blank.
The result is larger than expected: Check that the range includes the cell with the smaller value and that the value is numeric. Numbers imported as text may be ignored by MIN when they are in a referenced range.
The formula returns an error: Look for an error value such as #N/A or #VALUE! inside the referenced cells. Unlike ordinary text, an error in the range can cause MIN to return an error.
The answer changes after adding data: Verify that the formula’s range includes the newly added row. A fixed reference such as A2:A10 will not automatically include entries below row 10 unless the formula or source range is extended.
Blank-looking cells affect a condition: Check whether the cells are truly empty, contain formulas returning empty strings, or contain zero values hidden by formatting. Those cases can look alike but represent different data.

If numbers stored as text should count, convert them to real numbers rather than relying on MIN to interpret them. You can often correct imported data with Excel’s conversion options or a deliberate data-cleaning step. After conversion, check a few cells and confirm that arithmetic operations treat them as numbers.

Choosing the right approach

Use the simplest formula that matches the meaning of your data. In most ordinary lists, blanks are merely missing entries and zero is a valid number; use MIN. If zero marks missing or invalid observations, exclude zero deliberately. If the range contains text, formulas that display blanks, or mixed positive and negative values, decide which of those should qualify before writing a more complex formula.

Ignore empty cells, keep zeros and negatives: Use MIN on the range.
Keep only positive values: Use MINIFS with a greater-than-zero criterion.
Keep any numeric value except zero: In Microsoft 365 or Excel 2021, use FILTER with ISNUMBER and a not-equal-to-zero test.
Show a message if there are no qualifying values: Wrap the filtered minimum in IFERROR, as in the example above.

These formulas calculate a minimum; they do not remove rows or alter the source list. If you need to inspect only qualifying entries, use a filter or a separate filtered output as well. Keeping calculation and data-cleaning tasks separate makes it easier to audit what was excluded and why.

Frequently Asked Questions

Does Excel MIN ignore blank cells automatically?
Yes. When a range is referenced, MIN ignores truly empty cells and text entries. A numeric zero is not blank, so it is included in the minimum.

How do I find the minimum while ignoring zeros?
If all valid values must be positive, use MINIFS with a greater-than-zero condition. If negative values are valid and only zero should be excluded, use a numeric FILTER condition that removes zero instead.

Does MIN ignore a formula that returns an empty string?
Yes, when that result is in a referenced range, it is text rather than a number, so MIN ignores it. A formula that returns numeric 0 is included.

Why does MIN return zero when there seem to be no numbers?
A zero may be present or hidden by formatting. Also, some conditional-minimum formulas return 0 when no values meet their criteria. Inspect the source cells and the formula’s conditions before treating zero as a real result.

Can MIN calculate the smallest value while ignoring text?
Yes. Text in a referenced range is ignored, while numbers stored as text may also be ignored rather than treated as numeric values. Convert text-formatted numbers if they should participate in the calculation.

What if the range contains an error value?
An error in the referenced range can make MIN return an error. Find and resolve the source error, or build a formula that explicitly filters for numeric cells if that matches the intended calculation.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.