Spreadsheet good practice guidelines
Organizing workbooks
Most workbooks are poorly organized – being a random jumble of data, formulae, charts, PivotTables, etc. With the lack of a coherent design, it is difficult to know where to start, what to change, and which things are important. Sound familiar?
But it doesn't need to be that way. With a bit of thought, we can organize our workbooks. By doing things like following simple naming conventions, arranging content into modules, and being consistent, our workbooks can be tidy and organized.
With a well-organized design, our spreadsheets are easier to use, simpler to change, less prone to errors, and more credible.
Guidelines:
- Store related workbooks in a folder hierarchy
- Use a consistent filename convention.
- Filenames with dates use yyyy-mm[-dd] format
Using a coherent and consistent naming convention for files and folders is essential to avoid workbook chaos.
Guidelines:
- Divide a workbook into logical modules
- Modules are clearly separated and labelled
- The modules have a flow that fits the context
A fundamental principle of good spreadsheet design is the use of a modular structure. Using modules makes a spreadsheet easier to understand, use, modify, and test.
Guidelines:
- Use a consistent structure and orientation for similar content
- 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. Where inconsistencies are necessary, use highlighting to make them obvious.
Guidelines:
- Every worksheet has a heading
- All worksheet headings have the same style
A heading supplies guidance to the user about the worksheet's purpose and how it fits into the workbook's modular structure.
Guidelines:
- The workbook has no empty worksheets or ChartSheets
- Before removing, ensure that a sheet is unused as deletion cannot be undone
Empty worksheets and ChartSheets are unnecessary, and may be a distraction to the user, so they should be removed. Be sure that the sheet is empty and not used by, for example, VBA or Power Query.
Guidelines:
- Content flows logically from top-left to bottom-right
- Keep related content close together
- Prefer having a short arc of precedence
- Highlight deviations from the general flow
Ensure that a worksheet's content flows in a logical manner, to make it easy to understand and change.
