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

If Excel MAX is not giving the result you expect, first check that the cells contain real numbers and that the formula points to the right range. For values in B2:B20, enter =MAX(B2:B20); Excel returns the largest numeric value in that range.

How the MAX function works

MAX compares numbers and returns the greatest one. It can check a range, several separate ranges, or individual numbers. For example, with 12, 7, a blank cell, -3, and 19 in B2:B6, =MAX(B2:B6) returns 19. The function does not add values, count them, or return the value from the last row; it simply finds the largest number it can evaluate.

When you give MAX a cell reference or range, it ignores blank cells, text, and logical values such as TRUE and FALSE. That behavior explains many surprising results: a cell that looks like 25 may actually contain text, so MAX can skip it. An error value in a referenced range can also cause the formula to return an error rather than a maximum.

MAX returns 0 if its arguments contain no numeric values. That can look like a valid answer when the range is actually empty or contains only text. It can also be misleading when all the relevant numbers are negative: if B2:B4 contains -9, -4, and -2, then =MAX(B2:B4) returns -2. That is the greatest number in the range, even though it is the one closest to zero.

Check whether the values are real numbers

Numbers stored as text are one of the most common reasons MAX appears to miss a value. They may be left-aligned while ordinary numbers are right-aligned under the default cell formatting, or Excel may show a warning indicator. Imported data, copied web tables, leading apostrophes, and spaces can all leave digits stored as text. Alignment is only a clue, though: custom formatting can change how a real number looks.

For example, suppose A2:A5 contains the numeric values 8 and 14, the text value 25, and a blank. With those cells in that order, =MAX(A2:A5) returns 14 because the text entry is ignored. Convert the text entry to a number and the same range returns 25.

To convert a small number of entries, select the affected cells and use the warning menu’s conversion option if Excel offers one. For a larger imported column, select it and choose Data > Text to Columns, then finish the wizard without changing the delimiter settings if no splitting is needed. Check a few cells afterward, since the right cleanup method depends on the data and its separators.

A helper formula can also convert a text number. With text numbers in A2:A20, enter =VALUE(A2) in B2 and fill down; if A2 contains the text 25, B2 returns the numeric value 25. Then use the converted column in MAX. VALUE will not fix every malformed entry: extra characters, unexpected decimal or thousands separators, and nonbreaking spaces may need to be cleaned first. In regional settings where formulas use semicolons, Excel may display semicolons instead of commas as argument separators.

Verify the range and the intended result

A correct MAX formula can still answer the wrong question if its range is incomplete or includes the wrong cells. Inspect the colored reference outline after selecting the formula in the formula bar. Confirm that the first and last rows are included, that the formula refers to the intended column, and that a copied formula has not shifted its references. A range such as B2:B20 does not include B21, even if a new value was added there later.

For data that grows over time, an Excel table can make formulas easier to maintain. Tables expand when rows are added, and their column names make the intended data clearer. If the table is named Sales and its numeric column is Amount, a formula using =MAX(Sales[Amount]) checks the values in that column. Verify the actual table and column names in your workbook before using a structured reference.

Also consider whether the largest value is genuinely the answer you need. A date is stored as a serial number in Excel, so MAX over date cells finds the latest date; date formatting controls how that result appears. If you intended to find the largest sale for one region, a plain MAX over all sales will include other regions too. If you meant the largest absolute magnitude, MAX does not convert negative values to positive ones.

Understand blanks, errors, and displayed values

Blank cells are ignored, but a cell containing a formula that returns an empty string is text, not a truly empty cell. MAX generally ignores that text in a referenced range as well. By contrast, an error such as #N/A or #VALUE! in a referenced cell can prevent MAX from returning a number. Find and resolve the source error, or deliberately build an error-handling approach suited to the worksheet; do not hide errors automatically if they could signal bad data.

Formatting changes how a number appears, not the underlying value MAX evaluates. A cell displayed as 10.0 might store 10.04, and another displayed as 10.0 might store 10.01. MAX compares the stored values, so it can select 10.04 even though both appear identical at one decimal place. Increase the displayed decimal places to inspect the difference. Rounding the data or formula changes the calculation and should only be done if that matches the task.

If the result is 0, inspect the source cells rather than assuming MAX has found a zero. Check whether the range is empty, contains text-formatted numbers, or points to the wrong column. If the result is unexpectedly large, look for an accidental number in the range, such as a total row, an ID, or a date serial mixed into ordinary measurements.

Fix a formula that is not updating or is showing as text

