Excel for Engineers, Part 1: Avoid These Common Risks

Everyone uses Excel, but many use it poorly. Try these simple techniques to futureproof your workbooks as your company digitally transforms.

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.

If you understand Excel’s inherent riskiness and how to work around it, you can save yourself a lot of time and consternation if and when you transition to other digital tools.

Simple typos can create disasters

Data entry or formula errors can lead to inaccurate calculations and disastrous recommendations. The problem worsens because engineers rarely include data validations in their worksheets.

The risk reduction solution is to perform the following steps:

1. Resolve all error messages that appear starting with a # in formulas.
2. Inspect the workbook for problems using Go To Special (Home tab > Find & Select > Go To Special).
3. Perform all the Formula Auditing group commands under Formulas on the Excel ribbon.
4. Resolve all Warnings that Excel displays as small triangles in the top left corner of cells.
5. Visually review your Excel workbook formulas carefully.
6. Review calculation results for reasonableness.
7. Use an Excel error-checking software package to identify difficult-to-find problems.

Partial cell ranges distort data

Forgetting to update cell ranges, especially complex ones, is a common problem all engineers encounter. It leads to inaccurate calculations and conclusions.

Solutions include:

1. Use the Quick Analysis Tools to identify data integrity problems. Select a range of potentially problematic cells and click on the icon at the bottom right of the cell range.
2. Keep cell ranges simple.
3. Convert worksheets to tables using Home > Format as Table. This much-ignored Excel feature reduces the risk of problems with formulas and cell ranges.
4. Consistently leave a blank row between the column headings and the first row of data as well as the last row of data and the total row. Now inserting a row at the top or bottom doesn’t create a cell range error.
5. Review the cell ranges before completing the workbook.

Hidden rows or columns misinform

Revising an Excel worksheet with hidden rows or columns will produce unpredictable results.

Unhide all the rows and columns in a worksheet before making changes. To unhide all rows and columns, select the whole sheet by pressing Ctrl + A, then press Ctrl + Shift + 9 to show hidden rows and Ctrl + Shift + 0 to show hidden columns.

To simplify your worksheet and reduce the risk of errors, ask yourself if the hidden rows and columns distract from the current worksheet’s purpose and should be moved to a new one.

Link worksheets, not workbooks

Links are essential to share values among worksheets or workbooks. Links fulfill the critical goal of maintaining data integrity across all the worksheets within a workbook.

However, erroneous or unrevised links among workbooks can easily undermine data integrity. This lack of data integrity can lead to inaccurate results as versions of workbooks are created, and errors occur in the links if they’re not carefully reviewed.

Using tables, instead of worksheets, and Power Query M reduces this risk by working with named columns and ranges.

Don’t embed fixed values in formulas

Don’t write formulas with a fixed value like =b17*45. It isn’t easy to find all the fixed values when they need to be changed. That issue leads to calculation errors.

A better solution is to create a worksheet named Variable_Values. Create a column of variable names and one for values. Name the cells in the values column and use that name in calculations.

VBA macros add avoidable complexity

VBA macros make Excel workbooks more challenging to understand and revise. On the other hand, VBA macros are better than repeating the same calculation in many places in a workbook.

To reduce risk, use Excel functionality before resorting to VBA macros. When creating a VBA macro or Power Query M, ask yourself if the workbook is becoming more complex than Excel can reliably manage.

Avoid color

Too often, a color implies a data element, such as a category or a value range.

Since a color can’t be used in a formula, it’s better to be explicit about the meaning of the color by creating a new column or row.

Also, colors require a legend to communicate their meaning. That adds complexity for the reader to interpret the worksheet. Colors are challenging for people with varying degrees of color blindness to understand.

Watch out for dollar signs

Using a $ sign in a cell reference (e.g., E$8 and $G$14) can be helpful.

However, these $ signs can lead to calculation errors and create reference errors when you drag the bottom-right fill handle of a cell to copy a formula.

Instead, create a worksheet named Variable_Values as described above.

Avoid the data limitation of .xls

The Excel file extension .xls only allows for 65,536 rows, while the .xlsx limit is 1,048,576. This limitation can cause data loss when copying rows from one workbook to another.

The solution is to save every .xls file to .xlsx format whenever you open one. That way, you’ll eliminate this risk as your work proceeds.

Autocomplete creates surprises

Excel provides a useful autocomplete function. However, Excel can create problems when autocompleting dates.

The autocomplete will assume some character strings are supposed to be dates when they’re not. That mistake can lead to calculation errors and strange text.

The solution is to precede words that Excel tries to autocomplete with an apostrophe.

Merged cells prevent some operations

Some Excel developers like to merge cells to accommodate long text descriptors. Merging avoids huge white spaces in the worksheet.

However, merging cells interferes with the ability to move or insert rows and columns.

An alternative that works well is to uncheck Wrap Text under Home on the ribbon. Then the descriptors display correctly across multiple cells that are presumably empty.

Worksheets should contain one table

Clarity is reduced when worksheets contain more than one table, and the risk of errors increases significantly when changes to one table inadvertently affect another.

Excel makes it easy to add more worksheets and use links to maintain value relationships between worksheets.

Cells containing multiple values

Sometimes cells in an Excel worksheet contain more than one value. A telltale indicator of this problem is a cell with a dash as a delimiter.

Now you must use the Excel string operations to isolate one of the values. That adds complexity and the risk of errors.

A better solution is to ensure every cell contains only one value. Adding columns to a worksheet cost nothing and adds clarity.

Selecting multiple worksheets spells danger

In Excel, you can select multiple worksheets and then apply your changes to all of them at once.

This productive feature can also make a huge mess if the worksheets are not wholly identical.

Manage this risk by:

1. Ensuring your worksheets are indeed identical.
2. Using the feature only for simple operations like creating identical headers and footers.

Pivot tables can be dangerous

Excel pivot tables are a powerful feature for summarizing data in tables. However, data quality shortcomings can make the calculations wrong or at least misleading.

One common problem is that Excel pivot tables do not auto-update like Excel workbooks. Revising a workbook and forgetting to update the pivot table occurs all too often.

Update pivot tables before you share or print your work.

 —

Be careful out there in the real world. Excel is a marvelous tool. However, small mistakes can lead to significant, embarrassing recommendations. Taking the actions above reduces that risk.

Next: Excel for Engineers, Part 2: Follow These Best Practices