What is the MAX function in Excel and how do you use it?

By Indeed Editorial Team

Published 4 June 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

The MAX function in Excel is an incredibly simple tool that is easy to implement over several different scenarios. It's also a very versatile function that benefits from understanding how it works to use it effectively. In essence, the MAX function helps find the highest value in a set of data, but it can do a lot more than just that if you know how. In this article, we explain how to use the MAX function in Excel across several scenarios and provide how-to guides to help you.

What is the MAX function in Excel?

The MAX function in Excel is a way to return the highest value from a data set that you're working on. It's a very simple function with a syntax that looks like this: MAX(number1, [number2],[number3],...) The 'number' in the function above can represent any numerical value, named range, cell reference or array. Number1 is always involved, with additional numbers added optionally. The MAX function is available in almost all spreadsheet applications, including Excel dating back to 2007.

Related:15 helpful Microsoft Excel tips to use in the software

How to use the MAX formula in Excel

One of the strongest aspects of the MAX function is its simplicity, which makes it an excellent tool with lots of applications. To use the basic MAX function formula, type the numbers in a list argument, like so: =MAX(1, 2, 3, 4, 5) Datasets aren't coded with just numbers. You're likely to deal with specific ranges and cells instead of simply numbers. To build a MAX function formula that looks to find the highest value in a specific range, follow these steps:

  1. Select a cell and type =MAX(

  2. Select a number range with the cursor.

  3. Input the closing parenthesis to close the brackets.

  4. Hit the enter key to confirm the formula.

Example of the MAX formula in Excel

Using an example, if you were to try and determine the largest value in a spreadsheet for ranges A1:A10 then your formula would look like this: =MAX(A1:A10). If the number ranges are in a contiguous row or column, then Excel can automatically generate the MAX function formula for you. To do this, follow the steps below:

  1. Select the cells that contain your desired numbers.

  2. From the 'home' tab, click on 'formats' then 'autosum' and select 'max' from the available options.

This generates a pre-made formula in a cell underneath your desired range. Make sure there isn't any data in this cell, otherwise, the formula overwrites it.

Related: How to make a calendar in Excel using different methods

Considerations for the MAX function

To ensure that you're using the MAX function optimally, it's important to understand the following considerations about this function:

  • You can use up to 255 arguments when implementing the MAX function formula

  • If the arguments don't contain a numerical value, the function returns as zero.

  • If there are any error values in the arguments, the function returns an error.

  • The function universally ignores all empty cells in the range.

  • The function always processes logical values and text numbers as TRUE for 1 or FALSE for 0.

Related: How to make a flowchart in Excel including flowchart uses

Examples of how to use the MAX function in different scenarios

Below is a breakdown of the most commonly used scenarios where the MAX function is useful. Most of the time, there are a few different approaches that you might choose for the same problem, but the versatility of the MAX function means that it can be helpful across a wide range of tasks.

Finding the maximum value in a group of numbers

You can use the MAX function to determine the largest number from a group of numbers. To do this, input the number group into the MAX function formula like a range reference. This range can include as many rows and columns as necessary. For example, if you're trying to determine the highest value from the range A2:D8, you can use the following formula: =MAX(A2:D8)

Finding the highest value from non-adjacent ranges

If you're working with non-adjacent cells or ranges, it's important to reference individual cells and ranges in the MAX function. To do this, follow the steps below:

  1. Begin typing the MAX function formula in a cell.

  2. After the opening bracket, hold the CTRL key and click on the cells you'd like to include.

  3. Once you've clicked all of the items, release the CTRL key and close the brackets in the formula.

  4. Press enter to confirm.

With this done, Excel automatically generates the proper syntax and provides you with a formula like so: =MAX(A1:D1, A5:D5) For reference, the above formula returns the maximum sub-total value from rows A and D.

Finding the latest date range in Excel

The MAX function can also work with date ranges, allowing you to find the most recent date from a range of different dates. A date range is essentially a serial number when you use it in Excel, which means the MAX function handles these data sets with no issue. For example, if you wanted to find the latest date range from cells A1:A8, you would use the following formula: =MAX(A1:A8)

Related: How to calculate a discount formula in Excel with examples

Performing the MAX function with conditions

You can determine the maximum value based on specific conditions in a few different ways using the MAX function. Below is a breakdown of the different ways that you can do this using this example: Using products found in cells B2:B20 and the sales numbers in C2:C20, the MAX function can output the highest sales for a product in cell D1.

Using the Excel MAX IF formula

The IF function can work alongside the MAX function to determine the maximum value of individual product sales. The formula for this is below: =MAX(IF(B2:B20=F1, C2:C20)) . Press CTRL + SHIFT + ENTER at the same time so that it is input as an array formula. When done, the formula uses { } brackets to denote an array formula.

Using the non-array MAX IF formula

You can choose not to use array formulas if you'd prefer to avoid them by using the non-array MAX IF formula instead. This requires a combination of the MAX function with the SUMPRODUCT function. The formula looks like this: =SUMPRODUCT(MAX((B2:B20=F1)*(C2:C20)))

Using the MAXIFS function

In newer versions of Excel and other spreadsheet applications, you can implement the special MAXIFS function to find the highest value. You can use this function with criteria based on text, numbers, dates and other conditions. The formula for this function is this: =MAXIFS(C2:C20, B2:B20, F1)

Excluding zeros from the max value

You can opt to exclude zeros from the maximum value with a special form of conditional MAX functions. This works by implementing the 'not equal to' operator, which uses the term '<>0' in the formula. This only works if the data set contains negative numbers.

Using positive numbers here is redundant because all positive numbers are greater than zero. To use an example, if you're trying to determine the lowest discount available in the range B2:B9 then look for the largest value as this denotes the smallest discount. The formula looks like this: =MAX(IF(B2:B9<>0, B2:B9))

Finding the absolute maximum value in Excel

If you're dealing with a mixture of positive and negative numbers, you might want to determine the absolute maximum value regardless of whether it's positive or negative. To do this, you can use the ABS function alongside the MAX function which finds the absolute values of all numbers in a given range. The formula for this function looks like this: {=MAX(ABS)range))} As this is an array formula, it's important to press and hold CTRL + SHIFT + ENTER at the same time. This also only works with numbers, so if there is non-numerical data then it presents as an error.

Related: How to use data consolidation in Excel: a complete guide

Troubleshooting if the MAX function isn't working

Although the MAX function is one of the most simple functions available, it is prone to errors and it might not work as intended. There are a few common instances where this occurs, so to help you troubleshoot any problems take a look at the scenarios below:

MAX function returns zero

If your standard MAX function returns 0 when there are greater numbers in the range, the numbers are likely text-based rather than numerical. This is especially true when the MAX function runs through data that other formulas generated. To check for this, use the ISNUMBER function like so: =ISNUMBER(A1)If the return for this formula reads as FALSE, then the value found in cell A1 is non-numerical. This points to the problem being in the data rather than the formula or function.

MAX function returns a different error

If you receive a different error message from your MAX function, such as #N/A, #VALUE or something else, then it's a good idea to inspect all of the referenced cells. One or more of the cells likely contains some form of error, which is why the MAX function responds with an error message. Once you've established the error in the cell, the MAX function is likely to work as intended.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles