**MAX Formula in Excel (Table of Contents)**

- Introduction to MAX Formula in Excel
- Methods to Use MAX Formula in Excel
- How to Use MAX Formula in Excel?

## Introduction to MAX Formula in Excel

Max function is used to find the maximum value from a set of data. To know the highest number, we will use the MAX function. It comes under Statistical Function. As it is one of the statistical functions, it only works with numbers. It does not recognize any blanks, any texts or any logical values such as true or false.

**Syntax of MAX Formula**

**Arguments**

**Number1:**We need to provide a number, a cell reference to a numerical value, or a range consisting of numeric values.**Number2:**This is an optional argument. As number1, we can provide a number literally, a single cell reference to a numerical value, or a range consisting of numeric values.

### Methods to Use MAX Formula in Excel

MAX Formula can be implemented with the following methods:

#### Method #1:

We can also use the reference of multiple cells if the data is present in different cells in one worksheet, different worksheets or even different workbooks.

Max function can be used by using the following syntax:

=MAX (Reference 1, Reference 2, Reference 3, ….) and press Enter.

#### Method #2:

We can also use the range if the data is present one after the other. The range means the selection of cells more than one.

Max function can be used by using the following syntax:

=MAX (Range) and press Enter.

#### Method #3:

We can also use the FORMULAS tab for this. Max function can be used by using the following steps:

**Step 1**: Select any cell where you want to see the result. Click on the Formulas tab on the Ribbon. Go to Function Library. Click on the More Functions dropdown menu. Click on the Statistical dropdown menu.

**Step 2**: Select MAX function.

**Step 3**: A new dialog box named “Function Arguments” opens.

**Step 4**: Enter the 1^{st} and 2^{nd} Number. We can also select the reference or the range. Then press OK.

### How to Use MAX Formula in Excel?

MAX function in excel is very simple and easy to use. Let us understand the working of the MAX function with some examples.

#### Example #1 – MAX Formula Using Excel Reference

Let’s take an example of the marks of the 10 students. We have to find out the highest marks scored.

We will use the MAX function to get the result. The Syntax will be =MAX (B2:B9). We will put it in cell B10.

The result will show as 92, as Cristiano scored the highest marks from others.

We can also do the above task using predefined functions in the formula tab. Max function can be used by using the following steps:

Follow **Step 1** and **Step 2** as shown in the above **Method #3**.

**Step 3**: A new dialog box named “Function Arguments” opens. Select B2:B9 and then press OK.

**Step 4**: In cell B10, the result will show as 92, as Cristiano scored the highest marks from others.

#### Example #2 – MAX Formula Using Define Name

We can name the range using the Define Name command in the formula tab. Let’s take an example of the marks of 10 students. We have to find out the highest marks scored.

**Step 1**: Firstly, we will select the cell range B2 to B9, then click on the Formulas tab. Then under Defined Names Group, click on** the Define Name** dropdown menu. Then click on **D****efine Name**.

**Step 2**: A new dialog box named “New Name” will open. Write the name of the range, such as Mark or anything you want to give.

Refers to section will show the reference of the range selected. In this case, it will be **=’Example #2′!$B$2:$B$9**

And press OK.

**Step 3**: We will use the MAX function to get the result. The Syntax will be **=MAX (Mark)**. We will put it in cell B10.

The result will show 92 as Cristiano scored the highest marks from others.

#### Example #3 – MAX Formula with Blanks in Range

Now, let us take some blanks and see what result it shows.

We will use the MAX function to get the result. The Syntax will be **=MAX (B2: B9)**. We will put it in cell B10.

The result will show as 90 as Sourav scored the highest marks from others.

So, Max function just ignored the blanks. It shows the result based on the numerical values given in the rest of the cells of the selected range.

#### Example #4 – MAX Function with Text in the Range

Now, let us take some texts and see what result it shows.

We will use the MAX function to get the result. The Syntax will be **=MAX (B3: B11)**. As Max is a Statistical function, it does not recognize any text, blanks or any logical values, as explained earlier. Max function can be used by using the following steps:

Follow **Step 1** and **Step 2** as shown in the above **Method #3.**

**Step 3**: Select B2:B9. Then press OK.

**Step 4**: In cell B14, the result will show as 90. Though Cristiano scored the highest marks from others, it is in the text “Ninety Two”, which is ignored by MAX Function. Therefore, MAX Function is taking like 90 of Sourav.

### Things to Remember

- The MAX function can accept up to 255 arguments. If no numbers are present in cells, it will show the result as 0.
- MAX function does not recognize logical values such as true and false as well as any texts and numbers written in text format.
- MAX function does not recognize blank cells. It will show the result as 0.
- Arguments can be used in the form of numbers, name range by using “Define Name”, range of cells, numbers containing in tables or references.

### Recommended Articles

This is a guide to MAX Formula in Excel. Here we discuss how to implement MAX Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

23 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion

4.9

View Course

Related Courses