Structure and References

Design before typing

Most spreadsheet disasters are structural rather than arithmetic. Before entering data, decide the layout:

• Separate inputs, calculations and outputs — ideally on separate sheets. Hard-coded numbers buried inside formulas are the single most common cause of a model that nobody can check or update.
• One fact per cell — never “12 units @ 4.50” in one cell.
• Data in a flat table — one row per record, one column per field, a single header row, no blank rows or merged cells within the data. This is what every analysis tool expects, and merged cells break sorting, filtering and pivot tables.
• Consistent data types — a column is numbers or text, not both. Numbers stored as text are the most common reason a total comes out wrong.
• Document assumptions — a sheet stating source, date, units, currency, exchange rates and any assumption made.

References

• Relative — A1 shifts when the formula is copied.
• Absolute — $A$1 never shifts.
• Mixed — $A1 locks the column, A$1 locks the row. This is what makes a grid of calculations from a row and a column of inputs work with one formula copied across.
• Named ranges — TaxRate is clearer than $B$4 and does not break when rows are inserted.
• Cross-sheet — Data!A1. Links to other workbooks are fragile; prefer importing the data.

Formatting that helps

• Use number formats rather than typing symbols: currency, percentage, thousands separators and a consistent number of decimal places.
• Format dates as dates. A date stored as text cannot be sorted or calculated with, and regional date order is a classic source of silent error.
• Use a consistent visual convention — for example one colour for input cells, another for formulas — so a reader can see instantly where to type.
• Freeze panes so headers stay visible; set print areas and repeating header rows before anyone prints it.
• Avoid merged cells entirely. Use centre across selection if you need the appearance.

Errors

• #DIV/0! — division by zero or by an empty cell.
• #VALUE! — wrong data type, usually text where a number is expected.
• #REF! — the referenced cell was deleted. Always investigate; never just delete it.
• #N/A — a lookup found nothing. Often correct and meaningful.
• #NAME? — a misspelled function or an undefined name.
• ###### — the column is too narrow, not an error.

Wrap error handling deliberately with IFERROR, but only where the error is expected. Blanket IFERROR around everything hides genuine faults and is a common way for a model to be quietly wrong.