The Do’s and Don’ts of Engineering Spreadsheets (part 3 of 3)

This is the final installment in a series of articles about engineering spreadsheets. If you haven’t read Part 1 and Part 2 then you can read them later as they are like Simpsons episodes, can be viewed in any order.

These articles outline simple tips that make your spreadsheets more reviewable and understandable for others. They follow the ‘Form over Function’ principle in that usability should be put ahead of printability in your spreadsheets.

“usability should be put ahead of printability in your spreadsheets.”

The best way to achieve more reviewable and understandable spreadsheets is to use a framework, where simple, logical guidelines are provided around how engineering calculations should be prepared on a spreadsheet. These articles and the discussions on various technical groups act as a basis for the framework, and with the help of several collaborators will result in an engineering spreadsheet protocol.

The protocol will aid the communication between engineers about spreadsheet calculations but also between spreadsheets and other engineering software. As the whole profession moves towards more 3D visualization, collaborative workflows and automating repetitive design tasks, it is important that our spreadsheets can communicate with these new technologies.

Now for the final set of tips.

References

Engineering calculations are a strange breed. They are based on a mix of empirical and physical findings and then modified to account for legal, financial and construction requirements.

These calculations and design procedures are usually documented in either building design standards, journal papers, textbooks or design guides. When we create a spreadsheet we often follow the procedure and check that our calculations are consistent with the documentation. Now if you don’t actually make a note about where the equation is then it can be awfully difficult for the reviewer to find it and understand why it was used.

I like to be explicit about referencing. I include “Ref:” at the start and writing out the document and the clause number.

References for the seismic hazard parameters in the New Zealand design standard

Including figures

I don’t need to repeat the hundred cliques about the importance and value of pictures to explain your design process. Just do it.

Here are a few simple guidelines:

  • Keep them consistent — a consistent layout not only looks good but makes it faster for a reviewer to understand
  • Don’t make them too big on the page — allow them to be viewed at the same scale as your spreadsheet calculations
  • Keep plenty of white space around them — don’t put them too close to the equations, and especially don’t put them over equations.

Instructions to the user

It is important to write out any unusual aspects about the spreadsheet. Even if the user is just you. I like to have a separate section that states the steps a user should take when using the spreadsheet, kind of like a checklist.

I usually list where they should enter the inputs and what design checks are included in the spreadsheet and more importantly what checks are not included. Also if the user needs to use Goal Seek to find equilibrium, then I include the instructions in this section.

Explain the macros

I get scared when I see a .xlsm file. A spreadsheet that contains macros. I get scared to the point that I tell people to avoid using macros all together. However, macros are a powerful tool and admittedly there are a few engineering problems that require them. When deciding to use macros I suggest you go through these steps:

  1. Ask, do I really need macros or can this problem be solved using standard cell calculations
  2. Always use named cells — as it makes them easier to understand and harder to break
  3. Provide an explanation on the purpose of the macro, as this will help people understand it and fix it if it breaks.

The example below is deliberately simple, but note that an explanation is provided and that the calculations use named cells rather than cell references.

Using named cells in macros makes them easier to understand and more robust

Dropdown boxes

Dropdown boxes are a great way to guide the user on what range of inputs are suitable for the spreadsheet. They are especially important when you use text as an input. Eg. You are inputting the load type and the options are “Live load”, “Dead load” or “Snow load”. By making the user select options rather than typing them out avoids issues caused by spelling mistakes. Check out a video on how to add a dropdown box in the new version of Excel here.

Including a dropdown menu for text inputs makes it easier for everyone to use

Design checks

Because engineering calculations are so unique they often have quite strange design checks. Eg. The longitudinal reinforcement in a concrete beam seen below. Compare this to accounting where everything should sum to zero.

Because the checks are unusual, make them explicit. I include “check:” in front of all of my design checks and then using an if statement I provide the answer to the check in plain in English, check out the example below for a concrete beam design.

Design checks entered into a spreadsheet
Design checks expanded in Pensolve PDF view

Summary

That concludes the series about engineering spreadsheet do’s and don’ts. I hope they bring further efficiency to your office and if you are interested in learning some more engineering spreadsheet tips then sign up to download the universal engineering spreadsheet protocol when it comes out later this year.

I will continue to write about engineering spreadsheets but more in relation to my engineering software Pensolve and how it enhances the way we deal with engineering calculations.