ICAEW.com works better with JavaScript enabled.
thought leadership

20 principles for good spreadsheet practice

The ‘Twenty principles for good spreadsheet practice’, is a list of key spreadsheet considerations that could save you hours of effort or help prevent a career-ending spreadsheet catastrophe.

Preface by Alan Vallance

"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

Strategy and Plan

 

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. 
Principle 2
Principle 2:

 

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. 

Design and Build

 

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.
Principle 7
Principle 7:

 

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.
Principle 9
Principle 9:

 

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.
Principle 12
Principle 12:

 

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.  

Control and management

 

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.  

Principle 16
Principle 16:

 

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. 

Principle 20
Principle 20: Please note the web version of Excel has been used in the screenshot to demonstrate the principle

Download 20 Principles workbook template

The template is designed as a simple first stage in implementing the 20 Principles. It includes sheets that document details of the workbook's contents and version history.

On demand Excel series
20 principles of good spreadsheet practice

PDF (102kb)

Download the 20 principles summary.

Download

More from ICAEW Tech

Resources
Keep up-to-date with tech issues and developments, including artificial intelligence (AI), blockchain, big data, and cyber security.
Technology

Keep up-to-date with tech issues and developments, including artificial intelligence (AI), blockchain, big data, and cyber security.

Read more
Elearning
Finance in a Digital World - support for ICAEW members and students on digital transformation and technology
Finance in a Digital World

ICAEW has worked with Deloitte to develop Finance in a Digital World, a suite of online learning modules to support ICAEW members and students, develop awareness and build understanding of digital technologies and their impact on finance.

ICAEW Community
Abacus
Excel

Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Join the Excel Community.

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