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

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.

Excel MAX function

The MAX function in Excel returns the highest value in a set of data that you specify.

The syntax is as follows:

MAX(number1, [number2], …)

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.

How to make a MAX formula in Excel

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:

  1. In a cell, type =MAX(
  2. Select a range of numbers using the mouse.
  3. Type the closing parenthesis.
  4. Press the Enter key to complete your formula.

For example, to work out the largest value in the range A1:A6, the formula would go as follows:

=MAX(A1:A6)
Building a MAX formula in Excel

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:

  1. Select the cells with your numbers.
  2. On the Home tab, in the Formats group, click AutoSum and pick Max from the drop-down list. (Or click AutoSum > Max on the Formulas tab in the Function Library group.)

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:
Use AutoSum to insert a MAX formula automatically.

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 use MAX function in Excel – formula examples

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.

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)
Finding the max value in a group

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:
MAX formula for non-adjacent ranges

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)
Find the max date in Excel.

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).

Excel MAX IF formula

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.

Non-array MAX IF formula

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.

MAXIFS function

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:
Max function in Excel with conditions

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.

MAX IF

Be sure to press Ctrl + Shift + Enter to correctly complete this array formula:

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

MAXIFS

It's a regular formula, and a usual Enter keystroke will suffice.

=MAXIFS(C2:C7,C2:C7,"<>0")
Find the maximum value ignoring zeros

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:
Find the highest value in Excel ignoring errors

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:

{=MAX(ABS(range))}

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:
Find absolute maximum value in Excel

Return the maximum absolute value preserving the sign

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:
Get the max absolute value keeping the sign

How to highlight max value in Excel

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.

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:
Highlighting the highest value in a range

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.

Highlighting the largest value in each row

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.

Excel MAX function not working

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.

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.

That's how to find max value in Excel. I thank you for reading and hope to see you on our blog soon!

Available downloads:

Excel MAX sample workbook

Table of contents

134 comments

  1. I have a spreadsheet to hold a series of quiz results
    Quiz Number of questions Contestant 1 Score Fractional score Max Score
    1 40 25.5 0.6375 18
    2 30 17 0.566666667
    3 20 18 0.9

    I want a formula that calculates the "Max Score", based on the highest "Fractional Score". To calculate "Fractional Score" I divide the actual score by the number of questions.
    I can calculate "Max Score" using
    =INDIRECT("C"&MATCH(MAX($D:$D),($D:$D),0))
    In the sample the "Max Score" is 18 ie the value in Column C where the highest value of "Fractional Score" occurs.
    I want to do this without showing or using the "Fractional Score" column. I have tried such things as:
    =INDIRECT("C"&MATCH(MAX($C:$C/$B:$B),$C:$C/$B:$B,0))
    but it shows #N/A.
    Would you be able to help?

      • Hi,
        Thanks for that! I needed to replace "E1:E3" with "C1:C3" and enter it using SHIFT/CTRL/RTN, but it works perfectly. I now need to figure out how I can use whole column references (eg "C:C" rather than "C1:C3") so that I don't have to edit it every time a new row is added.
        Thanks again.

  2. I have a data table with datewise sales. each date has lot of number of transactions. I want to find a date and value of sales with highest value (Highest value of sales in a day)

  3. I'm trying to calculate my students' grades. They take 4 exams. The highest (max) is weighted a bit more than the other 3. So, I'd like to create a function to identify the max of the 4 #s, multiply it by .2, and then take the other 3 #s and multiply each by .15, and then add them all together. I appreciate your help!

    • Hi!
      If I understood you correctly, determine the maximum value using the MAX function and subtract it from the total grades.

      =MAX(A1:A4)*0.2+(SUM(A1:A4)-MAX(A1:A4))*0.15

  4. Hello Everyone,

    How to automatically find the amount using the cut-off date?
    just so happened no dates for 01/31/2021, instead, the date before that could be taken
    Result must be 700.00

    Cut-off Date: 01/31/2021
    Amount: ?

    Date Amount
    12/31/2020 1,000.00
    12/31/2020 700.00 (this must be the result)
    02/28/2021 900.00
    04/14/2021 800.00
    04/30/2021 1,200.00
    05/14/2021 500.00
    06/30/2021 600.00

    I cannot find the right formula, pls help me do it correctly.
    thank you very much.

  5. I have cell address written in text. Would like to know how to find the max value from the defined addresses.
    Example:
    Cell A1 input text as $C$56
    Cell A2 input text as $C$12356
    Would like to find max($C$56:$C$12356)

    Instead of selecting the cell range manually, the formula should success based on the text input in A1 & A2.

  6. Hello!

    In one of the data's column (let's say it's "A") I have DATE information (formatted like this: 03/14/01) and in the other column (let's say "B") I have PERCENTAGE value that changes based on some other data. Now, what I'd like to obtain is to have a MAXIMUM value of the PERCENTAGE column based on any given MONTH (January, February, March and so on). For example: what is the maximum percentage value for a month of January or February or March, etc..?
    Can someone please help me with a formula here?

    Thank you,
    Julia

  7. Hi,
    I am trying to return the Highest value from the below row considering the condition if A>B>C>D. The formula should return the highest value meeting the condition.

    A Approved
    B Approved with Comments
    C Resubmit
    D Rejected

    A>B>C>D

    D D C C C B B B A A "=FORMULA"

    • Hello!
      If your values are written in separate cells, then you can extract the desired letter using the INDEX + MATCH formula:

      =INDEX(B2:K2,MATCH(MIN( MATCH(B2:K2,{"A","B","C","D","E","F","G","H","I","K","L","M", "N","O","P","Q","R","S","T","U","V","X","Y","Z"},0)), MATCH(B2:K2,{"A","B","C","D","E","F","G","H","I","K","L","M", "N","O","P","Q","R","S","T","U","V","X","Y","Z"},0),0))

      • Hi,
        I got the solution by below formula:-

        =IFERROR(CHAR(MIN(CODE("A"),CODE("B"),CODE("C"),CODE("D")),"")

        Thanks for your help .

  8. RANDUM NUMBER ENTRY STOCK PICK THIS RESULT
    65.500 12.5+12.5+24.5+30+70+65.5 12.5+12.5+24.5+30

  9. I have a spreadsheet that calculates used/monthly accrued vacation time based on hours and minutes.
    Example: Hours/Minutes total for May shows 150:10 (the formula used to arrive at this figure is =SUM(F6-B7+E7). I need to add or edit this formula to tell it to max out and never exceed an accrual of more than 283:30. I tried =MAX(F36-B37+E37,&TIME(“283:30,0,0”), but that was not successful. Do you have any suggestions?

    • Hello!
      With the TIME function, you cannot set the time for more than 24 hours. Convert your time to days with this formula
      =283.5/24
      If I understand your task correctly, use MIN function and try the following formula:

      =MIN(F36-B37+E37,283.5/24)

      • That worked! Thank you.

  10. Can I use the MAX formula on a cell range that uses IF formula's to display a number?

    Ex.
    2 Mild ("2" is displayed using [=IF('Severity Rating'.I3="Yes";"2";"0")])
    3 Moderate ("3" is displayed using [=IF('Severity Rating'.I4="Yes";"3";"0")])
    4 Severe ("4" is displayed using [=IF('Severity Rating'.I5="Yes";"4";"0")])

    I want to create a function to identify the max number (4 in this case) and sum all the severity ratings in each category (the above is just one category). The IF formula identifies if Mild, Moderate, or Severe ratings were indicated by the client, but I don't want to add all of them, as that would be inaccurate. There are 5 separate categories with 20 being the highest score possible. I only want to sum the highest number displayed in each category.

    Hope that makes sense! Thanks for any help you can offer!

    - Catinreno

      • Except apparently that won't work in open office Calc. Any other options?

          • In Excel, what would the formula for this look like? From what I am understanding, the Max_range & Criteria_range1 are identical?? And then what would be the criteria1?

            Here's the list again for reference (It looks like I might have accidentally deleted the first row in my first post):
            1 Absent ("0" or "1" is displayed using [=IF((AND('Severity Rating'.I3="No",'Severity Rating'.I4="No",'Severity Rating'.I15="No")),"1","0")])
            2 Mild ("2" is displayed using [=IF('Severity Rating'.I3="Yes";"2";"0")])
            3 Moderate ("3" is displayed using [=IF('Severity Rating'.I4="Yes";"3";"0")])
            4 Severe ("4" is displayed using [=IF('Severity Rating'.I5="Yes";"4";"0")])

            Thanks again.

  11. Heya! I'm trying to create a football playoff scores tracker/spreadhseet, and I want to answer the following question: 'Out of this column of scores, what is the highest score?' Except, I need it to return the team name (adjacent to the cell containing their score), not just the highest number. I grasp how to use the max function for this, just not sure how to extend it to return a different field.

    Thanks!

      • Can you please be more specific on this. where to use that formula?
        I have a sheet to record performance. So names, in column A, then figures for each day of the week in B to F. I've got the highest values for each row selected in column L, but now i Struggle to sort them in order, so names and the highest values respectively, in highest to lowest order?

  12. Hi, I am trying to find the max value in cells containing letters in the beginning and numbers aftewards. i.e AB22002, CC22021, CB22003. Can I get the max of the numbers only. I am trying to avoid to have to split them in different cells.
    Thanks,
    Tony

  13. Hello! How to find the highest number using the IF condition?

  14. Hello,
    I would like to count the the latest date in each row (spanning columns B through F) that are equal to or less than another date in a separate row (Column A). I though maybe the Max feature might be helpful. Am a bit frustrated. What I am trying to do is retroactively count (based on a specific date in Column A) the number of students that had attained various levels of academic achievement (signified by Levels 3,4,5 etc) by that date is Column A.

    • Hello!
      To conditionally count, use the COUNTIF or COUNTIFS function. You can find examples and detailed instructions in this article. I hope I answered your question. If this is not what you wanted, please describe the problem in more detail.

  15. how to find a maximum items?
    like in your example here I want to get the highest cost fruit if it is grapes,lemon, apple
    I want a function that writes a word (which is items in your example) not a number.

    thank you!

    • Hello!
      To select the product with the maximum price from the list, use the INDEX+MATCH functions

      =INDEX(B3:B8,MATCH(TRUE,$C$3:$C$8=MAX($C$3:$C$8),0))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  16. im trying to find the best way to write a function that will take the max of a range of cells and display the row header associated with that cell... for example take the Max of cells A1:a17 and display the row header of whichever value is produced from the max function. any input?

  17. Is it possible to do an index match lookup between 2 tables and also find the highest value? The index match + max formulas I am finding all seem to be for within a single table.

    For example, if you have a list of cities and hotel prices at different times of the year in one table.
    Then in another table, you have a list of cities, with columns for different values and you want to pull in the highest hotel price from the other table. Is that possible with any kind of lookup?

  18. Hello,
    I have tried my best on my own forever. I am trying to get the largest sum of 5 numbers, however, based on a second column, the corresponding total cannot exceed a number. Example, for the below, I would like to have the left column total the largest of the 5 added up, but cannot exceed $135 based on the column to it's right. I have gotten close, but it will give me a number but don't actually know which 5 it has chosen! Ha. So best case scenario, it will highlight or say A, D, H or whatever are the five that total the most without going over $135 summed on the right column. Thanks for any help and I hope that makes sense.

    A 24.91 $37
    B 19.47 $23
    C 18.26 $38
    So On 17.27 $34
    13.48 $22
    12.37 $18
    11.45 $20
    10.68 $18
    9.49 $17
    9.34 $18
    8.99 $16
    6.25 $10
    6.15 $21
    5.38 $10
    4.28 $10

  19. I have a customer list with three columns representing stage1, stage2 and stage3. some customers have balance only in one stage while others have balance in two stages. I want to find out the stage with maximum balance for each customer. how to find the same.
    Customer STAGE1 STAGE2 STAGE3 Result I want
    ABC 100 400 STAGE3
    DEF 3000 2000 STAGE1
    MNO 5000 50 STAGE2
    KPL 250 STAGE1
    GHI -250 STAGE2

    How can I find the result with a formula for larger amount of data

  20. I have 2 columns of values, i have conditionally formatted each to show top 20%. I want ones that are in the top 20% of each category to be highlighted in another colour. e.g top 20% in column A Green, top 20% in column B Green. In top 20% in A and B Blue.

    Any assistance is appreciated

    Thanks,
    Luke

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)