Many spreadsheets start out to meet a quick need and then grow for years. Rows added at the bottom, columns inserted in the middle, formulas copied from one sheet to another, numbers typed directly into formulas. After a while nobody, not even the person who created it, knows for sure where a result comes from. Designing the sheet with a clear structure from the start takes a few extra minutes and saves hours of checking.
Separate the functions
The most useful principle is to keep the different parts of the work apart, ideally on different sheets of the same workbook:
| Sheet | Content | Who edits it |
|---|---|---|
| Data | the raw lists: sales, transactions, master data | whoever enters or imports |
| Parameters | rates, thresholds, percentages, lists for validation | only whoever is responsible |
| Calculations | helper columns and intermediate steps | nobody, formulas only |
| Results | summaries, charts, panels | nobody, formulas only |
With this structure, updating the sheet means pasting new data in one precise place, and changing a rule means changing a value in one precise place. The formulas are never touched.
Data as a list
The Data sheet should have the simplest possible shape: one row per event, one column per attribute, a header in the first row. No blank separator rows, no intermediate subtotals, no merged cells, no colours used as information ("the yellow rows are the ones already paid"): a colour cannot be filtered reliably or used in a formula. If a piece of information matters, it deserves a column.
A very common mistake is to organise data like a report, with months in side-by-side columns. It works for reading, but it makes conditional sums, pivots and charts difficult. A list with a "Month" column is always the better base; the column-style report is built afterwards, on the Results sheet.
No numbers inside formulas
A formula such as =B2*1.22 contains a hidden rule. When the rate changes, you have to find every formula containing it, and some will slip through. Instead, write the value on the Parameters sheet, give it a name and use it:
=B2*(1+VAT_Rate)
The same goes for thresholds, deadlines, commission percentages and exchange rates. The name makes the formula readable even to someone who does not know the file.
The only acceptable constants in formulas are those that by definition will never change: 24 hours in a day, 12 months in a year, 100 for percentages.
Simple, consistent formulas
- One formula per column. Every cell in a calculation column should contain the same formula. An exception typed by hand in a single row is invisible and sooner or later produces an error.
- Visible intermediate steps. A three-line formula with five nested functions is hard to check. Breaking it into two or three helper columns, on the Calculations sheet, makes each step verifiable.
- References pointing one way. Calculations read the data and parameters, results read the calculations. Avoid formulas that bounce back and forth between sheets: they make it impossible to follow a number's path.
Built-in checks
A good spreadsheet checks itself. A few examples of checks to place in a dedicated area of the Results sheet:
- the total of the pivot or summary matches the total of the source list;
- the number of rows with an empty code is zero;
- the number of non-numeric values in an amount column is zero, comparing COUNT and COUNTA;
- the sum of the shares in an allocation equals the total.
Each check can return "OK" or "Check" and, with conditional formatting, turn green or red. A glance at the checks area before using the results avoids sending out wrong numbers.
Names, labels, instructions
- Meaningful sheet names: "Sales_data", "Parameters", not "Sheet1" and "Sheet1 (2)".
- Explicit headers with units: "Net amount (€)", "Duration (hours)".
- Recognisable input cells: a consistent background colour for the cells the user has to fill in, and only those, explained in a legend.
- A "ReadMe" sheet at the start, with the purpose of the file, who maintains it, where the data comes from and how to update it. Ten lines are enough.
Protect what should not be touched
Once data, parameters and formulas are separated, it becomes easy to protect the calculation and results sheets while leaving only the input cells editable. Protection is not so much against bad actors as against accidental mistakes: a Delete key pressed on the wrong cell.
Versions and copies
Before a major change, save a copy with the date in the name, in yyyy-mm-dd format so the copies sort themselves. If the file lives in a cloud service with version history you are safer, but an explicit name is still useful for spotting a given month's version at a glance.
In my work, when I inherit a complicated spreadsheet, the first thing I do is rebuild this separation on a copy: often, just moving the fixed numbers into a parameters sheet brings out inconsistencies nobody had noticed.
In short
- Separate data, parameters, calculations and results, ideally on different sheets.
- Keep data as a list: one row per event, no subtotals, no colours as information.
- Move constants into parameters and use names instead of numbers in formulas.
- Add automatic checks that flag inconsistent totals or missing data.
- Document the file with clear names, recognisable input cells and a ReadMe sheet.
