Spreadsheet good practice guidelines
Data
Data is the basis of most spreadsheets. We use spreadsheets to analyse data, leading to insights that inform decision making.
But before we can do anything useful with data, we need to transform, organize, and clean our data. Often, we jump straight into the analysis part of building a spreadsheet, neglecting the need to make sure our data is in good shape. This is a mistake that undermines our analysis.
Many of the problems people have building spreadsheets stem from poor data: awkwardly structured data, duplicates or missing data, attempting to clean and analyse data at the same time, and many other issues. With a focus of first getting good data to work with, our subsequent analysis becomes much easier, less complex, and less error prone.
Guidelines:
- Values are not hard coded in formulae.
- Scale factors are not hard coded.
- Constants and symbols are in named cells.
Including values in a formula – known as hard coding – is a common, high-risk, practice that often leads to errors.
Guidelines:
- Each input, assumption, and data value is entered in only one location.
- If values are copied, then clearly mark the copies.
Duplicating values, also known as cloning, can cause many problems. To avoid those problems, ensure that data, assumptions, and inputs appear only once and are then referenced.
