How to Find the Highest Number in Excel

If your numbers are in A2:A6, enter =MAX(A2:A6) in an empty cell to return the largest one. For example, if those cells contain 12, 48, 31, 9, and 42, the result is 48. The MAX function is the quickest way to find the highest number in a range in Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

An Excel workbook containing the numbers you want to compare.
A clear idea of which cells or column make up the data to check.
An empty cell where Excel can display the result. You can also put the formula in a cell beside the data.

You do not need to sort the data first. MAX checks the values in the range and returns the largest numeric value, regardless of where it appears.

Step-by-Step

Identify the data range. Look at the worksheet and note the first and last cells containing the numbers. For example, if the values start in B2 and end in B25, the range is B2:B25. Exclude a heading such as Sales or Score, and avoid including unrelated columns. If you are unsure, click a data cell and check its column letter and row number at the top and left of the worksheet.
Choose a blank result cell. Click a cell that is not part of the data, such as C2 if your values are in B2:B25. Putting the result outside the range prevents Excel from including the formula cell in its own calculation. If you place the result in the same column, make sure it is below the last value and keep it outside the range you select.
Type the MAX formula. With the example values in A2:A6, click the result cell and enter =MAX(A2:A6). The sample values are 12, 48, 31, 9, and 42, so this returns 48. MAX compares the numbers and reports the greatest one; it does not change the order of the original data. Press Enter to calculate the result.
Use the range that matches your sheet. Replace A2:A6 with your actual cell addresses. For data in B2:B25, the formula would use B2:B25; for a short list in D4:D12, use D4:D12. The colon means “through,” so A2:A6 includes every cell from A2 down to A6. If your data is arranged across a row, use the first and last cells in that row, such as B2:F2.
Check that the result makes sense. Compare the returned value with the values in the range. You can scan the list, or use the filter and sort options described below. If your expected maximum is 48 but Excel returns 42, check whether the cell containing 48 was left outside the selected range. Double-click the formula cell or select it and look in the formula bar to inspect the range being used.
Find the highest number for one category, if needed. MAXIFS can return the largest value that meets a condition. For example, suppose categories are in A2:A6 and corresponding numbers are in B2:B6: A2:A6 contains North, South, North, North, South, and B2:B6 contains 12, 48, 31, 9, 42. Enter =MAXIFS(B2:B6,A2:A6,"North") to find the highest number for North; the result is 31, the greatest of 12, 31, and 9. MAXIFS is available in Microsoft 365 and Excel 2019 or later.
Sort the numbers to see the largest at the top. If you want to inspect the data rather than calculate a separate answer, select the number cells and choose Data > Sort Largest to Smallest. If each number belongs to a name or other row information, select the whole table before sorting so that the associated records stay together. If Excel asks whether to expand the selection, choose that option to keep each row intact.
Highlight the largest value visually. Select the number range, then choose Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change 10 to 1 in the rule dialog and choose a format, then select OK. Excel highlights the largest item in the selected range. This is useful when you want to spot the value in place, but the MAX formula is usually easier to maintain if the data changes.

Common Mistakes

Selecting the wrong range: A maximum outside the selected cells cannot be returned. Include all intended rows, but leave out unrelated figures such as totals from a different section.
Including a heading or notes: MAX ignores text in referenced cells, so a normal text heading will not become the answer. Still, use a clean numeric range so you can see exactly what the formula evaluates.
Assuming a number-looking entry is numeric: Values imported with an apostrophe or stored as text may be ignored by MAX. A cell that looks like 500 can therefore be missed if Excel treats it as text.
Putting the formula inside its own range: This can create a circular reference or an unexpected calculation. Put the result in a separate cell and check that the formula range does not include it.
Sorting only one column of a table: The numbers may sort while names or dates stay in place, disconnecting the records. Select the full table before sorting related rows.
Expecting MAX to show which row contains the answer: MAX returns the largest value only. If several rows contain that value, the formula still returns the value, not a unique matching name or position.

Tips

Use a whole-column reference for a changing list. If entries are in column B and new values will be added there, use =MAX(B:B) in a different column. The formula checks the entire column, so it includes later additions without changing the range. Keep the result outside column B to avoid including it in the data.
Use an Excel Table for growing data. Select the data and choose Insert > Table, ensuring the header option matches your layout. A table expands as you add rows, and structured references can make formulas easier to understand. Check the formula range after adding data, particularly if you have placed the result outside the table.
Keep the original values unchanged. MAX is a read-only calculation: it shows the result in the formula cell and leaves the data where it is. Sorting is different because it rearranges rows, so use the formula when preserving the current order matters.
Refresh your expectations when values change. A MAX formula recalculates when a value in its referenced range is edited. If calculation is set to manual, the displayed answer may not update immediately; choose Formulas > Calculation Options > Automatic, or press F9 to recalculate.
Know how blanks and errors behave. Blank cells and text in a referenced range are ignored by MAX, but an error value in the range can make the formula return an error. Also, if there are no numeric values, MAX returns 0, which can look like a valid result even when the range is empty.

Troubleshooting

The answer is lower than the largest value I can see. Check the formula’s range first. Select the result cell and inspect the reference in the formula bar, then confirm that the cell with the expected maximum is included. If it is included, select that value and check whether it is stored as text. You can often convert imported text numbers by selecting the affected cells and using the warning icon’s Convert to Number command when Excel offers it.

The formula displays an error. Check that the range uses a colon between its first and last cell, and that each parenthesis is present. Look for error values such as #N/A or #VALUE! inside the referenced cells; MAX may pass an error through instead of returning a number. Resolve the source error or exclude the affected cell only if it does not belong in the comparison.

The result is 0, but I expected a positive number. Confirm that the range contains numeric values, not numbers stored as text, and that the formula points to the right worksheet and cells. If all the cells are blank or contain text, MAX returns 0. If the data is negative, zero may also be the true maximum only when zero is included as a numeric value in the range; inspect the selected cells to confirm.

The highlighted cell is not the one I expected. Reopen Home > Conditional Formatting > Manage Rules and confirm that the rule applies to the intended range and that the Top 10 Items value is set to 1. If the highest number occurs more than once, more than one cell may qualify as a top value, depending on the rule and values in the selected range.

The formula appears as text instead of calculating. Change the cell’s number format from Text to General, then edit the cell and press Enter. Also make sure the entry begins with an equals sign and does not have an apostrophe before it. If formulas are showing throughout the worksheet, press Ctrl+` on Windows to toggle Show Formulas off; on many Mac keyboards use Control+` (the key may require the Option key depending on the keyboard layout).

Frequently Asked Questions

What Excel function finds the highest number?

Use MAX. It returns the largest numeric value in the cells you specify, such as a column, row, or selected range. It does not identify the row or label associated with that number.

Does MAX include blank cells or text?

No. MAX ignores blank cells and text in a referenced range. A number stored as text is also not treated as a numeric value, so convert it if it should be included in the comparison.

Can I find the highest number without a formula?

Yes. Sort the full data table from largest to smallest with Data > Sort Largest to Smallest, or highlight the top value using Conditional Formatting. Sorting changes the order of the data; MAX leaves it in place.

Can Excel find the highest number that meets a condition?

Yes. In Microsoft 365 or Excel 2019 and later, use MAXIFS with a maximum range, a criteria range, and the condition. For example, it can find the largest sales amount for a chosen region without sorting the whole table.

What if two or more cells share the highest number?

MAX returns the shared highest number either way. To see every cell containing it, sort or apply a conditional-formatting rule; the value itself does not indicate how many times it appears.

Does the formula work in Excel for Mac or Excel for the web?

Yes. MAX and the basic formula syntax work in Microsoft 365 for Mac and Excel for the web as well as Windows. Ribbon locations can vary slightly by platform, but you can enter the formula directly into a cell in each version.

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.