**Microsoft Excel** facilitates us to estimate various types of data as well as perform monetary, arithmetic, and statistical computations. In many cases, we want to get the greatest value or number in a dataset. **Excel** provides us with some effective functions **to find out the largest number** in a dataset in an easier way. In this article, you will learn about some quickest ways of finding the largest number in a dataset.

## Download Practice Workbook

## 2 Ways to Find the Largest Number in Excel

We are going to use the ** Excel 365 version** in this article. You can use any other version as well. Here, I will try to explain every step of the 2 ways

**to find the largest number in Excel**. To do that, we have taken a concise dataset that contains

**13 rows**and

**4 columns**of

**Rep Name**,

**Item**,

**Units**&

**Unit Cost**. We may modify the dataset if that is needed to explain the functions clearly.

### 1. Use Excel Functions to Find the Largest Number Within a Range in Excel

#### i. Using MAX Function

**The MAX function** in excel returns **the largest value** in a dataset we want to specify. The arguments can be either a numeric value, array, named range, a reference to a cell, or a range containing numbers.

Here, I will use **the Units column** to show you how to find the largest value using **the MAX function**. Let’s follow the following steps.

**Steps:**

- Select your preferred cell (i.e.
**D18**) to have your output.

- Insert the following formula in cell
**D18**to find the largest value.

`=MAX(D5:D16)`

Here, **D5:D16 **is the range of values of the **Units column**.

- Press
**ENTER Key**and the result will be shown in cell**D18**.

Here, we got the **Max Unit** that contains the largest value of the specified range.

Another way of finding the largest value in a dataset is using **the AutoSum feature**. We can easily use **the AutoSum feature **to apply **the MAX function** and can find the** highest value** in a dataset. I will use the same **Units **column to show this technique. Follow the following steps.

- Select your preferred cell (i.e.
**D18**) to show your output.

- Select the
**Formulas**tab. - Click
**AutoSum**. - Select
from the__M__ax**drop-down**.

- Insert the following formula in cell
**D18**to find the largest value.

`=MAX(D5:D16)`

Here, **D5:D16 **is the range of values of the **Units column**.

- Press
**ENTER Key**and you will get your result in cell**D18**.

Here, we got the same result of the **Max Unit** that contains the largest value of the specified range.

**Read More: ****How to Find Largest Lookup Value in Excel (3 Easy Ways)**

#### ii. Applying LARGE Function

Another Excel function to find the biggest number is **the LARGE function**. **The LARGE function** provides us with the largest value of the specific range we give. The **syntax** of** the LARGE function** is **LARGE(array, k)**. Here, the **array** is the range of data you want to specify and **k** is the position in the array of data you want to get. I will use the **Units column** to apply **the LARGE function**. You will be clear if you go through the following part.

**Steps:**

- Select cell
**D18**to show output.

- Insert the following formula in cell
**D18**to find the largest value.

`=LARGE(D5:D16,1)`

Here, **D5:D16 **is the array or range of values of the **Units column** and **1** is the **k** value which represents the position of data you want to get. So, **1** means **the first largest** value.

- Press
**ENTER Key**. You will get your result in cell**D18**.

Here, we got the **Max Unit** of the specified range by applying **Excel LARGE function**.

**Read More: ****How to Use Excel LARGE Function with Criteria( 4 Suitable Ways)**

**Similar Readings**

**Return Cell Address Instead of Value in Excel (5 Ways)****How to Use MAX IF Function in Excel (4 Practical Examples)****Find Second Largest Value with Criteria In Excel****Get Cell Value by Address in Excel (6 Simple Methods)****How to Use Excel IF Function with Range of Values (10 Examples)**

#### iii. Using AGGREGATE Function

**The AGGREGATE function** can be used to find the max value of a specified range of cells. In this case, you need to keep in mind that you are using **Excel 2010** or above versions. Suppose, in this method I want to know the **Max Unit Cost** of the **Unit Cost** column. Do follow the steps shown below.

**Steps:**

- Select cell
**D18**to show output.

- Insert the following formula in cell
**D18**to find the largest value.

`=AGGREGATE(4,7,E5:E16)`

Here, **4** indicates that we want to apply **the MAX function** to get the highest value. **7** indicates that we are ignoring ** hidden rows** and error values and

**E5:E16**is the array range of the

**Unit Cost column**.

- Press
**ENTER Key**. The output will be shown in cell**D18**.

So we got **the Max Unit Cost** (**$97.00)** of the **Unit Cost column** by using **the** **AGGREGATE function**.

**Read More: ****How to Use Excel Large Function in Multiple Ranges**

### 2. Finding the Largest Number Within a Range Based on Criteria

If we want to get the highest value based on **conditions** or **criteria**, we can do that also. We can use **the MAX function** and a combination of **MAX** &** IF** functions to compute the highest value of the specified range. Let’s go through the below part of this article.

#### i. Calculating Maximum Value by Using MAX Function

We can use **the MAX function** to find the largest value in a cell range based on one criterion. In this dataset, different sold units are listed for **‘Pencil’**. Suppose I want to know the highest sold units of **‘Pencil’**. Here, I will use the **Item **column and the **Units** column. Follow the below steps.

**Steps:**

- Select cell
**B19**to enter the criterion which is**‘Pencil’**.

- Enter the criterion which is
**‘Pencil’**.

- Select cell
**D19**to show output.

- Insert the following formula in cell
**D19**to find the largest value.

`=MAX((C5:C16=B19)*(D5:D16))`

** **Here, **C5:C16** is the range of the **Item** column, **D5:D16 **is the range of the **Units column** and **B19** is the criterion.

**Formula Breakdown**

**B19 →****‘Pencil’**is the criterion located in cell**B19**.**MAX((C5:C16=‘Pencil’)*(D5:D16)) →**becomes**MAX(({“Marker Pen”;“Pencil”;“Pen”;“Blinder”;“Pencil”;“Marker Pen”;“Pencil”;“Blinder”;“Desk”;“Eraser”;“Blinder”;“Pen”}=“***Pencil***”)*(D5:D16)) →**returns**TRUE**for the exact match**‘Pencil’**and otherwise returns**FALSE****Output → MAX(({***FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE*})*(D5:D16))

**MAX({***FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE*}*(D5:D16))**MAX({***FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE***}*{53;56;57;59;83;71;60;53;70;96;88;68}) →**returns**0**for**FALSE****Output → MAX({0;56;0;0;83;0;60;0;0;0;0;0})****Output → 83**

- Press
**ENTER Key**. The output will be shown in cell**D19**.

Here, we got the maximum number of sold units of **‘Pencil’** and that is **83**. In this way, we can use the **Excel MAX function** to find the largest value based on one criterion.

#### ii. Using Combination of MAX and IF Functions

In this method, I will show you how to get the largest value using the combination of excel **MAX** and **IF** functions. You can combine **the MAX** and **the IF function** to get the highest value of your selected range with criteria. In this dataset different sold units are listed for **‘Pencil’**. Suppose I want to get the highest sold units of **‘Pencil’**. To get this let’s follow the below steps.

**Steps:**

- Select cell
**B19**to enter the criteria which is**‘Pencil’**.

- Enter the criterion which is
**‘Pencil’**.

- Select cell
**D19**to get output.

- Insert the following formula in cell
**D19**to find the largest value.

`=MAX(IF(C5:C16=B19,D5:D16)))`

** **Here, **C5:C16** is the range of the **Item** column, **D5:D16 **is the range of the **Units column** and **B19** is the criterion.

**Formula Breakdown**

**B19 →****‘Pencil’**is the criterion located in cell**B19**.**MAX(IF(C5:C16=B19,D5:D16)) →**becomes**MAX(IF({“Marker Pen”;“Pencil”;“Pen”;“Blinder”;“Pencil”;“Marker Pen”;“Pencil”;“Blinder”;“Desk”;“Eraser”;“Blinder”;“Pen”}=“***Pencil***”,D5:D16)) →**returns**TRUE**for the exact match**‘Pencil’**and otherwise returns**FALSE**

**MAX(IF({***FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE***}, D5:D16)) →The IF function**will give us the numeric value for**TRUE**.**Output → MAX({***FALSE;56;FALSE;FALSE;83;FALSE;60;FALSE;FALSE;FALSE;FALSE;FALSE*})

**MAX({0;56;0;0;83;0;60;0;0;0;0;0})****Output → 83**

- Press
**ENTER Key**. The output will be shown in cell**D19**.

So, we got the **Max Unit** of **Item** **‘Pencil’ (83)** by applying a combination of **Excel MAX** and **IF** functions. In this way, we can also find another max unit of any other item by using **MAX** and **IF** functions.

**Read More: ****How to Use LARGE and SMALL Function in Excel**

## How to Find the Position of the Largest Number in Excel

From above, we have seen ways of finding the highest value in **Excel**. Now, in this section, I will discuss how to find the **cell address** of the maximum value using **Excel MAX**, **MATCH**, and **ADDRESS** functions. Let’s go through the below part.

**Steps:**

- Select cell
**G11**to apply these**3**functions. - Insert the following formula in cell
to find the cell address of the maximum value.**G11** - And then press
**ENTER Key**.

`=ADDRESS(MATCH(MAX(D5:D16),D5:D16,0),+4,4)`

Here, **D5:D16 **is the array or range of values of the **Units column **and **4** indicates that there are four extra rows before starting the values.

**Formula Breakdown**

**MAX(D5:D16) →**returns the maximum value of the range**D5:D16**.**A****DDRESS(MATCH(MAX(D5:D16),D5:D16,0)+4,4) →**becomesADDRESS(MATCH(**Output →***96*,D5:D16,0)+4,4)

**MATCH(***96***, D5:D16,0) → MATCH****function**returns the maximum exact match value (96) from range**D5:D16**.**0**is set to return the exact match.**MATCH(***96***,D5:D16,0) →**becomes**Output → ADDRESS****(***10***+4,4)**

**ADDRESS****(***10***+4,4)****→ ADDRESS function**creates a cell reference based on a given column and row number.**ADDRESS****(***10***+4,4)****→**becomes**Output →****ADDRESS(***14***,4)****Output → $D$14**

Here, we got the maximum value of the **Units** column which is **96**, and its cell address which is **$D$14** by applying **Excel MAX**, **MATCH**, and **ADDRESS** functions.

**Read More: ****How to Lookup Next Largest Value in Excel (4 Easy Ways)**

## Practice Section

You can use the following dataset to practice by yourself. If you need, you can modify it too. Hope it will help you in your learning process of how to find the **largest value** in **Excel** using different functions.

## Conclusion

Now let’s draw a conclusion from this article. Throughout this whole article, I tried my best to explain to you the ways to **find the largest value in Excel** step by step. I hope now you can gracefully handle these types of problems in your dataset. If you have any suggestions, leave a comment below and you can also follow **ExcelDemy** for more articles like this.

## Related Articles

**How to Use Excel Large Function with Text (3 Easy Ways)****What Is a Cell Address in Excel (Types with Example)****How to Use Multiple IF Statements with Text in Excel****[Fixed!] Sort Largest to Smallest Not Working in Excel****How to Return Cell Address of Match in Excel (3 Easy Ways)****Use SUMPRODUCT IF in Excel (2 Suitable Examples)****How to Use VBA Large Function in Excel (4 Suitable Examples)**