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 – choose this, 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 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
Suppose 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
Let’s look at adding conditional formatting to show a range of colours – let’s say I have a list of rooms named Red, Yellow and Green and I would like the fill colour to change according to the name of the room. This is handy if, for example, you need to keep a visual track of room bookings.
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! Magic!
Just think of how else you can use this – to highlight expiry or renewal dates, to flag where stock quantities exceed or drop below a given level, or to show duplicates in a list. I’m sure you can think of other examples. And what’s even better, these flagged areas of your sheet will stand out from the rest so they aren’t overlooked.
Have a look at your data and see how this can help you. I’m sure you’ll find lots of ways.
To learn more about Microsoft Excel and how you can speed up routine tasks, take a look at my Microsoft Excel courses for Windows and Mac.