Excel MAX Function: Find the Highest Value in a Range

Formulas & functions · Updated November 18, 2026

To find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing the range with the cells you want to check. MAX returns the highest numeric value in its arguments, making it useful for comparing scores, sales, measurements, dates, or other numeric data. You need a worksheet with the values you want to compare and a blank cell for the result.

What You’ll Need

  • An Excel workbook containing the values you want to compare. This guide uses Excel for Microsoft 365 on Windows.
  • A blank cell outside the data range where Excel can display the answer.
  • The cell addresses or range containing your data. The same MAX formulas also work in Excel for Mac and Excel for the web; ribbon locations may differ slightly.

Step-by-Step

  1. Check the values you want to compare. Identify the first and last cells in the list. For example, a column of weekly totals might run from B2 through B10. MAX can also evaluate numbers in rows, several separate ranges, or individual numeric cells. Decide whether you want the maximum across all the data or only part of it. If the range includes a heading, MAX generally ignores its text, but selecting only the data cells makes the formula easier to understand and maintain.
  2. Select an empty cell for the answer. Click a cell that is not part of the range you plan to examine. If your values are in B2:B10, B11 is a convenient place for the result. Putting the formula inside the range it evaluates can cause a circular reference, so choose a separate cell. You can type a nearby label such as Highest value in the cell beside the result if you want to make the worksheet clearer.
  3. Type the MAX formula. With the values 12, 19, 7.5, 16, and 14 in B2:B6, enter this formula in B7. It returns 19 because 19 is the largest value in that range: =MAX(B2:B6). The colon means the range includes every cell from B2 through B6. Make sure the formula begins with an equals sign, and use the actual addresses from your worksheet rather than copying the example addresses unchanged.
  4. Press Enter and check the result. Excel calculates the formula and displays the maximum in the selected cell. Compare the answer with the values in the range to confirm it is the largest. If the cell displays a row of hash marks, the column may be too narrow to show the result; widen the column. If you see the formula itself instead of an answer, check whether the cell is formatted as Text or whether Show Formulas is turned on.
  5. Use the formula bar or AutoSum if you prefer. You can type the formula directly into the selected cell or into the formula bar, then press Enter. On Windows, the Home tab has an AutoSum drop-down in the Editing group; choose Max to insert a maximum formula for a nearby range, then review the selected cells before pressing Enter. AutoSum may guess the range based on surrounding data, so do not assume its selection is correct. On Mac, the formula works the same way, although ribbon grouping and placement can vary by Excel version.
  6. Extend the range when new values are added. If you later add a value in B7 while the original formula checks B2:B6, the new value will not be included. Move the answer cell elsewhere, then edit the formula so its range covers the expanded data. For example, if the values now run from B2 through B7 and the answer is in B8, use =MAX(B2:B7). This example assumes the data are in B2:B7 and the formula is in B8; the result will be whichever number in that range is largest.
  7. Compare separate ranges if your data are split. MAX accepts more than one range. Suppose January values are in B2:B5 and February values are in D2:D5, with numbers 12, 19, 7, 16 and 4, 21, 18, 9 respectively. Enter this formula in an empty cell outside those ranges: =MAX(B2:B5,D2:D5). It returns 21, the largest value across both groups. Separate arguments with commas in standard English-language Excel settings; some regional settings use a different list separator.

Common Mistakes

Including the answer cell in the range: If the formula is in B7, do not use a range such as B2:B7 when B7 contains that formula. This can create a circular reference. Keep the result outside the range or adjust the range to include only source data.

Expecting text that looks like a number to count: MAX ignores text and blank cells in a referenced range. A value such as 25 stored as text in B4 may therefore be skipped, even though it looks numeric on screen. Convert it to a real number before relying on the result. A green error indicator may offer a Convert to Number option; you can also check the source data and cell format.

Assuming the formula finds the largest visible value: MAX includes numbers in rows hidden manually or hidden by a filter. If you need a result based only on visible rows, MAX alone is not sufficient. Choose an approach designed for filtered or hidden data, and verify it against the exact visibility behavior you need.

Confusing the maximum with the most frequent value: MAX returns the greatest numeric value, not the value that appears most often. If you need the most common entry, you are asking a different question and need a mode or frequency-based method.

