Excel Formula for Highest Value: MAX and Other Options

Formulas & functions · Updated November 18, 2026

To find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare are in cells B2 through B20. MAX returns the largest numeric value in that range; the right alternative depends on whether you need a maximum that meets criteria, the second-highest value, or the label beside the result.

What the MAX formula returns

MAX compares numeric values and returns the greatest one. For example, if the selected cells contain 12, 35, 28, and 41, the result is 41. The function returns a value, not the cell address or the row containing it, so a separate lookup is needed if you want to identify what that maximum represents.

For a normal cell reference such as B2:B20, MAX ignores blank cells and text entries. It does include zero, so zero can be the answer if the range contains no larger numbers. A date or time is also a number in Excel’s underlying storage, so MAX can return the latest date or time when those values are included in the range. The displayed result depends on the number format of the cell containing the formula.

If the referenced cells contain an error value, MAX may return that error instead of a number. That is different from text: a note such as “not available” in a cell is generally ignored by MAX, while an error such as #N/A can prevent the calculation from returning the expected maximum.

How to enter the formula in Excel

In Microsoft Excel for Microsoft 365 on Windows, select the cell where you want the answer, type the formula, and press Enter. In this example, the values to compare are in B2:B20:

With values in B2:B20, enter =MAX(B2:B20). If the largest value is 41, the formula returns 41.