If the displayed answer does not change after you edit an input, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. If calculation is set to Manual, formulas may wait for a recalculation. Press F9 to recalculate open workbooks, or use Ctrl+Alt+F9 to force a full calculation. On Mac, use Formulas > Calculation Options if available in the ribbon; keyboard shortcuts can differ by keyboard and function-key settings, so the ribbon command is the dependable route.

If the cell displays =MAX(B2:B20) instead of a result, it may be formatted as Text or the formula may have a leading apostrophe. Change the cell format to General, then edit and re-enter the formula so Excel evaluates it. Merely changing the format may not convert a formula already stored as text; press F2 and Enter after changing the format. Also check whether Show Formulas is on: press Ctrl+grave accent on Windows to toggle it, or use Formulas > Show Formulas. The grave accent key is usually near the upper-left of a US keyboard.

When a formula produces a result that looks wrong, verify that the cell is actually calculating the formula you edited. A pasted value, an old formula in a different cell, or a formula with a typo can be easy to overlook. Click the result cell and inspect the formula bar. Excel for the web has the same basic MAX function, though ribbon layout and keyboard shortcuts may differ from the Windows desktop app.

Know what filters and hidden rows do

MAX normally includes numeric values in filtered-out rows and manually hidden rows. Applying a filter therefore does not make MAX show the largest visible value only. This is a frequent source of confusion when a worksheet has been filtered to one category but the result still reflects a larger value in another category.

If you need the maximum for a condition, use a criteria-based function such as MAXIFS rather than relying on the visible rows. If you specifically need a calculation that responds to filtering, choose a method designed for visible cells and test it against the worksheet’s layout; MAX by itself does not have a “visible rows only” option. Avoid assuming that hiding a row removes its value from formulas.

Choose between MAX, MAXIFS, and related functions

Use MAX when you want the greatest number across the full range. Use MAXIFS when you want the greatest value only among rows that meet one or more conditions; MAXIFS is available in Excel 2019 and later, including Microsoft 365. For example, if regions are in A2:A6 and sales are in B2:B6, with East sales of 10, 23, and 16, =MAXIFS(B2:B6,A2:A6,"East") returns 23. The first range is the values to compare and the following range-and-criteria pair limits which rows count.

MAXIFS returning 0 can mean the qualifying values are all zero, but it can also mean no rows met the criteria. Check spelling, extra spaces, and the data type of the criteria column. If your Excel version does not include MAXIFS, you can use a compatible formula designed for the version you have, or filter and inspect the matching values carefully. Do not substitute MAXA without understanding its different rules: MAXA can treat logical values and text in references differently from MAX.

For a particular rank rather than the single greatest value, LARGE is another option. For instance, it can return the second-largest number when given a range and a rank. MAX is simpler when only the top value is needed. These distinctions matter because a formula may calculate correctly while still not matching the question the worksheet is meant to answer.

A practical troubleshooting sequence

Work through these checks in order; they separate a data problem from a formula or calculation setting problem:

Click the result cell and confirm the formula begins with an equals sign and refers to the intended cells.
Check whether the range contains numbers, text numbers, errors, dates, or unrelated values. Test a suspicious entry by selecting it and inspecting the formula bar.
Look for rows beyond the formula’s last reference, and decide whether filtered or hidden rows should count.
If the formula is displayed instead of evaluated, check the cell format, a leading apostrophe, and Show Formulas.
If the answer is stale, set calculation to Automatic and recalculate.
Compare the result with a small hand-checked sample, then expand the range or add criteria only after the basic calculation is clear.

This sequence is useful because changing several things at once can obscure the cause. For example, converting a text number and expanding a range at the same time may make the result change without showing which fix mattered. Correct one issue, recalculate, and confirm the new result against the source data.

Frequently Asked Questions

Why does Excel MAX return 0?
MAX returns 0 when it finds no numeric values in its arguments. Check for an empty or incorrect range, numbers stored as text, and criteria that match no rows if you are using MAXIFS.

Does MAX ignore text and blank cells?
In cell references and ranges, MAX ignores blank cells, text, and logical values. Numeric-looking text is still text until converted. An error in the referenced range can cause an error result.

Does MAX ignore filtered or hidden rows?
No. MAX normally includes numbers in both filtered-out and manually hidden rows. Use a criteria-based or visible-row-specific approach if the result must exclude them.

Why does MAX show the formula instead of the answer?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Change the format to General, re-enter the formula, and check Formulas > Show Formulas.

Why does MAX return the wrong date?
Excel stores dates as numbers, and MAX returns the greatest serial value. Check that the range contains valid dates only and that the selected cells cover the intended records; formatting affects the display, not which serial value is largest.

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.