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
Including values in a formula – known as hard-coding – is a common, high-risk, practice that often leads to errors.
Guidelines:
- Constants, symbols, and other special data values are in named ranges
- Scale factors are not embedded in formulae
Put special data values in a named range, then use that name in formulae.
Having a name gives the value a clearer meaning, making it easier to understand when used in formulae.
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.