You can also select the range with the mouse while entering the formula. Type =MAX(, select B2:B20, type the closing parenthesis, and press Enter. The formula bar shows the same range reference. If the data is in another worksheet, include the sheet name in the reference; for example, a range on a sheet named January is written with the sheet name followed by an exclamation mark.

Excel may use a semicolon rather than a comma between function arguments in some regional settings. The examples here use commas, as in standard English-language Excel syntax. When MAX has only one range argument, there is no separator to change.

Using multiple ranges or a whole column

MAX can compare more than one range in a single formula. This is useful when values are split across separate columns or worksheets. Each argument can be a range, and Excel returns the greatest numeric value across all of them.

With values in B2:B20 and D2:D20, enter =MAX(B2:B20,D2:D20). If the largest number in either range is 96, the result is 96.

For a growing list, you could refer to an entire column. With values anywhere in column B, enter =MAX(B:B). This can be convenient when new values will be added, because the formula includes them automatically. However, a bounded range or an Excel Table column is often easier to inspect and can make a workbook’s intent clearer. Avoid putting the formula itself in the column it evaluates: a whole-column reference that includes the result cell can create a circular reference.

If your data is formatted as an Excel Table, structured references can make formulas easier to read and maintain as rows are added. For example, if a table is named Sales and its numeric column is named Amount, MAX can refer to that column. Confirm the table and column names in your workbook before using a structured reference.

Find the highest value that meets a condition

MAX alone cannot limit its result to rows matching a condition. For that task, use MAXIFS in supported versions of Excel, including Microsoft 365. MAXIFS takes the values to compare first, followed by a criteria range and the condition to apply. The criteria range and the values range should have corresponding rows.

With regions in A2:A100 and sales figures in C2:C100, enter =MAXIFS(C2:C100,A2:A100,"East"). If the highest sales figure among rows labeled East is 725, the formula returns 725.

The condition can refer to a cell instead of being typed into the formula. That makes it easy to change the selected region without editing the formula. MAXIFS can also use multiple condition-range and condition pairs, such as a region and a product category. Keep the compared ranges the same size so each condition applies to the intended value’s row.

A result of zero needs interpretation. MAXIFS returns zero when no values meet the criteria, and zero may also be a genuine maximum among matching values. Check that the condition text matches the source data, including extra spaces or inconsistent spelling, and confirm whether matching rows actually contain numbers. If those checks do not resolve the ambiguity, use a separate count of matching rows to distinguish “no match” from a maximum of zero.

Get the second-highest or another ranked value

MAX returns only the highest number. Use LARGE when you want a value at a particular rank: the first-largest, second-largest, third-largest, and so on. Its second argument is the rank, often called k. It must be a positive integer no greater than the number of numeric values in the range.

With scores in B2:B20, enter =LARGE(B2:B20,2) to return the second-largest value. If the values in descending order begin 96, 91, and 84, the result is 91.

LARGE counts repeated values as separate entries. If the top values are 96, 96, and 91, the second-largest value is 96, not 91. This is useful when you mean the second row in a sorted list, but it may not be what you mean by the second distinct score. For a distinct ranking, first remove duplicates or use a distinct-value approach in a version of Excel that supports dynamic-array functions, then rank that resulting list.

If k is zero, negative, or larger than the number of numeric entries, LARGE returns #NUM!. Blank cells and text do not count as numeric entries for this purpose. Check the range and the requested rank if you see that error.

Return the label or row associated with the maximum

A common follow-up is “Which person, product, or date has the highest value?” MAX cannot return that label by itself, but XLOOKUP can find the maximum in one range and return the corresponding entry from another. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

With names in A2:A20 and their scores in B2:B20, enter =XLOOKUP(MAX(B2:B20),B2:B20,A2:A20). If the maximum score is in B7, the formula returns the name from A7.

If the maximum occurs more than once, this XLOOKUP formula returns the first matching entry in the lookup range. That is not necessarily the only person or item tied for the top value. To report all ties, use a filtering approach or inspect the matching rows; do not assume a single returned label proves the maximum is unique. If the label and score ranges are misaligned, the formula can return the wrong label, so make sure both ranges start and end on the same rows.

Calculate a maximum from visible rows

MAX includes values in rows hidden by a filter. If you want the highest value among currently visible rows, use SUBTOTAL instead. With 104 as its function number, SUBTOTAL performs a MAX calculation that also ignores manually hidden rows. Filtered-out rows are excluded by SUBTOTAL as well.

With values in B2:B20, enter =SUBTOTAL(104,B2:B20). The result is the largest value among the visible rows in that range.

There is an important distinction between SUBTOTAL function numbers 4 and 104: both calculate a maximum, but 4 includes manually hidden rows and 104 excludes them. Both exclude rows removed from view by a filter. This makes 104 useful when you want the calculation to respond both to filtering and to rows hidden manually. If hidden rows should still count, use MAX or choose the behavior that matches your report.

Common problems and checks

If the result seems too small, first check whether the values are truly numbers. Imported figures can be stored as text, especially when they contain unexpected spaces, currency symbols, or separators copied from another system. MAX ignores text in a referenced range, so a large-looking text entry may not participate. Convert or clean the source data before relying on the result.

  • The result is zero: Check for an empty range, values stored as text, or a genuine maximum of zero. With MAXIFS, also verify that at least one row meets the condition.
  • The formula shows an error: Look for error values in the referenced cells, an invalid range, or a misspelled worksheet name. LARGE can return #NUM! when its rank is outside the valid range.
  • The maximum appears to ignore a row: Confirm that the row is included in the range and that the value is numeric. A filter does not make MAX ignore a row.
  • The answer looks like a date: Check the result cell’s number format. Excel stores dates as numbers, so a maximum over dates is normally the latest date, but a general or date format can make the same stored value display differently.

It is also worth checking the range boundaries. A formula using B2:B20 will not include a new entry in B21. Update the range, use an Excel Table column, or choose a suitable expanding reference if new rows are routinely added. Keep the formula outside the data range when using whole-column references to avoid circular calculations.

Frequently Asked Questions

What is the Excel formula for the highest number?
Use MAX. If the values are in B2:B20, enter =MAX(B2:B20). It returns the greatest numeric value in that range.

Does MAX include zero and negative numbers?
Yes. Zero and negative numbers are valid numeric values. MAX returns whichever included number is greatest, so a range containing only negative values returns the negative value closest to zero.

Does MAX ignore blank cells and text?
For a cell-range reference, MAX ignores blank cells and text entries. It does not treat a number stored as text as an ordinary numeric value for this calculation, so convert imported text-numbers if they need to count.

How do I find the highest value for one category?
Use MAXIFS when available. Give it the numeric range, the range containing the category, and the category to match. Make sure the two ranges cover corresponding rows, and check whether zero means no matching numeric values or a real maximum.

How do I find the second-highest value?
Use LARGE with 2 as the rank, such as =LARGE(B2:B20,2) when the values are in B2:B20. Repeated values count separately, so ties can affect what is considered second-highest.

Can MAX tell me which row contains the highest value?
No. MAX returns the value only. Pair it with a lookup such as XLOOKUP to return an associated label, and account for duplicate maxima because a standard lookup returns one match rather than every tied row.

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.