Conditional Formatting Tutorial – Excel 2007 & 2010

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.

Excel 2007 Conditional Formatting Tutorial 1

 

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

Excel 2007 Conditional Formatting Tutorial 2

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.

Excel 2007 conditional formatting tutorial 3

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.

Excel 2007 conditional formatting tutorial 4

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.

Excel 2007 conditional formatting tutorial 5Click okay and your table should change to show the formatting.

Excel 2007 conditional formatting tutorial 6As 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

Excel 2007 conditional formatting tutorial 7

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.

Excel 2007 conditional formatting tutorial 8

As you add each rule it will appear along with your original rule.

Excel 2007 conditional formatting tutorial 9Continue to do this until you’ve added your full set of ranges.

Excel 2007 conditional formatting tutorial 10

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

Excel 2007 conditional formatting tutorial 11As 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.

Excel 2007 conditional formatting tutorial 12Here 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

, , , , , , , , , , , , , , , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment