Making your spreadsheets easy for others to understand is an essential task for engineers. A natural outcome of improving your Excel skills is that your spreadsheets will be shared around your organization. This is a great, because it means that you are trusted to solve problems. However, if your spreadsheets are difficult to comprehend you could spend a lot of time fielding questions from your fellow engineers. So, to help your spreadsheets stand alone, I put together three things that you can implement to make your engineering spreadsheets easier to understand.
Document their Purpose
Every spreadsheet you create should have its purpose documented in some manner. This could be a separate tab called “About” at the beginning of the workbook, or a section at the top of your main worksheet. It doesn’t matter where you put it or what you call it. However, it should contain at least two things:
- Assumptions – All engineering calculations have assumptions. Document them so that future engineers know what they are and don’t expect the spreadsheet to calculate for things that it cannot.
- Purpose – Why was this spreadsheet created? What will it do? What won’t it do? If I plug in some inputs, what kind of outputs can I expect? What project was this spreadsheet originally created for?
I prefer creating a separate “About” tab to document the purpose and assumptions of my spreadsheets.
Separate Inputs from Outputs
Most engineering spreadsheets have three main types of content:
- Inputs
- Intermediate Calculations
- Results
To make your spreadsheets easier to digest quickly, separate these cells accordingly. Excel makes it easy to simply grab a cell and drag it to a new location, so use this to your advantage.
Take the input cells that may be spread throughout your work sheet and move them to a centralized location. Label this section “Inputs” and change the fill color of the cells in this area so that it stands out. I like to put these on the far left of the worksheet “above the fold” (the portion of the spreadsheet that is immediately visible without having to scroll).
Nobody really cares about the Intermediate Calculations so move these down or “below the fold” to reduce some clutter.
Finally, gather up the cells containing Results and move them up next to the Inputs. Label these cells accordingly.
Organizing the Inputs and Results this way makes the spreadsheet easier to understand for people who are accustomed to reading left to right.
Create a Mini “Report”
This last technique is something I am going to start using more myself.
I’m not necessarily referring to a full-blown formal report, but simply a quick collection of relevant charts and conclusion statements into one location.
With advanced Excel functions like CONCATENATE and IF, the conclusion statements can be automated based on some standard pass/fail criteria.
Below I’ve created some automated conclusion statements for a pressure drop calculation. You can see both the formulas and their results.
Conclusion
There you have it, 3 ways to make your spreadsheets easier to understand. Hopefully you can use these tips in a project you are currently working on.
