5 Easy Ways to Highlight Highest Value in Excel

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.

dataset to highlight highest value

Method 1

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.

Sort & Filter

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.

Sort to highlight highest value

At this time, a window will pop up. Just select Expand the selection.

Thus, you found the highest value in your dataset.

Sort & Filter

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.

Filter to highlight highest value

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.

Method 2

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.

Conditional Formatting

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.

Top 10 items to highlight highest value

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

Conditional Formatting

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.

Built-in rule to highlight highest value

  • 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

Conditional Formatting

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.

MAX function to highlight highest value

  • 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

Conditional Formatting

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.

MAX function for multiple rows to highlight highest value

  • 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

Conditional Formatting

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.

MAX function for multiple columns to highlight highest value

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

Method 3

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.

LARGE Function

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)

LARGE function to highlight highest value

  • Finally, hit Enter to see the highest value.

📕 Read More: 6 Easy Ways to Highlight Lowest Value in Excel

LARGE Function

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)

LARGE function to highlight highest value

  • Next, hit Enter to see the highest value.

Hence, you got the first three values.

Method 4

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)

LARGE function to highlight highest value

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

INDEX-MATCH formula to highlight highest value

  • 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

Method 5

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)

SORT and FILTER functions to highlight highest value

  • 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

(Visited 66 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo