"We are in an age of rapid change where advances in technology are having a profound impact on the profession. And yet spreadsheets appear as important as ever, a constant presence for decades in the world of business, playing a pivotal role in the sharing and presenting of information. 

ICAEW’s Excel Community is testament to the value that Chartered Accountants place on spreadsheets, but while they play a crucial role in our profession, they are also fraught with challenges. Lack of consistency, incorrect formulas and well-publicised spreadsheet errors all conspire to give them a poor reputation for quality. 

Our Twenty Principles for Good Spreadsheet Practice were developed as an answer to some of these challenges, seeking to reduce spreadsheet risk through the adoption of a common set of principles in the design, build and management of spreadsheets. 

I would like to thank all the volunteers from our Excel Community Advisory Group and beyond who have given their time in the development and update of these principles, and encourage readers to take on board and apply the lessons laid out below to their own spreadsheets and those in use across their organisations."

- Alan Vallance, CEO, ICAEW

 

1. Determine how much your organisation depends on spreadsheets and plan your approach and processes accordingly.

Determine which spreadsheets relate to key organisational processes and will attract a higher level of risk. It is those spreadsheets where closest adherence to the principles is likely to be the most beneficial. For example, spreadsheets used for client work or integral to regulatory processes will pose a greater risk due to the reputational, financial, and legal impact of any potential errors. Key spreadsheets should be developed, tested, managed, and monitored to an appropriate level of risk.

 

2. Adopt a consistent methodology for spreadsheets in your organisation.

A common approach within an organisation facilitates collaboration, aids understanding, and saves development time. The approach should include, among other things, consistent conventions on the use of cell formatting. For example, use Cell Styles and Themes to easily apply an organisational standard formatting and colour scheme to your workbook. 

 

3. Ensure designers and users of spreadsheets have appropriate knowledge, competence, experience, and awareness of functionality.

Ensure everyone using a spreadsheet has an appropriate level of knowledge and competence to be able to use the spreadsheet effectively. Those designing spreadsheets should have sufficient experience and awareness of available functionality in order to select an optimal approach. 

 

4. Determine if a spreadsheet is a suitable tool.

A lot of time can be wasted, and errors caused, by using a spreadsheet when an alternative application would be more appropriate. For example, use a word processor if it is a table containing text, use a database if large quantities of similar data items need processing, or use a dedicated software package to undertake well established processes, such as bookkeeping. 

The ability of the spreadsheet developer and user(s) to understand the data and algorithms well enough to build and maintain a solution for current and future requirements also needs to be considered to determine if a spreadsheet is still the most appropriate tool. 

 

5. Focus on the purposes of the spreadsheet.

The purpose for a spreadsheet is usually self-evident: a calculation needs to be performed; data needs to be analysed; or a record needs to be kept. However, also be aware of any downstream processes that rely on the outputs of the spreadsheet. By focusing on purpose, the design of the spreadsheet can be limited to the scope of the requirements. A leaner, more targeted spreadsheet will enhance the quality of the outputs and lessen the risk of error. Remove worksheets and data that are no longer required to ensure focus is given to the relevant parts of the spreadsheet. 

 

6. Tailor the design and documentation to the needs of the audience and dependent processes.

Not everyone will understand the details and context of a spreadsheet as much as the designer. When designing a spreadsheet put yourself in the position of those who will be using it, assuming limited prior knowledge. Ensure context is communicated clearly through the way inputs and outputs are presented. Make the spreadsheet as self-explanatory as possible, using documentation to supplement the understanding and convey any inherent assumptions and logic. 

 

7. Make the spreadsheet easy to use, providing supporting guidance where helpful.

A well-designed spreadsheet should require minimal instruction, but must include as much guidance as is needed for any sufficiently capable user to understand its operation and, where appropriate, maintain it. This may take the form of an 'Overview' or 'About' sheet (to include details such as the owner, purpose, and version, as well as any over-arching conventions specific to the workbook), but may also be annotations throughout the spreadsheet. Any macros should include comments to support the traceability of the code; queries and connections should also be named and annotated clearly.

 

8. Design with adaptability in mind to minimise future manual intervention.

Design spreadsheets to adapt to foreseeable future changes and requirements. Examples include changes in values (tax rates, etc) or volume (eg, items in a data set) of data used in calculations. Construct formulas so that inserting new values – either by inserting new rows or by adding to the bottom of the existing data – will not require any amendments to the formulas. This could be done with functions that use ranges allowing blank space for new data, or by using Excel’s Table feature. 

 

9. Structure workbooks with a clear flow of inputs, processes, and outputs.

A properly structured spreadsheet with a clear and logical flow of inputs, processes and outputs will be easier to understand and to maintain. Good design ensures that any input is entered only once, and as far as possible all inputs are entered in the same section of the spreadsheet. Processes should be functional, easy to follow and auditable. Outputs must be clear and easy to comprehend.

 

10. Seek to control, clean, and optimise data quality and structure prior to using them.

The control and use of inputs, whether they are database connections, CSV files, web scrapes, other spreadsheets, or any other source, is a critical spreadsheet success factor. 

Cleaning and reshaping inputs into a structured data format, using tools such as Power Query, provides a firm foundation on which functions and features can operate consistently. 

Optimising data quality and structure in this way, removes manual effort, simplifies the subsequent formulas and features required, and significantly reduces the risk of error. 

 

11. Structure worksheets to avoid inconsistent logic and layout.

A consistent framework across spreadsheets, that is supported by appropriate templates, can reduce development time, and make collaboration easier. 

Within each workbook, the more consistent the structure, the easier and simpler the formulas will be, and the easier others will find it to understand. Complex workbooks with multiple worksheets should, where possible, follow the same basic layout and use the same set of formulas, to aid navigation. 

 

12. Keep formulas consistent across ranges.

Minimise the use of different formulas. Familiarity with basic techniques, such as the use of “$” signs to fix references, is crucial in achieving this. Dynamic array formulas can also reduce the number of different formulas required and enforce the consistency of a formula across a range.

 

13. Use the simplest features, functions, or formulas for the task.

Write formulas so they are easy to understand and review. Complex calculations should be staged through multiple cells. To make them easier to read, complex multi-step formulas can be spaced within cells, including line breaks.

Advanced features and techniques should only be used where capabilities of simpler options have been exhausted. For example, Power Pivot/DAX should only be considered once standard formulas and PivotTables have been fully investigated. Likewise, macros should be a last resort because of the specialist knowledge required to maintain them and the security challenges they can pose.

Consider organisational culture and employee skill set to determine which features are deemed to be simplest. 

 

14. Avoid using fixed values within formulas.

Place any fixed values into separate cells and reference them within a formula. 

Building spreadsheets in this way allows for formulas to be locked, reduces risk, increases transparency and ‘explainability’, and removes subsequent manual effort.

 

15. Refer to existing calculated results instead of reperforming the calculation.

Avoid reperforming the same calculations in multiple places. Perform the calculation once and then refer to it in larger and more complex formulas. Use formulas that allow the allocation of values and calculations to variables, such as LET and LAMBDA. This reduces risk of error, and is more efficient, since fewer calculations are being performed. 

Sometimes calculations need to be reperformed to avoid circularities and for cross-checking purposes. These instances should be exceptions.  

 

16. Collaborate in design and review processes. This ensures accuracy, robustness, and the mitigation of risks.

The extent of collaboration and review needed will depend on the size and complexity of your organisation and each project, and the risks associated with the spreadsheet. Leverage built-in collaboration features offered by the spreadsheet tool to facilitate the sharing of workbooks, allowing peers to work together and offer their input in real-time. Provide reviewers with the ability to suggest changes directly within the spreadsheet. 

It is important for the spreadsheet owner to monitor changes. The edit history should support the design and review processes and be open to any suggestions made.  

 

17. Build in checks, controls, and alerts from the outset.

There are natural balances in the arithmetic of financial spreadsheets, such as total sales being the same whether sliced by region or product, or that the trial balance nets to zero. Building automatic checks as part of the spreadsheet functionality will avoid the need for multiple, time-consuming reviews every time the spreadsheet is updated. Checks should be summarised sheet by sheet as well as at whole workbook level, so that any errors arising are easily seen and can be quickly traced to the source. The use of conditional formatting on these checks can further aid visibility of errors. 

 

18. Test and review to reduce the risk of error and identify inefficiencies.

Spreadsheets should be tested and reviewed to reduce the risk of error and identify inefficiencies. The level of testing performed will depend on the size, complexity, and criticality of the workbook. Riskier workbooks typically need a greater degree of independent testing. Self-review is limited in how many errors can be identified due to the bias of reviewing one’s own work. To truly improve the chances of catching mistakes in a spreadsheet, a system of peer review is recommended.  

Testing could include:  

• adjusting inputs to see if the change in outputs matches expectation;
• testing extreme or impossible values (for example a negative quantity or a non-numeric value for a numeric input) to see how the spreadsheet operates; and 
• double-checking the results of key calculations systematically. 

 

19. Ensure there is a regular process of backup and version control. This minimises loss of work and avoids the use of the wrong version.

Workbooks saved in SharePoint/OneDrive or Google Drive automatically benefit from features such as AutoSave and Version History. 

For spreadsheets saved locally or on network drives, ensure files are saved regularly and a consistent naming convention is in place when dealing with multiple versions. Previous versions could be moved into a subfolder so there is only one version of the file in the parent folder. This would mean that there is no ambiguity over which file is the latest version. 

 

20. Manage access levels and protection to reduce the risk of accidental or malicious changes.

Protecting spreadsheets is essential to maintain control and manage sensitive data. The level of protection will vary according to the nature of the spreadsheet and the kind of use or users it has. For example, spreadsheets where downstream operations depend on a specific structure should restrict the users' ability to modify that structure. Protecting all formulas helps to avoid introducing errors. It also ensures any formula changes are only made in collaboration when intended. 

Protection can be applied at multiple levels, including workbook, worksheet, and cell ranges. It can be inclusive (protecting specific elements that shouldn't be changed) or exclusive (protecting everything except specific elements that can be changed).  

Consider whether:

• other metadata such as file properties also need protection; and
• the spreadsheet tool supports restrictions on a named user basis (specific people or groups being given levels of access), or on a password basis - and if password, how this will be managed (who will know the password, how secure it is and so on).

Data privacy regulations should also be considered when dealing with spreadsheets containing sensitive data. Spreadsheet owners should be aware that many forms of spreadsheet access management or attempts to conceal sensitive data within a spreadsheet can be circumvented by a sufficiently competent user. 

Previous version

• Twenty principles for good spreadsheet practice (2018 edition)