The ubiquity of spreadsheets is not on the wane: businesses of all sizes continue to use spreadsheets for myriad reasons, from simple list management to modelling complex scenarios. The informal and flexible nature of a spreadsheet is what makes it such a popular tool, but this flexibility does not automatically translate into quality.

It is up to the creator of a spreadsheet to build a tool that is reliable and seamless to use. And it matters: a poorly constructed spreadsheet can have costly implications. In this article we discuss why spreadsheet quality matters so much, which principles you should follow when constructing a workbook and how you can ensure your spreadsheets are fit for purpose, going forward.

Why spreadsheet quality matters

The quality of a spreadsheet is of little consequence if it is merely used as a simple list. On the other hand, where a multi billion dollar infrastructure project is modelled using a spreadsheet the financial implications can be immense. Vast amounts can be lost if a spreadsheet contains errors or if a spreadsheet leads to erroneous interpretations.

Several factors compound this issue. Quality is important even if a spreadsheet is used only once, in a discrete environment. But in most cases, spreadsheets have a broad and long-lasting scope and often spreadsheets are utilised by a range of users:

Spreadsheets are sticky

It is not uncommon for the same Excel sheet to be used for business-critical decisions year-in, year-out. Spreadsheets are also commonly passed onwards and forwards via staff changes and business acquisitions. It is worth getting it right the first time if a spreadsheet will be in use by multiple users for a decade or more.

Assumption of correctness

Users, especially those who are less familiar with constructing complex spreadsheets, often assume that spreadsheets are intrinsically accurate. Spreadsheet users will rarely check results and will use spreadsheets in unexpected ways. That is why spreadsheets should not only function correctly but also be robust to a degree.

Dramatic consequences

Incorrect or risk-prone spreadsheets can undermine even the most carefully planned projects. Complex spreadsheets act as financial pillars, weaknesses can rapidly erode the financial prospects of a project or even lead to complete business failure. Compromising on spreadsheet quality leads to real horror stories.

Like many design and development projects involving computer tools, the ‘quick and dirty’ approach can be appealing, especially if at first it looks as if just one person will be making use of the finished product. But it is unwise to take this approach with a spreadsheet. Instead, anyone building a spreadsheet should always focus on quality, throughout.

Broad spreadsheet quality principles

The factors that make for a quality spreadsheet involve both broad principles and specific, technical points. We first examine some of the broader principles you should bear in mind when building a robust, high-quality spreadsheet.

Spreadsheets should be user-friendly

No degree of quality or robustness will prevent errors if there is any risk that a user will misconstrue your spreadsheet. Clarity is paramount, and users should not be in any doubt about how your spreadsheet works. Build a user-friendly spreadsheet and you reduce the risks that user error will lead to incorrect results.

Make it easy to audit your spreadsheet

While users may never look at the mechanics of a spreadsheet, someone who audits its workings will. It all comes down to computational simplicity, elegance and avoiding shortcuts that will be hard to understand by future analysts. You may even find yourself in the position of modifying or auditing a spreadsheet you create years go.

Robustness, reliability, flexibility

Just like any computing application your spreadsheet should behave robustly against a degree of user impatience, misunderstanding, and misuse. At the same time, your spreadsheet should be malleable to accommodate changes in future requirements. Avoid constructing a workbook that is too inflexible and rigid.

We’ve outlined some of the factors you should keep in mind while building a quality spreadsheet, but how do these principles reflect in the nitty-gritty of building a spreadsheet?

How to get it right

Staying mindful of spreadsheet quality at the planning stage is key, but the construction of your spreadsheet is equally important. We won’t list all of the technical good practice points that are pertinent – however see the next section for a good resource from the ICAEW. Nonetheless, these are some of the key technical points that are core to spreadsheet quality:

Consider your layout

Complex spreadsheets are often used by teams, which could include new members. The way you plan your spreadsheet can make it dramatically easier for new users to understand how a spreadsheet works. Make navigation simple, include clear user guidance and label appropriately. Avoid hiding cells. Also focus on clearly separating spreadsheet inputs, calculations, and spreadsheet outputs.

Avoid hardcoding

Some numbers can be sensibly hardcoded, the number of weeks in a year, for example. Even then, the purpose of a hardcoded number should be immediately obvious to a user. Other values that have any chance of changing – such as tax percentages – should never be hardcoded into formulas. Instead, make it as easy as possible for non-experts to adjust important variables when needed.

Minimise nesting

Typing expansive formulas into a single cell will initially save you time but it will make unwinding calculations more difficult. Many, simple, clear formulas are far better than a few complex, nested formulas. A simple but long formula is not necessarily complex, but a long formula that contains many nested calculations can spell trouble ahead. Instead, consider breaking up a complex formula into shorter, clearly labelled formulas.

Codifying spreadsheet quality

With so many factors affecting spreadsheet quality, it is understandable that a need for a codified guide emerges. Just as accountants can produce mutually intelligible reports, so spreadsheet modellers can produce workbooks that are easy to understand.

Quality spreadsheets are not proprietary to any single company, consultancy or vendor. In fact, experienced spreadsheet users such as Operis have always followed spreadsheet good practice to develop usable, robust and auditable spreadsheets for decades.

That said, in the UK the ICAEW recently published a Financial Modelling Code which outlines both broad and technical principles that are core to producing quality spreadsheets. Operis co-founder, David Colver, was involved in compiling the ICAEW Financial Modelling Code and we think the document provides an important baseline.

What about existing spreadsheets?

In this blog we’ve outlined some of the steps you need to take to ensure that your spreadsheet meets expectations now, and in the future. But what if you are faced with a spreadsheet you did not build? The author’s experience in developing spreadsheets may make matters easy for you, or you may find yourself in a sticky position.

Tackling an existing spreadsheet in a methodical manner, simplifying as you go, is the logical approach. Also, deploying a tool such as the Operis Analysis Toolkit (OAK) will help you untangle and audit an existing spreadsheet. Nonetheless, you are somewhat at the mercy of the creator of the spreadsheet.

In conclusion, we want to emphasise how important spreadsheet quality is particularly when it comes to the future users of a spreadsheet. Taking a little bit of extra time and effort at the outset can make a significant difference for future users and indeed determine whether the spreadsheet does the job it is intended for.