Conditional formatting in Excel is an extremely useful tool, enabling the user to quickly and easily create a visual impression of the data. It is extremely versatile allowing the user total freedom on the method and style of the formatting. The format of the cell itself changes dependant on the parameters set by the user so the data and the visual aid is in one place. Sometimes a list of numbers does not show the trend as well as a visual aid. Sometimes a visual aid such as a graph does not show the detail of the data. Conditional formatting can add coloured bars, coloured scales or coloured icons to show if the number is a good, bad or average and combines the visuals with the data to give a simple yet complete picture.

Begin by creating a range of numbers and labels in Excel. This example will use a simple list of sales staff and their monthly sales figures. In column A is the sales person name and in column B is the value they have sold in the current month in pound sterling. Highlight the full data range and go to the Home tab. There is a conditional formatting tool button and when you click it several options will appear.

The first option is 'Highlight Cell Rules.' This will allow the user to format a cell that has a particular kind of value. For example a value between two set values, duplicate values, a value greater than a set value etc. In our example, the fonts can be formatted to highlight all values under a particular sales target for example. The purpose of the formatting would be so the viewer can see at a glance, the specific values they require. Just as specific values can be formatted, the next option is to format the Top/Bottom rules to change the way a value/cell in the top or bottom ten percentile appears. This might be useful when attempting to identify the consistently poor sales performers in a given month. Other functions such as above and below average can be used to find sales people who stand out in relation to the average.

Each of these options allow further rules to be set if the parameters are more specific. Say for example the user wanted to highlight all duplicate values in a list. Perhaps there is an issue over the quality of the data and several customers have been added more than once. The name range could be formatted to highlight in red all duplicate names in the list. For advanced formatting requirments a formula can be used to decide which cells are formatted based on number of set parameters. Excel attempts to give the user a list of the more common formatting solutions so even novice users can make use of the functionality, while giving advanced users the option to make elaborate formatting decisions on the data.

These types of formatting are about highlighting data to specific requirements, but the other side to conditional formatting is the Data bars, Color Scales and Icon Sets. If you highlight the range of data, choose conditional formatting and then hover over Data Bars, a range of options including gradient or solid fill will appear. Hovering over a particular option will give a preview on your live data so you can see exactly what the finished format will look like. This formatting appears like a bar graph, but each bar of the graph is in the cell itself with the relevant data. The user can immediately see the high and low bars to indicate which numbers are the highest. Once again there is a More Rules section where further tweaking can be carried out such as removing the numbers to leave a graph style and changing the colours of the bars.

As well as Data Bars there are Color Scales. This option fills the entire cell with a colour which is representative of the value. For example, if the person with the most sales will have a green cell, the person with the least sales will have a red cell and all other values will have an automatic colour somewhere in between. The better an employee is performing in relation to his/her colleagues, the greener the cell will appear. This is where a combination of formatting may be useful since it may be that some sales people are better than others, but nobody reached their target. This colour scale could compare all sales people, but an additional formatting rule could show any values in bold that did reach a target. Colours are not limited to standard green to red scales, but can be shades of one or several colours specified.

Icon sets are used in a similar way, but show a specific coloured icon as the performance indicator. A user could show green to red flags or circles, ticks and crosses or an arrow system to indicate the rating. There is even a segmented black and white circle. When considering which icons to use, it is worth looking at which ones the user will be able to look at and instantly understand. Think about the type of work the viewers do. Arrows may indicate movement to a financial group instead of a simple top and bottom indicator and this may cause confusion.

As previously noted, conditional formatting can be combined if required, although this can look overly fussy. A data bar with an icon next to it is not necessarily showing the user anything additional. Instead it can look confusing since trying to show the same thing in two different ways on top of each other can detract from the point. If more than one conditional formatting rule is combined, be sure that both show different points in a clear way. To remove the formatting choose Clear Rule and clear the rules from the selected cells.