Excel 2010 KPI tutorial 1: Conditional formatting

Simon Hurst returns to the scene of a previous management reporting tutorial to see how he can improve the presentation using new facilities in Excel 2010.

With the advent of PowerPivot and the graphical enhancements in Excel 2007 and 2010, Microsoft is positioning Excel as a “self-service business intelligence” tool. If you are going to be presenting key performance information to your colleagues or clients, it’s worth paying particular attention to presenting the information clearly and with maximum impact. It would be a shame to spend days collecting and collating the vital figures and then fail to present them in a way that provokes appropriate action.

For this first part of the series we are going to revisit some data we looked at almost exactly three years ago in a previous KPI reporting tutorial:

Here we used the conditional formatting in Excel 2003 to help focus attention by using a simple “traffic light” colour coding for the sales values.

This time we will use one of the new types of conditional format that Excel 2007 introduced – the data bar.

Other ExcelZone management reporting tutorials

• 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.

Continued...