Conditional Formatting in Excel

Conditional Formatting is, as its name says, the possibility to format (meaning changing the style, color, etc... of text, cells) automatically a cell depending of what this value in this cell is.

Let's say, you are rolling a dice and writing the numbers in Excel.

For Even numbers you want the cell to be Blue and for Odd numbers you want it to be Orange.

If you roll the dice 1000 times, then changing the color each time manually would be very tedious. That is where Conditional Formatting intervenes.

Here this example with the table containing the rolls (only 20 for the example). The table is horizontal here just for reading purposes. Putting the results vertically could be better if you are interesting in filtering the data on a later stage.

dice roll

Now we want to select the CONDITIONAL FORMATING button in the HOME ribbon and we select the Highlight cells rules where the TEXT contains...

Select all the results.

conditional formating

The following window opens:

text that contains

Select CUSTOM FORMAT...

custom format

 

Press OK then OK again.... and this first change will appear.

dice roll

 

Repeat the operation and enter the letter o instead of e, change the colour to orange and you will get.

dice roll

 

This was a very basic Conditional formating.

Vertically this would look like following and you can see that I changed it into a table so that I can now filter it if I want.

vertical dice rolling

 

Apple farm

You can see that for our tree counting, we used another Conditional Formatting which is the Data Bars. In the column E, the blue bars represent the number of apples.

Isn't this a great way to visualize numbers!!

conditional formating

 

If you wanted to created THREE (3) colors for the apple quantity, Green over 600 apples, Orange between 200 and 599 and Red under 200, then you would have to create a new rule. In that case the FORMAT ONLY CELLS THAT CONTAINS part.

Before that you have to SELECT the Cells you want to format. E5 to E12.

FORMAT ONLY CELLS THAT CONTAINS

Then enter the minimum value and maximum value.

Select the FORMAT... you want the cells to take.

format conditional formating

And press OK.

REPEAT this 3 times....

rules in conditional formating

To finally get:

apple tree

 

You can change apply other rules and get slightly different results:

format all cells based on their values

Or this one

icon sets

To finally get

apple tree

 

 

So that's it for the conditional formatting. 

These were only some basics. Now you will learn by doing.