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

How to design better spreadsheets

by
6th Apr 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

It's the age-old curse of Excel - users end up building ever-expanding spreadsheets because they did not stop to think about how they were going to collect, manipulate and present their information first.

ExcelZone contributor Simon Hurst refers likens this bottomless pit to the "grimpen mire" that claims the lives of several sad souls in 'The Hound of the Baskervilles'. He's not the only expert to be concerned about spreadsheet bloat.

At New Zealand's University of Canterbury, professor John Raffensperger has applied an academic's rigour to the subject, and produced an influential set of guidelines for writing spreadsheets.

Raffensperger has been working in this field for several years. The article ExcelZone found was published in 2000, but since it deals with principles rather than the latest Excel technologies, we felt it was important to point readers in its direction.

As the author puts it, "spreadsheets contain errors like a dog has fleas". The key problem is overconfidence on the part of designers. "Never assume a spreadsheet is right, even your own' You cannot error-proof a spreadsheet. Errors are too easy to add. You can only be careful."

The recommended approach is to a spreadsheet as you would any other computer program and break it up into a modular format based around your data, formulas, and output.

The full article provides some interesting pointers and rules to improve your design capabilities, for example by organising your data and the formulae so that the "arc of precedence" flows down and to the right. And where one data dimension is likely to be more numerous than another, but the longer series into rows - again reflecting the way people tend to read.

The key points are summarised in Raffensperger's spreadsheet checklist - both it and the full article are recommended reading for any aspiring Excel guru.

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By listerramjet
11th Apr 2006 12:51

oh dennis!
"But isn't the real issue that professional accountants don't have the training to perform developer tasks, however much a project is broken down? What about documentation?"

Spreadsheets are useful but risky, and you are correct in your assertion that accountants are not developers - but how on earth will documentation help them in spotting the errors they have created? As I recall, accountants find it difficult enough to document the basics of client systems! Understanding spreadsheet risks, and learning some basic design principles sounds like a much more effective route, and surely more preferable to not using them at all?

Thanks (0)
Dennis Howlett
By dahowlett
11th Apr 2006 05:41

But isn't the real issue
That professional accountants don't have the training to perform developer tasks, however much a project is broken down? What about documentation?

Thanks (0)
avatar
By AnonymousUser
11th Apr 2006 12:30

What about documentation?
The key is creating "just enough" documentation. That's a piece-of-string that depends on who other than the author is going to use and modify the spreadsheet, and the relative skill levels expected.

I deal with this in my book 'Spreadsheet Check and Control'. Here is an excerpt:

"Keep documentation close to where it used so that it can be updated along with what it refers to. Otherwise, neglected documentation or cell comments may become actually misleading. Section 1.1.2 on page 11 shows how to use cell comments to store information that may be needed by the user at the time of data entry.
Place in a separate Instructions worksheet the user instructions and explanatory notes on calculations and dependencies on external data sources. "

Thanks (0)