What is Conditional Formatting?

Shelley Fishel Fishel Author

Conditional formatting is exactly that, a format applied that matches a condition.

There are 5 kinds of Conditional Formatting to choose from:

Highlight Cells Rules - pick this to show cells that match a particular number/text value/date.
Top/Bottom Rules - pick this one, for example, to find the Top 10, Top 10%, Bottom 10 or Bottom 10%.
Data Bars - these shade the cells based on top and bottom values, so the greater the value the more shading in the cell. You can pick the colour scheme to suit.
Colour Scales - use Colour Scales to apply a scale from smallest to largest and set what each colour means.
Icon Sets - Use Icon Sets to show crosses and ticks, traffic lights etc as symbols in the highlighted cells.

Create your own rules

You can also create and apply your own rules to automatically change the formatting of cells based on the contents.

Where does Conditional Formatting live?

You will find Conditional Formatting on the Home Ribbon. To use it, first select the cells you want to apply the condition to and then choose the type of condition to set, followed by the format you would like to see.

Select the data
Click on Conditional Formatting
Select the type of formatting

Once you have decided which of the formats to use, you can then drill down and specify the exact format you want.

For example, if I want to see Total Sales at Koffee Island above £900 I will choose the Highlight Cells Rules and then Greater than, adding in the amount I want it to look for and setting the format.

Have a play with all the different types of Conditional Formatting, you will be amazed at how easy it is to spot patterns and get information from your data when you can visualise it.

Change the background fill of a cell

Your executive or client has asked you to create a workbook or worksheet for her. She wants the background colour of some of the cells to change based on the text typed in or a number entered. For this you will need to create a custom rule.

Use a formula to control conditional formatting

To add conditional formatting to show a range of colours – I have a list of rooms names Red Yellow and Green and I would like the fill colour to change according to the name of the room. This is handy if you need to keep a visual track of room bookings for example.

Change the colour fill

I applied three formats to the cells one on top of the other.

If A2:A10 contain the word Red apply Red formatting

If A2:A10 contain the word Yellow apply Yellow formatting

If A2:A10 contain the word Green apply Green formatting

Apply the first condition

Select the cells to which you want to apply the conditional formatting and then click on Conditional Formatting and select New Rule.

Format cells that contain

In my example, I selected Format only cells that contain

Then picked Specific Text from the drop-down list

Added the word Green in this case

Set the format by clicking Format and going to the Fill tab to pick the colour.

Repeat for the other conditions

Go back and add the other two rules to the selected cells. Remember to change the criteria for each accordingly.

Now test your rules! Type one of the words into one of the cells that you selected when you started and watch the background colour change!

Have you used Conditional Formatting? If not will you start to use it now?

To bring your Microsoft Excel Skills up to date and learn how to use more of the functionality, get my course Essential Excel Skills for The Savvy Assistant which is just one of the five Essential Courses included in Essential Office for The Savvy Assistant.

You can also sign up for my weekly newsletter - and get my Outlook Keyboard Shortcuts tip sheet here - Weekly News.

Categories: : Blog, Microsoft Excel