Computational Engineering / Programming and automation for engineers
Spreadsheet safety
The eight ways a structural spreadsheet goes wrong, and an audit that finds them.
Lesson review: expert verification remains open
This lesson has received a full-context editorial review and a check of its technical explanations. Selected arithmetic has been checked separately. This is not independent engineering sign-off.
Checks needed for project use
- A computational engineering reviewer must check algorithms, convergence claims, statistical assumptions and numerical examples beyond the cases explicitly recorded.
These are educational models and exercises. Check their assumptions before using the results. The full technical content and every diagram have not yet been independently verified.
Concrete and steel calculations retain their stated first-generation Eurocode teaching basis. Use the standards and amendments specified for your project; do not mix generations without an appropriate design basis.
Read the lesson register and outstanding review notesWhy 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.
Spreadsheet failure modes
Formulas are often hidden behind displayed values. Inspect them directly, using formula views or auditing tools, rather than assuming that a plausible value came from the intended calculation.
Long dependency chains make changes harder to trace. External links may refer to a workbook that has moved or changed, or retain a value from an earlier calculation. Check dependencies and the status of linked data.
Spreadsheets generally permit arithmetic on inconsistent units. Label quantities and check dimensional consistency, especially where kN and N or metres and millimetres appear together.
A formula overwritten with a constant may display the same number until the inputs change. Copied formulas can also propagate incorrect relative references. Compare formula patterns and use known-answer cases to test the workbook after changes.
Explain assumptions and constants. A factor such as 1.35 needs a stated purpose and source; its meaning cannot be inferred reliably from the value alone. Review macros and other code that can modify the workbook.
Practices that help review
- Separate and clearly label inputs, with units.
- Use an independent check appropriate to the calculation, such as applied load against support reactions where equilibrium applies.
- Record the basis and date of assumptions, factors and material properties.
- Keep a known-answer example and rerun it when the workbook changes.
- Record versions and changes so that a reviewer can identify the calculation used for a particular decision.
A readable layout helps, but it does not replace inspection of formulas, assumptions and test results.
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 may look unchanged until formulas and constants are inspected
- 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