Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Excel MAX Function (6 Useful Examples)

There are several statistical functions in Excel to help you perform your desired tasks easily and swiftly. Today we are going to show you how to use a function called: MAX. For this session, we are using Excel 2019, feel free to use yours (at least 2003). Today in this article, I am sharing with you how to use MAX function in Excel.

The following image shows an overview of the article which represents the use of MAX function in Excel.

Overview of using MAX function in Excel


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Introduction to MAX Function in Excel

The MAX Function is categorized under Excel STATISTICAL functions. This function returns the largest value in a given list of arguments.

⦿ Syntax:

Syntax - Excel MAX function

Returns the largest value in a set of values. Ignores logical values and text.

MAX (number1, [number2], ...)

⦿ Arguments:

Argument Required/Optional Value
number1 Required reference to a numeric value, or range that contains numeric values.
[number2] Optional Additional numbers, cell references, or ranges of numeric values.

You can provide up to a maximum of 255 number arguments.

Read More: The Different Ways of Counting in Excel


6 Common Uses of MAX Function in Excel

1. Finding the Maximum Value in a Row

Using the MAX function, you can find the maximum or highest value of a row.

We have brought a data table of students with their respective scores in three quizzes to show examples.

Sample Dataset of MAX Function

Now we are set to find the highest value from each row. Our formula for the first row will be

=MAX(C5:E5)

C5 to E5 is the Cell Reference of the first row.

Formula of MAX Function for rows

Our formula has provided the highest score for Angela on the quizzes.

Output of MAX Function for Rows

A similar formula (changes in the Cell Reference) will provide the maximum value for the rest of the rows.

Autofill to fill column with MAX Function


2. Finding the Maximum Value in a Column

Similar to the rows the MAX function can be used for the columns to find the largest value from the columns.

For example, we can find the highest score for each quiz from our previously used dataset.

Sample Dataset of MAX Function for Columns

Scores for quizzes are stored in three columns. Our formula will be-

=MAX(C5:C10)

Formula of MAX for Columns

Within the function, we have inserted the Cell Reference for the first column.

We have found the highest score for Quiz 1. Similarly, we can find the largest for the rest of the columns.

Output of MAX Function for Columns


3. Get the Highest Value for Entire Data

If you need to find the maximum from the entire table or dataset, what will you do? No worries, simple answer, need to use the MAX function for the entire dataset.

To show you examples, we have introduced a dataset of several items with prices in different shops.

Sample Dataset of MAX Function for Entire Data

We are aiming to find the largest value from the dataset and our formula will be

=MAX(C5:E10)

Formula of MAX for Entire Data

We have found the maximum value from our table.

Output using MAX Function for Entire Data


4. Using MAX Function to Get Highest Value in Non-Adjacent Columns

You can find the maximum from the non-adjacent columns using the MAX function.

We are using the previous dataset for this example.

Sample Dataset of Non-adjacent Columns

Let we want to find the largest from Shop1 and Shop3.

The formula will be-

=MAX(C5:C10,E5:E10)

Formula of MAX for Non-Adjacent Columns

Here we have set the column references separated by comma (,).

The formula provided the maximum value for the two non-adjacent columns.

Output for Non-Adjacent Columns


5. Utilizing Max Function to Find Recent Date

The MAX function can be used for finding the most recent date from the dataset.

Here we have a dataset of a few items from a couple of shops and delivery date.

Sample Dataset to Determine Recent Date using MAX Function

Write the MAX formula for the date column.

=MAX(D5:D10)

Formula of MAX to Determine Recent Date

Excel stores dates as sequential serial numbers. By default 1 January 1900 is the 1st date, and its serial number is 1. The functions convert dates to their respective serial number before doing the operations.

The MAX function converts the dates into their respective serial numbers and finds the most recent date. Thus the formula returned the most recent date.

Output using MAX to Determine Recent Date


6. Performing MAX Function with Criteria

You can find the maximum value by comparing any criteria.

For example purposes, we have brought a dataset of several sports items and their quantity.

Sample Dataset for using MAX function with Criteria

We can set our desired criteria, for example, here we have set criteria of Cricket Bats.  

Sample Dataset for using MAX function with Criteria

We need to set the condition using the IF function. To know about the function, visit this IF article.

Our formula will be-

=MAX(IF(C5:C13=G5,D5:D13))

Combination of MAX and IF functions to provide multiple criteria

The IF function checks whether the criteria have been matched or not, if matched then it triggers the MAX operation.

Output of MAX Function with Criteria

Here we have found the highest quantity of Cricket Balls. If we change the criteria item the result will be changed.

Output Changes if the Criteria is changes

Note that, it is an array formula, you need to press CTRL+ SHIFT + ENTER to execute the formula.


Similar Readings


Quick Notes

  • You can set the Cell References separated by comma (,).

Setting the Cell Reference with Comma (,)

We have inserted 3 cells in the function and these are separated by a comma. And this worked fine.

Output after applying MAX function

  • Alongside the Cell References, you can insert the number in the function.

Use Cell Reference and Numbers inside the Formula of MAX

You will get the highest value among the inserted numbers.

Output of MAX Function with Cell Reference and Numbers

  • Numbers can be inserted along with the ranges also.

Use of Cell Ranges inside MAX Formula

All you need to use comma (,) to separate range and number.

Output after putting Cell Range and Number inside the MAX Function

  • Not only the cell references you can directly input the numbers into the function.

Putting Numbers deirectly inside the formula

You will get the largest value among them.

Output with Largest value using the MAX function

  • Sometimes your provided cells may contain text or Boolean values.

Applying MAX formula with Cell Range

The MAX function will ignore anything but the numbers and return the largest value among them.

Output with Largest Numeric Values while ignoring Texts


Conclusion

That’s all for today. We have tried showing how you can use the Excel MAX function. You can use the function to fetch the largest value from ranges or numbers. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your MAX function-related scenarios where you have stuck, we are ready to help.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo