Guidelines:
- Use a consistent structure for similar content.
- Maintain consistent orientation.
- Have consistent styles throughout the workbook.
- Terminology is used consistently.
- Highlight inconsistent formulae in a block.
Using consistent structure and style makes a workbook easier to understand and use.
Consistent structure across worksheets
Where worksheets have related content, ensure that they use the same columns. Using different columns for similar content makes the spreadsheet difficult to understand and leads to errors.
For example, suppose that we have workbook that has a set of financial accounts. The worksheet 'BalanceSheet' has year 2024 in column C and 2025 in column D. Therefore, on worksheet 'CashFlow', mirror the column structure by putting year 2024 in column C and 2025 in column D. It is common to see structures like the year 2024 in column C on one sheet and column D on another sheet. That's just asking for trouble.
Consistent formulae within a block
Blocks of formulae are easier to understand and change when the formulae are consistent. Some errors, such as an incorrect reference, may be more obvious if they deviate from the consistent structure.
Be very suspicious of any cell within a block that differs from those around it without good reason. For example, a cell that has a hard-coded value, but is surrounded by formulae, is especially suspicious and is likely to be wrong – either now or later.
A common process is to create a formula in the top-left cell of a block, then copy the formula to all cells in the block. The formulae may change from cell to cell as relative references change, but the formulae all have a consistent structure.
Any exceptions – which usually occur at the edges or corners of a block – should be demarcated by borders or highlighted via formatting such as font colour or cell shading. Highlighting inconsistent cells reduces the likelihood that an inconsistent formula will be copied to the other cells in the block. The rationale for inconsistent formulae should be clear, and possibly documented with a cell comment.
For example, as shown in the image, the cells B9:B13 all have the same formula (other than relative ranges), of the form B9: =B8*(1+$B$5).
The first formula is an exception, B8: =B4. We highlight the exception using a different font colour and shading, to make it obvious that this cell differs from those in the same column. By highlighting the exception, we make it less likely that someone will drag the formula in B8 down to the other cells or drag the formula in other cells to replace B8.
Consistent orientation
Avoid having a block of the same or related content arranged across columns in one place and down rows in another place. A change in orientation suggests the need for a better design.
An exception may occur where data are imported from an external source and needs to be transposed for further analysis. The change in orientation should be documented.
Consistent style
A workbook should have a consistent style throughout. That is, apply consistent heading styles, font sizes, colours, and other formatting to all worksheets. Using the Styles feature can help maintain consistency. Applying a consistent style across all workbooks in an organisation is even better.
Any variations in style should be documented along with a rationale – for example, due to requirements for reporting to an external organisation (such as a regulator).
Consistent terminology
Consistent terminology should be used throughout a workbook, for ease of understanding. Inconsistent terminology often occurs when different people have worked on distinct parts of a workbook. Review the workbook to ensure consistency.
If acronyms, abbreviations, or jargon are used, then consider whether a glossary or list of definitions is needed. Even terminology that "everybody knows" can inhibit comprehension, if not managed well.
