To find the highest number in an Excel range, enter =MAX(B2:B20) in an empty cell, replacing B2:B20 with your range. Excel returns the largest numeric value. You need a workbook with the data you want to check and a blank cell for the answer; the steps below also show how to handle filters, find the associated label, and troubleshoot unexpected results.
What You’ll Need
- An Excel workbook containing the numbers you want to compare. The instructions use Excel for Microsoft 365 on Windows; the MAX function works in current desktop and web versions.
- A blank cell for the result, or a clear place to view a quick result on the status bar.
- If you want to identify the record associated with the maximum, a second column containing labels such as product names, dates, or employee names.
Step-by-Step
- Check the data range. Find the column or cells that contain the values to compare. For example, if sales figures are in B2:B20, the first data value is in B2 and the last is in B20. Leave out a heading such as “Sales” in B1. If your numbers are in separate blocks, note each range before writing the formula. MAX can evaluate multiple ranges, so you do not have to move the data first.
- Select an empty cell for the answer. Click a blank cell outside the data, such as D2, so the result does not overwrite a value. If the range is inside an Excel table, put the result in a cell outside the table or in a separate summary area. You can also place the formula in a cell above or below the data, provided that cell is not part of the range being checked.
- Enter the MAX formula. With numbers in B2:B20, type
=MAX(B2:B20)and press Enter. Excel calculates and displays the highest number in that range. For example, if B2:B6 contains 4200, 6100, 9850, 7200, and 9850, the result is 9850. If you update a value in the range, Excel recalculates the answer automatically when calculation is set to Automatic. - Check separated ranges if needed. You can include more than one range in the same formula. With figures in B2:B10 and D2:D10, enter
=MAX(B2:B10,D2:D10)in an empty cell. If the largest value in the first range is 820 and the largest in the second is 960, the result is 960. Use commas between the arguments, and make sure every range points to the intended cells. - Find the label beside the maximum. MAX returns a number, not the product or person associated with it. If names are in A2:A6 and values are in B2:B6, enter
=XLOOKUP(MAX(B2:B6),B2:B6,A2:A6)in a blank cell. For the example values 4200, 6100, 9850, 7200, and 9850, with Cobalt beside the first 9850, the formula returns Cobalt. If the maximum occurs more than once, XLOOKUP returns the label from the first matching row. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; in older versions, you can locate the matching number with Find or sort a copy of the data from largest to smallest. - Calculate the maximum for a category. To get the highest value only for records that meet a condition, use MAXIFS. With amounts in B2:B6 and regions in C2:C6, enter
=MAXIFS(B2:B6,C2:C6,"East"). If the East amounts are 4200, 9850, and 7200, the result is 9850. MAXIFS is available in Microsoft 365 and Excel 2019 or later. Check that the criteria text matches the entries in the region column; for a value stored in another cell, use that cell as the criteria argument instead of typing the text. - Choose whether filtered-out rows count. MAX includes values in rows hidden by a filter. If you want the maximum among visible rows only, use SUBTOTAL instead. With amounts in B2:B20, enter
=SUBTOTAL(104,B2:B20)in a blank cell. The 104 option calculates the maximum while excluding filtered-out and manually hidden rows. For example, if the currently visible values are 4200, 9850, and 7200, the result is 9850. If hidden rows should count, use MAX instead. Filter the table using Data > Filter, then apply the formula to the relevant value range.
For a quick check without adding a formula, select the numeric cells and look at Excel’s status bar at the bottom of the window. Right-click the status bar and make sure Maximum is selected. The highest value appears there while the cells remain selected. This is useful for a one-time check, but a formula is better when you want a result that stays in the worksheet or can be used in other calculations.
To make the maximum stand out in the data, select the rows or cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With values in B2:B6 and data in rows 2 through 6, use =$B2=MAX($B$2:$B$6) as the rule formula. Choose a format and confirm. The rule highlights each row tied for the highest value; in the example with two values of 9850, both matching rows are highlighted. Keep the row number in $B2 relative so Excel checks each row, and lock the comparison range with dollar signs.
Common Mistakes
- Including a heading or the wrong cells. A text heading is ignored by MAX, but an incorrect range can still produce a plausible yet wrong answer. Select the range carefully and check its first and last cell references in the formula bar.
- Expecting MAX to identify a record. The answer is the largest number only. Use a lookup formula or inspect the matching row if you also need a name, date, or product.
- Forgetting that filters do not affect MAX. MAX evaluates all numbers in its range, including values in filtered-out rows. Use SUBTOTAL with function number 104 when only visible rows should count.
- Assuming a tie has one unique winner. MAX returns one numeric result even when several cells share it. A lookup usually returns the first matching label; use highlighting or filter/sort the data to see all matching records.
- Confusing numbers with text. A value that looks like a number may be stored as text and ignored by MAX. Common causes include imported data, leading apostrophes, or spaces before or after the digits.
Tips
- Use a table for expanding data. If you convert a list to an Excel table with Insert > Table, use a structured reference such as
=MAX(Sales[Amount])when the table is named Sales and its value column is Amount. The formula can include new table rows as the table expands. - Check the value type before interpreting the result. Excel stores dates as serial numbers, so MAX on a date column finds the latest date, not the largest day number as displayed. Format the result as a date if it appears as a serial number.
- Use a stable range when copying formulas. If you copy a formula and want it always to check the same cells, lock the range with dollar signs, for example
=MAX($B$2:$B$20). In Excel, select a reference in the formula and press F4 on Windows to cycle through reference styles; on Mac, use Command+T in many Excel versions, or enter the dollar signs directly. - Keep the result easy to audit. Add a label such as “Highest sales” beside the answer and note whether the calculation includes filtered-out rows. This helps prevent someone from mistaking a visible-only maximum for the maximum of the full dataset.
- Use the same formula on Mac or the web. MAX and the examples above work in Excel for Mac and Excel for the web. Ribbon layouts and keyboard shortcuts can differ slightly by platform, but you can enter the formula directly in a cell on all three.
Troubleshooting
- The result is 0, but the cells look populated. MAX returns 0 when it finds no numeric values in the supplied range. Check whether the entries are stored as text: select a sample cell and look for a warning icon or check its alignment and number format. Changing the format alone may not convert text to numbers. You can often select the affected cells and choose the warning menu’s Convert to Number option. If that option is unavailable, use Data > Text to Columns and complete the steps to convert a clean column, or clean imported spaces and re-import the data.
- The answer is smaller than the largest visible value. Confirm the value is actually inside the referenced range and is numeric. If it is visible but stored as text, MAX ignores it. Also check for a leading apostrophe, an unexpected decimal or thousands separator, or a formula that returns text rather than a number.
- The answer includes a row hidden by a filter. That is expected with MAX. Replace it with SUBTOTAL using function number 104 if filtered-out and manually hidden rows should be excluded. If the result still seems wrong, confirm that the filtered range and the range in the formula refer to the same value column and rows.
- The formula displays instead of calculating. The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, edit the formula (press F2, then Enter on Windows; on Mac, use the equivalent edit-cell shortcut or click the formula bar), and check that the formula begins with an equals sign. Toggle Show Formulas from Formulas > Show Formulas if formulas are displayed throughout the sheet.
- You see a formula error. Check that parentheses and range references are complete. In regional settings that use semicolons as list separators, Excel may require semicolons between function arguments instead of commas. Excel’s formula suggestions will reflect the separator expected by your installation; do not mix separators within one formula.
Frequently Asked Questions
What is the simplest way to find the highest value in Excel?
Enter =MAX(range) in an empty cell, replacing range with the cells to check, such as B2:B20. The formula returns the largest numeric value in that range.
Does MAX include zeros and negative numbers?
Yes. Zero and negative numbers are numeric values, so MAX includes them. If every number is negative, it returns the value closest to zero. For example, the maximum of -12, -4, and -9 is -4.
Does MAX ignore blank cells and text?
Yes. MAX ignores blank cells and text in a referenced range, but it includes numbers, including zero. A number stored as text is not treated as a numeric value until it is converted.
How do I return the name or item with the highest value?
Use XLOOKUP with MAX, matching the value range and returning the parallel label range. If several rows tie, XLOOKUP returns the first match by default; use conditional formatting or sort the data to reveal all tied records.
How can I find the highest value in a filtered list?
Use =SUBTOTAL(104,range) for a maximum that ignores filtered-out and manually hidden rows. Use MAX if hidden values should still count.
Can I find the highest value across several columns?
Yes. Give MAX each range separated by commas, such as B2:B20 and D2:D20. Check that every range includes only the records you intend to compare.
Related
- Enable Scroll Lock in Excel: Keyboard and On-Screen Methods
- How to Enable the Share Option in Excel for Collaboration
- Link Quantities in Excel: A Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ISERROR Function in Excel: What It Does and How to Use ItIn Excel, ISERROR checks whether a value or formula result is any error value, and returns TRUE if…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
- Excel Filter With Multiple Criteria: A Practical GuideTo filter a worksheet by more than one condition in Excel for Microsoft 365 on Windows, select your…
- SUMPRODUCT in Excel: Conditional CalculationsYes. In Excel, SUMPRODUCT can add values that meet one or more conditions by turning each condition into…
- Filter Data Between Two Dates in Excel: A Practical GuideTo filter data between two dates in Excel for Microsoft 365 on Windows, select a cell in your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.