Excel: Conditional Formatting – Color Max Value in Range

CF_IQuestion: can you tell me how to color the maximum value in a cell range with conditional formatting? Yes I can and i will show you how it is done. Simple and effective. First open Excel and create a range with random numbers not sequentially but in random order. Follow the next steps:

– Select the range of cells you would like to apply the conditional formatting on
– On the Home tab click on the button Conditional Formatting (see pic. on the left)

Take a look at the various options to choose from. That are some fine options to choose from. We might be thinking about using the =MAX function for the question. However i think that i have a better solution to offer you. Click the second option from the menu Top/Bottom Rules. That little black arrow on the right is an indicator that another menu will appear. From that menu choose the first option (as shown below…)


See those three dots at the end of Top 10 Items… well that indicates that a dialog window will open. From that dialog change the default settings from 10 to 1 and choose your coloring. The image below shows the default setting an the setting after we have customized it.


Now all we have to do is click OK and your formatting will be applied on the selected range. The highest value should now be colored. The great advantage of Conditional Formatting is that it is dynamic. So if a number changes and that becomes the highest number the appropriate cell will be colored and the previous one will be white again.

CF_IVNow this is one way to do the Conditional Formatting for this question. You can also choose to add a new rule yourself. In that case choose New Rule… from the menu. You will see the next dialog pop up. Choose the third option from that dialog and apply the same settings (top 1 – color) and click OK.


That is all there is to it! Simple, effective and colorful.
Homework: how to apply conditional formatting to the minimum value in a cell range?

Happy coloring 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s