Make your data talk to you

Conditional Formatting

One question I get asked a lot is how can I get Excel to flag up when I've spent too much / my stock is low / I've reached my targets, etc. The answer is conditional formatting.


Conditional formatting does exactly what it says. It applies formatting to the cells in your spreadsheet when certain conditions are met. In Excel 2003 and earlier versions, the conditional formatting was quite basic and difficult to use, but from 2007 onwards it is easier to use with much improved functionality.

There are lots of ways to use it.

• Apply it to your monthly budget sheets to flag up when expenditure exceeds a certain limit.
• Use it to identify overdue invoices
• Find the highest or lowest values in a range of cells
• Use it to highlight duplicate records
• Use traffic lights or other symbols to track performance against targets

So how does it work?

On the Home tab, about halfway along, you will see the Conditional Formatting button. Click on it to see a drop-down menu of conditional formatting functionality.

The first option is Highlight Cell Rules.  Use this to highlight any cell that meets your criteria.  This could be a value greater than, equal to, lower than or between whatever values you set.  It could also be a text string, a date before or after your defined date or a duplicate value.  You then choose what format to apply.  There are a few built-in formats (e.g. light red fill, dark red text) or you can define your own.

The second option is the Top/Bottom Rules.  Here you will be presented with six options: Top 10, Top 10%, Bottom 10, Bottom 10%, Above average and Below average.  Don't be put off if you want, for example, the top 3 items or the bottom 25% - you have the opportunity to change this once you have chosen the appropriate option.

Next is Data Bars.  This is particularly useful if you have a column of data as it creates the appearance of a bar chart in your cells.  This can give a table of numbers much more visual impact, making it clear at a glance how the numbers relate to each other.

The next one is Color Scales.  Here you can choose the colour to represent the values at either end of your range, and Excel will apply colours to each cell that fade through from one end to the other, so that you can see where any item is on the scale.

When the data is jumbled up this can look a bit messy as you can see on the left, but when it is sorted into order it becomes much clearer as on the right.

Finally we get to Icon Sets.  This allows you to apply icons such as flags, ticks and traffic lights to the data based on the values so you can see if your targets are being met.  

Initially Excel will split the data equally between the icons, but you can change the criteria. However, your targets might be based on particular values rather than an equal split. The Manage Rules option at the bottom of the Conditional Formatting menu will allow you set your own criteria.

Conditional Formatting is fun to play with and can make your data much easier to interpret if you use it carefully.

Go on and have a go!

And don't forget if you want help with Conditional Formatting or with any aspects of using Microsoft Office, book a training session with NIMBUS solutions.