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

**Table of Contents**hide

## Practice Workbook

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

## Excel MAX Function

### 1. Basics of MAX Function

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

#### Summary

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

#### Syntax

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

#### Arguments

**number1:** Number, 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.

#### Versions

Workable from Excel 2003.

### 2. Uses of MAX Function

#### I. MAX for Rows

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.

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

`=MAX(C4:E4)`

**C4 **to **E4 **is the **Cell Reference** of the first row.

Our formula has provided the highest score for *Angela *in the quizzes.

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

#### II. MAX for Columns

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.

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

`=MAX(C4:C9)`

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.

#### III. MAX 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.

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

`=MAX(C4:E9)`

We have found the maximum value from our table.

#### IV. MAX for 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.

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

The formula will be

`=MAX(C4:C9,E4:E9)`

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

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

#### V. Recent Date using MAX

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

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

Write the **MAX **formula for the date column.

`=MAX(D4:D9)`

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.

#### VI. MAX with criteria

You can find the maximum value comparing any criteria.

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

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

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(C4:C12=G4,D4:D12))`

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

Here we have found the highest quantity for *Cricket Bats. *If we change the criteria item the result will be changed.

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

### 3. Quick Notes

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

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

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

You will get the highest value among the inserted numbers.

- Numbers can be inserted along with the ranges also.

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

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

You will get the largest value among them.

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

## Conclusion

That’s all for today. We have tried showing how you can use the **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.

## Further Readings

- How to Use LARGE Function in Excel (6 Easy Examples)
- How to Use MIN Function in Excel (5 Relevant Examples)
- How to Use SMALL Function in Excel (4 Common Examples)
- How to use AVERAGE function in Excel (5 Examples)
- How to calculate Average, Median, & Mode in Excel
- The Different Ways of Counting in Excel
- How to Use AVERAGEIFS Function in Excel (4 Examples)
- How to Use COUNT Function in Excel (With 5 Examples)
- How to Use COUNTA Function in Excel (3 Suitable Examples)
- How to Use RANK Function in Excel (With 5 Examples)