To return the greater of two numeric values in Excel, use =MAX(A2,B2), assuming the values are in cells A2 and B2. MAX returns the larger number; if the values are equal, it returns that shared value.
The simplest formula: MAX
MAX is the standard choice when you want the largest of two or more numbers. Enter the formula in the cell where you want the result, changing A2 and B2 to match your worksheet. For example, if A2 contains 18 and B2 contains 24, the result is 24. If A2 contains 31 and B2 contains 24, the result is 31.
The formula works with cell references, and you can also compare a wider set of cells. With the values to compare in A2 and B2, the formula =MAX(A2,B2) returns whichever number is higher. This is usually easier to read and maintain than a longer conditional formula.
MAX returns a value, not an explanation of which cell supplied it. If you need the larger value for a total, a calculation, or a later formula, that is often exactly what you need. If you also need to identify the winning cell or its label, use a separate comparison, as described below.
How MAX handles the comparison
Excel evaluates the arguments supplied to MAX and returns the greatest numeric value. The order of the references does not affect the result: the formula gives the same answer whether the first value is larger or the second is larger. A tie is not an error; MAX simply returns the tied value.
MAX can compare more than two cells. With three values in A2, B2, and C2, use =MAX(A2:C2) to return the largest number in that range. The same range approach works for a row or column of values. If the cells are not next to one another, you can list separate references: with values in A2, C2, and E2, use =MAX(A2,C2,E2).
These formulas can be copied down a worksheet. If the formula is in D2 and you fill it down, Excel normally adjusts the references to compare A3 with B3 on the next row, then A4 with B4, and so on. Check the first few results after filling to confirm that the references match the intended rows.
Worked examples for common worksheets
Suppose a sales worksheet has a morning total in B2 and an afternoon total in C2, and you want to show the higher total in D2. With those values in B2 and C2, enter =MAX(B2,C2) in D2. If B2 is 460 and C2 is 525, D2 displays 525. If the afternoon total is 410 instead, it displays 460.
For a score sheet, imagine the first attempt is in C5 and the second attempt is in D5. With those two scores in C5 and D5, put =MAX(C5,D5) in the result cell. If the scores are 72 and 88, the result is 88. The formula returns the higher score but does not indicate which attempt earned it.
For a comparison across several bids in B2 through E2, the layout is one bid per cell in that range. Use =MAX(B2:E2) to return the largest bid. This calculation identifies the numerically highest amount; it does not determine which bid is best overall, since factors such as scope and delivery terms are outside the formula.
- Use two cell references when comparing two individual values.
- Use a range reference when the values occupy adjacent cells.
- Use separate references when the values are in nonadjacent cells.
MAX compared with IF
MAX is concise, but IF makes the comparison rule explicit. With the values in A2 and B2, use =IF(A2>B2,A2,B2) to return A2 when it is greater than B2 and otherwise return B2. If A2 is 18 and B2 is 24, the result is 24; if A2 is 31 and B2 is 24, the result is 31.
There is one small tie detail: because the test is whether A2 is strictly greater than B2, a tie makes the formula return B2. The displayed numeric result is still the tied value, so this only matters if you later adapt the formula to return labels or other information. You could use a greater-than-or-equal test to make the first branch win a tie.
For a simple numeric maximum, MAX is generally clearer. IF is useful when the rule involves what should happen in different cases—for instance, returning a particular result when one value exceeds a threshold. If your actual goal is to show the word or label associated with the larger value, compare the cells with IF and return the relevant label rather than using MAX alone.
Blank cells, text, and numbers stored as text
MAX has behavior that can surprise people when a cell is not a number. When its arguments are cell references, MAX ignores empty cells and text in those referenced cells. For example, with a number in A2 and a blank or ordinary text entry in B2, =MAX(A2,B2) returns the numeric value in A2. If both referenced cells contain no numeric values, MAX returns 0, which can look like a real result even though the inputs are empty or text.
A number that looks like a number but is stored as text is not necessarily treated like a numeric cell value by MAX. This can happen after importing data, when a source file uses an unexpected format, or when an entry has a leading apostrophe. If a value seems to be ignored, check the cell format and the underlying content. Converting the entry to a real number, for example with Excel’s available number-conversion tools, is usually better than building a workaround into the comparison.
Be careful about replacing MAX with IF just to handle unusual cell contents. IF comparisons can behave differently when cells contain text, blanks, or mixed types, and the result may not express the intended business rule. Decide first how missing or nonnumeric entries should be handled: should they be ignored, treated as zero, or flagged for correction? Then make that policy explicit in the worksheet.
Dates, percentages, and other numeric values
Dates and percentages can also be compared with MAX because Excel stores dates and percentages as numeric values. With dates in A2 and B2, =MAX(A2,B2) returns the later date. Format the result cell as a date if Excel displays a serial number instead of a calendar date. The underlying maximum is still the same; the number format controls how it appears.
With percentages in A2 and B2, the same formula returns the greater percentage. For example, 8% is greater than 6.5% when both cells contain actual percentage values. Make sure the entries have the intended units and formats: a value entered as 8 is not equivalent to a value entered as 8% in Excel’s numeric representation.
The formula compares the stored numbers, not the meaning or units you intend. Do not compare values measured on different scales—such as dollars and cents, or minutes and hours—without first converting them to a common unit. MAX cannot detect that two numbers represent unlike things.
Common errors and ways to check the result
A misspelled cell reference or a reference to the wrong row can produce an incorrect result even when the formula itself is valid. After entering a formula, inspect the references shown in the formula bar and verify the values in the referenced cells. If you copy a formula, confirm that the row references moved as expected.
MAX does not silently repair error values in its referenced cells. If a cell being evaluated contains an Excel error, the maximum calculation can return an error as well. Find and fix the underlying error rather than assuming the result is a valid comparison. Also check for hidden rows or filtered data: a normal MAX over a range considers the range’s values, including values in hidden rows.
For a quick check, compare the displayed answer with the two source cells and try a tie case. The result should match the larger input, and equal inputs should produce that same value. If the result is 0 unexpectedly, check whether both inputs are blank or text rather than numbers. If a date appears as a large integer, apply a date number format to the result cell.
Frequently Asked Questions
What is the formula for the greater of two cells in Excel?
With the two values in A2 and B2, enter =MAX(A2,B2). It returns the larger numeric value, or the shared value if the two are equal.
Can MAX compare more than two values?
Yes. With values in A2 through A10, use =MAX(A2:A10) to return the largest numeric value in that range. For nonadjacent cells, list their references as separate arguments.
How do I show which cell has the greater value?
MAX returns the greater value, not the source cell or its label. With numeric values in A2 and B2, an IF comparison can return a label or other output you choose. Decide how you want ties handled, since a strict greater-than comparison sends a tie to its alternative result.
What happens if the two values are the same?
MAX returns that shared value. An IF formula using a strict greater-than test also returns a numeric value in a tie, but it takes the false branch, so it returns the second cell’s value.
Why does MAX return 0 when the cells look empty?
MAX ignores blank cells and text in referenced cells. If there are no numeric values among the supplied references, it returns 0. Check whether the inputs are genuinely numeric and decide whether an empty-input result should be shown differently in your worksheet.
Does this formula work in Excel for Mac or Excel for the web?
Yes. MAX and IF are standard Excel functions, and the formulas work in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. In some regional settings, Excel may use semicolons instead of commas as formula separators; the examples here use the standard English-language comma syntax.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Named Ranges in XLOOKUP: How to Use Them in ExcelTo use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name…
- Excel Formula for Tomorrow’s Date: TODAY()+1 ExplainedIn Excel, use =TODAY()+1 to return tomorrow’s date. Enter it in a cell and format that cell as…
- Excel INDIRECT Named Range: How It Works and When to Use ItIn Excel, INDIRECT turns text into a cell or range reference, so you can use it to refer…
- IFERROR vs ISERROR: Which Excel Function Should You Use?IFERROR catches an error produced by a calculation and returns a replacement value; ISERROR checks whether a value…
- Filter an Inventory Spreadsheet in ExcelTo filter an inventory spreadsheet in Excel, click a cell in the data and choose Data > Filter;…
- Excel IFS in Conditional Formatting: How to Use ItYes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.