Why spreadsheets deserve care
Spreadsheets are the most common calculation tool in technical work. They are quick to start and easy to share, and that convenience is also the risk: a formula can be wrong without any error message, and a sheet that only its author understands is hard to trust. The aim is a spreadsheet that a competent colleague could read, follow and check without asking you what it does.
This is general guidance, not a substitute for the checking procedures your organisation or industry requires. Where a calculation supports a safety-related or regulated decision, follow the rules and review processes that apply to you.
Lay the sheet out so it can be read
A consistent structure does most of the work.
- Inputs in one place. Put every assumption and measured value in clearly marked cells, ideally with a distinct fill colour. Anyone should be able to see what can be changed.
- Calculations in the middle, flowing in one direction, usually top to bottom or left to right.
- Results in a clearly labelled block, with the units shown.
- A notes area stating the purpose, the date, the author, the source of any constants and the assumptions made.
Give each input a label, a unit and, where useful, a short comment explaining where the value came from. A cell that holds “0.85” tells a reader nothing; “Efficiency (fraction), from manufacturer data” tells them what to question.
Keep units visible and consistent
Many spreadsheet errors are unit errors. Put the unit in a column next to every quantity, and convert once, at the input stage, so the rest of the sheet uses a single system. Mixing millimetres and metres, or hours and seconds, in one formula gives a plausible-looking wrong answer. Habits from dimensional analysis help here: check that each formula’s result has the dimension you expect. If you must convert, show the conversion factor in its own labelled cell rather than burying it in a formula; the same care applies to any unit conversion.
Avoid hidden constants
A formula such as =A2*9.81*0.35 hides two numbers. Instead, place the values in labelled cells and refer to them, so the formula reads like the equation it implements. Named cells or ranges can make this clearer still, for example a formula that reads as voltage squared divided by resistance rather than a string of cell addresses.
A small worked illustration
Suppose you want the power dissipated in a resistor, using P = V² / R. As an illustration, take V = 5 V and R = 100 ohms.
- Input cells: voltage (V) = 5, resistance (ohm) = 100.
- Calculation cell: power (W) = voltage squared divided by resistance.
- Result: 5² = 25, and 25 / 100 = 0.25 W.
You can verify this by hand in a few seconds, which is exactly the point. If the sheet had returned 2.5 or 0.025, the hand check would catch it. A simple known case should always be run through a new sheet before real data goes in.
Build in checks
Make the sheet help you find its own mistakes.
- Sanity checks. Compare results with a rough estimate. Quick order-of-magnitude estimates catch many slips, and routine sanity checks are cheap insurance.
- Range checks. Add a cell that flags an input outside a reasonable range, such as a negative length.
- Cross-checks. Compute a quantity two different ways and confirm that they agree, or check that components sum to a known total.
- Test cases. Keep one worked example with a known answer on the sheet, so you can confirm it still works after edits.
Common errors to watch for
- Wrong cell references. Copying a formula can shift a reference you meant to keep fixed. Learn the difference between relative and absolute references.
- Ranges that miss rows. A sum that stops one row short after new data is added is easy to overlook.
- Overwritten formulas. Someone types a number over a formula, and the cell no longer updates. Colour or protect calculation cells so this stands out.
- Rounding and display. A cell may show two decimals while holding more, which can confuse hand checks. Be deliberate about significant figures and keep full precision until the final result.
- Silent text-as-number problems. Imported values stored as text may be ignored by formulas.
- Copied-in values. Pasting values from another sheet breaks the link, so the numbers can go stale.
- Misused functions. Check the exact behaviour of any function you use, especially for statistics and lookups. For example, choosing between interpolation and a nearest-value lookup changes results; see linear interpolation for tables.
Reviewing and sharing
Ask a colleague to review the sheet, or review it yourself after a break. Trace the logic from inputs to result, read each formula, and test with extreme and ordinary values. Save versions with clear names or dates, and keep a note of what changed. For anything long-lived, good documentation practice and basic version control habits matter just as much for spreadsheets as for code.
Finally, know when to stop using a spreadsheet. If a model grows large, needs many interlinked sheets, or must be run repeatedly by several people, a script or dedicated tool with proper testing may be safer than an ever-growing workbook.