The Excel formula for the lowest value in a range is =MIN(A1:A10). Replace A1:A10 with the cells you want to check; Excel returns the smallest numeric value in that range.
What the MIN formula does
MIN is Excel’s straightforward function for finding a numeric minimum. It can check a range of cells, several separate ranges, or individual numeric values. For example, a sales analyst might use it to find the smallest order amount, while a student could use it to identify the lowest test score. The function returns a value; it does not identify the cell where that value appears.
The colon in a reference such as A2:A20 means all cells from A2 through A20, inclusive. A reference can also point to a row, such as B3:G3, or to a larger rectangular block. If you add more data later, consider using an Excel table or expanding the reference so new entries are included in the calculation.
MIN does not require the values to be sorted. It checks the specified inputs and returns the smallest number, whether that number is at the top, bottom, or somewhere in the middle of the range. Sorting is useful for viewing data in order, but it is not necessary to calculate the minimum.
How to enter the formula
With the numbers in A2:A8, enter this formula in an empty cell to find the lowest one:
=MIN(A2:A8)
To enter it in Excel for Microsoft 365 on Windows, select the result cell, type the formula, and press Enter. You can also type =MIN(, select the range with the mouse, type the closing parenthesis, and press Enter. Excel displays the answer in the cell; the formula bar shows the formula when that cell is selected.
If the data is in a different column, change the reference. With numbers in D5:D30, for example, the formula should refer to D5:D30. Check that the range begins and ends at the intended cells: including a heading usually does no harm because MIN ignores text in a referenced range, but leaving out the last data row can produce an incorrect result.
On Excel for Mac, the formula works the same way and Enter confirms it; depending on keyboard settings, Return may also confirm. Excel for the web uses the same function and formula syntax. The location of some controls differs by platform, but there is no platform-specific MIN formula.
A worked example
Suppose a list of six prices is in B2:B7: 18, 12.5, 21, 9.75, 14, and 11. With those prices in B2:B7, use:
=MIN(B2:B7)
The result is 9.75, because that is the smallest number in the list. The function does not change the original prices or reorder the cells. If 9.75 appears more than once, MIN still returns 9.75; it does not report how many times the value occurs.
You can also compare separate ranges. With one set of numbers in B2:B7 and another in D2:D7, use:
=MIN(B2:B7,D2:D7)
Excel checks both ranges and returns the smallest number found in either. Commas separate the function’s arguments in the standard English-language Excel syntax shown here. Regional settings can affect the separator Excel expects, so if a formula using commas is rejected, check the list-separator settings for your installation.
Finding the lowest value that meets a condition
MIN checks every numeric value in its inputs. When the question is more specific—for example, the lowest price for one product category—use MINIFS. This function returns the minimum from a range only when corresponding cells meet one or more criteria. MINIFS is available in Excel 2019 and Microsoft 365, among later versions.
Assume product categories are in A2:A7 and prices are in B2:B7, with the rows Paper, Pens, Paper, Pens, Paper, and Pens and the prices from the worked example. To find the lowest price for Paper, enter:
=MINIFS(B2:B7,A2:A7,"Paper")
The result is 14: the Paper prices are 18, 21, and 14. The first argument is the range to minimize; the next argument identifies the range to test, followed by the criterion. The criteria range and the minimum range should have matching dimensions so each price is tested against the category on the same row.
Criteria can also be based on numbers or dates. With dates in A2:A20 and corresponding values in B2:B20, a MINIFS formula can return the lowest value for rows whose date meets a specified condition. Criteria written as text, such as a comparison operator followed by a number, need quotation marks inside the formula. Make sure the criterion matches the data you actually want to include.
Zeros, blanks, and negative numbers
A common surprise is that zero counts as a number. If a range contains 0 and positive amounts, MIN returns 0—even if zero means no entry in your particular worksheet. Decide whether zero is a genuine result or a placeholder before choosing a formula; excluding it without checking can hide meaningful data.
With values in B2:B7 and a requirement to find the smallest value greater than zero, use:
=MINIFS(B2:B7,B2:B7,">0")
If the values are 0, 18, 12.5, 21, 9.75, and 14, the result is 9.75. To exclude zero while still allowing negative values, use the criterion "<>0" instead. The distinction matters: a greater-than-zero condition excludes both zero and every negative number, while not-equal-to-zero excludes only zero.
Blank cells in a referenced range are ignored by MIN; they are not treated as zero. Text entries in a referenced range are also ignored. This makes it possible to calculate a minimum in a column that contains labels or unused cells, but it also means a misspelled number stored as text may be silently left out. If a range has no numeric values at all, MIN returns 0, so a displayed zero does not always prove that a zero was present in the source data.
Dates, text, and errors in the range
Excel stores dates as serial numbers, so MIN can find the earliest date in a range of genuine Excel dates. With dates in C2:C10, use:
=MIN(C2:C10)
The answer is the earliest date, but the result cell may initially display a serial number or a general number format. Apply a date format to the result cell using Home > Number Format, then choose an appropriate date style. Formatting changes how the value is displayed, not the underlying date calculation.
Dates that look correct but are stored as text may not be included in the calculation. A clue is that some entries align differently from the others or do not respond to date formatting. Convert such entries to real dates before relying on the result. Likewise, numbers imported from another file can be stored as text; check the source and convert them to numeric values if they should count.
An error value in a referenced range can cause the MIN calculation to return an error instead of a minimum. Inspect the source cells for errors such as #VALUE! or #N/A and resolve them where appropriate. Do not simply hide an error unless you understand why it occurred; it may indicate missing or invalid input that affects the result.
MIN compared with related approaches
Use MIN when you want the smallest numeric value without conditions. Use MINIFS when the minimum must meet a condition, such as a category, date range, or positive-value rule. This choice keeps the formula direct and makes the rule visible to someone reviewing the worksheet.
Sorting from smallest to largest is an alternative when you want to inspect the full ordered list, but sorting changes the order of the data and can affect related rows if you select the wrong range. MIN is safer when you only need the result. Conditional formatting can highlight low values visually, but it does not replace a formula when you need a minimum value for another calculation or report.
If you need to know which item has the minimum value, MIN alone is not enough: it returns the number, not the associated name or row. A lookup formula can retrieve the related item, but the right approach depends on whether minimum values can tie and which Excel version is in use. First calculate the minimum, then choose a lookup method that handles duplicate values as intended.
Common problems and checks
- The answer is zero: Check whether the range contains a real zero, whether it contains any numeric values, and whether the range includes the intended cells.
- The result seems too high: Look for numbers stored as text, a missed row, or a criterion that excludes values you expected to include.
- The formula returns an error: Check the referenced cells for errors and confirm that the formula’s parentheses and range references are complete.
- A date appears as a number: Apply a date number format to the result cell.
- New rows are not counted: Expand the range or use an Excel table so that added records can be included more reliably.
For a quick check, compare the formula’s reference with the visible data and confirm whether the lowest-looking entry is a number Excel recognizes. Click the result cell to review its formula, then inspect the relevant source cells. If the worksheet uses filters, remember that MIN does not automatically limit its calculation to visible rows; it evaluates the referenced data, including filtered-out rows. A visible-rows-only calculation requires a different approach.
Frequently Asked Questions
What is the formula for the lowest number in Excel?
Use MIN with the cells to check. With numbers in A1:A10, enter =MIN(A1:A10). Excel returns the smallest numeric value in that range.
Does MIN include zero?
Yes. Zero is a numeric value, so it is the minimum if no value in the range is smaller. If you need to exclude zero, use MINIFS with a not-equal-to-zero criterion, or a greater-than-zero criterion if negative values must also be excluded.
Does MIN ignore blank cells?
Yes. Blank cells in a referenced range are ignored, as are text entries. If there are no numeric values to evaluate, MIN returns 0.
Can MIN find the earliest date?
Yes. Excel dates are stored as numbers, so MIN returns the earliest date when the cells contain genuine Excel dates. Format the result as a date if Excel displays it as a serial number.
How do I find the lowest value for one category?
Use MINIFS, with the values to minimize first and the category range and criterion after it. For example, if categories are in A2:A7 and values are in B2:B7, use =MINIFS(B2:B7,A2:A7,"Paper").
Will MIN show which cell contains the lowest value?
No. MIN returns the lowest value only. To return a matching name or identify a row, combine the minimum with a lookup approach and decide how duplicate minimum values should be handled.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Calculate Your Age from Birth DateIf the birth date is in cell A2, enter =DATEDIF(A2,TODAY(),"Y") to calculate age in completed years as of…
- Excel Rank by Number: Formulas and Tie HandlingTo rank numbers in Excel, use RANK.EQ. With the value to rank in B2 and comparison values in…
- How to Calculate ROI Percentage in Excel With a FormulaTo calculate return on investment (ROI) in Excel, subtract the investment cost from its final value, divide the…
- Excel Filter and Sort by Color: Steps, Options, and TipsIn Excel for Microsoft 365 on Windows, use the filter arrow in a column and choose Filter by…
- Excel IFS Function: What Happens If No Test Is TRUE?If every test in Excel’s IFS function is FALSE, the function returns #N/A; it does not have a…
- Highlight Upcoming Dates in Excel with a Formula RuleTo highlight dates coming up in the next 30 days, select the date cells and create a formula-based…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.