How to Highlight Highest Value in Excel (3 Quick Ways)

When we are working in Excel, it will be easy to find the maximum value if we highlight it. This article will guide you on how to highlight the highest value in each row or column in Excel with 3 quick methods.

How to Highlight Highest Value in Excel: 3 Quick Methods

Here, we will show you 3 ways to highlight highest value in Excel Using Conditional Formatting and different functions.

Method 1: Use Conditional Formatting to Highlight Highest Value in a Column in Excel

Let’s get introduced to our dataset first. Here, I have placed some fruits’ prices in my dataset within 2 columns and 8 rows. Now I’ll use Conditional Formatting to highlight the highest value in Excel.

Step 1:

➥ Firstly, select the data range.

➥ Then, click Home > Conditional Formatting > New Rule

A dialog box named “New Formatting Rule” will open up.

Step 2:

➥ After that, select ‘Format only top or bottom ranked values’ from the ‘Select a Rule Type’ box.

➥ Now, type 1 in the box under ‘Format values that rank in the’ option.

➥ Then, press the Format tab.

A dialog box named “Format Cells” will appear.

Step 3:

➥ Next, choose your desired highlight color from the Fill option and press OK.

Then, this box will be closed and will return to the previous dialog box.

Step 4:

➥ Now, just press OK

Finally, see that the highest value is highlighted with green color.

Method 2: Apply Conditional Formatting to Highlight Highest Value in Each Row in Excel

For this method, I have rearranged the dataset. I have used three columns to show prices for three consecutive months.

Step 1:

➥ In the beginning, select the whole values from the dataset.

➥ After that, click as follows: Home > Conditional Formatting > New Rule

A dialog box will appear.

Step 2:

➥ Next, select ‘Use a formula to determine which cells to format’ option from the ‘Select a Rule Type’ box.

➥ Then, type the following formula in ‘Format values where this formula is true’ box.

=C5=MAX($C5:$E5)

➥ Lastly, press on the Format button.

The ‘Format Cells’ dialog box will open up.

Step 3:

➥ Now, select your desired fill color and press OK.

I have chosen the green color.

Step 4:

➥ Again, press OK again.

Finally, look at the image below, the highest value of each row is now highlighted with green color.

Method 3: Create Excel Chart to Highlight Highest Value in Excel

In our last method, I’ll show how we can highlight the highest value by creating an Excel Chart. For that, I have modified the dataset again. I have added two extra columns in our initial dataset which will represent the maximum value and the rest of the values.

At first, we’ll find the maximum value.

Step 1:

➥ To start with, type the given formula in Cell D5–

=IF(C5=MAX($C$5:$C$11),C5,"")

➥ Then, hit the Enter button and use the Fill Handle tool to copy the formula to the other cells.

Step 2:

➥ Next, activating Cell E5 write the formula as given below-

=IF(D5="",C5,"")

➥ After that, press the Enter button and use the Fill Handle tool to copy the formula for the other cells.

Now, our maximum value and the rest of the values are distinguished.

Step 3:

➥ Afterward, press and hold the Ctrl button and select the Products, Max, and Rest columns.

➥ Then, go to the Insert ribbon and click the arrow icon from the Charts bar.

A dialog box named ‘Insert Chart’ will appear.

Step 4:

➥ After that, select the chart type as you wish from the ‘Recommended Charts’ option and press OK.

Finally, the chart shows the highest value with a different color.

Read More: How to Highlight Text in Excel

Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Download Practice Book

You can download the free Excel template from here and practice on your own.

Highlighting Highest Value.xlsx

Conclusion

I hope all of the methods described above will be helpful enough to highlight the highest value in Excel. Feel free to ask any questions in the comment section and please give me feedback.

<< Go Back to Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!