by Svetlana Cheusheva, updated on
The tutorial explains the MAX function with many formula examples that show how to find highest value in Excel and highlight largest number in your worksheet.
MAX is one of the most straightforward and easy-to-use Excel functions. However, it does have a couple of tricks knowing which will give you a big advantage. Say, how do you use the MAX function with conditions? Or how would you extract the absolute largest value? This tutorial provides more than one solution for these and other related tasks.
The MAX function in Excel returns the highest value in a set of data that you specify.
The syntax is as follows:
Where number can be represented by a numeric value, array, named range, a reference to a cell or range containing numbers.
Number1 is required, number2 and subsequent arguments are optional.
The MAX function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.
To create a MAX formula in its simplest from, you can type numbers directly in the list of arguments, like this:
=MAX(1, 2, 3)
In practice, it's quite a rare case when numbers are "hardcoded". For the most part, you will deal with ranges and cells.
The fastest way to build a Max formula that finds the highest value in a range is this:
For example, to work out the largest value in the range A1:A6, the formula would go as follows:
=MAX(A1:A6)
If your numbers are in a contiguous row or column (like in this example), you can get Excel to make a Max formula for you automatically. Here's how:
This will insert a ready-to-use formula in a cell below the selected range, so please make sure there is at least one blank cell underneath the list of numbers that you've selected:
To successfully use Max formulas your worksheets, please remember these simple facts:
Below you will find a few typical uses of the Excel MAX function. In many cases, there are a few different solutions for the same task, so I encourage you to test all the formulas to choose the one best suited for your data type.
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)
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:
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:
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)
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).
If you a looking for a formula that works in all versions of Excel 2000 through Excel 2019, use the IF function to test the condition, and then pass the resulting array to the MAX function:
=MAX(IF(B2:B15=F1, C2:C15))
For the formula to work, it must press Ctrl + Shift + Enter simultaneously to enter it as an array formula. If all done correctly, Excel will enclose your formula in {curly braces}, which is a visual indication of an array formula.
It is also possible to evaluate several conditions in a single formula, and the following tutorial shows how: MAX IF with multiple conditions.
If you don't like using array formulas in your worksheets, then combine MAX with the SUMPRODUCT function that processes arrays natively:
=SUMPRODUCT(MAX((B2:B15=F1)*(C2:C15)))
For more information, please see MAX IF without array.
In Excel 2019 and Excel for Office 365, there is a special function named MAXIFS, which is designed to find the highest value with up to 126 criteria.
In our case, there is just one condition, so the formula is as simple as:
=MAXIFS(C2:C15, B2:B15, F1)
For the detailed explanation of the syntax, please see Excel MAXIFS with formula examples.
The below screenshot shows all 3 formulas in action:
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.
Be sure to press Ctrl + Shift + Enter to correctly complete this array formula:
=MAX(IF(C2:C7<>0, C2:C7))
It's a regular formula, and a usual Enter keystroke will suffice.
=MAXIFS(C2:C7,C2:C7,"<>0")
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:
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:
In some situations, you may have a need to find the largest absolute value but return the number with its original sign, not the absolute value.
Assuming the numbers are in cells A1:B5, here's the formula to use:
=IF(ABS(MAX(A1:B5))>ABS(MIN(A1:B5)), MAX(A1:B5), MIN(A1:B5))
Complex at first sight, the logic is quite easy to follow. First, you find the largest and smallest numbers in the range and compare their absolute values. If the absolute max value is greater than the absolute min value, the maximum number is returned, otherwise – the minimum number. Because the formula returns the original and not absolute value, it keeps the sign information:
In situation when you want to identify the largest number in the original data set, the fastest way is to highlight it with Excel conditional formatting. The below examples will walk you through two different scenarios.
Microsoft Excel has a predefined rule to format top ranked values, which suits our needs perfectly. Here are the steps to apply it:
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:
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:
=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.
Tip. In a similar manner, you can highlight the highest value in each column. The steps are exactly the same, except that you write a formula for the first column range and lock the row coordinates: =C2=MAX(C$2:C$7)
For more information, please see How to create a formula-based conditional formatting rule.
MAX is one of the most straightforward Excel functions to use. If against all expectations it does not work right, it's most likely to be one of the following issues.
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.
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.
That's how to find max value in Excel. I thank you for reading and hope to see you on our blog soon!
Table of contents