Excel KPI tutorial 4: Less clutter, more info

Columnist
Share this content
9

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

 

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.

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

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)

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)

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)

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)

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)

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)

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)

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)

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)