Finding the largest value from the given values is important when making decisions about things like what is the highest score in the class or what is the highest gross profit margin %, etc.
When there are so many values, it is difficult to do it manually.
In Excel, we have many simple and easy methods to find the largest value accurately.
In this article, I will show you some simple methods that you can use to find the largest value in Excel.
Method 1: Use the MAX Function to Find the Largest Value in Excel
Sometimes we are given a range of values, and we have to figure out which value is the largest or highest. In that case, we simply use the Excel MAX function to find the largest number in the range that is given.
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Now, I want to find what is the highest score of these data in Cell F1.
I can use the below formula to find the highest score of the students.
=MAX(C2:C6)
The syntax of the MAX function is MAX(number1, [number2], …). For the MAX function, we have to enter at least one number.
In this case, I have entered the scores of all students as a range. So, I have selected cells C2 to C6 as arguments of the MAX function.
Then, Excel returns the largest value from the selected range. In this case, I got 91 as the highest score as it is the largest value in cells C2 to C6.
The MAX function ignores empty cells, text values, and logical values (Such as TRUE, which Excel evaluates as 1) from arrays or references.
If you want to consider those as part of your calculation, you have to either directly enter them to list the arguments of the MAX function or use the MAXA function. The syntax of the MAXA function is MAXA(value1,[value2],…).
Also read: Find Last Occurrence of a Value in a Column in Excel
Method 2: Use the LARGE Function to Find the Largest Value in Excel
Sometimes, you need to find the second largest, third largest value and so on. In that case, you cannot use the MAX function.
Because in the MAX function, you can find only the largest value of the selected values.
Excel has a solution for that. You can use the Excel LARGE function. You can use this function to find the largest value as well as any position from the largest value (such as second largest, third largest etc.).
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Now, I want to find what is the highest score of these data in Cell F1.
I can use the below formula to find the highest score.
=LARGE(C2:C6,1)
The syntax of the LARGE function is LARGE(array, k). The first argument of the function is the array.
I have selected cells C2 to C6 as the first argument of the function. In this scenario, I want to find the largest value.
In other words, the value that gets position 1 from the largest values. So, I have entered 1 for the second argument. Then, I get the largest value from the selected range. In this case, it is 91.
If you want to find the second largest value, then you have to enter 2 for the second argument of the above function.
Also read: Find the Closest Match in Excel (Nearest Value)
Method 3: Use the SUBTOTAL Function to Find the Largest Value in Excel
Sometimes, we apply filters to our data set and we want to find the largest value from the filtered data.
If we apply the MAX function or LARGE function, Excel returns the largest value from all the selected data for the array.
To get the largest value from the filtered data, you can apply the Excel SUBTOTAL function.
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Sometimes I use the filter option to filter data. You can see that I have filtered students of group A.
Now, I want to find what is the highest score from the filtered data in Cell F1.
I can apply the below formula to find the largest value from the filtered data.
=SUBTOTAL(4,C2:C6)
The syntax of the SUBTOTAL function is SUBTOTAL(function_num,ref1,[ref2],…).
As the first argument of this function, I have to enter the function number. The function number for the MAX function is 4.
Then, for the second argument, I have to select the array. I have selected cells A2 to A6 for the second argument.
Now, Excel returns the largest number from the filtered data. So, in this case, Excel returns the largest value from the group A students. That is 88.
Note: If you want to ignore the manually hidden rows also when finding the largest value, then use the function number 104.
Also read: How to Find Outliers in Excel?
Method 4: Use the AGGREGATE Function to Find the Largest Value in Excel
Sometimes, we apply filters to our data set and we want to find the largest value from the filtered data.
As you have learned in the previous method, you can use the SUBTOTAL function to find the largest value from the filtered data.
But, when you want to find the second largest, third largest value and so on from the filtered data, you can’t use the SUBTOTAL function.
The reason is that you don’t have a function number for the LARGE function in the SUBTOTAL function. In such a situation, you can use the Excel AGGREGATE function.
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Sometimes I use the filter option to filter data. You can see that I have filtered students of group A.
Now, I want to find what is the highest score from the filtered data in Cell F1.
I can apply the below formula to find the largest value from the filtered data.
=AGGREGATE(14,7,C2:C6,1)
The syntax of the AGGREGATE function is AGGREGATE(function_num, options, array, [k]).
The first argument of the AGGREGATE function is the function number. I have entered 14 for the first argument as I want to get the LARGE function.
Then, for the second argument, you have to select one of the options from the below.
I have selected option 7 and entered 7 for the second argument. You can even double-click and select an option from the options list.
Then, I have to select the array for the third argument. I have selected cells C2 to C6 for the third argument.
For the final argument, I have to enter the position of the largest value. As I want to find the largest value, I have entered 1 for the last argument.
If you want to find the second largest value, you have to enter 2 for that argument.
Then, Excel returns the largest value from the filtered data. So, here I got 88 as the highest score of group A students.
Note: If you want to find only the largest value (without the second largest, third largest, etc.) from the filtered data, you can use function number 4 for the second argument and ignore the last argument of the function. Function number 4 is for the MAX function.
Also read: How to Count Filtered Rows in Excel?
Method 5: Use the MAX Function to Find the Largest Value Based on Conditions in Excel
Sometimes you need to find the largest value based on some conditions.
You can use the MAX function itself to find the largest value based on some conditions.
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Now, I want to find the highest score group A students in Cell F1. Please note that I don’t want to apply filters for my data set.
I can use the below formula to find the highest score of group A students.
=MAX((C2:C6)*(A2:A6=F1))
First I applied the MAX function. The syntax of the MAX function is MAX(number1, [number2], …).
For the MAX function, we have to enter at least one number. In this case, I have entered the scores of all students as a range. So, I have selected cells C2 to C6 as arguments of the MAX function.
But, I want to find the largest value of group A students. So, I have to multiply the range with the logical condition.
The condition that I have applied is (A2:A6=F1). Then, Excel evaluates this logical condition and considers only the TRUE values for the MAX calculation.
If the output of the logical condition for any cell is FALSE, the MAX function ignores that cell.
So, in this case, Excel returns 88 as the highest score of group A students. (Even Though 91 is the highest score from the entire list, the student who obtained that (Ian) is from group B. So, Excel ignores 91).
Method 6: Use the MAXIFS Function to Find the Largest Value Based on Conditions in Excel
In the previous method, you have learned how to insert conditions inside the MAX function to find the largest value based on conditions.
But, if you are not confident about applying conditions inside formulas, you can use the MAXIFS function to find the largest value based on conditions.
Compared to the previous method, this method is user-friendly and minimizes errors.
Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.
Now, I want to find the highest score group A students in Cell F1. Please note that I don’t want to apply filters for my data set.
I can use the below formula to find the highest score of group A students.
=MAXIFS(C2:C6,A2:A6,F1)
The syntax of the MAXIFS function is MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).
For the first argument of this function, I have selected the range in which I want to find the largest or maximum value. In this case, I want to find the highest score.
So, I have selected cells that contain scores which are cells C2 to C6. Then, I have to enter the range of cells to evaluate the first criteria.
I want to find group A from the group column. So, I have selected cells A2 to A6 for the second argument.
Next, I have to enter the criteria for the selected range. I want to get the group name that is given in cell F1.
So, I have selected cell F1. But, I can also enter A within quotes for the third argument. You can enter up to 127 pairs of such conditions for the MAXIFS function. But, in this case, I have used only one condition pair.
So, Excel returns the highest score from group A students, which is 88.
You now know six different methods to find the largest value in Excel. Learn about all of the methods and choose the one that best meets your needs.
Other Excel articles you may also like:
