The Do’s and Don’ts of Engineering Spreadsheets
How can we improve one of the most efficient engineering tools?!
Spreadsheets are possibly the fastest way to write a series of engineering calculations, unfortunately they are also the hardest calculations to review.
This is the first of a series of posts about best practices with engineering spreadsheets, looking at some of the small tweaks we can make to our spreadsheets to make them easier for others to understand and use. Eventually I would like to take the opinions and comments about these posts and write a formal document about the widely-accepted best practices for engineering spreadsheets.
Why am I explicit about “engineering” spreadsheets? Well engineering spreadsheets are quite different to other spreadsheet calculations for the following reasons:
- Engineering calculations are a mixture of theoretical and empirical expressions. There are uncertainties in the inputs and calculations, and the assumptions around how these represent the real world need to be stated.
- Engineering design expressions are formalised in documents such as a design standards and engineering calculations must comply with these documents.
- Engineering spreadsheets are used across multiple different jobs by many different engineers for several years. On average a spreadsheets get updated seven times in its life.
From my research I think the main issues with engineering spreadsheets stem from two reasons:
- There is an obsession with trying to prepare an engineering spreadsheet for printing. Surely making a spreadsheet understandable, reviewable and modifiable should be higher priorities than printing.
- Many spreadsheets start out as brain dumps and because there is no formalised structure the spreadsheet becomes very difficult to interpret.
For those that are interested in a more academic discussion, there are some notable papers on the increased difficulty to detect errors when spreadsheets have been poorly designed.
Instead I have some simple and practical tips to improve engineering spreadsheets.
Issue 1: Lack of naming and commenting
I often see spreadsheets that look like this…
This may have made sense when you first built the spreadsheet, but for someone who wants to review or modify these calculations, these unlabelled cells can cause a lot of grief.
Instead follow a simple pattern of: description, symbol, number/formula, units, Ref: reference to design standard, further comments. As seen in this beam design spreadsheet.
Issue 2: Calculation flow
The other issue with the first spreadsheet is that the flow of calculations is not clear. I adopt a calculation flow that goes down the page. This not only allows the reviewer follow my thought process but it also allows you to easily insert new rows or calculations if want to demonstrate more steps.
Issue 3: Structure
Often spreadsheet calculations can become unwieldy simply due to the number of calculations required, approximately 39% of engineering calculations contain more than 1000 cells of calculations.
The best way to deal with big documents is to break them into sections or chapters (much like a test cricket innings) and engineering calculations are no exception. By breaking your calculations into sections it helps you order your mind and gives it structure. It also dramatically speeds up the review, as the reviewer can gain a quick high-level understanding of the calculations and navigate to the critical areas more quickly.
I use bold font to indicate a section header.
Issue 4: Unused cells and unused sheets
I find it frustrating when there are a whole series of inputs that are not used at all in the spreadsheet. I spend a few minutes trying to assess whether the input is appropriate only to find that it doesn’t influence the calculations at all. It should be best practice to remove these unused inputs as it makes your spreadsheet cleaner and avoids confusion.
The same can be said for unused sheets, often worksheets are copied for different iterations of a design (I will include some more specific issues around copying in my next post). The main point here is, if it is not important then don’t keep it (it has no nostalgic value, LET.IT.GO.). Imagine how annoyed you would be if you reviewed a worksheet only to find out that it was completely outdated.
Issue 5: Hidden cells
The opposite of unused cells are hidden cells. It has always surprised me when I find a series of important calculations hidden by folding a series of rows or even worse, changing the font colour to white. It is surprising both in that I don’t expect to find it, and it baffles me to try and understand why it was hidden in the first place. I think it often occurs due to the desire to make a spreadsheet printable, but please, please, please first make your spreadsheet understandable, reviewable and modifiable.
We should be transparent about our calculations, this will enhance collaboration and reduce errors as well as decrease the amount of time wasted deciphering someone else’s spreadsheet, so that we can spend more time building (and in Christchurch, rebuilding) structures instead of spreadsheets.
Please subscribe if you are interested in improving the efficiency of spreadsheet use in your firm and if you agree or disagree with any of my tips then please leave a comment.
Disclaimer: Maxim is the cofounder of the engineering spreadsheet review software called Pensolve.