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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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