Traffic light management reporting with Excel conditional formatting

This question was fired at me the other day: "Can I use Excel to colour-code my numbers like traffic lights?". That's an easy one - if you know how.

For this one the two steps we took were:

1. Setting triggers to 'fire' red, green or amber, based on the numbers being reported
2. Applying conditional formatting and colour-coding cells based on the results of the trigger.

Setting the triggers to fire

In the example below we used?three different methods to get the triggers to fire. You could pick the one that's most familar/ easiest for you. If, Vlookup and Index (combined with Match) can all do the trick. Scroll to the bottom for a link where you can download the example used here.

Assigning conditional formatting

Setting the conditional formatting is easy (if you know how). First select the cells you want the formatting to apply to.

Then go Home > Conditional Formatting > New Rule > Format only cells that contain and a box should pop up.

Under "Format only cells with:" select "Cell value", "equal to" and then select the cell with the first condition "Green" in it.

Next assign a solid fill colour to the cell (with a white bold font to stand out against that). Once it's set up you'll get a preview of the formatting as shown in the box above.

Finally you'll need to repeat the exercise to assign the red and amber formatting (you can apply multiple conditional formats to cells).

A quicker way to colour code cells

You can colour code cells even more quickly if you wish (although the first method above gives you maximum flexibility and transparency). Select the cells you are interested in and, under "Conditional Formatting" select the colour scale you want:

Some warnings about conditional formatting

Here are some good modelling practice guidelines and warnings for conditional formatting:

• Conditional formatting is memory hungry so don't use too much of it. Don't for example apply it across a whole tab. Just apply it to the cells you really think you need to format.
• Sometimes you'll find it a bit tricky to copy your conditional formats across different cells. You can copy and paste conditional formatting like normal cell formatting, but you may find you end up with multiple conditional formats in your spreadsheet, all chewing memory. For this reason, you may wish to wait until you're at the end of your work before applying your conditional formatting.
• Separate out all the different elements as we did in this example (the names of the "Green", "Amber", "Red" conditions and the 2.5 and 3.0 values at which they trigger). That way you can vary them later. Don't bury these values inside the conditional formatting where it becomes hidden in the spreadsheet.
• A text-based trigger like "Red" combined with a solid fill colour gives max visibility when a condition is breached. Just shading e.g. the value 3.5 with a red font colour does not make the breach stand out quite so clearly.

With conditional formatting the big point here is: "Have fun with it, but don't have too much fun with it!". Conditional formatting has?maximum impact (and minimum problems) when it's used sparingly.