Hassan Shuvo
Are you a teacher? Do you need to find the students who scored the highest in the final exam? Perhaps you are an entrepreneur looking for an easier way to determine the highest amount of products you delivered in a month. If you are neither, but you want to find the highest values in a data range in Excel, you have come to the right place. In this article, we will show you how to find and highlight the highest value in a set of data in Excel.
📁 Download Excel File
Download the file to practice with us.
Learn to Highlight the Highest Value in Excel with These 5 Unique Methods
In today’s article, I will demonstrate 6 simple ways you can highlight the highest value in an array in Excel. The methods will include Excel’s built-in features and some functions like MAX, LARGE, INDEX, MATCH, SORT, and FILTER. As an example, we choose to show the marks obtained by a couple of students in an exam.
1. Using Sort & Filter Feature
For our first method, we choose to show you Excel’s own built-in feature, Sort & Filter. We will explain them individually.
1.1. Using Sort Feature
Using Sort, we can sort our data from largest to smallest or from smallest to largest. Let’s apply the data to our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the data range that you want to sort. Since we want to find the highest mark we will sort our data from largest to smallest. So we are selecting C6:C17.
- Next, we will go to Home tab and from Editing group select Sort & Filter. Finally choose Sort Largest to Smallest to get your result accordingly.
At this time, a window will pop up. Just select Expand the selection.
Thus, you found the highest value in your dataset.
1.2. Using Filter Feature
Using Filter, we can sort data from largest to smallest in a single click.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your entire data range, as we did for B5:C17.
- Again, go to the Home tab, and from the Editing group, select Sort & Filter. Finally select Filter.
You will see Drop-down buttons beside your headers.
- If you click the Drop-down button beside ‘Obtained Marks’ you will get options to sort your data from largest to smallest. Click Sort Largest to Smallest to sort them.
Your highest value is in the first row now.
2. Applying Conditional Formatting Feature
One of the most popular features of Excel is the Conditional Formatting feature. You have the option of using the built-in Top/Bottom option or creating your own rule based on your own formula. We will show you both. Also, we will show you how you can use the Conditional Formatting feature to locate and highlight the highest value in a row or column in Excel.
2.1. Applying Top/Bottom Rule
We can use Conditional Formatting to locate and highlight the Top-ranked value using the Top/Bottom rules in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected B6:C17.
- Then go to the Home tab, and from the Styles group select Conditional Formatting. Then, choose Top/Bottom Rules from the menu. Finally, choose the Top 10 Items.
- In the Top 10 Items window, we can select the number of top items we want to highlight and which color to identify with. Since we need to highlight the highest value, we will select 1(you can put any number here), and Green color to highlight. Then we press OK.
Finally, you can see that your highest value has been highlighted.
2.2. Applying Built-in Rule
We can use Conditional Formatting to locate the Top-ranked value using the built-in rule from the New Rule section.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range as we selected B6:C17.
- Then go to the Home tab, and from the Styles group select Conditional Formatting. Finally, choose New Rule from the menu.
- A New Formatting Rule window will appear. From here, select Format only top or bottom ranked values. Selecting this will give you the option to locate top or bottom values. Also, it will give you a choice to find the number of top or highest-ranking values. We will select Top, and the number will be just 1 (Again, just put any number according to your preference).
- Next, we need to select a color to highlight the highest value so will select Format. The Format Cells dialogue box will help us in this case. From Fill tab choose any color you want. We are selecting Green, then press OK.
Finally, you can see that your highest value has been highlighted.
📕 Read More: Conditional Formatting Based on Date Range in Excel
2.3. Applying New Rule Based on Formula
We can also use Conditional Formatting to locate the Top-ranked value using our own formula. In order to find the largest value in the given data, we created a formula using the MAX function.
The MAX function has the following syntax:
MAX(number1, [number2], …)
number1 – a number, a reference to a numeric value, or a range of numeric values.
number2 – a number, a reference to a numeric value, or a range of numeric values. This is optional.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected B6:C17.
- Now go to Home tab, and from Styles group section, choose Conditional Formatting. Finally, choose New Rule option you can find from the menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
=C6=MAX($C$6:$C$17)
- Again, to highlight the highest value, select Format. A Format Cells dialogue box will help us in this case. From Fill tab choose the color you want. We are selecting Green then press OK.
Now, your highest value has been highlighted.
📕 Read More: How to Apply Excel Formula to Change Text Color Based on Value
2.4. Applying New Rule for Multiple Rows
Next, we will use Conditional Formatting to locate the Top-ranked values in multiple Rows using a formula.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected C7:E11.
- After that, go to Home tab, and from the Styles group select Conditional Formatting. Finally, choose New Rule from the menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
=C7=MAX($C7:$E7)
- Again, to highlight the highest values, select Format. A Format Cells window will help us in this case. Then, from Fill tab choose the color you want. We are selecting Green then press OK.
Now, your highest values in each Row have been highlighted.
📕 Read More: Conditional Formatting to Highlight Row Based on Date in Excel
2.5. Applying New Rule for Multiple Columns
Finally, we will use Conditional Formatting to locate the Top-ranked values in multiple Columns using a formula.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your data range, as we selected C7:E11.
- Then, selectt Home tab, and from Styles group, select the Conditional Formatting. Finally, choose New Rule from context menu.
- In order to put a new formula we will select Use a formula to determine which cells to format. This selection will bring forth a box where we will write our formula. Type this formula in the box.
=C7=MAX(C$7:C$11)
- Again, to highlight the highest values, select Format. The Format Cells window will help us in this case. From Fill tab choose the color you want. We are selecting red then press OK.
Now, your highest values in each Column have been highlighted.
3. Utilizing LARGE Function
If you need to find the highest value and place it in another cell, you will have to use functions. In this way, the original table remains intact. So in this example, using the LARGE function, we will show you how to identify the highest value and also place it somewhere else. But first, let’s learn the syntax of the function.
LARGE(array, k)
The arguments are:
array – a collection of numerical values in an array.
k – position, expressed as an integer, with 1 being the largest value.
The LARGE function returns the array’s kth largest value.
3.1. Finding Largest Value
Let’s use the LARGE function to find or highlight the highest value in our array in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your highest value from the array. We selected C19 in this case.
- Next in the Formula Bar type this formula.
=LARGE(C6:C17,1)
- Finally, hit Enter to see the highest value.
📕 Read More: 6 Easy Ways to Highlight Lowest Value in Excel
3.2. Finding Top 3 Value
We can also use the LARGE function to find the top 3(or any number you want) values in our array.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your highest value from the array. We selected cell C20 in this case.
- Then, in the Formula Bar type this formula.
=LARGE($C$6:$C$17,B20)
- Next, hit Enter to see the highest value.
- Finally, use the Fill Handle tool to automatically copy the formula.
Hence, you got the first three values.
4. Using INDEX & MATCH Functions
The problem with the previous method is that it only shows the highest marks. But if you also want the names, you can use the INDEX and MATCH functions together. We also used the LARGE function here.
Syntax of the INDEX function:
INDEX(array, row_num, [column_num])
The arguments are as follows.
array – cells in a certain range or an array constant.
row_num – unless column_num is provided, it is necessary. chooses which row in the array to return a value from. Column_num is necessary if row_num is left out.
column_num – chooses the array column that will contain the value to be returned. Row_num is necessary if column_num is left out.
And syntax of the MATCH function:
MATCH(lookup value, lookup array, [match type])
Arguments:
lookup value – this is a must. The lookup array value you’re trying to match.
lookup array – the number of cells being looked for.
match type – the lookup_value and values in the lookup_array are matched by Excel according to the match type argument. The match types are:
-1 smallest value larger than or equal to lookup value.
0 first value that is exactly equal to lookup value.
1 greatest number that is less than or equal to lookup value.
Let’s get to the example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell where you want to put your highest value from the array. We selected cell C20 in this case.
- Then, in the Formula Bar type this formula.
=LARGE($C$6:$C$17,B20)
- Next, hit Enter to see the highest value.
- Then, use the Fill Handle tool to automatically copy the formula.
- Now to get the names of those who got the highest marks select cell D20 and type this formula.
=INDEX($B$6:$B$17,MATCH(C20,$C$6:$C$17,0))
- Next, hit Enter to see the name of the student who got the highest value.
- Then, use the Fill Handle tool to automatically copy the formula.
🔨 Formula Breakdown
👉 First, the MATCH function will look at the content of C20 as the lookup_value and then, search in the lookup_array $C$6:$C$17 and we provided 0 to get the exact match.
👉 Then, the INDEX function will print the content of Column B according to the lookup_value found by the MATCH function in Column C.
📕 Read More: Conditional Formatting Based on Another Cell Date in Excel
5. Incorporating SORT & FILTER Functions Together
As you can see, we used two formulas in our previous example to get the job done. But what if you can use just one formula to do that? The SORT and FILTER functions used together can help us in this case.
The syntax of the functions:
SORT(array,[sort_index],[sort_order],[by_col])
A sorted array of an array’s items is what SORT returns. The length of the returned array matches that of the array parameter.
The arguments are
array – the dataset, or collection, to sort. This argument is mandatory.
sort_index – number of rows or columns to sort by
sort_order – a number specifying the preferred sort order, with 1 being the default ascending order and -1 being the descending order.
by_col – a logical value that represents the preferred sort direction; TRUE to sort by column, FALSE to sort by row.
The last three arguments are optional.
FILTER(array,include,[if_empty])
The arguments:
array – the data range to filter
include – a boolean array with the same height or width as the array
If_empty – a value that will be returned if the included array is empty of all values.
Let’s use these functions in our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your destination cell as we selected B20.
- Next in the Formula Bar type this formula.
=SORT(FILTER($B$6:$C$17,$C$6:$C$17>=LARGE($C$6:$C$17,3)),2,-1)
- Then, hit Enter to see the first 3 names and highest values.
🔨 Formula Breakdown
👉 First, the LARGE function will get the 3rd largest value, which is 91 in $C$6:$C$17 and the FILTER function will collect those larger than or equal to 91.
👉 Finally, the SORT function will sort the values in descending order. Subsequently, we specified 2 in the sort index as we want the second column to sort by and -1 in the sort order for descending order.
📄 Important Notes
🖊️ In the case of the LARGE function, your formula may result in “ties” if there are duplicates in the array. For instance, if two cells contain the number 1, and the LARGE function is set to return the largest and second largest values, you will obtain 1 in both circumstances.
🖊️ The LARGE function will return an error if the array includes one or more errors.
🖊️ The INDEX-MATCH formula won’t work for duplicate values. Click here to learn how to highlight the top 10 list with duplicates.
📝 Takeaways from This Article
📌 First, we showed how to use the built-in Sort & Filter feature to highlight the highest value in Excel.
📌 Then we demonstrated the Conditional Formatting feature in different cases.
📌 Next, we explained the LARGE function and its functionality to find the highest values.
📌 We also showed the use of the INDEX and MATCH functions to achieve the same goal.
📌 Finally, to make the process effortless, we explain how to use the SORT and FILTER functions together in this context.
Conclusion
Finally, you have concluded our article. I hope you understood the processes and the functions well enough to use them on your own from now on. Moreover, if you have any more questions or if you know any other way we can highlight the highest possible value in an array in Excel, please leave a comment below to let us know. For more articles like this, visit Excelden.com.
Related Articles
- Excel VBA: If Font Color Is Red Then Return Specific Output
- 4 Ways to Use Conditional Formatting for Dates Overdue in Excel
