How to Find Largest Number in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

overview picture of finding largest value in excel


How to Find Largest Number in Excel: 2 Ways

We are going to use the Microsoft Office 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, and Unit Cost. We may modify the dataset if that is needed to explain the functions clearly.

dataset for finding the largest number in excel


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.

selecting preferred cell to get output of finding largest number in excel

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

inserting formula to use MAX function for finding largest value in excel

  • Press the ENTER key and the result will be shown in cell D18.

output after applying MAX function to find the largest value in excel

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

selecting the preferred cell to show the largest value

  • Select the Formulas tab.
  • Click AutoSum.
  • Select Max from the drop-down.

Selecting AutoSum feature from the Formulas tab to apply the MAX function

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

inserting formula to use MAX function from the AutoSum feature to find largest value in excel

  • Press the ENTER key and you will get your result in cell D18.

output after applying MAX function through AutoSum feature in excel

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


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

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

inserting formula to use LARGE function

  • Press the ENTER key. You will get your result in cell D18.

output after applying the LARGE function to find the largest value in excel

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

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


iii. Using AGGREGATE Function

The AGGREGATE function can be used to find the maximum value of a specified range of cells. In this case, you need to keep in mind that you are using MS Office 2010 or above versions. Suppose, 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.

selecting preferred cell to show the max unit cost

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

inserting formula to use the AGGREGATE function for finding largest value

  • Press the ENTER key. The output will be shown in cell D18.

output after applying the AGGREGATE function to find the largest value in excel

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


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.

Steps:

  • Select cell B19 to enter the criterion which is Pencil.

selecting cell to enter the criteria

  • Enter the criterion which is Pencil.

entering criterion in B19

  • Select cell D19 to show output.

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

inserting formula to use MAX function with one criterion for finding largest value

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 the ENTER key. The output will be shown in cell D19.

output of MAX function with one criterion to find the largest value in excel

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.

selecting cell B19 to enter the criterion

  • Enter the criterion which is Pencil.

entering criterion in B19

  • Select cell D19 to get output.

selecting cell D19 to show 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.

inserting a Combination of MAX and IF formulas to find the largest value based on criteria

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 the ENTER key. The output will be shown in cell D19.

output of MAX and IF functions with one criterion to find the largest value in excel

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

Read More: How to Use Excel LARGE Function with Criteria


How to Find the Position of the Largest Number in Excel

From above, we have seen ways of finding the highest value in Excel. 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 G11 to find the cell address of the maximum value.
  • Then press the 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.

inserting formula to find largest number's position

Formula Breakdown

  • MAX(D5:D16) → returns the maximum value of the range D5:D16.
  • ADDRESS(MATCH(MAX(D5:D16),D5:D16,0)+4,4) → becomes
    • OutputADDRESS(MATCH(96,D5:D16,0)+4,4) 
  • MATCH(96, D5:D16,0) → The 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)The ADDRESS function creates a cell reference based on a given column and row number.
    • ADDRESS(10+4,4) → becomes
      • OutputADDRESS(14,4)
        • Output → $D$14

finding maximum value’s position in Excel

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


Practice Section

You can use the following dataset to practice by yourself. 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.

dataset for finding the largest value in Excel


Download Practice Workbook


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 our website for more articles like this.


Related Articles


<< Go Back to Excel LARGE Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nujat Tasnim
Nujat Tasnim

Nujat Tasnim, BSc in Computer Science and Engineering from American International University-Bangladesh, has a year-long tenure with ExcelDemy. Initially a Content Developer at SOFTEKO, she transitioned to the Software Team as Junior Software Quality Assurance Engineer. Currently serving as a Junior SQA Engineer at SOFTEKO, she excels in SDLC, STLC, and requirement analysis and possesses profound insights into OOP-related software development processes. Nujat is also proficient in design software like Photoshop and Illustrator, contributing diverse skills to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo