The main use of the Excel MAX function is to find the largest value in a set. However, it can also be used to search for other values. Learn more about this feature and find a shortcut to use the MAX feature.
These instructions apply to Excel 2019, 2016, 2013, 2010, Excel for Office 365, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.
- Find the largest number, slowest time, longest distance or highest temperature
- Syntax and arguments of the MAX function
- MAX function example
- MAX Function Shortcut
Find the largest number, slowest time, longest distance or highest temperature
The MAX function always finds the largest or maximum number in a list of values, but depending on the data and how the data is formatted, it can also be used to search:
The slowest time
The longest distance.
The fastest speed.
The largest amount of money.
And while it is often easy to pick the largest value from a small sample of integers, the task becomes much more difficult for large amounts of data, or if the data turns out to be:
Time is measured in hundredths of a second.
Exchange rates are calculated to an accuracy of ten-thousandths of a cent.
Numbers are formatted as fractions.
Examples of such numbers are shown in the image above, and while the MAX function itself does not change, its versatility in working with numbers in different formats is apparent and is one of the reasons this function is so useful.
Syntax and arguments of the MAX function
Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.
The syntax for the MAX function is:
=MAX(Number1, Number2, … Number255)
- Number 1 requires
- Number 2 (up to number255) optional
Arguments in parentheses can be:
Cells refer to the location of the data on the sheet
Boolean values are entered directly into the argument list.
If the arguments do not contain numbers, the function returns null.
If the array, named range, or cell reference used in the argument contains empty cells, booleans, or text data, those cells are ignored by the function, as shown in the example on line 7 in the image above.
Row 7 has the number 10 in cell C7 formatted as text (note the green triangle in the top left corner of the cell, which indicates that the number was stored as text).
As a result, along with the boolean value (TRUE) in cell A7 and the empty cell B7, it is ignored by the function.
As a result, the function in cell E7 returns zero for the answer because the range from A7 to C7 contains no numbers.
MAX function example
Here’s how to enter the MAX function in cell E2 in the sample image at the top of this page. As shown, the range of cell references is included as the number argument for the function.
An advantage of using cell references or a named range over direct data entry is that if the data in the range changes, the function’s results are automatically updated without editing the formula itself.
Options for entering formulas include:
Enter a formula containing a function =Max(A2:C2) , directly to cell E2 and click Enter on keyboard.
Enter arguments using the MAX function dialog box.
Using the MAX function shortcut on the Home tab of the ribbon.
MAX Function Shortcut
This shortcut for using the MAX function in Excel is one of many popular Excel functions whose shortcuts are grouped under the AutoSum icon on the Home tab of the Ribbon.
To use this shortcut to access the MAX function:
Click on a cell E2 to make it active.
If necessary, go to the tab House on the tape.
At the far right of the ribbon, click the down arrow next to the button Σ AutoSum to open the drop-down list of functions.
Click MAX in the list to enter the MAX function in cell E2.
Highlight cells A2 to C2 on the worksheet to enter that range as a function argument.
Press key Enter on the keyboard to complete the function.
Answer -6,587,447 appears in cell E2 because it is the largest negative number in that row.
When you click cell E2, the full function =MAX(A2:C2) appears in the formula bar above the worksheet.