The Conditional Formatting tool in Excel is an extremely useful part of the application. When reporting figures in the format of tables full of numbers either to individuals or groups, it has long been recognised that a visual representation will help to get the meaning of the numbers across. The value in one field is 65, but is that good or bad? It usually depends on what you are comparing it to. Conditional formatting provides a simple way to show a comparison between figures in a table or the meaning behind the figures without having to spend time compiling a full report with pages of pie charts and fancy bar graphs.

The tool allows the use of data bars, colour scales or icon sets. While rules can be set up to show these according to specific criteria the user decides, they can also be used across an entire column of data to show a simple comparison. When this is the case, how should you determine which one is correct for the type of data you are presenting? This article explores this issue through the use of some simple yet common sales data scenarios.

Data Bars
Imagine presenting a table of sales data to a group of senior managers with the purpose of deciding which of the sales people are bringing in the most cash. You have managed to crowbar them away from their busy lives and are now showing them a list of people with their sales figures written out neatly in an excel spreadsheet. You do not feel comfortable showing a pie chart as there are other figures involved such as the number of sales which may influence the final decision so a table is best. The numbers can stay, but in order to quickly show which sales members are really getting the results, you can use the conditional formatting tool to put simple comparison bars on each row. This will give an instant visual representation alongside the figures.

You can simply highlight the column you require, click the conditional formatting button and select Data Bars, then choose a gradient or solid fill. This essentially makes your table into a bar chart and the size of the bars directly relate to the data in the column. It is particularly easy to use because no rules are required. It is far clearer to use the bars for this type of comparison instead of icon sets or colour scales since it is a simple comparison between the figures with no attached rules. The largest bar wins, but the variation between the people is still clearly given. Using colour scales may confuse the issue since the colour scaling would need to be explained and the meaning may be less apparent.

Colour Scales
Colour scales are a simple comparison, but should be used on simple data. For example, having one column with the sales values per person, may mean you are about to fire the person that brings in the least amount of money. If you were to use the icon sets, the column of data would be filled out in various degrees of colour which as a whole can be more ambiguous and will hinder the experience for anyone who is colour blind!

Unless you order the data by that column then there will be various colours all over the column. These will range from bright red for the lowest figure to bright green for the highest figure. Because the colours are in various shades it can muffle the point you are trying to make. When using colour scales for the entire column, it is best to have only a small table of data with a simple point to make.

Icon Sets
Icon sets are for the black and white, no grey areas in between kind of data sets. A small symbol will be put next to each number with, for example, red, green or yellow in a sort of traffic light system. There is no gradient with this one, excel will look at all the values given and decide the higher numbers are green, the middle ones are yellow and the low numbers are red. They should therefore be used on data sets where a yes/no or good/bad answer is required.

In the sales value example, it may be that the top sales people stay and the bottom ones do not. In that case the icon sets give an immediate show of who is bringing in the money, who is trying and who isn't. It is worth remembering that these will not show any other factors that may be taken into account unless you start to use this functionality with rules. It therefore will not show that Bob had a bad month because he was ill, but previously he has been your best man.

Obviously this example is very simplified, but the point still stands. Icon sets will show the good, the bad and the ugly as it were, so use them only for this purpose and not when a sliding scale comparison is required or when other factors not shown in the data does need to be taken into account.