Five spreadsheet habits worth keeping

Most spreadsheet problems are structural rather than formula errors. These five habits cost nothing to adopt and save the afternoon you would otherwise spend working out what past you meant.

Separate input, calculation and output

Three areas, clearly marked. Cells people type into, cells that compute, and cells meant to be read. When these are mixed, somebody eventually types over a formula and the sheet is quietly wrong from that point on.

Never hard-code a number inside a formula

A tax rate written into forty formulas is forty edits when it changes, and you will miss one. Put it in a named cell and reference it. The formula becomes readable at the same time, which is a bonus rather than the point.

Name your ranges

• A formula referring to TaxRate is self-documenting. One referring to $B$42 is not.
• Named ranges survive inserted rows, which is where most broken references come from.
• They make errors obvious: a misspelled name fails loudly instead of silently returning the wrong cell.

Keep one row per record

Resist merged cells and multi-row entries. Every analysis tool, pivot table and formula assumes one record per row, and a sheet that breaks this is a sheet nothing else can read.

Write down what it does

One cell at the top saying what the sheet is for, who maintains it, and what the inputs are. Thirty seconds now, against twenty minutes of archaeology in six months when somebody else inherits it.