How to use Excel MAX Function in Excel with examples?

The Excel MAX Function is a fundamental tool for anyone looking to extract maximum values from their datasets quickly and efficiently. Whether you’re assessing financial transactions, measuring performance metrics, or analyzing environmental data, the MAX function simplifies the process of identifying the highest values. By mastering this function, you can enhance your data analysis, highlight significant achievements, and make informed decisions based on peak figures. Incorporate the Excel MAX Function into your analytical repertoire to elevate the precision and depth of your insights.

This Tutorial Covers:

Excel MAX function
How to create an Excel MAX formula
How to utilize the MAX function in Excel – formula examples
- Finding the maximum value in a group
- Find the greatest value in non-adjacent cells or ranges
- How to obtain the max (recent) date in Excel
MAX function in Excel with conditions
- Excel MAX IF formula
- Non-array MAX IF formula
- MAXIFS function
Get max value ignoring zeros
- MAX IF
- MAXIFS
Find highest value ignoring errors
How to find the absolute maximum value in Excel
- Return the highest possible absolute value while maintaining the sign
How to highlight max value in Excel
- Highlight highest number in a range
- Highlight max value in each row
Failure of the Excel MAX function
5 things to know about MAX function

1. Excel MAX function

In Excel, the MAX function in Excel returns the greatest value found in a specified set of data.

The following is the syntax:

=MAX(number1, [number2], …)

where a reference to a cell or range containing numbers, an array, a named range, or a numeric value can all be used to represent a number.

The first parameter is necessary, but the rest are optional.

All versions of Excel for Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and earlier support the MAX function.

2. How to create an Excel MAX formula?

In reality, it happens very infrequently for numbers to be “hard coded.” You will primarily work with ranges and cells.

The following is the quickest approach to create a Max formula that locates the highest value inside a range:

Step 1: Type =MAX( in a cell.

Step 2: Select a set of numbers with the mouse.

Step 3: Put a closing parenthesis in place. To complete your formula, press Enter. The result looks like below:

You can ask Excel to automatically create a Max formula for you if your numbers are in a row or column that is not broken up (as in this example). This is how:

Step 1: Choose the cells that contain your numbers.

Step 2: Select “Max” from the drop-down list under “AutoSum” icon in the “Editing” group under the “Home” tab. By doing this, a ready-to-use formula will be inserted in a cell beneath the chosen range.

After that, it will show the result immediately.

3. How to utilize the MAX function in Excel – formula examples?

The Excel MAX function is used in a few of the examples below. You should try each formula to determine which one is appropriate for your data type because there are frequently multiple solutions available for the same task.

Finding the maximum value in a group:

Give the group of integers you want to extract the greatest number from to the MAX Excel function as a range reference. A range can have any number of rows and columns. Use this straightforward formula, for instance, to determine the maximum value in the range B3:F5.

=MAX(B3:F5)

Find the greatest value in non-adjacent cells or ranges:

You must provide a reference to each distinct cell and/or range when creating a MAX formula for non-contiguous cells and ranges.

You’ll be able to accomplish that swiftly and expertly by taking the actions below:

Step 1: Enter the Max formula into a cell.

Step 2: Hold down the Ctrl key when selecting the cells and ranges on the sheet after typing the beginning parenthesis. Release Ctrl after choosing the final item, then type the closing parentheses.

Step 3: Enter the key. Excel Function MAX will automatically apply the proper syntax once you press Enter, and you will see a formula that looks somewhat like this:

=MAX(C6:G6,C11:G11)

The formula produces the highest sub-total value from rows 6 and 11, as seen in the screenshot below:

How to obtain the max (recent) date in Excel?

Dates are treated by the MAX function Excel without issue because they are nothing more than serial numbers in the internal Excel system.

For instance, create a standard Max formula that you would apply for integers to get the most recent delivery date in C2:C6:

=MAX(C2:C6)

4. MAX function in Excel with conditions

There are various formulas you can use when you want to get the maximum value based on conditions. We will test each formula on the same set of data to ensure that they all provide the same result.

The goal is to determine which item input in E2 had the highest sale out of the items listed in B2 through B15 and the sales data in C2 through C15 (please see the screenshot at the end of the next section).

Excel MAX IF formula:

Use the IF function to test the condition and then feed the resulting array to the MAX function to create a formula that is compatible with Excel 2000 through Excel 2019:

=MAX(IF(B2:B15=E2, C2:C15))

To input, the formula as an array formula, press Ctrl + Shift + Enter at the same time. If everything is done correctly, Excel will surround the formula in “curly brackets,” which is a visual cue that the calculation is an array.

Non-array MAX IF formula:

If you prefer not to use array formulae in your worksheets, pair MAX with the natively supporting SUMPRODUCT function:

=SUMPRODUCT(MAX((B2:B15=E2)*(C2:C15)))

MAXIFS function:

There is a unique function called MAXIFS in Excel 2019 and Excel for Office 365 that is made to discover the greatest value with up to 126 criteria.

The formula is straightforward because there is only one condition in our case:

=MAXIFS(C2:C15, B2:B15, E2)

5. Get max value ignoring zeros

In actuality, this is a variant of the conditional MAX explained in the earlier example. Use the “not equal to” logical operator and the expression “>0” in either the MAXIFS criterion or the MAX IF logical test to exclude zeros.

As you can see, testing this condition only makes sense when the numbers are negative. This check is unnecessary for positive values because they are all greater than zero.

Locate the lowest discount in the range C2:C15 to test it out. The lowest discount actually has the highest value because all discounted are indicated by negative numbers.

MAX IF:

Therefore, for the array formula to be completed correctly, press Ctrl + Shift + Enter:

=MAX(IF(C2:C15<>0, C2:C15))

MAXIFS:

It’s a standard formula, so hitting Enter as normal will do.

=MAXIFS(C2:C15,C2:C15,”<>0″)

6. Find highest value ignoring errors

It’s likely that some of your formulas will produce errors while working with a lot of data generated by different formulas, which will cause a MAX formula to also return an error.

You can use MAX IF together with ISERROR as a workaround. Given that you are looking at the area between A1 and B6, the formula looks like this:

=MAX(IF(ISERROR(A1:B6), “”, A1:B6))

Use the IFERROR function in place of the IF ISERROR combination to make the formula simpler. Additionally, this will help the reasoning to be a little bit clearer. If there is an error in the range A1:B6, replace it with an empty string (“), and then get the highest number in the range:

=MAX(IFERROR(A1:B6, “”))

The one kink in the formula is that it only functions as an array formula, so you have to remember to hit Ctrl + Shift + Enter.

If your data set includes at least one positive number or zero value, the MAXIFS function in Excel 2019 and Excel for Office 356 can be a help:

=MAXIFS(A1:B6,A1:B6,”>=0″)

The formula won’t function for a data collection that only contains negative values since it looks for the highest value with the criterion “greater than or equal to 0.”

All of these restrictions are undesirable, and it is clear that a better solution is required. The following situations are ideal for the AGGREGATE function, which has a wide range of operations and ignores incorrect values:

=AGGREGATE(4, 6, A1:B6)

Your goal range is between A1 and B6, and the first argument’s value of 4 denotes the MAX function. The second argument’s value of 6 denotes the “ignore errors” option.

Under ideal conditions, each of the four formulas will provide the same output:

7. How to find the absolute maximum value in Excel?

Sometimes you might want to determine the largest absolute value, independent of the sign, when working with a range of positive and negative numbers.

The first thing that comes to mind is to use the ABS function to obtain the absolute values of each integer in the range and then pass those values onto MAX:

{=MAX(ABS(range))}

Remember to check this formula using the shortcut Ctrl + Shift + Enter since it is an array formula. Another restriction is that it only functions with numerical data and generates an error when dealing with non-numeric data.

Having problems with this formula? Let’s construct something more practical then.

What happens if we identify the lowest value, change its sign, or ignore it, and then evaluate it with all other numbers? Yes, that will function normally as a formula. Additionally, it easily manages text input and mistakes.

The following formulas apply to the source numbers in A2 through B7.

Ctrl + Shift + Enter completes the following array formula:

=MAX(ABS(A2:B7))

Regular formula (enter to finish):

=MAX(MAX(A2:B7), -MIN(A2:B7))

=MAX(MAX(A2:B7), ABS(MIN(A2:B7)))

Results are displayed in the screenshot below:

Return the highest possible absolute value while maintaining the sign:

The biggest absolute value may be needed in some circumstances; however, you may only need to return the number with its original sign rather than the absolute value.

Here is the formula to use, assuming the numbers are in cells A2 through B7:

=IF(ABS(MAX(A2:B7))>ABS(MIN(A2:B7)), MAX(A2:B7), MIN(A2:B7))

Despite appearing complex at first, the rationale is actually fairly simple. The first step is to locate and compare the two largest and smallest numbers in the range. The maximum number is returned, or – (minus) the minimum number, if the absolute max value is higher than the absolute min value. The formula retains the sign information because it delivers the original value rather than the absolute value:

8. How to highlight max value in Excel?

The quickest technique to find the greatest number in the original data set is to use Excel conditional formatting to highlight it. You can follow along with two distinct scenarios by using the examples below.

Highlight highest number in a range:

Top-ranked data can be formatted according to a predefined criteria in Microsoft Excel, which is ideal for our purposes.

To use it, follow these instructions:

Step 1: Choose a range of numbers (C2:G9 in our case).

Step 2: Click Conditional formatting > New Rule in the Styles category on the Home tab of the program.

Step 3: Choose “Format only top or bottom ranked values” in the New Formatting Rule dialog box. Select Top from the drop-down list in the lower pane and put 1 into the box next to it (meaning you want to highlight just one cell containing the largest value). Select the preferred format by clicking the Format button. To close both windows, click OK twice.

Done! The selected range’s highest value is automatically highlighted. Excel will highlight all of the duplicate maximum values if there are any:

Highlight max value in each row:

You will need to set your own rule based on a MAX formula in order to make the highest value from each row stand out because there is no built-in method for doing so.

This is how:

Step 1: Choose every row you want to highlight for maximum value.

Step 2: Click Conditional formatting > New Rule in the Styles category on the “Home” tab.

Step 3: Choose “Use a formula to determine which cells to format” option. Enter the following formula in the box labeled Format values where this formula is true:

=C2=MAX($C2:$G2)

Where $C2:$G2 is the first row range and C2 is the leftmost cell. Make sure to use the $ symbol to lock the column coordinates in the range for the rule to function. Select the desired format by clicking the Format button. Press OK twice.

Done! The selected range’s highest value is automatically highlighted in each row. Excel will highlight all of the duplicate maximum values if there are any:

Tip: The highest value in each column can be highlighted in a similar way. With the exception of writing a formula for the first column range and locking the row coordinates, the processes are identical: =C2=MAX (C$2:C$7)

9. Failure of the Excel MAX function

One of the simplest Excel functions to use is MAX. If, despite all odds, it does not function properly, one of the following problems is most likely to be the cause.

MAX formula returns zero:

Even though there are greater values in the given range, a typical MAX formula might yield 0, which suggests that the higher numbers are text-formatted. This is especially true when using the MAX function on data that was generated by another calculation. Using the ISNUMBER function, you may verify this, for instance:

=ISNUMBER(A1)

The value in A1 is not a numeric number if the above calculation yields FALSE. In other words, rather than using a MAX formula, troubleshoot the original data.

The #N/A, #VALUE, or other error returned by the MAX formula:

Please carefully review the cells that are referenced. A MAX formula will produce the same error if any of the referred cells have an error. See how to get the maximum value while ignoring all mistakes to get around this.

10. 5 things to know about the MAX function:

Please keep in mind these basic principles so you can use Max formulae in your worksheets with success:

A MAX formula in the most recent versions of Excel can take up to 255 parameters.
The MAX function returns 0 if the parameters don’t include any numbers.
An error is returned if one or more error values are present in the inputs.
Cells that are empty are not taken into consideration.

The list of parameters contains both logical values and text representations of numbers that are processed (TRUE evaluates as 1, FALSE evaluates as 0). References disregard logical and text values.

Application of Excel MAX Function

Identifying Highest Values: Use the MAX function to quickly find the highest value in a range of numbers, ideal for sales data, test scores, or financial metrics analysis.
Performance Evaluation: Evaluate employee or departmental performance by identifying the maximum output or sales figure, aiding in recognizing top performers.
Inventory Management: Determine the maximum stock level in inventory data to understand stock capacity and optimize inventory ordering processes.
Financial Analysis: Apply the MAX function to financial datasets to pinpoint the highest transaction, investment return, or expense in a given period.
Comparative Market Analysis: Use MAX to compare product prices or market data across different sources, identifying the highest value for benchmarking or competitive analysis.
Temperature or Measurement Extremes: Find the maximum temperature, pressure, or other environmental measurements recorded in a dataset for climatological or scientific research purposes.

For ready-to-use Dashboard Templates: