Excel for Engineers, Part 2: Follow These Best Practices

Don’t let sloppy spreadsheets drag down your digital transformation. With a bit of planning and some simple standards, you can make any workbook work better.

Engineers are the world leaders in misusing Excel, and the spreadsheet software can be a big impediment to digital transformation. But it’s also so flexible and popular that—let’s be realistic—it’s not going anywhere soon.

In Excel for Engineers, Part 1 we explored the software’s inherent riskiness and detailed actions to avoid it. Today, we’ll explain more general best practices you should follow to boost productivity and ensure your workbooks won’t end up holding you back.

Implementing widely accepted best practices when working with Excel will:

  1. Improve engineer productivity, especially for workbook maintenance and enhancement.
  2. Reduce business risks of inaccurate calculations leading to misleading recommendations.
  3. Address well-known Excel shortcomings.

Careful review avoids hasty Excel errors

Most engineers working with Excel workbooks do not invest much time looking for errors. As a result, they don’t find any and certainly not many. Finding Excel errors is quite tedious because workbooks contain a mix of code and data.

The combination of time pressures, confidence in one’s work and the difficulty of finding errors combine to leave mistakes in workbooks that are deemed complete.

There is no alternative to engineers allocating time for a careful review of their work with a focus on the following:

  1. Data integrity errors—not addressing gaps in the input data.
  2. Formula errors—performing incorrect calculations.
  3. Analysis errors—not asking the right questions in the workbook.
  4. Process errors—not recognizing version errors in the input data, pivot tables or the workbook.
  5. Communication errors—the conclusions and recommendations of the workbook are not accurately presented.

Apply Excel standards to improve quality

The design of many Excel workbooks is inadequate because an engineer started the workbook to support a small ad hoc task. With unanticipated growth, workbooks:

  1. Are error-prone due to formula defects.
  2. Are challenging to maintain and enhance due to an inability to understand how the workbook is constructed.
  3. Suffer from poor performance once data volumes grow due to convoluted calculations.

Example standards for Excel workbooks include:

  1. Lay out worksheets with descriptors on the left, values in the middle and totals on the right and bottom.
  2. Order worksheets as follows: input, calculation and presentation.
  3. Define naming conventions for workbooks, worksheets and variables.
  4. Restrict worksheets to one table. Multiple tables cause problems when sorting, inserting/deleting rows or formatting cells.
  5. Perform calculations once and reuse the value through a named cell.
  6. Include worksheets named Introduction, Variable_Values and Change_Log.
  7. Display charts on individual worksheets.
  8. Build in data verification checks.
  9. Simplify formulas for readability.
  10. Avoid the use of macros.
  11. Define typical cell formatting.
  12. Format title, subtitle and column headings uniformly.
  13. Avoid merged cells because of the risk of problems with calculations and references.
  14. Use and format running headers and footers consistently.
  15. Minimize conditional formatting.
  16. Avoid cell borders.
  17. Show time advancing from left to right.
  18. Label numbers with units of measure.
  19. Avoid ornamentation, such as colors and graphics, that detract from the analysis and the recommendations.
  20. Do not use colors to imply a category that’s actually a data element.
  21. Use simple ranges. Avoid compound ranges.
  22. Define the details of version control.

Think before you build an Excel workbook

First and foremost, engineers should spend time refining the problem statement they want to address. Convince yourself that your problem statement is not a symptom of an underlying problem.

Second, define the outputs that will communicate your recommendations well. This step is often called “Begin with the end in mind.”

Third, understand the data sources you will use as input to your Excel workbook. Some related questions to consider are:

  1. Is the data quality adequate?
  2. Is the data granularity sufficient?
  3. Are the geographic and time period coverages adequate?
  4. Is the update frequency often enough?

Fourth, improve designs by using a standard for Excel workbooks. Choose clarity of communication over visual appeal. Standards offer these benefits:

  1. Reduce the risk of common Excel errors, undermining the reliability of results.
  2. Make it easier to communicate the conclusions and recommendations of workbooks.
  3. Make it easier for someone to understand a workbook they have not seen before.
  4. Lower effort to maintain and enhance workbooks.

Hold peer reviews to improve Excel quality

Too often, teams accept the recommendations derived from an Excel workbook without much discussion. Few realize that the workbook is a first draft rather than a refined product.

Engineers understand the value of peer review of their work products. They should apply the same practice to their Excel workbooks. Some starter review questions to consider include:

  1. Has the purpose of the workbook been achieved?
  2. Does the workbook perform sufficient data validation?
  3. Does the workbook design indicate best practices for Excel were followed?
  4. How thoroughly was the workbook tested?
  5. What is the level of business risk associated with the topic of the workbook?
  6. What is the potential impact of unknown errors in the workbook?
  7. Has the author demonstrated sufficient competence given the subject matter and complexity of the workbook?

Explain your Excel work

Too many Excel workbooks are only understandable to the original engineer who started them. This situation leads to avoidable risks and errors when that person is unavailable.

To help others understand your work, make use of the following Excel features and design concepts to explain your work:

  1. On the Ribbon Insert > Comments to capture design discussions with colleagues.
  2. Right-click in a cell > New Note to explain formulas, the general sequence of calculations and specific data sources.
  3. Add a few lines of text below each table and chart to explain their purpose.
  4. Add an “Introduction” worksheet to provide an overview of the workbook and the developers’ names.
  5. Minimize the complexity of formulas, even if that means adding columns or rows.
  6. Minimize the number of workbooks in the solution. Use more worksheets instead.
  7. Avoid hiding rows and columns.
  8. Minimize the use of relative links, especially to other workbooks.

Engineers use Excel all day long because of the design freedom and flexibility it offers. When you adopt best practices for Excel workbooks, you improve the quality of your work and reduce the risk of misleading recommendations.

Written by

Yogi Schulz

Yogi Schulz has over 40 years of Information Technology experience in various industries. He writes for ITWorldCanada and other trade publications. Yogi works extensively in the petroleum industry to select and implement financial, production revenue accounting, land & contracts, and geotechnical systems. He manages projects that arise from changes in business requirements, from the need to leverage technology opportunities and from mergers. His specialties include IT strategy, web strategy, and systems project management.