We looked at using conditional formatting to present data in the first article in this tutorial series. This time we are going to build on a technique I first saw in a presentation by Bob Phillips at an Excel User conference. That was back in the days before Excel 2007 when conditional formatting was nowhere near as flexible as it now is.
Excel 2007 introduced built-in icons for conditional formatting, if you wanted to achieve a similar result in Excel 2003 you had to create the icon and conditional format manually. We can achieve this by including the symbols in a column adjacent to our numbers, then change the font colour of that column to white. This makes all the symbols invisible. A conditional format, based on a formula that refers to the adjacent values, then changes the font colour when the condition is satisfied. This has the effect of displaying a coloured version of the chosen symbol according to the conditional format formula.
As we’ve just mentioned, this sort of thing is much easier in Excel 2007 and 2010 where icons can be added directly to value cells and, in the previous article in this series, we saw that PowerPivot in Excel 2010 also includes its own KPI icon sets.
A recent Any Answers question looked for a way of displaying a symbol in a 6x6 matrix depending on the value in cells elsewhere in the spreadsheet. One solution would be the conditional formatting with invisible characters approach, with each cell in the grid displaying the same symbol and the conditional format formula making only the symbol in the required cell visible. This method has the advantage of allowing you to choose any symbol you like, but might require use of a complex formula referring to the relevant cells.
In the example that follows the cells are formatted as white font on a white background. A conditional format formula checks our row and column values and where there is a match applies a green font format. Here is the formula:
In Excel 2007 and 2010 an alternative approach would be to use one of the icon set conditional formats and enter formulae in the grid cells themselves. Here we have used a double minus to adapt the formula slightly to coerce the results to 1 or 0 rather than True or False in order to make the conditional format formula easier:
In Excel 2010 we can choose the icons individually and set any icons we don’t want to use to “No Cell Icon”. In both Excel 2007 and 2010 we can choose to “Show Icon Only”. This should produce a similar result to the white on white approach.
Excel 2007 lacks the ability to choose each icon individually which means it is less easy to avoid the other icons. Of course, you could try and set the conditions so only one can possibly be true for your set of data, but for the icon set rules the third rule is set automatically depending on the middle rule to ensure all possible values are dealt with.
Other ExcelZone management reporting tutorials
KPI tutorial 1: Conditional formatting
KPI tutorial 2: Simplify your charts
KPI tutorial 3: Dashboards
KPI tutorial 4: Less clutter, more info
KPI tutorial 5: PowerPivot tips
Applied Excel: Building a traffic light KPI dashboard (2008)
Sales analysis at Northwind Traders
Improve your reporting skills with self-teach tutorials
Interested in Pivot Tables? Start here
About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.