Excel Max Value in a Column: Find the Largest Number

How-to basics · Updated November 18, 2026

If your values are in B2:B100, enter =MAX(B2:B100) in an empty cell; Excel returns the largest numeric value in that range. To check a whole column instead, use =MAX(B:B). The MAX function is usually the quickest way to find a column’s highest number, but filters, conditions, ties, text, and errors can affect which result you need.

How MAX finds the largest value

MAX compares the numbers in its arguments and returns the greatest one. It does not sort the column or change the source data; it produces a single result wherever you put the formula. For example, if B2:B6 contains 12, 38, 7, 41, and 25, the maximum is 41. If two or more cells contain 41, MAX still returns 41 once. It does not tell you which row contains that value or how many times it appears.

For a basic calculation, place the formula in a cell outside the range being checked. With figures in B2:B100, the formula is =MAX(B2:B100). Keeping the result outside the input range makes the sheet easier to understand and avoids including the answer cell in later calculations. You can also select a cell, type the formula, and press Enter. In Microsoft 365 for Windows, Enter confirms this ordinary formula; no special array keystroke is required.

MAX accepts more than one range or individual number. With monthly figures in B2:B13 and another set in C2:C13, the formula =MAX(B2:B13,C2:C13) returns the largest value across both ranges. The function can take up to 255 arguments. For most worksheets, however, a single clearly defined range is easier to check and maintain.

Choose a range or check the entire column

A bounded range such as B2:B100 is a good choice when you know where the data begins and ends. It documents which rows count and can avoid including unrelated figures farther down the sheet. If new records will be added, expand the range when needed or convert the data to an Excel table so the reference can grow with it.

For a quick calculation that should include any numeric value in column B, use =MAX(B:B). A column reference includes the header and every row in that worksheet column, but MAX ignores ordinary text in referenced cells, so a heading such as “Sales” does not prevent the formula from working. A full-column reference is convenient, though a bounded range or table reference may be clearer in a large workbook with many calculations.

If the data is formatted as an Excel table named Table1, and the column heading is Sales, the formula is =MAX(Table1[Sales]). This structured reference is tied to the Sales column by name and normally adjusts as table rows are added or removed. To create a table, select the data and choose Insert > Table, then confirm whether the range has headers. Table names and column headings must match the names used in the formula.

Find the maximum among filtered or qualifying rows

A common surprise is that MAX still considers rows hidden by a filter. Filtering a list changes which rows are displayed, but it does not make =MAX(B2:B100) ignore the filtered-out values. If you want the greatest value only among records that meet a condition, use MAXIFS. For example, with sales amounts in B2:B100 and regions in A2:A100, the formula =MAXIFS(B2:B100,A2:A100,"North") returns the largest sales amount for the North region. MAXIFS is available in Excel 2019 and Microsoft 365.

MAXIFS can check multiple conditions. If dates are in C2:C100 and you want the maximum sales value for the North region on or after January 1, 2026, use a date cell such as E1 containing that date. The formula is =MAXIFS(B2:B100,A2:A100,"North",C2:C100,">="&E1). The criteria ranges must align with the maximum range: in this example, each range covers rows 2 through 100. The ampersand joins the comparison operator to the date value in E1.

Another related question is how to find the highest positive number while excluding zero and negative values. With the numbers in B2:B100, use =MAXIFS(B2:B100,B2:B100,">0"). If no values meet the criteria, MAXIFS returns 0, so 0 can mean either that the qualifying maximum is zero or that no positive value was found. Check the underlying data or add a separate count check if that distinction matters.

Return the name or row associated with the maximum

MAX returns a number, not its label. Suppose names are in A2:A100 and scores are in B2:B100. To return the name on the first row whose score equals the maximum, use =XLOOKUP(MAX(B2:B100),B2:B100,A2:A100,"No match"). XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The result is the name from column A that corresponds to the highest score.

If several people share the top score, that XLOOKUP formula returns the first matching name in the lookup range. It does not return every tie. To display all tied names in Microsoft 365, use FILTER with the maximum as the condition: =FILTER(A2:A100,B2:B100=MAX(B2:B100),"No matches"). FILTER is available in Microsoft 365 and Excel 2021 or later; its results spill into cells below the formula, so leave room for them. If you only need to know how often the maximum occurs, use =COUNTIF(B2:B100,MAX(B2:B100)). For the sample values 12, 38, 7, 41, and 41, this count is 2.

