Subscribe to Podcast

Subscribe on iTunes
Subscribe on Stitcher
Subscribe on Google Play
Subscribe on Spotify

You are a busy VA – that’s a given! A client has asked you to show her what her data means. What is the top value in the list of sales? Can you see values that meet a condition? Conditional Formatting will show the way.

What is Conditional Formatting?

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.

Conditional formatting

  1. Select the data
  2. Click on Conditional Formatting
  3. 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.

Conditional Formatting

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 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

Conditional Formatting


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

Conditional formatting


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

Conditional Formatting


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!

How did you find this tip? How will you use it? Leave me a comment to let me know.

To learn more about Excel you can take one of my Excel Courses, there is one for Windows and One for Mac.

Find them here : Courses

Or get in touch if you would like to work with me one to one!