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.
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:
- 14. Never embed in a formula anything that might change or need to be changed
- 19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design
- 20. Protect parts of the workbook that are not supposed to be changed by users
You can use the Excel Community article archive portal to display links to all of the articles in the 20 Principles series:
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?