Spreadsheet hygiene: seven habits that prevent errors

A spreadsheet mistake does not announce itself. There is no error message, nothing turns red, and the file continues producing numbers that look exactly as authoritative as correct ones. A formula extended one row short of the data, a hardcoded figure buried inside a calculation, a column sorted while an adjacent one stayed put — each of these produces a plausible answer. Plausibility is the problem: an implausible answer gets investigated, while a plausible wrong one gets used.

This matters disproportionately for small businesses because the spreadsheet frequently is the system. Pricing, stock, payroll, forecasts and the numbers presented to a bank often live in one file that grew organically, was built by whoever needed it first, and has no tests, no review and no documentation. The habits below are not about elegance. Each one closes off a specific way that files like that produce wrong numbers confidently, and all seven can be applied to a file that already exists.

The first habit is structural and does the most work: three areas, kept apart, ideally as three tabs. One holds raw inputs — figures you or someone else types in, or that arrive from elsewhere. One holds calculations, which reference the input sheet and contain no typed numbers. One holds the output you actually read or share. In a file with this shape, every number has exactly one home, and updating a figure means changing it in one place.

The failure this prevents is the most common of all: the same value typed in several places, updated in some of them, and now quietly inconsistent. It also makes the file auditable, because you can point at the input sheet and say these are the assumptions — a claim that is impossible in a file where inputs are scattered among formulas. Retrofitting this into an existing sheet is tedious rather than difficult: create the input tab, move every typed number onto it, and replace each original with a reference. The rows that resist are informative, because a number you cannot classify as input or calculation is usually one nobody understands any more.

The second habit follows from the first. A formula reading `=B4*1.18` works, and it hides a decision. In six months nobody will know whether 1.18 is a tax rate, a markup or a growth assumption, whether it is still current, or how many other cells contain it. When the rate changes, the file has to be searched, and any occurrence missed becomes a silent error that survives every subsequent review.

The fix is to give every constant a labelled cell on the input sheet and reference it: `=B4*$Inputs.$B$7`, with `Inputs!B7` labelled with what it is, where it came from and when it was checked. This makes assumptions visible, which is the real benefit — a file's assumptions are the part most likely to be wrong and the part hardest to see. The one number that may sit inside a formula is a genuine mathematical constant, such as dividing by 12 to convert a year to months, because that will not change. Anything determined by the outside world is an input, and the test is simple: could this number ever be different? Then it belongs in a labelled cell.

Habits three and four attack the same problem from either side. Every column gets a header saying what it holds and in what unit, and every assumption gets a label saying what it is and where it came from. "Rate" is not a label; "GST rate, 18%, per invoice checked March" is. Units are where this pays off most, because a column mixing rupees and thousands of rupees, or units and cases, produces errors that are invisible in the cell and enormous in the total. Write the unit in the header even when it feels obvious, since it is obvious only to whoever built the file and only for a few weeks.

Validation stops bad data entering rather than catching it later. Restrict a cell that should hold a date to dates, a quantity to positive numbers, and a category to a list you define, so a typo is refused at the point of entry instead of propagating into every calculation downstream. This matters most where someone other than the author types, which in practice is any file that survives. Inconsistent categories — "Delivery", "delivery", "Del." treated as three things by every summary formula — are the single most common cause of a total that is wrong for no visible reason.

The remaining three habits are mechanical and take minutes. Freezing the header row means you can always see which column you are in, which prevents the specific error of reading or typing a value into the wrong column after scrolling — an error that is invisible afterwards because the number looks fine where it landed. Protecting the cells that contain formulas prevents the other common accident: someone types a value over a calculation, the formula is gone, and the cell continues showing a number that no longer computes from anything.

Backing up before a significant change is the habit that turns a disaster into an inconvenience. Restructuring a file, deleting columns that look unused, or repointing a set of formulas are all operations that can go wrong in ways discovered days later. A dated copy taken beforehand — with the date in the filename, not just the file's timestamp — costs nothing and is the only way back. Version history in cloud spreadsheets covers much of this automatically, which is a good reason to prefer them, though it is worth checking how far back the history actually reaches before relying on it.

A clean spreadsheet can be confidently wrong. None of these habits checks whether the logic is right, whether the formula expresses what you intended, or whether the assumptions on your tidy input sheet bear any relation to reality. A well-structured file built on an optimistic growth figure produces beautifully organised nonsense, and the structure makes it more persuasive rather than less. The visible assumptions are an invitation to challenge them, and that invitation still has to be accepted by someone.

So pair hygiene with two things it cannot supply. Sanity checks inside the file: a total that should equal another total, a percentage that must sum to a hundred, a stock figure that cannot go negative — each as a cell that flags when its condition breaks, since an error the file catches is worth more than one a reader might notice. And a second pair of eyes on anything consequential, because the author of a spreadsheet is the person least able to see its errors: they read the intention rather than the formula. Where a file drives a decision that would be expensive to get wrong, someone who did not build it should trace at least one number from input to output.