Conditional Formatting is the practice of formatting cells in Excel spreadsheets to change cell appearance depending on certain conditions.
It can be extremely useful for immediately highlighting certain parts of a spreadsheet so that your attention is drawn to them – for example when a value goes over a certain threshold, or a date passes its deadline.
In this tutorial we’ll show you how to use conditional formatting to change the colour of cells depending on a percentage.
Why not check out our Top 10 Excel Time Saving Formulas for more helpful tips.
Conditional Formatting Tutorial – Colour Coded Data
Here’s our example data. We’ve used the 2011 market share figures for smart phone operating systems worldwide.
As you can see the data is already in numerical order but if you were to add a forecast or update the spreadsheet with each new set of figures then the numbers would presumably change. Once you have a great deal of data a good way to see at a glance who is doing well is to implement colour. You can do this manually but a better solution is to implement conditional formatting to do this for you as you go. The conditional formatting menu is found under home>styles>conditional formatting. This tutorial is for Excel 2007 but the process is still largely unchanged on Excel 2010
Conditional Formatting Pre-sets
As you can see Excel has a pre-set list of colour scales which will automatically define a set of rules and implement them. This isn’t the best option as your data ranges could change dramatically with each new update.
Creating New Rules
The better way is to create a New Rule (or click More Rules in Colour Scales). A New Formatting Rule window will open, select ‘Format only cells that contain’. This allows you to choose your own parameters for the rule.
Your first step towards implementing your conditional formatting is to input the first set of rules. In our example we start with our lowest range – we’ve decided to use 0-9.9 to categorise the results in chunks of 10%. Input your lowest value and highest value and pick a colour – we’ve decided on ascending Red to Green so have inputted 0 and then 9.9 and picked red for the lowest rank.
Click okay and your table should change to show the formatting.
As you can see this has only affected the lowest figures so now we need to change the rule to include the other 10% bands. Click on the Conditional Formatting tab and then ‘Manage Rules’.
Managing Rules
A window will appear that shows your current rule. You need to add to this using ‘New Rule’. The ‘New Formatting Rule’ window will appear so follow the same steps as earlier to add new rules.
As you add each rule it will appear along with your original rule.
Continue to do this until you’ve added your full set of ranges.
Click apply and your table will change to reflect the new rules in place. The data is now all colour coded to show how well each OS is doing in terms of market share.
The finished product
As there is only one set of data and it is already numerically ordered the formatting isn’t very necessary but once you start adding more data you can see the role it plays.
Here we’ve added the 2015 forecast for mobile OS market share as an example of how the table changes with new data. The table is no longer numerically ordered and so conditional formatting allows you to distinguish between results at a glance. Windows Mobile was languishing at 3.8% in 2011 but is predicted to overtake iOS and Blackberry and has gone from red to yellow.
This is just one made up example of how to use conditional formatting and there are many other ways to use it – as well as distinguishing between sets of data and using colour coding to rank stats there are also organisational benefits. If you set up an Excel spreadsheet as a to-do list you could set the date for a task to change colour once it has passed and include a box that changes colour depending on whether the task is incomplete, in progress or complete.
If you are interested in learning more have a look at our Beginner Microsoft Excel Courses and Advanced Microsoft Excel Courses.
Tags
Advanced Excel Training, conditional formating tutorial, conditional formatting, excel, excel 2008, excel courses, excel help, excel hints, excel tips, excel top tips, excel training, hints, how to, how to in excel, microsoft excel, microsoft excel training, microsoft office, microsoft office excel, quick excel tips