Understand blanks, text, and errors

When MAX refers to cells, it ignores empty cells, text entries, and logical values such as TRUE and FALSE. It evaluates numeric values. This means a value that looks like a number but is stored as text may be skipped. If a column contains 10, 25, and the text entry “90”, MAX can return 25 rather than 90. Convert the text to numbers if it is intended to be part of the calculation; changing the cell’s number format alone does not necessarily convert text into a numeric value.

Errors in the referenced range can make the MAX formula return an error instead of a number. Check for error cells such as #N/A or #VALUE! in the range before assuming the maximum is wrong. A useful first step is to inspect the source cells, formulas, and imported data rather than hide the error automatically. Wrapping a calculation in IFERROR can conceal a problem that needs fixing, so use it only when a fallback result is appropriate for the report.

If a range contains no numeric values, MAX returns 0. That can be misleading when zero is also a valid measurement. For example, a result of 0 might mean that every value is zero, or that the selected cells contain only blanks and text. Check the range and, if necessary, count numeric entries separately before interpreting the result.

Use the right approach for visible rows

MAX does not offer a “visible cells only” option. If you filter a list and need the maximum among visible records, you have several choices depending on how the sheet is built. MAXIFS is often simplest when the filter corresponds to a clear condition, such as a particular department or date period. You can state that condition in the formula instead of relying on which rows happen to be hidden.

For a one-off check, you can copy the visible values to a temporary range and apply MAX to that range. This is straightforward, but the copied values will not automatically stay in sync with later changes to the source data. If the result must update with a filter selection, consider adding a helper column that marks qualifying rows and calculating from those rows, or use a purpose-built formula appropriate to the workbook’s layout. Verify the result against the visible records, especially when several filters or manually hidden rows are involved.

Subtotal is another Excel function family used in filtered lists, but it is not a direct substitute for MAX in every setup. SUBTOTAL supports several operations and can treat filtered-out rows differently depending on the function number used. If your requirement is specifically “largest visible value,” test the chosen method with a small known example that includes both visible and filtered-out high values. That confirms the formula is following the intended visibility rules rather than returning the largest value in the full data.

Common mistakes and practical checks

  • Including the wrong cells: Check the formula’s range in the formula bar and confirm it includes all intended records but not unrelated totals or old data.
  • Expecting filters to change MAX: A normal MAX formula includes filtered-out rows. Use criteria or a visible-row method when that is not what you want.
  • Overlooking numbers stored as text: Imported data can look numeric but be ignored by MAX. Convert the affected entries to real numbers and recalculate.
  • Confusing a maximum with its location: MAX returns only the value. Use a lookup formula to identify the related label or row.
  • Assuming 0 proves the data has no problem: A zero can be a genuine maximum or the result of having no numeric values. Inspect the source range.
  • Using a formula inside its own range: Put the result in a separate cell so the formula does not create a circular reference.

For a quick audit, compare the result with the visible values, check the first and last rows in the referenced range, and look for errors or text-formatted numbers. If the source data changes frequently, a table with a structured reference can reduce the chance of forgetting to extend a fixed range. These checks are especially useful in reports where a single incorrect maximum could be mistaken for a real outlier.

Frequently Asked Questions

What is the formula for the highest number in an Excel column?
With the values in B2:B100, enter =MAX(B2:B100). To include all of column B, use =MAX(B:B). MAX returns the greatest numeric value it finds in the referenced cells.

Does MAX ignore blank cells?
Yes. MAX ignores blank cells in a referenced range, along with text and logical values. It uses numeric values; if the range contains no numbers, it returns 0.

How do I find the largest value in Excel without including zeros?
With values in B2:B100, use =MAXIFS(B2:B100,B2:B100,">0") to return the greatest value above zero. MAXIFS is available in Excel 2019 and Microsoft 365. If no positive values qualify, the result is 0.

Why does my MAX formula return 0?
The range may contain no numeric values, or all its numeric values may be zero or negative. Check for numbers stored as text and confirm the formula points to the intended cells. MAX returns 0 when it has no numeric values to evaluate.

Can Excel show the row containing the maximum?
Yes. If names are in A2:A100 and values are in B2:B100, XLOOKUP can return the name associated with the maximum. If multiple rows tie, a basic XLOOKUP returns the first match; FILTER can return all matching names in Microsoft 365 or Excel 2021 and later.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.