If your numbers are in B2:B7, enter =MIN(B2:B7) in an empty cell; Excel returns the smallest number in the range. For example, if the cells contain 18, 7.5, a blank, -2, 7.5, and 0, the result is -2. You need a workbook with the values you want to compare, and a blank cell for the answer.
What You’ll Need
- A Microsoft Excel workbook containing the values you want to check. These instructions are for Excel for Microsoft 365 on Windows.
- An empty worksheet cell for the result, if you plan to use a formula. You can also select the values and show the minimum on the status bar without placing a result in a cell.
- A clear idea of which rows should count. For example, decide whether the answer should include zero, negative values, or rows hidden by a filter.
Step-by-Step
- Check the range you want to compare. Find the column or cells containing the numbers. Confirm that the range starts and ends where you expect: including a header is usually harmless to MIN, but accidentally leaving out the final row can change the result. If the list has gaps, you can still include the blank cells in the range. In the example, the values occupy B2:B7, with a blank at B4.
- Choose an empty cell for the answer. Click a blank cell outside the data, such as D2. If you put the formula inside the range it is examining, Excel may report a circular reference or create a confusing result. Make sure the answer cell is not inside a table column that will automatically copy the formula into every row unless that is what you want.
- Enter the MIN formula and press Enter. Type the formula shown in the opening example into the answer cell and press Enter. Excel evaluates the range and displays its smallest numeric value. With the sample values 18, 7.5, blank, -2, 7.5, and 0 in B2:B7, the displayed answer is -2. The formula updates when a value in the referenced range changes. If you add more values below row 7, however, expand the range or use an Excel table so the new rows are included.
- Check that the result makes sense. Scan the source values for the result. In the sample, -2 is lower than 0 and every positive number, so it is the correct minimum. If the answer seems wrong, check whether a negative number or zero should count, whether the range includes all relevant rows, and whether any apparent numbers are stored as text.
- Format the result if needed. MIN returns a number; it does not automatically apply a currency, date, or percentage format. Select the result cell and use the number-format controls on the Home tab to match the data. Formatting changes how a value looks, not which value MIN chooses. For instance, if the range contains prices, a currency format makes the result easier to read but does not change the calculation.
- Optionally check the minimum without a formula. Select the numeric cells, then right-click the status bar at the bottom of the Excel window and turn on Minimum if it is not already shown. The status bar displays the lowest selected number. This is useful for a quick check, but it does not place a result in the worksheet, so a formula is better when you need a saved, reusable answer. On Mac, select the range and use the status bar’s context menu as well; the exact appearance can vary by Excel version.
To find the lowest value that meets a condition: MIN checks every numeric value in its range. If you need the minimum for a category, use MINIFS. With regions in A2:A7 (East, West, East, East, West, East) and amounts in B2:B7 (18, 7.5, 9, 20, 7.5, 14), enter =MINIFS(B2:B7,A2:A7,"East") in an empty cell. The result is 9, the smallest amount for East. The amounts and region cells must line up row by row, and the criteria range should be the same size as the minimum range.
To find the label beside the lowest value: With item names in A2:A7 (Alpha, Beta, Gamma, Delta, Epsilon, Zeta) and values in B2:B7 (18, 7.5, blank, -2, 7.5, 0), enter =XLOOKUP(MIN(B2:B7),B2:B7,A2:A7) in an empty cell. It returns Delta, the name on the row containing -2. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If the lowest number appears more than once, this formula returns the label for its first matching row.
To check only rows left visible by a filter: With values in B2:B7 as in the first example, suppose a filter hides row 5, the row containing -2. Enter =SUBTOTAL(5,B2:B7) in an empty cell. It returns 0, the lowest value among the remaining visible rows. The 5 argument tells SUBTOTAL to calculate a minimum and ignore rows excluded by a filter. This version includes manually hidden rows; use function number 105 instead if manually hidden rows should also be excluded.
Common Mistakes
- Using the wrong range. A formula can be syntactically correct but still miss the first or last data row. Click the formula cell and inspect the highlighted range, or edit the range reference before relying on the result.
- Assuming blank cells count as zero. MIN ignores blank cells in a referenced range. A blank does not lower a positive minimum. If a cell contains an actual 0, however, zero is included and may be the answer.
- Forgetting that negative numbers are smaller than zero. If negative values are valid, MIN includes them. If you meant to find the smallest positive value, a basic MIN formula is not enough; filter or otherwise exclude nonpositive values before calculating.
- Using numbers stored as text. A cell may look like a number but be stored as text, particularly after importing data. MIN generally ignores text in referenced cells, so the apparent low value may not be considered. Convert the entries to numbers before recalculating.
- Expecting a filtered list to change MIN. MIN normally considers values in hidden or filtered-out rows too. Use SUBTOTAL when you specifically want the minimum from rows that remain visible after filtering.
- Expecting MIN to return a matching name or row. MIN returns only the numeric value. Use a lookup formula such as XLOOKUP alongside MIN when you also need a label, and be aware that duplicate minimum values can have more than one matching row.
Tips
- Use a table for lists that grow. Select your data and choose Insert > Table to make a table. A formula that refers to a table column can include new rows added to that table, reducing the chance that the minimum formula stops at an outdated final row.
- Keep the answer near the data but outside the range. A clearly labeled result cell makes it easier for someone else to understand what the number represents. Add a label such as “Lowest amount” beside it.
- Use MINIFS for category-based comparisons. It is clearer than manually filtering and copying a subset when you need the lowest value for a department, product, or date condition. Check that the condition refers to the intended category and that the ranges cover corresponding rows.
- Use SUBTOTAL for changing filters. A normal MIN calculation stays the same when you hide rows with a filter. SUBTOTAL is useful for a report where the minimum should update as the visible selection changes.
- Keep the original precision in mind. A displayed value may be rounded while the stored value has more decimal places. If two results look identical, increase the displayed decimal places to see whether one is actually smaller.
Troubleshooting
The answer is 0, but the range has no zero. Check whether a formula is being used with criteria, such as MINIFS. MINIFS returns 0 when no cells meet its criteria, which can look like a real minimum. Confirm that the criteria text matches the data, including spaces, and verify that the criteria range covers the same rows as the values. Also look for a genuine zero formatted in a way that makes it hard to notice.
The answer is larger than a number you can see. The smaller-looking entry may be stored as text, or it may fall outside the formula’s range. Select the cell and check the formula bar and alignment; text is often left-aligned by default, although formatting can change alignment. Convert text-formatted numbers using Excel’s available warning option, Text to Columns, or a deliberate data-cleaning step, then confirm the range includes the converted cell.
Excel displays a formula instead of a result. Check whether the cell is formatted as Text. Change its number format to General, then edit the cell and press Enter again. Also make sure Show Formulas is not enabled: on Windows, press Ctrl+grave accent (the key usually above Tab) to toggle it. On Mac, the equivalent shortcut is Control+grave accent in many Excel versions; if it does not work, check the Formulas tab for Show Formulas.
The formula shows an error or is not accepted. Check for a missing closing parenthesis, an incorrect cell reference, or a separator mismatch. The formulas in this guide use commas, as in typical English-language Excel settings; some regional settings require semicolons instead. If Excel reports a circular reference, move the result formula outside the range it evaluates.
The minimum does not change after editing the data. Confirm that calculation is set to Automatic under Formulas > Calculation Options. If the workbook is set to Manual, choose Calculate Now or press F9 on Windows; on Mac, use the equivalent recalculation command available for your Excel version. Also verify that the edited cell is included in the formula’s range.
Frequently Asked Questions
What is the simplest way to find the lowest number in Excel?
Use MIN with the cells to compare. For values in B2:B7, =MIN(B2:B7) returns the smallest numeric value in that range.
Does MIN include zero and negative numbers?
Yes. MIN includes numeric zeros and negative values. It ignores blank cells and text in a referenced range.
How do I find the lowest value in a filtered list?
Use SUBTOTAL with function number 5 for the minimum of rows visible after filtering. A regular MIN formula still includes filtered-out rows.
Can Excel find the lowest value for one category?
Yes. Use MINIFS with the values range, a criteria range, and the condition. Check that the ranges correspond row by row. MINIFS is available in current Microsoft 365 Excel.
How can I return the name beside the lowest value?
Use a lookup formula with MIN, such as XLOOKUP, to search for the minimum and return a value from the matching row. If the minimum is duplicated, decide whether you need the first match or all matching labels.
Can there be more than one lowest value?
Yes. If multiple cells tie, MIN returns the shared lowest number. A lookup formula usually returns just one matching row; inspect the range or use a method designed to list every tie if you need all associated records.
Related
- How to Email an Excel File That’s Too Large Safely
- Excel Audit Tools: How to Enable and Use Them
- How to Enable an Audit Trail in Excel: Track Changes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Count Names in Excel: Formulas and ExamplesTo count how often a particular name appears in Excel, use COUNTIF: with names in A2:A100 and the…
- Extract a Word From a Cell in Excel With a FormulaTo return a particular word from a cell, put the text in A2 and the word’s position in…
- Excel Filter Not Grouping Dates by Month? Causes and FixesIf Excel’s filter dropdown is not grouping dates by month, first check that every value in the date…
- VLOOKUP Examples for Practice: Learn Excel Lookup SkillsFor a first VLOOKUP practice problem, put product IDs in A2:A6, prices in B2:B6, and the lookup ID…
- How to Edit a Filter in Excel: Change Criteria and RangesTo edit a filter in Excel, open the drop-down arrow in a filtered column and change its selected…
- Excel Day Name from Date: Formulas, Formats, and FixesTo show the day name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.