How to Use Excel MAX Function (6 Useful Examples)

In this article, we will learn how we can use the MAX function in Excel. We will also discuss various applications of the MAX function in different scenarios. Here, we will use the combination of the MAX function and the IF function to find maximum values based on conditions. We will also use the VBA macro feature of Excel to use the MAX function.

MAX functions can quickly find the maximum value from a set of data or a range. Whether you’re a beginner or an experienced Excel user, understanding and applying the MAX function can significantly enhance your data analysis capabilities in Excel.

Overview of using MAX function in Excel

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

MAX Function in Excel: 6 Common Uses

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

Here, C5: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

  • Drag the Fill Handle to get the maximum values 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:

Formula of MAX for Columns

  • We have found the highest score for Quiz 1. Similarly, we can find the largest for the rest of the columns by dragging the Fill Handle to the right.

Output of MAX Function for Columns

Read More: How to Find Max Value in Range with Excel Formula

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

Formula of MAX for Entire Data

We have found the maximum value in 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 us find the largest from Shop1 and Shop3. The formula will be:

Formula of MAX for Non-Adjacent Columns

Here we have set the column references separated by a 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 dates.

Sample Dataset to Determine Recent Date using MAX Function

  • Write the MAX formula for the date column.

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, 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 for Cricket Bats in cell G5.  

Sample Dataset for using MAX function with Criteria

  • We need to set the condition using the IF function. Our formula will be:

Combination of MAX and IF functions to provide multiple criteria

The IF function checks whether the criteria have been matched or not, and 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 adjusted.

Output Changes if the Criteria is changes

Note: It is an array formula; you need to press CTRL+ SHIFT + ENTER to execute the formula if you are using an older version of Excel. 

Read More: How to Find Maximum Value in Excel with Condition

How to Find Maximum Value with Excel VBA

Enabling Developer Tab in Excel

To use VBA, you need to enable the Developer tab first. If you already enabled the Developer option, you can skip this part.

  • Right-click on any blank area of the Ribbon >> Select the Customize the Ribbon option.

Customizing Ribbon to enable Developer tab

  • In the right side of the Excel Options dialogue box, check the box beside the Developer option >> click OK.

Enabling Developer tab in Excel

As a result, the Developer tab will be added to your ribbon.

Using VBA Code to Find Maximum Value

  • First, go to the Developer tab >> click on the Visual Basic option.

Using Visual basic option

  • Go to the Insert tab in the Microsoft Visual Basic for Applications windows >> select the Module option from the drop-down.

Creating new Module

  • Write the following VBA code in the newly created Module >> click on the Save icon >> use the keyboard shortcut ALT + F11 to return back to the worksheet.
Sub maxvalue()

Dim maxVal As Variant
Set inputRng = Selection
maxVal = WorksheetFunction.Max(inputRng)
Range("C12").Value = maxVal

End Sub

Writing and Saving VBA code

  • Select the range of data for finding the maximum value >> go to the Developer tab >> click on the Macros option.
  • Select the maxvalue option in the Macro dialogue box >> click on Run.

Using created Macro

Consequently, you have the maximum value in cell C12, as shown in the following image.

Maximum value found by using VBA Max function

Quick Notes

  • You can set the Cell References separated by a 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. 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. 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. 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. The MAX function will ignore anything but the numbers and return the largest value among them.

Output with Largest Numeric Values while ignoring Texts

Download Practice Workbook

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


That’s all for today. We have tried showing you 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. Here, we have also provided an article to show you how to use MAX and MIN functions in the same formula.

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

Excel MAX Function: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF