Excel KPI tutorial 4: Less clutter, more info

Simon Hurst completes his management dashboard tutorial series with a look at effective visual presentation techniques.

In Part 2, just before our PowerPivot dashboard interlude, we looked at Excel charts. The next challenge is to harness their obvious flexibility to the need to keep charts simple to ensure they communicate the figures clearly. This final part of our KPI dashboard tutorial series will explore some of the latest features that can help in this respect.

If you are going to be presenting information graphically, it’s worth considering how to make your graphics as effective and informative as possible. Whether or not you agree with all of his conclusions, Edward Tufte’s book: ‘The Visual Display of Quantitative Information’ highlights many issues around effective use of charts and could well inspire you to make the most of Excel’s graphical capabilities. We’ll cover the practical application of a few of Tufte’s points here.

Less D, more clarity

Some of the available chart types in Excel can be very tempting. For example, the Cone format is more dramatic than a simple column chart. Although the cones might add a bit of visual interest, this is at the cost of being able to easily see the exact values involved. Even switching to straightforward 3D columns doesn’t completely solve the problem. With a boring old 2D column at least we know our onions are 20,000.

In fact, we can go further to simplify our chart by removing all the unnecessary elements (or ‘chartjunk’ as Tufte would call it) and just display a small-sized line chart to illustrate data in the dashboard.

Sparklines

Tufte called these minimal charts “Sparklines” and Excel 2010 incorporated a Sparklines section in the Insert ribbon tab. This allows you to select a block of data and then create a set of charts in a column or row of cells that represent the values in each row or column.

The Excel 2010 Sparklines can be set up as lines or columns or as simple win/loss diagrams which show only the differences between positives, zeros and negatives.

Other ExcelZone management reporting tutorials

• Excel 2010 KPI tutorial 1: Conditional formatting
• Excel 2010 KPI tutorial 2: Simplify your charts
• Excel 2010 KPI tutorial 3: Dashboards
• Excel 2010 KPI tutorial 5: PowerPivot tips
• Excel 2010 KPI tutorial 6: Conditional format icons
• Applied Excel: Building a traffic light KPI dashboard (2008)
• Build a dashboard with Excel 2010 Sparklines
• Presenting financial figures with Excel: Charting choices
• Applied Excel: Building a traffic light KPI dashboard (2008)
• Build a dashboard with Excel 2010 Sparklines
• Presenting financial figures with Excel: Charting choices
• 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...