Excel MAX Function Not Working? Causes, Checks, and Fixes

If Excel’s MAX function is not returning the value you expect, check that the formula points to the intended cells and that those cells contain real numbers rather than numbers stored as text. With values in B2:B10, enter =MAX(B2:B10); if the largest numeric value in that range is 25, the result is 25.

What the MAX function does

MAX returns the greatest numeric value among the numbers you give it. Its basic form is =MAX(number1,[number2],...). You can provide individual values, cell references, or ranges. For routine use, a range reference is usually easiest to check and maintain.

MAX does not count blank cells or text in referenced cells as numbers. It also ignores logical values such as TRUE and FALSE when they appear in a cell range. If the range contains no numeric values, MAX returns 0. Errors are different: an error value such as #N/A or #VALUE! in the referenced range can make the whole MAX formula return an error.

Excel stores dates and times as numbers, so MAX can return the latest date or time in a range. The result may look like an unexpected serial number if the result cell is formatted as General or Number instead of Date or Time.

Check the formula and the range first

A formula can calculate correctly while still looking wrong because it refers to the wrong cells. Click the cell containing MAX and inspect the colored range outline shown on the worksheet. Confirm that the reference starts and ends where you expect, and that the formula was not copied with a relative reference that shifted.

For example, with scores in C2:C6, the formula =MAX(C2:C6) checks those five cells only. If the final score is in C7, it is outside that range. A formula entered in a table may also use structured references, which can expand with the table; verify that the referenced column is the one containing the values you want to compare.

Use these quick checks before changing data:

Click the formula cell and inspect its full contents in the formula bar.
Check the highlighted cells to confirm the range includes every intended value and excludes unrelated cells.
Look for a leading apostrophe, spaces, or other characters in values that appear numeric.
Check whether the cells contain errors, dates, or values hidden by a number format.
Press F9 only after selecting a portion of a formula if you understand the calculation; press Esc afterward to avoid replacing the formula with a calculated value.

On Windows, F2 edits the selected cell and Ctrl+` toggles formula display. On Mac, use Control+U to edit a cell and Control+` to toggle formula display; keyboard behavior can vary with the keyboard layout. In Excel for the web, select the cell and inspect the formula bar. These checks help distinguish a calculation problem from a display or reference problem.

Numbers stored as text are a frequent cause

A cell may look like the number 125 but contain text, often because it was imported from a website, CSV file, or another system. MAX ignores text in a referenced range, so a text value that should be the largest number may not affect the result. A green error indicator may offer Convert to Number, but not every text value displays that indicator.

To check one cell, with the suspected value in B2, enter =ISNUMBER(B2) in another cell. If B2 contains the numeric value 125, the formula returns TRUE; if it contains text such as “125”, it returns FALSE. This check is useful when values look identical on screen but behave differently in calculations.

For a small range, select the cells and use the warning icon’s Convert to Number option if Excel offers it. You can also select the range and choose Data > Text to Columns, then select Finish to make many simple text-formatted numbers numeric. Review the result first if the cells contain dates or values with unusual separators, because conversion depends on how the data is written and your regional settings.

For a single text value that Excel can interpret as a number, with the text value 125 in B2, enter =VALUE(B2) in another cell. The result is the numeric value 125. VALUE may not resolve text containing unrecognized symbols, extra spaces, or separators that conflict with your regional settings; clean or re-import that data when necessary. After converting, update MAX to include the numeric cells.

Understand zero, negatives, blanks, and hidden rows

A result of 0 does not always mean MAX has failed. If every cell in the referenced range is blank or text, MAX returns 0 because it finds no numeric value. Check for numbers stored as text before assuming that zero is one of the values in the source data.

On the other hand, MAX can return a negative number. If a range contains -12, -4, and -19, the maximum is -4: it is the greatest value, even though it is below zero. MAX does not automatically treat zero as a floor. If the range contains actual zeros, zero will be greater than any negative number.

Hidden and filtered rows are another source of surprise. MAX includes numeric values in hidden or filtered-out rows when they are part of the referenced range. Filtering a list does not change what MAX considers. If you want a maximum that changes with a filter, use a method designed for visible rows, such as SUBTOTAL, and confirm that its behavior matches your needs before replacing MAX.

Formatting can also make a correct result appear incorrect. A date’s underlying value is numeric, and a result cell formatted as General may show a serial number instead of a readable date. Select the result and choose Home > Number Format, then choose an appropriate date, time, percentage, or number format. Formatting changes how a value looks, not the value MAX calculated.

Fix errors in the source range

If MAX displays #N/A, #VALUE!, or another error, inspect the cells in its range for the same error. MAX generally propagates errors rather than skipping them, because an error may indicate that a source value could not be calculated. Correct the underlying formula or data where possible, then check the maximum again.

Use Find to locate visible error text, or inspect the cells included in the formula one by one. If the range is large, temporarily narrow the reference to sections of the data to identify where the error occurs. Avoid wrapping the entire calculation in an error-handling formula simply to make the message disappear: replacing an error with 0 can produce a plausible-looking but misleading maximum.

Also check whether a formula has been entered as text. If the cell shows the characters =MAX(B2:B10) instead of a result, the cell may be formatted as Text or the formula may have a leading apostrophe. Change the format to General, edit the cell, and press Enter. In Excel for Microsoft 365 on Windows, calculation settings are under Formulas > Calculation Options; if the workbook is set to Manual, choose Automatic or recalculate with F9. On Mac, use Formulas > Calculation Options as well. Manual calculation delays updates but does not change what MAX means.

Use MAXIFS when you need a condition

MAX looks across all numeric values in its range. It does not select the maximum for a category by itself. If you need the largest sale for one region, use MAXIFS, available in Excel 2019 and Microsoft 365. Its syntax is =MAXIFS(max_range,criteria_range1,criteria1,...); the maximum range and criteria ranges must correspond row by row.

Suppose regions are in A2:A6 and sales are in B2:B6, with West sales of 80, 125, and 95. Enter =MAXIFS(B2:B6,A2:A6,"West") to return 125. If the formula returns 0, check the spelling and spacing of the criterion, confirm that the criteria range aligns with the sales range, and make sure the matching sales values are numeric. If no row meets the condition, MAXIFS returns 0.

MAXIFS is not the same as MAX with a filter applied to the worksheet: ordinary MAX still includes filtered-out rows. Use the function that expresses the calculation you actually want—an overall maximum, a category-specific maximum, or a maximum over visible rows.

Choose the right fix for the symptom

These symptoms point to different causes, so changing the formula is not always the answer:

MAX returns 0: check for an empty or text-only range, or criteria that match no rows in MAXIFS.
It returns a smaller value than expected: check whether the largest entry is text, outside the selected range, or in a different column.
It returns an error: inspect the referenced cells for errors and repair their source.
It returns a date serial or an unexpected decimal: check the result cell’s number format and the underlying values.
It does not change after editing data: check calculation mode and whether the changed cell is included in the range.
It includes a value from a hidden row: remember that MAX includes hidden and filtered-out rows.

Excel formulas normally use commas between arguments, as in the examples here. Some regional settings use semicolons instead. If Excel rejects a formula at entry, check the list separator used by your installation rather than changing the function name. Excel for the web supports MAX and MAXIFS; ribbon wording and conversion options can differ slightly from the desktop app, but the calculation rules are the same.

Frequently Asked Questions

Why does Excel MAX return 0 when my cells contain numbers?
Those entries may be numbers stored as text, or the formula may reference different cells from the ones you are viewing. Test a suspected cell with ISNUMBER, then inspect the formula’s highlighted range. If the cells are genuinely numeric and in the range, check for a calculation or data issue.

Does MAX ignore blank cells?
Yes. Blank cells in a referenced range do not affect the result. Text in referenced cells is also ignored. If there are no numeric values to compare, MAX returns 0.

Does MAX include hidden or filtered rows?
Yes. MAX includes numeric values in the referenced range even when their rows are hidden or filtered out. Filtering the worksheet alone does not make MAX calculate only the visible records.

Why does MAX return a number instead of my latest date?
Excel stores dates as serial numbers. The maximum may be the correct latest date but displayed using a General or Number format. Change the result cell’s number format to a date format to display it as a date.

How do I find the largest value that meets a condition?
Use MAXIFS in Excel 2019 or Microsoft 365. Supply the values to compare, the matching criteria range, and the criterion, then check that the ranges line up and that the matching values are numeric.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.