Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel KPI tutorial 4: Less clutter, more info

by
13th Jan 2012
Save content
Have you found this content useful? Use the button above to save it to your profile.

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

First of all, here’s an example how how to achieve greater clarity through simplicity. Some of the available chart types in Excel can be very tempting. For example, here we have opted for the Cone format instead of a simple column chart:

Excel 2010 Cone chart option

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:

Excel 2010 3D bar chart

We’ll compare these two approaches to the boring 2D column approach:

Excel 2010 bar chart

Perhaps it’s a less exciting demonstration of Excel’s charting capabilities, but 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):

Size does matter

Not only is the above chart clearer than the 3D options, it can also be made much smaller with the detail still remaining easily visible. If you can make the graphic smaller without losing information, it allows you to show more information in the same physical space. This can greatly increase the power of your graphics by allowing your users to make comparisons and correlations much more easily than if they have to go from sheet to sheet or page to page to see each chart.

Tufte suggests taking this approach much further. If the purpose of the chart is simply to show a trend or comparison, then the individual figures may not matter. This allows for the chart to be simplified further:

This approach probably makes more sense if we are looking at a trend over time and the use of a line chart. Excel 2007 and 2010 include this minimalist type of chart as one of the line chart layouts in the Chart Tools, Design tab:

Excel 2010 line chart layout options

Although there is no such option in the column or bar chart layouts, there is a trick to quickly achieve the same layout. Change your chart to a line chart and apply the minimal line chart layout, then change the chart type back to the column or bar. You should find that the chart ‘remembers’ the line chart layout option:

Minimal bar chart option

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:

Excel 2010 Sparklines KPI dashboard

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

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.

Replies (9)

Please login or register to join the discussion.

avatar
By User deleted
13th Jan 2012 10:49

Heat maps ? .....

Useful way of showing large amounts of data (generally 3rd party)

But - http://how.best-free-information.com/2009/04/how-to-create-a-heat-map-in-excel/

 

Thanks (0)
avatar
By SimonP
19th Jan 2012 22:50

Broken links

I wanted to check out the "Other ExcelZone management reporting tutorials" only to discover that only KPI tutorial 1, KPI tutorial 2 and KPI tutorial 3 links work.

3 out of 7 ain't bad, I suppose.

Thanks (1)
Simon Hurst
By Simon Hurst
20th Jan 2012 12:11

3 out of 7 ain't bad...

That was by Meat Loaf wasn't it? Thanks for pointing that out - I'm sure John will be on the case shortly.

Thanks (0)
Replying to Kent accountant:
avatar
By SimonP
24th Jan 2012 01:52

You're almost right ...

It was "Two out of three ain't bad".

Meat obviously felt that a 66.666% success rate was better than nothing. On that basis, I'm not sure that he'd have made a good auditor.

On the other hand 2 out of 7, or 42.86% is definitely nowhere near as good.

However, I am extremely pleased to report that it is now 7 out of 7 as the links have been fixed. Merci.

 

shurst wrote:

That was by Meat Loaf wasn't it? Thanks for pointing that out - I'm sure John will be on the case shortly.

Thanks (0)
Replying to cheekychappy:
avatar
By jono136
02nd Feb 2012 14:55

You're not almost right ... way off

Not sure that equating 2 out of 7 to 43% (forget the spurious accuracy) would make a good auditor either!

Thanks (0)
Replying to Mitch:
avatar
By SimonP
02nd Feb 2012 16:29

Fat fingers wins again

Thanks for picking up on the msiytpe. Of course it was 3 out of 7 = 43% (rounded) as previously stated.

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
20th Jan 2012 13:04

Links fixed - sorry

Our apologies again for misfunctioning links. There's an automated mechanism that doesn't like the way I paste in old links to new items and it keeps catching me out. I'll make sure to double-check next time to stop this happening again.

Thanks (0)
avatar
By datakeyword
05th Mar 2012 07:07

I have read "Tap Utmost Value of Excel" just now

 I had met some complex analysis, and found that Excel can only process simple KPI computation, so someone suggested me a free power tool, named esproc.

It's a good helper to Excel I think.

Some information about it:

Excel® is the most widely-used spreadsheet tool. The nontechnical persons love to use it for computation and analysis though, they usually find the formulas and functions available in Excel® are rather poor and the VBA® is just double Dutch to them for further analysis. Thus, a huge volume of data with valuable information has been wasted in vain.

esProc is introduced to better the situation. Empowered esProc users can tap the utmost value of Excel® by taking the esProc advantages of powerful computation ability, agile and easy-to-use analysis style, and programmed running mode.

---------------------------------------------------

Check http://www.esproc.com/library/product/tap-utmost-value-of-excel.html for  more details.

Thanks (0)
avatar
By User deleted
05th Mar 2012 08:44

Sounds like an advertisement ....

So why not come clean and declare an interest instead of some covert '.. so someone suggested me a free power tool..'

Just make a disclosure

Thanks (0)