Overlooking negative numbers or dates: MAX returns the greatest number even when every value is negative. In that case, the answer is the negative number closest to zero, not zero itself. Excel stores dates as serial numbers, so MAX over valid date cells returns the latest date; the result cell may need a date format to display it as a calendar date.

Tips

  • Use a table for growing lists. If you convert a data range to an Excel table with Insert > Table, new rows can be included as the table expands. Structured references can make formulas easier to interpret, but confirm that the formula refers to the intended table column.
  • Use MAXIFS for a maximum that meets a condition. MAX does not filter values by a category on its own. For example, you might need the highest sale for one region rather than the highest sale overall. MAXIFS is available in Excel for Microsoft 365 and Excel 2019 or later. Set up the criteria carefully and confirm the criteria range lines up with the values range.
  • Format the answer to match the data. For currency, percentages, or dates, apply the appropriate number format from the Home tab. Formatting changes how a value appears, not the underlying maximum that MAX calculates.
  • Keep the formula easy to audit. Use a clearly labeled result cell and avoid unnecessarily broad ranges that include unrelated numbers elsewhere on the worksheet. If you copy a formula to another column, check whether the cell references should move with it or remain fixed.
  • Do a quick manual check with small lists. Scan the values once to verify the result, especially when numbers may be stored as text or when the range includes hidden rows. This helps reveal data issues that a syntactically correct formula cannot fix.

Troubleshooting

The result is 0, but you expected a number. Check that the selected range contains actual numbers and that you did not point to the wrong cells. MAX returns zero when it has no numeric values to evaluate. Text-formatted numbers, values imported with leading apostrophes, and numbers with unexpected spaces are common causes. Try selecting a suspect cell and checking whether Excel treats it as a number; convert the source values if needed, then recalculate.

The answer is smaller than the largest value you can see. The apparent largest value may be text, outside the range, or part of a different column. Click the cell and inspect its contents and address. Confirm that the formula includes the entire intended range, and check for numbers stored as text. Also consider whether the formula is meant to include hidden or filtered-out rows, because MAX includes them.

Excel shows an error. Check for a misspelled function name, unmatched parentheses, or a range reference that no longer exists. A referenced error value, such as #N/A or #VALUE!, can also make MAX return an error. Trace the cells in the range and fix or handle the underlying error at its source. If you pasted a formula from a source that uses semicolons, replace the separators with the list separator your Excel settings require; the examples here use commas.

The formula appears as text instead of calculating. Change the result cell’s format from Text to General, then re-enter the formula. A quick way is to click the formula bar, press F2, and then press Enter after changing the format. On Mac, use the equivalent edit-cell action for your keyboard settings, or simply retype the formula after applying General format. Also check Formulas > Show Formulas; if that view is enabled, turn it off to display calculated results.

The result does not update after editing a value. Check Formulas > Calculation Options and confirm that calculation is set to Automatic. If the workbook is set to Manual, use Calculate Now on the Formulas tab or switch back to Automatic. Also make sure the changed cell is included in the formula range.

Frequently Asked Questions

Does MAX ignore blank cells?
Yes. Blank cells in a referenced range are ignored. Text in a referenced range is also ignored, while real numeric values are compared. If a cell looks blank but contains a formula returning an empty string, it is text rather than a numeric value and will not become the maximum.

What does MAX return if every number is negative?
It returns the number with the greatest numeric value, which is the one closest to zero. For example, among -8, -3, and -11, the maximum is -3. MAX does not automatically add zero to the comparison.

Can MAX return the latest date?
Yes. Excel stores valid dates as serial numbers, so the latest date is the greatest date value. Use a range containing the date cells, then format the result cell as a date if Excel displays a serial number instead.

Does MAX include filtered-out or hidden rows?
Yes. MAX evaluates numeric values in the range even when their rows are hidden or filtered out. If you need a maximum based only on visible rows, use a method intended for visible data rather than relying on MAX alone.

Can I find the highest value for one category?
Yes, but plain MAX does not apply a category condition. MAXIFS can return a maximum based on one or more criteria in supported Excel versions, including Microsoft 365 and Excel 2019 or later. Make sure the criteria ranges correspond row by row with the values being evaluated.

Can MAX compare values in more than one range?
Yes. Provide each range as a separate argument, as in the two-range example above. MAX compares all numeric values in the supplied arguments and returns the greatest one.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.