To find the largest number in Excel, enter =MAX(B2:B10), replacing B2:B10 with the cells you want to check. MAX returns the greatest numeric value in the selected range; it ignores blank cells and text stored in cells, so it is a quick way to identify a top score, sale, measurement, or other figure.
What MAX does in Excel
MAX is a built-in Excel function that compares numbers and returns the highest one. It does not rearrange your data or change any cells: it produces a result in the cell containing the formula. If the underlying values change, Excel recalculates the result.
The function is useful when the largest value matters more than the order of the data. For example, a list of monthly expenses can remain in date order while a MAX formula shows the biggest expense. You can also use it to find the highest test score, the latest date, or the largest quantity in an inventory list.
MAX finds a numerical maximum, not necessarily the item or person associated with it. If you want to return a name alongside the highest sales figure, you will need an additional lookup formula or another method. MAX answers the narrower question: “What is the largest number in these values?”
MAX syntax and where to enter the formula
The basic syntax is =MAX(number1, [number2], ...). The first argument is required; later arguments are optional. An argument can be a cell reference, a range, a named range, or a number. In Microsoft 365 for Windows, select the cell for the answer, type the formula in the cell or formula bar, and press Enter. Excel uses commas between arguments in the examples here; regional settings may use a different list separator.
A range is a convenient way to check many cells at once. The colon in B2:B10 means every cell from B2 through B10, including both endpoints. You can also supply separate ranges or individual values, as long as the formula has at least one argument.
Find the maximum in a list or several ranges
Suppose cells B2:B6 contain 12, 18, 25, 9, and 31. With those values in B2:B6, enter =MAX(B2:B6) in another cell. The result is 31. If a new value of 34 is entered in B7, changing the formula to cover B2:B7 makes the result 34.
When the values to compare are in separate areas, include each area as an argument. For example, if B2:B4 contains 12, 18, and 25, and D2:D4 contains 9, 31, and 20, enter =MAX(B2:B4,D2:D4). The answer is 31. This can be useful when figures are split across sections of a worksheet, though a single continuous data range is often easier to maintain.
You can also compare numbers directly. The formula =MAX(7,14,3) returns 14. That can be useful inside a larger calculation, but for a changing list, referencing cells is usually preferable: you can update the data without editing the formula each time.
How MAX treats blanks, text, dates, and errors
When a cell reference or range is supplied, MAX ignores blank cells, text entries, and logical values such as TRUE or FALSE in the referenced cells. This means a heading like “Sales” in the same column will not be treated as a number. However, a number stored as text is not a numeric value for this purpose and may be ignored, which can lead to a result that looks unexpectedly low.
Dates and times are stored by Excel as numbers, so MAX can compare them. A date is represented by a serial number, and a later date has a larger serial value. If B2:B4 contains dates for January 4, January 12, and January 8 of the same year, =MAX(B2:B4) returns the serial value for January 12; format the result cell as a date to display it as a calendar date. The result is not meaningful as a date if the cells contain unrelated numeric measurements.
If the referenced range contains an error such as #N/A or #VALUE!, MAX generally returns an error rather than silently skipping it. Correct the source error or use a deliberate error-handling approach if the data is expected to contain errors. If the range contains no numeric values at all, MAX returns 0. That zero is a default result, not evidence that a real measured value was zero.
Find the largest value that meets a condition
Use MAXIFS when you need the highest value only among records that meet one or more criteria. MAXIFS is available in Microsoft 365 and Excel 2019 or later. Its basic structure is =MAXIFS(max_range, criteria_range1, criteria1, ...): the max range contains the numbers to compare, and each criteria range is checked against its matching criterion.
For example, suppose A2:A6 contains the departments North, South, North, West, and South, while B2:B6 contains sales of 120, 95, 180, 110, and 150. With department names in A2:A6 and sales in B2:B6, enter =MAXIFS(B2:B6,A2:A6,"North"). The result is 180, the largest sale among North records. The text criterion is enclosed in straight double quotes.
Criteria can also refer to a cell. If D2 contains North, use =MAXIFS(B2:B6,A2:A6,D2) with the same layout and D2 holding the department to check; it returns 180. This makes it easy to change the selected department without rewriting the formula. MAXIFS can use multiple criteria pairs, such as department and month, provided the corresponding ranges align with the max range.
One detail matters when no records meet the criteria: MAXIFS returns 0. If the values being tested can all be negative, that zero could look like a valid maximum even though there were no matching records. Check whether the criterion matches any rows before interpreting the result.
MAX and hidden or filtered rows
MAX includes values in rows hidden manually and rows removed from view by a filter. Filtering a table changes which records are displayed, not what MAX considers. That behavior is appropriate when you want the maximum across the full dataset, but it can surprise someone expecting the result to change with the visible rows.
For a maximum that responds to filtering, use SUBTOTAL instead. With values in B2:B6, enter =SUBTOTAL(104,B2:B6). The result is the largest value among the visible rows: SUBTOTAL function number 104 uses MAX while excluding manually hidden rows as well as filtered-out rows. Function number 4 also uses MAX and ignores filtered-out rows, but includes manually hidden rows. This distinction is useful when a report should reflect only the records currently shown.
MAX compared with MIN, MAXA, and MAXIFS
Choose the function based on what you want to count and compare:
- MAX returns the greatest numeric value and is the usual choice for a straightforward range.
- MIN returns the smallest numeric value, such as the lowest score or earliest date.
- MAXA also evaluates logical values and text in references rather than treating them as MAX does. In particular, TRUE is treated as 1, FALSE as 0, and text as 0. It is useful only when those values should participate in the comparison.
- MAXIFS returns the greatest value that meets specified criteria, such as the highest sale for one department.
A common misconception is that MAXA is simply a more powerful MAX. It can produce a different answer because it assigns numeric meaning to logical values and text. For ordinary numeric data, MAX is generally clearer and less likely to let a text entry affect the result.
Common MAX problems and how to fix them
If the answer is zero or lower than expected, first check whether the values are genuinely numeric. A number imported as text may look identical to a number but be ignored by MAX. Select a sample cell and inspect its contents or test whether the data needs converting to numbers. Also confirm that the formula range reaches the last row; =MAX(B2:B10) will not include a value in B11.
If the answer is an error, inspect the referenced cells for errors. If the answer does not change after filtering, remember that MAX includes filtered-out records; use SUBTOTAL when the visible rows should determine the result. If a date appears as a large number, apply a date number format to the result cell. Conversely, if the result is formatted as a date but represents a measurement, change the number format to General or Number.
For a maximum that updates as new records are added, consider storing the data in an Excel table. Table columns expand when rows are added, and structured references can make formulas easier to read than fixed cell addresses. Another simple option is to leave room in the range, but an overly broad range can make the formula harder to audit. Whichever approach you choose, verify that the range covers the intended records and no unrelated figures.
Frequently Asked Questions
What is the MAX formula in Excel?
Use =MAX(range), replacing range with the cells to compare, such as =MAX(B2:B10). It returns the highest numeric value in that range.
Does MAX ignore blank cells?
Yes. MAX ignores blank cells, along with text and logical values stored in referenced cells. It does not ignore errors in the range.
Can MAX find the highest value based on a condition?
Yes. Use MAXIFS, such as =MAXIFS(B2:B6,A2:A6,"North"), to return the greatest value in B2:B6 where the corresponding department in A2:A6 is North.
Why does MAX return zero?
It returns 0 when it finds no numeric values to compare. Check whether the range is empty, contains numbers stored as text, or excludes the cells you intended to include.
Does MAX change when I filter a list?
No. MAX includes filtered-out rows and manually hidden rows. Use =SUBTOTAL(104,B2:B10) when the maximum should reflect only visible rows in that range.
Related
- Shortcut Key to Capitalize Letters in Excel
- Excel Zoom Shortcut: How to Zoom In on a Worksheet
- Excel Insert Picture Shortcut: Windows, Mac, and Web
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Merge Multiple Cells Into One Cell: A Clear GuideTo merge cells in Excel, select the range, then choose Home > Merge & Center ▼ > Merge…
- How to Randomize in Excel: Shuffle Lists and NumbersTo randomize a list in Excel, add a helper column with =RAND(), fill it down beside your data,…
- How to Insert a Check Box in Word for Lists or FormsTo add a clickable check box in the Word desktop app, open the Developer tab and select Check…
- Microsoft Excel Cash Flow Template: Build or Download?To get a Microsoft Excel cash flow template, open Excel for Microsoft 365 on Windows, choose File >…
- How to Delete Blank Rows in Excel in One Go SafelyTo delete completely blank rows in Excel at once, add a helper column that marks rows with no…
- Outlook Written Signature: How to Create and Set It UpTo create a written signature in Outlook, type the name and contact details you want at the bottom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.