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.
Read More: How to Highlight Lowest Value in Excel
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 is showing the highest value with a different color.
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.
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.
Further Readings
- How to Click One Cell and Highlight Another in Excel
- How to Highlight Cells Based on Text in Excel
- Highlight Cells That Contain Text from a List in Excel
- How to Highlight a Column in Excel
- How to Highlight Active Row in Excel
- How to Highlight Row If Cell Is Not Blank
- How to Highlight Row If Cell Contains Any Text in Excel
- How to Highlight Every 5 Rows in Excel
- How to Highlight from Top to Bottom in Excel
- How to Highlight Cell Using the If Statement in Excel
- How to Compare Two Excel Sheets and Highlight Differences
- Excel VBA to Highlight Cell Based on Value
- How to Highlight Text in Text Box in Excel
- How to Highlight Selected Cells in Excel
- How to Highlight Text in Excel
- How to Highlight Cells in Excel Based on Value