Spreadsheet Hell

We've all inherited a nightmare of an spreadsheet that has links all over the place, multiple nested IF statements along with a few INDEX, MATCH and OFFSET formulas just for fun.

Don't get me wrong - I love Excel and the power it provides to gather, wrangle, analyse and then visualise data. There is, however, a very high risk that your spreadsheet has an error in it. According to a study by ACCA 90% of spreadsheets have an error in them, and 90% of the owners of the spreadsheet don't think there are any errors.

While there are plenty of other reporting solutions out there, let's face it lots of us will continue to use Excel. Which got me thinking about the tips, tricks and techniques I've gathered over the years to minimise the risk of errors.

Framework

There are two main spreadsheet standardisation efforts which provide an attempt at standardisation (I've only seen a handful of organisations actually use them though).

Spreadsheet standards review board (SSRB) and the FAST Standard Organisation. They both offer a very comprehensive framework and are attempting to standardise, globally, the use of Excel. Quite a lofty task! There are some good pointers in there, some of which I've incorporated into my use of Excel.

For the framework to be of use though, it has to be used consistently across the organisation and this is, in my opinion, the biggest hurdle. Unless a comprehensive training program is undertaken then you'll be fighting an uphill battle.

Tips, Tricks and Techniques

• Design the structure prior to starting development of the file. Jot the structure out on paper
• Don't cram 10 formulas into the one cell. You won't be billed per cell used so you don't have to have a 10 line formula; split it up as it will be easier to understand (and find an error!)
•  Don't use array formulas unless absolutely necessary. I've used them in the past, and regretted it - in particular when the workbook is being used by other people. Trying to explain the CTRL+SHIFT+ENTER to get the formula to work can be frustrating, as simple as it sound. There will be another way to get the solution you're after
• Multiple nested IF statements. These can be a real nightmare to get your head around if they are too deep
• I always include a cover sheet and contents sheet (simple listing of all the worksheets with a brief description). Put key information on the cover sheet - e.g. short description, author (with contact details), document control if required
• Use a version number. Put this on the cover sheet and then reference it on each worksheet in the header or footer
• Error checking - there's always a cross-check that can be done, do it and thank yourself later. Do this for each worksheet and then have a summary error checking worksheet where you'll easily see for the entire workbook if there are any errors
• Use cell labels (cell names) but keep the names as short as possible, without losing meaning. Using labels will make your formulas easier to understand
• Use colour coding for input cells, output cells, hard coded values, formulas. This will make it clear to the user of your workbook what is required. The colour legend can be kept on a separate worksheet after the cover page
• Don't hard code numbers in formulas, ever. One of the most difficult errors to find is the hard coded number when you've said to yourself "I'll go back and fix that before next month-end". Believe me, you'll forget the hard coded number is there and be searching for hours. Put it in a new cell. You can even have a separate worksheet for all 'assumptions' used in your workbook

These are just a few of the tips and tricks I use. I'm interested to see what other people are using to avoid getting into spreadsheet hell, or restore an inherited workbook that's currently hellish.