Show Menu
Cheatography

20 Excel Spreadsheet Best Practises Cheat Sheet (DRAFT) by [deleted]

Best practises creating spreadsheets

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Introd­uction

Many spread­sheets are created with little thought given to design, with inadequate or non-ex­istent docume­nta­tion, with no integrity checks, by people who have had little or no training in their use. A few high-p­rofile, costly spread­sheet errors hit the headlines from time to time, but also a vast number of less specta­cular problems occur throughout businesses of all shapes and sizes.

The purpose of these Twenty principles for good spread­sheet practice is to help reduce the amount of time wasted, and the number of errors caused, by businesses (including accoun­tancy practices) as a conseq­uence of the way they and their employees use spread­sheets.

The spread­sheet’s business enviro­nment

1. Determine what role spread­sheets play in your business, and plan your spread­sheet standards and processes accord­ingly
If you have spread­sheets that play a key or critical role in your organi­zation, ensure that they are developed and tested, managed and monitored to an approp­riate level. Spread­sheets that form part of an organi­zat­ion’s key business processes will need to be managed differ­ently from ad hoc spread­sheets for short-term use by an indivi­dual.

2. Adopt a standard for your organi­zation and stick to it.
This might be one that is developed in-house, or adopted from outside and shared with other organi­zat­ions. A common standard within an organi­sation facili­tates collab­ora­tion, aids unders­tanding and saves develo­pment time. The standard should include, among other things, consistent conven­tions on use of cell format­ting.

3. Ensure that everyone involved in the creation or use of spread­sheets has an approp­riate level of knowledge and compet­ence
For anyone designing, developing or mainta­ining (as distinct from just using) a spread­sheet, this will include: awareness of the range of functions available, clear unders­tanding of such basic concepts as relative and absolute cell refere­nces, and an apprec­iation of the importance of carefully checking the results of functions.

4. Work collab­ora­tively, share ownership, peer review.
The extent of collab­oration and review needed will depend on the size and complexity of your organi­zation and of each project.

Spread­sheet risks and controls

17. Have a system of backup and version control, which should be applied consis­tently within an organi­zat­ion. The approp­riate levels of backup and version control will depend on the organi­zation and the nature of the work, but there should always be, at the very least, a reliable means of preser­ving, identi­fying and restoring earlier versions of a workbook.

18. Rigorously test the workbook
The level of testing required will depend on the size, complexity and critic­ality of the workbook, with riskier workbooks needing a greater degree of indepe­ndent testing.

19. Build in checks, controls and alerts from the outset and during the course of spread­sheet design.
These checks might include, for example, tests to ensure that a balance sheet balances, assets do not depreciate below zero, and so on. One approach would be to build in a set of audit tests to check validity and use flags to signal compliance or non-co­mpl­iance. Use a master flag to summarize all the individual flags and place it promin­ently (on the output sheet, or even throughout the workbook eg, on sheet headers) so that users are bound to see it.

20. Protect parts of the workbook that are not supposed to be changed by users.
The level of protection will vary according to the nature of the spread­sheet and the kind of use/users it will have. It might include locking whole worksh­eets, all cells containing formulae, or everything except designated input cells.
 

Designing and building your spread­sheet

5. Before starting, satisfy yourself that a spread­sheet is approp­riate tool to use.
Spread­sheets are not best solution for every problem. Lot's of time will be wasted when another applic­ation would be more approp­riate. The more approp­riate tool might be a word processor (if it’s a table of text), a database (if processing large quantities of similar data items) or an existing software package (if it’s is a well-e­sta­blished processes, such as bookke­eping). Even if a spread­sheet is still the right answer it’s worth looking for existing templates before starting a new one from scratch.
6. Identify the Audien­ce.
If a spread­sheet is intended to be understood and used by others, the design should facilitate this. If the only ‘audience’ envisaged is yourself, you might perhaps justify less explan­ation and help. Even so, good docume­ntation is helpful if you come back to a spread­sheet a while after you created it; and many spread­sheets come to have a much wider audience than originally intended. Ensure that adequate instru­ctions, validation and help are included to promote ease of use and avoid input errors. Even if parts of a spread­sheet are ‘locked’, keep calcul­ations visible.
7. Include an ‘About­’/‘­Wel­come’ sheet to document the spread­sheet
Include basic inform­ation as author, purpose, version number, and descri­ption of general approach. Also include explan­ations of color codes and other formatting conven­tions, any sources of input data (with, where approp­riate, hyperlinks to the original data), and any macros and what they do. The more complex the workbook, or the more it needs to be shared, the greater the requir­ement for good docume­nta­tion. Conver­sely, a simple
8. Design for Longev­ity.
Design spread­sheets to adapt to any reasonably forese­eable future changes in values (tax rates, etc) or volume (eg, items in a dataset) of data used in calcul­ations. However, the need for adapta­bility should be balanced against following the Agile principle of ‘The simplest thing that could possibly work’.
9. Focus on the required outputs
Work backwards: be clear about the purpose of the spread­sheet, what outputs achieve that purpose and therefore what inputs and logic are required to derive the outputs.
10. Separate and clearly identify inputs, workings and outputs.
A properly structured spread­sheet will be easier to understand and to maintain. If pivot tables are used, it may be possible to relax this principle, but clarity remains crucial. Design to ensure that any input should be entered only once.
11. Be consistent in struct­ure
Use the same columns for the same things in each workbook, especially when working with time series. A consistent convention within a workbook reduces the risk of error where one sheet refers to another. For example, a common convention is that time flows horizo­ntally from left to right (and a specific column is always ‘Year 1’) and calcul­ations flow vertically from top to bottom. Such a structure will help to avoid circular refere­nces.
12. Be consistent in the use of formulae
On any worksheet use the smallest practi­cable number of different formulae. Where it is necessary to use different formulae, ensure that groups of cells using the different formulae are clearly separated.
13. Keep formulae as short and simple as practi­cab­le.*
Shorter formulae are easier to build (less likely to contain errors) and easier to unders­tan­d/r­evi­ewed. Stage a calcul­ation through multiple cells rather than building comple­x/long formula.
14. Never embed in a formula anything that might change or need to be changed.
Instead, put such values into separate cells and reference them. This ensures that values enter the spread­sheet only once, and if change is needed would happen in just one place. It also allows for all formulae cells to be locked without denying access to input values.
15. Perform a calcul­ation once and then refer back to that calcul­ati­on.
Do not calculate the same value in multiple places (except perhaps for cross checking purposes). This reduces risk of error, is more efficient, as fewer calcul­ations are being performed.
**16. Avoid using advanced features where simpler features could achieve the same result. In partic­ular, avoid using progra­mming code unless necessary – in which case ensure that it is clearly documented within the code itself, as well as in a docume­ntation worksheet. Similarly, avoid circular refere­nces, and control and document any except­ions. Do not change the software’s key default settings (for example, do not turn off automatic recalc­ula­tion) unless essential, in which case include a prominent message to warn users.