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.
Download Practice Book
You can download the free Excel template from here and practice on your own.
3 Quick Methods to Highlight Highest Value in Excel
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.
➥ First, select the data range.
➥ Click Home > Conditional Formatting > New Rule
A dialog box named “New Formatting Rule” will open up.
➥ Select ‘Format only top or bottom ranked values’ from the ‘Select a Rule Type’ box.
➥ 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.
➥ 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.
➥ Now just press OK
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.
➥ Select the whole values from the dataset.
➥ Click as follows: Home > Conditional Formatting > New Rule
A dialog box will appear.
➥ Press ‘Use a formula to determine which cells to format’ from the ‘Select a Rule Type’ box
➥ Type the formula in ‘Format values where this formula is true’ bar as given below-
➥ Then press Format tab
The ‘Format Cells’ dialog box will open up.
➥ Select your desired fill color and press OK.
I have chosen the green color.
➥ Now press OK again.
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.
➥ Type the given formula in Cell D5–
➥ Hit the Enter button and use the Fill Handle tool to copy the formula to the other cells.
➥ Activating Cell E5 write the formula as given below-
➥ Then 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.
➥ 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.
➥ After that select the chart type as you wish from the ‘Recommended Charts’ option and press OK.
See, the chart is showing the highest value with a different color.
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.