Module 4 · Lesson 4.3
Spreadsheet safety
The eight ways a structural spreadsheet goes wrong, and an audit that finds them.
Why this matters
More structural engineering is done in spreadsheets than in any other computational tool, and spreadsheets have a distinctive risk profile: the numbers are visible and the logic is not. You see 187.4; you do not see that it came from a cell that was overwritten with a constant last March.
This lesson is a checklist, and the interactive is an audit of a fictional workbook containing every fault on it.
The eight failure modes
1. Hidden formulas. The formula is only visible when the cell is selected. A wrong one is invisible until somebody happens to click on it.
2. Long dependency chains. A number twelve steps from its input, with no way to see the chain. Changing an input somewhere near the top has consequences nobody can trace.
3. External links. A reference to another workbook that has since moved, been renamed or been edited. The value may be stale, and the spreadsheet will not necessarily say so.
4. Unit inconsistency. kN in one column, N in the next, mm and m in the same row. Nothing enforces consistency, and a dimensionally wrong sum computes perfectly happily.
5. Overwritten formulas. Somebody typed a value over a formula to check something and did not undo it. This is the single most common serious fault, and it is close to invisible: the cell shows a number, exactly as it did before.
6. Copied formula errors. A relative reference that was right in row 12 and wrong in row 13. Copying propagates it down two hundred rows in one action.
7. Undocumented assumptions. A 1.35 with no note. Is it a partial factor, a dynamic allowance, a contingency? Nobody knows, including whoever typed it, six months later.
8. Opaque macros. Code that runs on open, or on a button, and that nobody has read. It may be doing anything.
To which add: no independent check. A spreadsheet that has never been tested against a case with a known answer is an untested calculation, whatever it looks like.
What good practice looks like
- Inputs in one clearly-marked block, formatted differently from everything else.
- Units in the column heading, and stated once per column rather than assumed.
- A check row: a quantity computed a different way that must agree. Total load against total reaction is the structural version, and it belongs in every workbook.
- A dated assumption note beside every constant that is not a physical property.
- A worked example on its own sheet, with the expected answer written down, that is re-run whenever the workbook changes.
- Version control, or at the very least a version cell and a change log.
beam-check-final-v3-REVISED.xlsxis not version control.
Try it
Spreadsheet audit
A fictional structural workbook containing every fault on the list. Flag the ones you think are wrong, then open a cell to see what it really contains. Nothing here comes from any real workbook.
Select a cell to inspect it. Flag the ones you believe are faulty. The workbook contains 8 faults among 10 cells.
| Cell | Displays | Inspect | Flag |
|---|---|---|---|
| B4 | 576 | ||
| B7 | 9.5 | ||
| B9 | 1.35 | ||
| D12 | #REF! | ||
| E15 | 48200 | ||
| F20 | 0 | ||
| C31 | 142.6 | ||
| H3 | 7.2 | ||
| Module1 | Sub Recalculate() | ||
| B22 | 5472 |
- Faults found
- 0 of 8
- Correct cells flagged
- 0
- Answer-changing faults still missed
- 6
The distinction that matters
- Some faults change the answer now. Others — a hard-coded span, an unexplained constant — do not yet, and are waiting for the day somebody changes an input.
- A workbook can be entirely correct today and structurally unsafe as a document. Both classes have to be fixed, and only the first announces itself.
- A check row — a total computed a second way that must agree — would have caught three of these the moment they happened.
What this shows: A spreadsheet shows numbers and hides logic — and the commonest serious fault, a formula overwritten with a value, leaves no visual trace at all.
Check yourself
Which spreadsheet fault is hardest to detect by looking at the workbook?
Practice
A spreadsheet computes a total floor load as area × (permanent + imposed). The area cell reads 576, the permanent 4.8 and the imposed 4.7, and the total reads 3072. What value has replaced the imposed load in the formula, in kN/m²?
Check yourself
What is the fundamental problem with a spreadsheet as an engineering tool?
Summary
- A spreadsheet shows numbers and hides logic — that is its defining risk
- An overwritten formula is the commonest serious fault and the hardest to see
- Inputs in one block, units in the headings, assumptions dated, a check row that must agree
- A workbook with no known-answer test case is an untested calculation
- Wide and shallow suits a spreadsheet; deep and iterative suits a script
This is educational material. It uses simplified examples to teach principles, and must not be relied on for real design or safety-critical decisions. Module overview and checkpoint