Conditional Formatting: Colour Me a Rainbow

Excel cells can be just so….. dreary and overwhelming at times when they are filled only with boring old numbers and word descriptions, right? Sometimes, all we need to break the monotony and help our eyes see better is some color.

For example, wouldn’t it be great if cells automatically turned red when they hold values signaling poor performance, shouting out to us “Warning! Warning! Please pay attention to me!”?

Excel actually has a fantastic built-in tool for this called Conditional Formatting. For years, it was one of the many buttons on my Excel ribbon that looked so intimidating, and hence daunting, to attempt to learn how to use it.

But for those of you who have the same hesitations as I had, fear not! It really is quite a simple and beautiful tool to master and incorporate into your excel reports.

Let’s begin.


Step 1. Click on the Conditional Formatting button

You will see an array of options available to you.  

Excel Dropdown list from the Conditional Formatting button

My advice is to ignore all the other options and go straight to the “New Rule” option. It provides step-by-step instructions to set an applicable rule, making the process less confusing.


Step 2. Select the “New Rule” option

A window should pop up. Here, you see a variety of rule types that you can set.

Excel Conditional Formatting "new Rule" window pop-up

For the sake of simplicity, I will only cover my favourite rule and format style here. Don’t worry, the overarching logic and functionality of the rules and format styles remains the same across the board. So once you have figured out how to work with one rule, the rest should be a piece of cake.  


Step 3. Set up your applicable rule

Step 3.1. Pick the Format all cells based on their values rule and choose the Icon Sets format style.

Step 3.2. Set the value range you want to work with under the Value option.

Step 3.3. Click on “OK” and viola!

What I have essentially done is that I have set a cell to reflect a colored circle based on the value it carries. With my rule set-up below, a green circle will be shown if a number more than or equal to 1 is reflected in the cell, an orange circle if it is between 0 and 1 and a red circle if it is a negative figure.

As you can see from the drop down list under the Type option, you are not restricted to just numbers. You can set your value range to work with percent, formula or percentile instead. Lastly, if you do not want any figures to appear in the cells together with the circles, remember to click on the little box that is accompanied by the text “Show Icon Only”.

Excel Conditional Formatting new rule description set-up

That wasn’t so painful, was it?


Step 4. Make changes

Once you have set a rule, you can easily edit it directly through the drop down options that are presented when you click on the Conditional Formatting button. I prefer to go via the “Manage Rules” option instead, just because I find that it gives me a clearer step-by-step editing approach. It’s all a matter of personal choice really!  

Excel Conditional Formatting default rule options

Now go color your Excel sheet!