How to Get the Highest Number in Excel with the MAX Function

To get the highest number in a range, use Excel’s MAX function. With sample values in A2:A8, enter =MAX(A2:A8) in an empty cell; if those cells contain 18, 42, 7, 96, 31, 54, and 12, the result is 96. MAX returns the largest numeric value in the range, and the same method works in Excel for Microsoft 365 on Windows, Mac, and the web.

Before using the formula, check that the cells you want to compare contain numbers and that you have selected the correct range. The result is a value only: MAX does not tell you which row contains that value or whether it appears more than once. The steps below cover how to select the range, check the answer, and handle common variations such as filtered data.

What You’ll Need

An Excel workbook containing the numbers you want to compare.
The cell addresses for the data range, such as A2:A50. Include the data cells, not the column heading.
An empty cell for the result. Put it outside the range being checked so the formula does not include itself.

You do not need an add-in or a special setting. MAX is a built-in Excel function and works in older desktop versions as well as Microsoft 365. If your workbook uses a table, you can select the relevant column’s data cells instead of typing cell addresses.

Step-by-Step

Open the worksheet and identify the values. Find the column or row whose numbers you want to compare. For example, if monthly totals are in column A, note the first and last rows containing totals. If the first cell is A2 and the last is A50, the range is A2:A50. Exclude a heading such as Total in A1, since headings are not part of the numeric data.
Choose a result cell. Click a blank cell near the data, such as B2, or use a separate summary area. Make sure it is not inside the range you are checking. A formula placed in A2 while checking A2:A50 would refer to itself, creating a circular reference rather than giving a reliable maximum. Add a nearby label such as Highest value if other people will use the worksheet.
Enter the MAX formula. With the example values in A2:A8, type =MAX(A2:A8) into the blank result cell and press Enter. The colon means the range includes every cell from A2 through A8. Excel will display 96 for the sample numbers. For your data, replace A2:A8 with the range you identified; for example, use A2:A50 when the values run from row 2 to row 50.
Confirm Excel accepted the formula. After you press Enter, the selected result cell should show a number, not the formula text. Select the cell to see the formula in the formula bar. If Excel displays the formula itself, the cell may be formatted as Text or the formula may have been entered with an apostrophe before the equals sign. The troubleshooting section explains how to correct that.
Check the result against the source data. Scan the relevant range for a value larger than the result. You can also sort the data from largest to smallest as a separate check, but select the entire table before sorting so names, dates, and other related information stay with their original rows. The formula recalculates when numeric values in its range change, so you usually do not need to enter it again after editing a value.
Adjust the range when new rows are added. A fixed range such as A2:A50 will not include a new value entered in A51. If you expect the list to grow, use a range that includes a reasonable number of future rows, or convert the data to an Excel table and calculate the maximum from the table’s number column. Avoid including unrelated figures below the list: MAX will include them too if they fall within the selected range.
Use a whole-column range only when it suits the sheet. You can check all of column A by using A:A as the range in the formula. This is convenient when entries will be added far down the column, but it can include numbers elsewhere in that column, such as notes or summary figures. Keep the result formula in another column, and use a more precise range when you need to control exactly which values count.
Decide whether filtered or hidden rows should count. MAX includes numeric values in rows hidden by a filter and in manually hidden rows. If your intention is to find the greatest value among only the currently visible rows, use SUBTOTAL instead. With values in A2:A6 consisting of 12, 88, 41, 63, and 27, and a filter leaving 12, 41, and 27 visible, enter =SUBTOTAL(104,A2:A6) in a cell outside the range; it returns 41. The function number 104 calculates a maximum while excluding filtered-out and manually hidden rows.

Common Mistakes

Selecting the wrong range: A missing first or last row can make Excel overlook the real maximum. Check the range shown inside the parentheses and compare its endpoints with the first and last data cells.
Including unrelated numbers: If a total, date, or note value is inside the selected range, MAX treats it as another number. Narrow the range to the relevant records, or keep summaries in a separate area.
Expecting text that looks like a number to count: A value imported as text, such as text-formatted 950, may be ignored by MAX. Convert it to a number before relying on the result. A warning marker or left alignment can be a clue, although formatting alone is not a definitive test.
Assuming the result identifies a record: MAX returns the highest value, not its row, date, or owner. If you need the associated record, sort the full table by the number column from largest to smallest or use a lookup approach appropriate to your data.
Forgetting that dates are numbers in Excel: Excel stores dates as serial numbers. If a date falls in the range, it may be the largest numeric value and the result cell may display it as a date. Check the selected cells and the result cell’s number format.
Putting the formula inside its own range: A formula that includes its own cell can cause a circular reference warning. Move the formula outside the range or change the range endpoints to contain only the source data.

Tips

Make the result easy to understand. Add a label next to the answer and format the result consistently with the data. For currency, use a currency number format; for whole-number counts, use a number format with zero decimal places. Formatting changes how a result appears, not the value MAX calculates.
Keep headings out of the formula range. MAX ignores text in referenced cells, so including a text heading often does not change the result. Still, using only the data cells makes the formula clearer and prevents confusion if the heading or surrounding cells later change.
Use a table for a list that grows. Select the data and choose Insert > Table, making sure the header option is set correctly. Excel tables expand as you add rows, which helps formulas and related analysis keep up with new records. When writing a formula, you can select the data column rather than guessing the final row number.
Keep sorting safe. To inspect the top value, sort the whole dataset—not just the numeric column—so each row remains intact. On Windows, use the sort controls on the Data tab after selecting the table. On Mac, the same general sorting controls are on the Data tab. In Excel for the web, select the table or range and use the available Data sorting controls.
Expect automatic recalculation. When calculation is set to Automatic, Excel updates the MAX result after a value in the referenced range changes. If the result stays unchanged after editing data, check Formulas > Calculation Options > Automatic on Windows. The equivalent setting is available from Excel’s calculation options on Mac; Excel for the web generally recalculates formulas automatically.
Remember the formula works across platforms. The MAX syntax is the same in Excel for Windows, Mac, and the web. You can enter it in a cell or in the formula bar. Keyboard shortcuts for editing cells differ slightly by platform, but no special shortcut is required to calculate the maximum.

Troubleshooting

The answer is zero, but the range appears to contain values. Check whether the entries are genuine numbers or text that only looks numeric. Select a suspect cell and look at the formula bar; imported values may have leading apostrophes or spaces. You can convert text values using Excel’s number-conversion warning when it appears, or clean the source data and then test the formula again. Also confirm that the formula points to the cells you intended.

The answer is lower than a value you can see. Verify the range endpoints and whether the larger-looking entry is stored as text. Check for filters too: MAX still includes filtered-out rows, so filtering alone will not restrict its result. If you want the maximum among visible rows only, use the SUBTOTAL approach in the steps above and confirm that the cells you want counted are actually visible.

The formula appears in the cell instead of a result. Select the formula cell and change its number format from Text to General using Home > Number Format. Then edit the cell—press F2 and Enter on Windows, or Control+U and Return on Mac—or re-enter the formula. Also remove any apostrophe before the equals sign. Merely changing the format may not convert a formula that Excel already stored as text until you re-enter it.

Excel shows an error or a circular-reference warning. Check that the formula begins with an equals sign, has balanced parentheses, and uses a valid cell range. Confirm the result cell is outside the range. If the range contains an error value such as #N/A or #VALUE!, MAX may return an error rather than a maximum; correct the source error or use a range that excludes the problem cell.

The result looks like a date or has unexpected decimal places. The underlying answer may be correct while the result cell has an unsuitable number format. Select it and choose an appropriate format from Home > Number Format. If the source range itself includes dates or decimal values, decide whether those values belong in the comparison before changing how the result is displayed.

Frequently Asked Questions

What is the Excel formula for the highest number?
Use MAX with the range you want to compare, such as =MAX(B2:B20) when the values are in B2:B20. Put the formula in a cell outside that range. MAX returns the largest numeric value it finds.

Does MAX ignore blank cells and text?
Yes. When you give MAX a cell reference or range, it ignores blank cells and text entries. It does not treat a text-formatted number as a numeric value, so convert such entries if they should be included. Error values in the referenced range can cause the formula to return an error.

How can I find the row containing the highest number?
MAX gives you the value, not its location. Sort the complete table by the relevant number column from largest to smallest; the first data row then contains the highest value. Keep all columns selected while sorting so each record remains together. If several rows share the top value, they may appear together at the top.

What happens if the highest number appears more than once?
MAX returns that number once and does not report how many times it occurs. To see every matching record, filter the value column for the returned maximum or sort the full table so duplicate maximum values appear next to one another.

How do I find the highest value in filtered rows only?
Use SUBTOTAL with function number 104 and the value range. For example, if the data is in A2:A50, =SUBTOTAL(104,A2:A50) returns the largest visible value and excludes rows hidden by a filter or hidden manually. Use MAX instead when hidden rows should still count.

Can I use MAX in Excel for Mac or Excel for the web?
Yes. MAX uses the same formula syntax in Microsoft Excel for Windows, Mac, and the web. Enter it in a worksheet cell as usual; the main differences between platforms are in some interface details, not in this function.

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.