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.


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


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.


» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.


Heat maps ? .....

JC | | Permalink

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/


Broken links    1 thanks

SimonP | | Permalink

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.

shurst's picture

3 out of 7 ain't bad...

shurst | | Permalink

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

John Stokdyk's picture

Links fixed - sorry

John Stokdyk | | Permalink

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.

You're almost right ...

SimonP | | Permalink

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.

You're not almost right ... way off

jono136 | | Permalink

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

Fat fingers wins again

SimonP | | Permalink

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

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

datakeyword | | Permalink

 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.

Sounds like an advertisement ....

JC | | Permalink

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