ICAEW.com works better with JavaScript enabled.
Exclusive

Excel community

What could possibly go wrong?

Author: Simon Hurst

Published: 07 Jun 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Taking advantage of the recent lockdown relaxation, we were able to take a short, and long-postponed, holiday last week. In the gaps between eating and drinking, we managed to enjoy some longish walks.

For one such walk we invested in one of the many walking guide pamphlets that provided instructions for half-a-dozen local walks. It was certainly true that following the guide it was possible to complete the walk without getting lost. However, our encounters with other, somewhat puzzled, walkers who were using similar guides suggested that success was far from guaranteed. The problem with this particular guide was that it provided no information regarding one of the first junctions. You were fine if you stayed on the main path and recognised that the guide didn't mention that junction specifically. However, it was easy to mistake the junction for the next one in the instructions which would have led to you wandering off in exactly the wrong direction. Perhaps the writer of the guide should have thought not only about the manoeuvres that you needed to make, but should also have considered all the possible paths that you could have taken, and then included instructions to make sure you only took the correct ones.

Landscape

Obviously, spreadsheets are never far from my mind, and I quickly realised the relevance of the walking guide issues to spreadsheet design.

First of all, any consideration of spreadsheet design is probably fairly rare. Most spreadsheets are probably closer to 'stream of consciousness' than being meticulously planned. Even when design and structure are considered, the thought process is probably similar to that of the walking guide: "How could I create a spreadsheet that allowed me to come up with the correct answer to my problem?". For simple spreadsheets, that are definitely only going to be used by their creator over a very short timescale, this might not be too disastrous an approach, but for a spreadsheet that could be used by multiple people, over an extended timescale, the question should be closer to: "How could I create a spreadsheet that would ensure that anyone that uses it could not possibly come up with the wrong answer?".

Looking at the design of the spreadsheet from this point of view demonstrates the importance of many of the 20 Principles for Good Spreadsheet Practice. For example:

You can use the Excel Community article archive portal to display links to all of the articles in the 20 Principles series:

 

Allow Microsoft Cookies

This content is provided by Microsoft Power BI. We ask for your permission before anything is loaded, as they are using cookies and other technologies. You may want to read Microsoft's privacy policy before accepting. To view this content, please choose Allow all cookies.

We'll expand on each of these three principles in turn.

Principle 14

Entering an input figure, such as a VAT rate, as part of a formula could indeed give the correct answer at the time of entry. However, far from making it impossible for our spreadsheet to generate the wrong answer, it would mean that, should that VAT rate no longer be appropriate, our spreadsheet will end up producing the wrong answer without the user doing anything, and with the cause of the error being invisible unless the user happens to examine the cell in question. Even if our user is aware of the need to change the input figure, to do so they need to edit a formula cell making it all too possible to corrupt the formula itself.

Principle 19

Checks, controls and alerts might not be necessary for a spreadsheet to generate the correct answer, but they can play a significant role in ensuring that potential errors are identified, highlighted and corrected. Also, bad spreadsheets are not just ones that produce misleading results, but also those that waste time and effort through inefficiency during creation or use. By building these controls in from the start, it is much easier to identify design problems at an early stage and to isolate problems to recent changes, rather than having to examine the entirety of a complex spreadsheet to find out where the problem is.

Principle 20

Of course, there are many other design considerations that arise from thinking about your spreadsheets defensively. For example, the use of Data Validation to help ensure that users enter the right type of data and even the use of Power Query to dramatically reduce the number of cells that contain formulae.

So, unless you want to inflict on your users the spreadsheet equivalent of wandering, in increasing desperation, across a desolate mire, in dense fog, it might pay to try and design your spreadsheets to make missteps impossible.

Is that the distant baying of an enormous hound that I can hear?

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250