How to Use the MAX Function in Excel

This guide will explain how to start using the MAX function in Excel.

When you’re dealing with a list of products and their prices, it’s often useful to quickly find the highest price. If your dataset includes a table of products along with their prices, identifying the largest value in that range can be a key step in your analysis.

We can use the MAX function in Excel to find the highest value in a range. In this guide, we will provide a step-by-step tutorial on how to use the MAX function in Excel.

The Anatomy of the MAX Function

The syntax of the MAX function is as follows:

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

Let’s look at each argument to understand how to use the MAX function.

• MAX() refers to our MAX function. This function returns the largest value in a set of values.
• Number1, number2, …   – The Number1 argument is required, and all subsequent number inputs are optional.
• If the arguments contain no numbers, MAX returns a value of 0.
• The arguments can be numbers, names, arrays, or cell references that contain numerical data.

A Real Example of the MAX Function in Excel

Let’s explore a simple use case for the MAX function in Excel.

In the table above, we have a table containing the final grades of a group of students. We want to determine the highest final grade in this group of students.

Given this sample data, we can use the following formula to find the maximum value

=MAX(B2:B20)

The MAX function requires at least one argument which should refer to a number or a cell range containing numbers.

After evaluating the MAX formula, we’ve determined that 97 is the highest final grade achieved by this specific batch of students.

We can also include additional arguments for the MAX function to analyze.

In the example above, we included two cell ranges (B2:B20 and D2:D20) as input for the MAX function.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to find the largest value in a range using the MAX function in Excel.

How to Use the MAX Function in Excel

1. First, identify the range you wish to search for the maximum value. Select an empty cell where you wish to output the maximum value.
   
   In this example, we’ll look for the largest value in the range B2:B20 and return the result in cell E1.
   
2. In your desired cell, type “=MAX(“ to start the MAX function.
   
3. While your cursor is still in the formula bar, type the range of cells where you want to find the maximum value. Close the formula with a closing parenthesis.
   
   In our example, we’ll type in the range in our table containing the final grades of our students (B2:B20).
   
4. Hit the Enter key to evaluate the MAX function.
   
   With our MAX formula, we find out that the highest final grade from our batch of students is 97.

These are all the steps you need to know to start using the MAX function in Excel.

FAQs

1. What happens if the MAX function in Google Sheets is applied to a range containing both numbers and text?
   When the MAX function is applied to a range that includes both numbers and text, Google Sheets will ignore the text values and only consider the numeric values to determine the maximum number. If the range contains only text and no numbers, the MAX function will return 0.
   
2. Is it possible to use the MAX function across multiple sheets within the same Google Sheets document?
   Yes, it is possible to use the MAX function across multiple sheets within the same Google Sheets document. To do this, you need to use a formula that references the specific sheets and cell ranges you want to include in the calculation. For example, =MAX(Sheet1!A1:A20, Sheet2!A1:A20) calculates the maximum value between two ranges located in Sheet1 and Sheet2.
   
3. How do I find the nth largest value in a range rather than just the largest?
   While the MAX function returns the maximum value in a given dataset, the LARGE function allows you to find not only the maximum value but also the second largest, third largest, and so on, by specifying the rank of the value you’re interested in.
   For example, the formula =LARGE(A1:A10,3) will return the third largest value in the range A1:A10.

To learn more about using Excel for analyzing a dataset for maximum values, you can read our post on how to use the LARGE function in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!