Max Function Not Working in Excel? Fix Common Causes

If Excel’s MAX function is returning 0, the wrong value, or an error, first check that the cells you selected contain real numbers and that the formula points to the intended range. For example, with values in B2:B6, use =MAX(B2:B6); if the result is still unexpected, the checks below can help identify why.

What the MAX function does

MAX returns the largest numeric value among its arguments. It can compare individual numbers, cell references, ranges, and combinations of these. With sales figures in B2:B6, for example, it returns the greatest sale in that range—not the total or the most frequently occurring value.

MAX has a useful distinction that explains many apparent failures: it ignores blank cells and text in referenced cells. It also ignores logical values such as TRUE and FALSE when they are inside a referenced range. However, a number typed directly into the formula is evaluated as a number. An empty set of numeric values returns 0. An error value in a referenced range can make MAX return an error instead of a maximum.

The function does not care whether the values represent prices, scores, dates, or times. 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 ordinary number if the result cell is formatted as General; applying a date or time format can make it readable.

Check the formula and range first

A simple range mistake is often the cause. The formula may omit the newest row, include a heading, point to another worksheet, or use a fixed range that no longer covers all the data. Click the formula cell and inspect the colored outline around the referenced cells. Confirm that the range includes every value that should count and excludes unrelated values.

With scores in B2:B6, the formula =MAX(B2:B6) compares five cells. If B7 contains a newer score, it is not included. Change the range to B2:B7 or use a table-based reference so it can expand with added records. For a table named Sales with a column named Amount, the formula =MAX(Sales[Amount]) finds the greatest value in that column and normally includes new table rows automatically.

Also check for a leading apostrophe or an accidental character in the formula. A formula entered as text will not calculate, and a misspelled function name may produce a name error. In Excel for Microsoft 365 on Windows, select the cell and inspect the formula bar. A valid formula begins with an equals sign; if the cell displays the formula itself, check whether the cell is formatted as Text or whether Show Formulas is enabled.

Numbers stored as text

Numbers that look numeric may actually be text. This can happen after importing a CSV, copying data from a website, or entering values with an apostrophe. MAX ignores text inside a referenced range, so a list that appears full of numbers can produce 0 if every entry is text. If only some entries are text, MAX may return a smaller value than expected because it compares only the genuine numbers.

Click a suspicious cell and look for a warning icon or a number aligned differently from nearby values. Alignment alone is not proof, because formatting can change it. A stronger check is to select a cell and look at the formula bar, or use Excel’s conversion options. If the cells show a green error indicator, select them and choose Convert to Number when that option is offered.

Convert a small range: Select the cells, choose the warning icon if present, and select Convert to Number.
Convert through Text to Columns: Select the affected column, choose Data > Text to Columns, then select Finish. For a simple conversion, leave the default delimiters unchanged.
Use a helper column: With a text-formatted number in A2, enter =VALUE(A2) in B2 and fill down. If A2 contains the text 18, the formula returns the numeric value 18. MAX can then use the converted helper values.

These methods may need adjustment if the imported values use a decimal or thousands separator that differs from your regional settings. Also watch for nonbreaking spaces or other hidden characters copied along with the digits; removing visible spaces may not remove every such character. Do not convert identifiers such as postal codes or account numbers merely because they contain digits—those may be labels, not quantities to compare.

Why MAX returns zero or a smaller value

If MAX returns 0, first determine whether the range contains any numeric values Excel recognizes. Blank cells, text, and numbers stored as text do not contribute to a range-based MAX. A formula that returns an empty string, such as one displaying an empty result, is also text rather than a numeric zero. If all cells are blank or nonnumeric, 0 is the expected result.

A result that is too small often means the largest-looking entry is text, excluded from the selected range, or not actually the value it appears to be. For instance, a cell may display 900 while its underlying value is 90 because a custom number format adds a digit-like display effect. Click the cell and inspect the formula bar to see the stored value. Similarly, a percentage displayed as 80% is stored as 0.8; if other values use a different scale, the comparison may not match what you intended.

Hidden rows are another common source of surprise. MAX includes values in hidden rows and filtered-out records. If the goal is to find the largest visible value after filtering, MAX alone is not the right tool. In Microsoft 365, a formula such as =SUBTOTAL(104,B2:B100) returns the maximum while ignoring filtered-out rows and manually hidden rows. If you want to ignore filtered rows but include manually hidden rows, use function number 4 instead: =SUBTOTAL(4,B2:B100). Check the intended behavior before choosing one.

Fix errors inside the range

If any referenced cell contains an error such as #N/A, #VALUE!, or #DIV/0!, MAX can return that error rather than a number. Find the error in the range and correct its cause where possible. For example, a lookup formula may return #N/A because it cannot find a match; fixing the lookup is generally better than hiding the error.

If errors are expected and the goal is specifically to calculate the maximum of the remaining numeric values, AGGREGATE can ignore errors. With values and possible errors in B2:B6, enter =AGGREGATE(4,6,B2:B6). The first argument, 4, selects the MAX operation; the second, 6, tells the function to ignore error values. If the valid numbers in the range are 12, 7, and 19, with errors in the other cells, the result is 19. AGGREGATE is available in Excel 2010 and later. Ignoring errors should be deliberate: it can conceal an underlying data problem.

Check calculation and display settings

Sometimes the formula is correct but its displayed result has not updated. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. In Manual calculation mode, formulas may keep showing an old result until Excel recalculates. Press F9 to calculate formulas in open workbooks. On a Mac, use Fn+F9 on keyboards where the function keys control hardware features; the exact key behavior depends on the keyboard settings.

To check whether Excel is displaying formulas instead of results, go to the Formulas tab and inspect Show Formulas. The Windows shortcut Ctrl+` toggles this view; on Mac, the equivalent is Control+`. The backtick key is usually near the upper-left corner of the keyboard, though its position can vary by layout. If only one cell shows its formula as text, select it, change its number format from Text to General, then re-enter the formula. Changing the format alone may not convert an existing text entry into a working formula.

A result can also appear wrong because of formatting. A result cell formatted as a date may display the serial number as a date, and a cell with too few decimal places may hide differences between values. Select the result and inspect Home > Number Format. Choose General or a suitable numeric format to see the underlying result clearly.

Choose the right function for the task

MAX answers one specific question: what is the largest numeric value? It does not return the row containing that value, identify every tied maximum, or exclude records based on a condition. Choosing the right function helps avoid trying to repair a formula that is doing exactly what it was designed to do.

MAX: Returns the largest number in a range, including values in hidden or filtered rows.
MAXIFS: Returns the largest value that meets one or more criteria. For example, it can find the highest sale for a particular region.
SUBTOTAL: Can calculate a maximum that responds to filtered or hidden rows, depending on the function number used.
AGGREGATE: Can calculate a maximum while ignoring errors or applying other supported options.

If two or more cells share the maximum, MAX returns the value only; it does not indicate which record produced it. To identify the associated record, pair the maximum with a lookup or filtering method appropriate to your Excel version and data layout. If all you need is the largest value among a short list of cells, a direct formula such as =MAX(B2,D2,F2) is also valid, even when the cells are not adjacent.

Frequently Asked Questions

Why does MAX return 0 when my cells contain numbers?
If the entries are numbers stored as text, MAX ignores them. Convert the entries to numeric values and check that the formula references the cells you expect. If the range contains only blanks, text, or formulas returning empty text, 0 is also the expected result.

Does MAX ignore hidden or filtered rows?
No. MAX includes numeric values in hidden rows and in rows hidden by a filter. Use SUBTOTAL with the appropriate function number when the maximum should reflect visible rows.

Can MAX ignore errors in a range?
Plain MAX does not reliably skip errors in its referenced range; an error can cause the formula to return an error. AGGREGATE with operation 4 and option 6 can calculate a maximum while ignoring errors, but investigate errors when they indicate a real data issue.

Why does MAX show the wrong date?
Dates are stored as serial numbers, so MAX returns the latest numeric date. Check that the dates are real Excel dates rather than text, that the range is correct, and that the result cell uses a date format. Text dates may be ignored.

Why is the formula visible instead of the answer?
Check whether the cell is formatted as Text, whether the entry begins with an apostrophe, or whether Show Formulas is turned on. Set the cell to General and re-enter the formula, or toggle Show Formulas off if the entire sheet is displaying formulas.

A quick troubleshooting checklist

When MAX still seems wrong, work through the checks in this order. It helps separate a formula problem from a data or display problem:

Confirm the formula begins with an equals sign and references the intended cells.
Check whether the largest candidate is a real number rather than text or a text-formatted formula result.
Look for errors in the referenced cells and decide whether to fix them or intentionally ignore them.
Decide whether hidden or filtered rows should count; use SUBTOTAL if the answer should depend on visibility.
Check calculation mode, Show Formulas, and the result cell’s number format.

Excel installations using some regional settings require semicolons rather than commas between function arguments. If Excel rejects a formula copied from an example, replace the argument separators with the separator used by your installation. The MAX range example has only one argument, but functions such as SUBTOTAL and AGGREGATE make the difference visible. After verifying the data, range, and display settings, MAX usually becomes straightforward: it can only return the largest numeric value Excel recognizes among the cells you actually gave it.

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.