Select Page

Irrespective of your profession or job, you may require to highlight maximum & minimum value in a list of data. A teacher may want to highlight marks of students who stood first in the class; similarly a salesman may like to know highest and lowest order value booked by him

Today we will learn tricks to highlight maximum & minimum value in Excel using conditional formatting

Suppose, we have to highlight maximum & minimum number in the data in range “B4:O4” as shown here below:

Select the range

Option: 1

Select the data, in this case select range “B4:O4” and go to

HOME > Style > Conditional Formatting > Top/Bottom Rules > Top 10 items > More Rules….

A new window will open as shown below:

Pop-up window for formatting

Select all or as required from the fill color, border style and font style to highlight the top or bottom value

Option: 2

We can also achieve required result using formula with “MAX” and “MIN” functions in conditional formatting. For this select the range from which, you want to highlight highest or lowest number and go to

HOME > Style > Conditional Formatting > New Rules…. > Use a formula to determine which cells to format

A pop-up window as shown below will open to update the formula and format to highlight the cells

Pop-Up window for formatting rules

Now, select the “Top” from drop-down button, if you want to highlight “Highest” value and “Bottom” to highlight the “Lowest” value. To highlight both the values, you have to repeat the process as explained here

Update the adjacent box with “1” as shown above and click “Format….” To set the highlight rules in the new window opened as appearing here under:

Pop-up window to insert formula

To highlight highest value in the range “B4:O4”, select the range with “B4” as active cell and update the below formula in window opened as above

=B4=MAX(\$B\$4:\$O\$4)

To highlight lowest value, replace the “MAX” with “MIN” in above formula as shown below:

=B4=MIN(\$B\$4:\$O\$4)

After updating formula click the “Format” and update required formatting details as explained in Option 1 given above

Still have some un-answered questions, ask through comment to this blog post. We are happy to help 🙂

“The extremists are afraid of books and pens, the power of education frightens them. They are afraid of women.”

― Malala Yousafzai

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!