Use Excel's MAX Function Shortcut to Find the Largest Values

The MAX function always finds the largest or maximum number in a list of values, but, depending upon the data and the way that data is formatted, it can also be used to find:

• The slowest time.
• The longest distance.
• The fastest speed.
• The latest date.
• The highest temperature.
• The greatest amount of money.

And while it is often easy to pick out the largest value in a small sample of integers, the task becomes much more difficult for large amounts of data or if that data happens to be:

• Negative numbers.
• Times measured in hundredths of a second.
• Currency exchange rates calculated to the ten-thousandths of a cent.
• Numbers formatted as fractions.

Examples of such numbers are shown in the image above, and while the MAX function itself doesn't change, its versatility in dealing with numbers in a variety of formats is apparent and is one reason why the function is so useful.

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the MAX function is:

=MAX( Number1, Number2, ... Number255 )

• Number1 is required
• Number2 (up to Number255) is optional

The arguments, in parentheses, can be:

• Numbers
• Named ranges
• Arrays
• Cell references to the location of the data in a worksheet
• Boolean values typed directly into the list of arguments.

Notes:

If the arguments do not contain numbers, the function will return a value of zero.

If an array, a named range, or a cell reference used in an argument contains empty cells, Boolean values or text data, those cells are ignored by the function as shown in the example in row 7 in the image above.

In row 7, the number 10 in cell C7 is formatted as text (note the green triangle in the top left corner of the cell indicating that the number is stored as text).

As a result, it, along with the Boolean value (TRUE) in cell A7 and the empty cell B7, are ignored by the function.

As a result, the function in cell E7 returns zero for an answer, since the range A7 to C7 doesn't contain any numbers.

Here's how to enter the MAX function into cell E2 in the image example near the top of this page. As shown, a range of cell references will be included as the number argument for the function.

One advantage of using cell references or a named range as opposed to directly entering the data is that if the data in the range changes, the results of the function will automatically update without having to edit the formula itself.

Options for entering the formula include:

• Typing the formula containing the function =Max(A2:C2) directly into cell E2 and pressing Enter on the keyboard.
• Entering the arguments using the MAX function's dialog box.
• Using the MAX function shortcut located on the Home tab of the ribbon.

This shortcut to using Excel's MAX function is one of several popular Excel functions that have shortcuts grouped together under the AutoSum icon on the Home tab of the ribbon.

To use this shortcut to enter the MAX function:

1. Click cell E2 to make it the active cell.
2. Click the Home tab of the ribbon if necessary.
3. At the far right end of the ribbon, click on the down arrow beside the Σ AutoSum button to open the drop-down list of functions.
4. Click MAX in the list to enter the MAX function into cell E2.
5. Highlight cells A2 to C2 in the worksheet to enter this range as the function's argument.
6. Press the Enter key on the keyboard to complete the function.

The answer -6,587,447 appears in cell E2 since it is the largest negative number in that row.

If you click cell E2 the complete function =MAX(A2:C2) appears in the formula bar above the worksheet.