Proprietary (paid for)
Purchased from external providers; developed as commercial software.
Quality varies; must undergo QA/QC before widespread use.
In-house
Created and maintained by the organisation; used internally.
Often well-structured but may become outdated; risk increases if original authors leave.
Project
Built by individuals for single projects; often lacks long-term support.
Highest risk when reused or shared; typically lacks documentation and validation.
This document outlines five key principles to guide engineers in creating and validating spreadsheets effectively. A team, company, or organisation may want to build off these principles to create a standard approach to improve consistency.
Creating spreadsheets
1) Provide references and equations
Engineering decisions, assumptions, and equations must be defensible and traceable, including references to relevant standards and codes. Explicitly stating the equations used and where they come from ensures transparency and compliance.
This practice not only supports verification and validation but also helps others understand the rationale behind the calculations. Where possible, cite clause numbers and provide document excerpts and links to reinforce credibility.
2) Separate inputs, calculations, and outputs
In engineering spreadsheets, clarity is paramount. Mixing inputs, calculations, and outputs in the same area can lead to confusion, errors, and difficulty in auditing. By clearly separating these components, engineers can trace data flow more easily, validate assumptions, and update inputs without inadvertently altering formulas. This structure also facilitates peer review and future reuse of the spreadsheet.
Use formatting to identify inputs that need to be manually adjusted and show those that are derived. A colour code can be adopted to highlight, for example: input cells, calculation cells, and output cells.
3) Avoid hardcoding values
Hardcoded values are silent risks. Embedding constants directly into formulas makes it harder to spot errors, update parameters, or adapt the spreadsheet to new scenarios. Instead, define all variables in a dedicated input section and reference them in formulas. This approach improves maintainability, supports scenario testing, and ensures that changes propagate correctly throughout the model.
Named ranges are not mandatory, and some calculations may not suit them. However, using named cells for inputs at the top of the spreadsheet can improve traceability, especially when those inputs are not visible on the screen at the same time as the formula.
4) Re-order, tidy layout, and add a summary
A well-organised spreadsheet mirrors the logical flow of engineering thought. Group related calculations, follow a top-down or left-to-right structure, and use consistent formatting to guide the reader. A thoughtful layout reduces cognitive load, minimises the risk of oversight, and enhances the overall usability of the tool.
A clear written narrative is just as vital as the calculations themselves. The spreadsheet should read from top to bottom like a traditional calculation pad, with explanations and sketches that guide the reader through each step and design decision.
A summary of key results (eg, maximum stress, deflection, safety factors) allows stakeholders to quickly grasp the outcomes without digging through formulas. This front-loaded clarity is especially valuable in design reviews and decision-making. There is no strict rule for what must appear at the start of a set of calculations, but a typical introduction might include:
-
A brief statement of the purpose.
-
Relevant background information and the codes being followed.
-
Properties of materials, sections, and applied loads.
-
A description of the analysis method or design checks used.
-
A summary of results and a conclusion.
5) Include self documentation
Engineers often share spreadsheets across teams and disciplines. Add documentation to the spreadsheet, including clearly stating the purpose, scope, and assumptions at the beginning of the file.
Consider adding conditional formatting and warning messages to allow someone to know if their input is beyond the limitations of the spreadsheet.
Checking
1) Confirm documentation and summary clarity
Ensure the spreadsheet includes a clear purpose statement, outlines key assumptions, and provides a summary of the main results. This helps future users understand its intent and limitations.
Check that limitations are explicitly stated, particularly whether any checks have been omitted.
2) Check the spreadsheet independently
Avoid relying on its appearance or reputation. Approach it with a fresh perspective and verify all calculations from first principles where possible.
Manually follow the logic of important formulas—don’t just check the result, understand how it was derived. Ask: Is this the best approach? or is there another method that should be used?
If a check is missing, determine whether this was an intentional exclusion or an oversight that needs addressing. For any checks that have been deliberately skipped, it should be clearly stated in the limitations that they are not being performed.
3) Test with known and edge-case inputs
Start by testing the spreadsheet with known inputs—do the outputs match what you expect to see? Even if they are slightly off, investigate and understand why. This is the first basic check.
The second check is testing for edge cases, which is critical. Just because the spreadsheet may not have been designed with them in mind doesn’t mean it won’t be used in such scenarios. These edge cases often present the greatest risk to structural safety when spreadsheets are reused.
If the spreadsheet fails on an edge case, it either needs to be amended to perform the check, or further notes on the limitations should be added at the top.
4) Identify hardcoded values and hidden risks
Look for values embedded directly in formulas or hidden cells. These can be easy to miss and hard to update, increasing the risk of errors.
Ideally, everything should be visible. If the spreadsheet is set up for printing, as is often the case, some elements may sit outside the print area, but formulas should never be hidden from the user.
It’s recommended to run the following macro, if able to, to unhide all the hidden sheets, rows, and columns in the spreadsheet. If using Excel on Windows, you can run macros by pressing Alt + Fn + F11 to open the macro window, and paste in the following code:
Sub UnhideAll()
'Unhide all sheets
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next ws
'Unhide all rows and columns in each sheet
For Each ws In Worksheets
ws.Rows.Hidden = False
ws.Columns.Hidden = False
Next ws
End Sub
5) Verify version and change history
Ensure you're reviewing the latest version. Check what has changed since the last review and why.
If it has been updated as a result of an error, make sure everyone is aware they need to use the latest version (and potentially need to re-perform checks).
If it has been updated to perform additional checks or alternative checks (e.g. updated to a particular national annex), then make sure the distinction between the two documents is clear and documented.
Ensure its documented with the original creator, the last person to modify it, and the last person to check it.