Statistical
MAX Function in Excel
Returns the largest value in a set of values.
Syntax
- =MAX(number1, [number2], ...)
Arguments
- number1 (required): First number or range
- number2 (optional): Additional numbers or ranges
Examples
- =MAX(A1:A100) - Find largest value in range - Result: Maximum value
- =MAX(A1:A10, B1:B10, 100) - Max across multiple ranges and value - Result: Overall maximum
Excel MAX function documentation
- MAX returns the largest numeric value in a set of arguments or range.
- Syntax: =MAX(number1, [number2], …). Ignores text and blank cells.
- Use [MAXIFS](/functions/maxifs/) when the maximum depends on criteria such as region or date.
- Related: [MIN](/functions/min/), [LARGE](/functions/large/), [AGGREGATE](/functions/aggregate/).
MAX syntax and edge cases
- Empty range or all text returns 0 — not an error.
- Negative numbers are valid; MAX returns the least negative (closest to zero) among negatives.
- Dates are numbers; MAX finds the latest date in a date column.
- Logical values in references are ignored unless typed directly as arguments.
Step-by-step: highlight peak sales month
- Step 1 — Monthly revenue in B2:B13 with month labels in A2:A13.
- Step 2 — Peak value: =MAX(B2:B13).
- Step 3 — Which month: use [INDEX](/functions/index/) + [MATCH](/functions/match/): =INDEX(A:A, MATCH(MAX(B:B), B:B, 0)).
- Step 4 — For max in one region filter first or use =MAXIFS(amount, region, "East").
- Step 5 — Compare with chart high point for sanity check.
MAX vs MAXIFS vs LARGE
- MAX — largest value in entire range.
- MAXIFS — largest value where criteria match (M365 / Excel 2019+).
- LARGE — nth largest (1st, 2nd, 3rd) — use LARGE(range, 2) for second-highest.
- Category hub: [Statistical functions](/categories/statistical/).
Worked examples to copy
- Peak daily sales: =MAX(D2:D366).
- Latest date: =MAX(A:A) when column holds date serials.
- Max of two cells: =MAX(E2, F2) for scenario comparison.
- Ignore errors: =AGGREGATE(4, 6, range) when #N/A values exist in the column.
People also ask
- Does MAX ignore text? — Yes. Text and blanks are skipped.
- Why MAX returns 0? — No numeric values in the range.
- MAX vs highest in filter? — MAX includes hidden rows; SUBTOTAL or MAX on visible range differs.
- Can MAX work with criteria? — Use MAXIFS or MAX with IF array (legacy) patterns.
Common errors
- Returns 0 if range contains only text/blanks
- Ignores logical values and text
Use cases
- Finding peaks
- Data validation
- Range analysis
Frequently asked questions
- What is the difference between MAX and MAXIFS? MAX finds the largest value in a range unconditionally. MAXIFS finds the largest value that meets criteria: =MAXIFS(B:B, A:A, "East") finds max in B where A is East.
- Does MAX include text and logical values? No, MAX ignores text and logical values (TRUE/FALSE). Use MAXA to include them (TRUE=1, FALSE=0, text=0). =MAX(1,TRUE,"5")=1, =MAXA(1,TRUE,"5")=1.
- How do I find the second largest value? Use LARGE: =LARGE(A:A, 2) returns second largest. =LARGE(A:A, 3) returns third largest. MAX is equivalent to LARGE(range, 1).
- Can MAX compare dates? Yes! Dates are numbers in Excel. =MAX(A:A) on a date column returns the most recent date. Format the result cell as a date to display properly.
- How do I find max with multiple conditions? Use MAXIFS: =MAXIFS(C:C, A:A, "East", B:B, ">2024-01-01") finds max in C where A is East AND B is after Jan 1, 2024.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use MAX
- Finding peaks — common Statistical scenario for MAX.
- Data validation — common Statistical scenario for MAX.
- Range analysis — common Statistical scenario for MAX.
MAX in the Statistical category
- Browse all Statistical functions at /categories/statistical/ for related formulas.
- MAX syntax: =MAX(number1, [number2], ...)
- number1 (required): First number or range
- number2 (optional): Additional numbers or ranges
- Confirm MAX arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm MAX arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- MIN (/functions/min/): Returns the smallest value in a set of values.
- MAXIFS (/functions/maxifs/): Returns the maximum value among cells that meet multiple criteria.
- LARGE (/functions/large/): Returns the k-th largest value in a data set.
- MAXA (/functions/maxa/): Returns the largest value in a list of arguments, including text and logical values. Text and FALSE = 0, TRUE = 1.
Errors to watch for
- Returns 0 if range contains only text/blanks — review causes on linked error pages in the directory.
- Ignores logical values and text — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter MAX on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
MAX worked examples to copy
- =MAX(A1:A100) — Find largest value in range. Expected result: Maximum value.
- =MAX(A1:A10, B1:B10, 100) — Max across multiple ranges and value. Expected result: Overall maximum.
MAX reference summary for crawlers and offline review
- MAX belongs to the Statistical category in Excel. Returns the largest value in a set of values.
- Full syntax: =MAX(number1, [number2], ...). Open /functions/max/ for parameters, FAQs, and related pages.
- Common mistakes: Returns 0 if range contains only text/blanks; Ignores logical values and text
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.