MAX Function In Excel

This Excel Tutorial demonstrates how to use the Excel MAX Function in Excel to calculate the largest number, with formula examples.

MAX Function Description:

The MAX Function Calculates the largest number.

Formula Examples:

max function examples

Example Formula Result
1 =MAX(B5:B10) 6
1 Other Functions:
3 =MIN(B5:B10)
4 =MEDIAN(B5:B10)
5 =MODE(B5:B10)
6

Syntax and Arguments:

The Syntax for the MAX Formula is:

Function Arguments ( Inputs ):

array – An array of numbers.

Additional Notes

The MIN Function returns the smallest number in a series.

MAX Examples in VBA

You can also access the Excel MAX Function from within VBA, using Application.WorksheetFunction.

The statement above will return 30.43 as the max value (21 + 9.43 = 30.43)

MAX can also accept a range (or Named Range) as a parameter as shown in the VBA code below

 

VBA Max Function Table

In addition, MAX can accept the name of a table like the one shown above, as a parameter:

MAX ignores non-numeric values, so the result of the code above is 80.

 

Return to the List of all Functions in Excel

How to use the MAX Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
=AND(
After entering it in the cell, notice how the AND formula inputs appear below the cell:
max formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=MAX(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the max function in excel

For more information about the MAX Formula visit the
Microsoft Website.

How To Use The MAX Function

The MAX Function returns the largest value from a set of data. 

Here let’s use the MAX Function to find the highest GDP in the world:

=MAX(B2:B11)

<example 1>

Empty Cells or Cells With Text

The MAX Function ignores cells that are empty or that contain non-numeric values.

<example 2>

MAX Date

Since Excel stores date as serial numbers, we can use the MAX Function to find the maximum date. 

=MAX(B2:B8)

<example 3>

VLOOKUP & MAX

In our above example, we used the MAX Function to determine the highest GDP in the world. After finding the MAX GDP, we can use the VLOOKUP Function to find the country with the highest GDP:

=VLOOKUP(MAX(A2:A9), A2:B9, 2, FALSE)

<example 4>

The above function calculates the highest GDP by using the MAX Function. The VLOOKUP Function searches for the highest GDP and returns the information from the second column (as indicated by “2” in our formula) of the search range (A2:B9). 

Note: The item we are looking for (GDP) must be in the first column when using the VLOOKUP Function.

MAXIFS Function

MAXIFS Function returns the maximum value from a data range based on one or more criteria. From our GDP example, suppose we want to find the highest GDP of an Asian country. Use the formula: 

=MAXIFS($C$2:$C$11, $B$2:$B$11, E3)

<example 5>

The function narrows down its data range by applying the conditions we provided. It looks for Asia in B2:B11, thus creating a subset from the original data range. The maximum value is then calculated from this subset of data. 

To learn more about how the MAXIFS Function works, read our tutorial on MAXIFS and MINIFS.

Note: MAXIFS Function is only available in Excel 2019 or Excel 365. If you have an older Excel version, you can use Array Function to make your own MAX IF. 

MAX IF 

If you are using older Excel versions, you can combine IF Function with MAX Function using an Array Function. Taking the same example as above, use the formula:

=MAX(IF(E2=$B$2:$B$11, $C$2:$C$11))

<example 6>

Note: When building array functions, you must press CTRL + SHIFT + ENTER instead of just ENTER after creating your formula.

You’ll notice how the curly brackets appear. You can not just manually type in the curly brackets; you must use CTRL + SHIFT + ENTER.

The formula creates a subset of GDP by using the search criteria. The IF Function searches for “Asia” in B2:B11 and returns the corresponding values from C2:C11 to the MAX Function. Thus, calculating the highest GDP in Asia only. 

Note: The above formula uses absolute references (the $ signs) to lock cell references when copying formulas. If you aren’t familiar with this, please read our Excel References Guide