MAX function in Excel: formula examples to find and highlight highest value

5 things to know about MAX function

To successfully use Max formulas your worksheets, please remember these simple facts:

1. In the current versions of Excel, a MAX formula can accept up to 255 arguments.
2. If the arguments do not contain a single number, the MAX function returns zero.
3. If the arguments contain one or more error values, an error is returned.
4. Empty cells are ignored.
5. Logical values and text representations of numbers supplied directly in the list of arguments are processed (TRUE evaluates as 1, FALSE evaluates as 0). In references, logical and text values are ignored.

How to find max value in a group

To extract the largest number in a group of numbers, supply that group to the MAX function as a range reference. A range can contain as many rows and columns as you desire. For example, to get the highest value in the range C2:E7, use this simple formula:

=MAX(C2:E7)

Find highest value in non-adjacent cells or ranges

To make a MAX formula for non-contiguous cells and ranges, you need to include a reference to each individual cell and/or range. The following steps will help you to do that quickly and flawlessly:

1. Start typing a Max formula in a cell.
2. After you've typed the opening parenthesis, hold down the Ctrl key and select the cells and ranges in the sheet.
3. After selecting the last item, release Ctrl and type the closing parenthesis.
4. Press Enter.

Excel will use an appropriate syntax automatically, and you will get a formula similar to this:

=MAX(C5:E5, C9:E9)

As shown in the screenshot below, the formula returns the maximum sub-total value from rows 5 and 9:

How to get max (latest) date in Excel

In the internal Excel system, dates are nothing else but serial numbers, so the MAX function handles them without a hitch.

For instance, to find the latest delivery date in C2:C7, make a usual Max formula that you'd use for numbers:

=MAX(C2:C7)

MAX function in Excel with conditions

When you wish to get the maximum value based on conditions, there are several formulas for you to choose from. To make sure that all the formulas return the identical result, we will test them on the same set of data.

The task: With the items listed in B2:B15 and sales figures in C2:C15, we aim to find the highest sale for a specific item input in F1 (please see the screenshot at the end of this section).

Get max value ignoring zeros

This is, in fact, a variation of conditional MAX discussed in the previous example. To exclude zeros, use the "not equal to" logical operator and put the expression "<>0" in either the criteria of MAXIFS or the logical test of MAX IF.

As you understand, testing this condition only makes sense in case of negative numbers. With positive numbers, this check is superfluous because any positive number is greater than zero.

To give it a try, let's find the lowest discount in the range C2:C7. As all the discounts are represented by negative numbers, the smallest discount is actually the largest value.

Find highest value ignoring errors

When you work with a large amount of data driven by various formulas, chances are that some of your formulas will result in errors, which will cause a MAX formula to return an error too.

As a workaround, you can use MAX IF together with ISERROR. Given that you are searching in the range A1:B5, the formula takes this shape:

=MAX(IF(ISERROR(A1:B5)), "", A1:B5))

To simplify the formula, use the IFERROR function instead of the IF ISERROR combination. This will also make the logic a bit more obvious – if there's an error in A1:B5, replace it with an empty string (''), and then get the maximum value in the range:

=MAX(IFERROR(A1:B5, ""))

A fly in the ointment is that you need to remember to press Ctrl + Shift + Enter because this only works as an array formula.

In Excel 2019 and Excel for Office 356, the MAXIFS function can be a solution, provided that your data set contains at least one positive number or zero value:

=MAXIFS(A1:B5,A1:B5,">=0")

Since the formula searches for the highest value with the condition "greater than or equal to 0", it won't work for a data set consisting of solely negative numbers.

All these limitations are not good, and we are evidently in need of a better solution. The AGGREGATE function, which can perform a number of operations and ignore error values, fits perfectly:

=AGGREGATE(4, 6, A1:B5)

The number 4 in the 1st argument indicates the MAX function, the number 6 in the 2nd argument is the "ignore errors" option, and A1:B5 is your target range.

Under perfect circumstances, all three formulas will return the same result:

How to find absolute max value in Excel

When working with a range of positive and negative numbers, sometimes you may wish to find the largest absolute value regardless of the sign.

The first idea that comes to mind is to get the absolutes values of all numbers in the range by using the ABS function and feed those to MAX:

This is an array formula, so don't forget to confirm it with the Ctrl + Shift + Enter shortcut. Another caveat is that it only works with numbers and results in an error in case of non-numeric data.

Not happy with this formula? Then let us build something more viable :)

What if we find the minimum value, reverse or ignore its sign, and then evaluate along with all other numbers? Yep, that will work perfectly as a normal formula. As an extra bonus, it handles text entries and errors just fine:

With the source numbers in A1:B5, the formulas go as follows.

Array formula (completed with Ctrl + Shift + Enter):

=MAX(ABS(A1:B5))

Regular formula (completed with Enter):

=MAX(MAX(A1:B5), -MIN(A1:B5))

or

=MAX(MAX(A1:B5), ABS(MIN(A1:B5)))

The below screenshot shows the results:

Highlight highest number in a range

Microsoft Excel has a predefined rule to format top ranked values, which suits our needs perfectly. Here are the steps to apply it:

1. Select your range of numbers (C2:C7 in our case).
2. On the Home tab, in the Styles group, click Conditional formatting > New Rule.
3. In the New Formatting Rule dialog box, choose Format only top or bottom ranked values.
4. In the lower pane, pick Top from the drop-down list and type 1 in the box next to it (meaning you want to highlight just one cell containing the largest value).
5. Click the Format button and select the desired format.
6. Click OK twice to close both windows.

Done! The highest value in the selected range is automatically highlighted. If there is more than one max value (duplicates), Excel will highlight them all:

Highlight max value in each row

Since there is no built-in rule to make the highest value stand out from each row, you will have to configure your own one based on a MAX formula. Here's how:

1. Select all the rows in which you want to highlight max values (C2:C7 in this example).
2. On the Home tab, in the Styles group, click New Rule > Use a formula to determine which cells to format.
3. In the Format values where this formula is true box, enter this formula:

   =C2=MAX($C2:$E2)

   Where C2 is the leftmost cell and $C2:$E2 is the first row range. For the rule to work, be sure to lock the column coordinates in the range with the $ sign.

4. Click the Format button and choose the format you want.
5. Click OK twice.

For more information, please see How to create a formula-based conditional formatting rule.

MAX formula returns zero

If a normal MAX formula returns 0 even though there are higher numbers in the specified range, chances are those numbers are formatted as text. It's especially the case when you run the MAX function on data driven by other formulas. You can check this by using the ISNUMBER function, for example:

=ISNUMBER(A1)

If the above formula returns FALSE, the value in A1 is not numeric. Meaning, you should troubleshoot the original data, not a MAX formula.

MAX formula returns #N/A, #VALUE or other error

Please check the referenced cells carefully. If any of the referenced cells contains an error, a MAX formula will result in the same error. To bypass this, see how to get the max value ignoring all errors.