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