Excel is the most common tool used by engineers of all disciplines around the world every day. There is almost no training available in Excel for engineering applications, yet those who master it can stand out from the crowd and gain the respect of their peers in the engineering community.

In this post, I’ll provide actionable tips to help you:

• Solve challenging engineering problems
• Communicate clearly through spreadsheets
• Get more done, faster
• Add value to your engineering team
• Be a leader in your workplace

1. Keep Track of Units with Custom Number Formats

When you use data in the wrong units the results are disastrous. Just ask these guys.

Most engineers keep track of units in their spreadsheets by entering them in a separate cell next to the data.

But there’s an easy way to store the units right in the same cell as the data.

By using customer number formats, numbers and text are displayed together and the cell can still be used in calculations.

Example: Adding Units to a Number with a Custom Number Format

Select the cell you want to modify and open the “Format Cells” dialog:

Select “Custom” from the list on the left:

Under “Type”, insert the units in quotation marks:

2. Name Cells to Preserve Your Sanity

Whether you are looking at someone else’s spreadsheet or one you created previously, understanding the logic of an existing spreadsheet can be painful.

The hardest part is decoding (or remembering) what value was contained in a cell, row, or column. Equations written in terms of variables are much easier to understand than those written with Excel’s cell notation.

The good news is that it is easy to write equations in terms of variables in Excel.

Example: Creating an Easy-to-Understand Formula with Named Cells

Select the variable cells and enter the variable names

Enter the formula in the appropriate cell, using the variable names instead of cell references.

3. Make Changes to Scatter Plots Quickly Via the Formula Bar

You’ve probably created countless charts in Excel using the GUI. Chances are, like many engineers, you always use the GUI for editing your charts too. But did you know that you can use the formula bar to make changes to your charts?

The formula bar offers an easy way to make changes to scatter plots quickly.

This feature can be used in many ways:

• Quickly update the x- and y-data selection
• Change the name of the series
• Change the order in which data series appear in the chart

The formula format for a chart series is easy to decode. It contains four parts:

• Series Name
• X-Data
• Y-Data
• Series Position

The formula looks something like this:

=SERIES(Name, X-Data, Y-Data, Position)

Let’s look at an example.

Example: Expanding a Data Selection

Selecting the data series in the chart below reveals that not all the data in the x- and y-columns has been selected. The formula shows that the x-data is contained in cells A2:A8 and the y-data is contained in cells B2:B8.

By changing $A$8 to $A$11 and $B$8 to $B$11 in the formula bar, all the rows of data are included in the chart. Of course, this example is trivial, but you can quickly see how this capability opens enormous possibilities.

4. Convert Function: Let Excel Find the Conversion Factors

Excel has a little known built-in function that can convert units from one measurement system to another.

Chances are, that throughout your engineering education and career you have committed “a few” unit conversion factors to memory.

Unfortunately, no one can remember them all.

You could look them up in a book or even online. But an even easier solution is to let Excel’s CONVERT function do the work for you.

Example: Using the Convert Function

The syntax for the convert function looks like this:

=convert(number, from_unit, to_unit)

Enter the formula using the format shown above. A complete list of recognized units is available here.

Excel converts the number from an existing unit system to the one you have specified.

5. Evaluate Formula: Find and Fix an Error

The likelihood of an error in a cell increases with the complexity of a formula. Unfortunately, it’s that very complexity that makes the error that much harder to track down.

Fortunately, Excel is equipped with a built-in tool to help you find the root cause of spreadsheet errors.

The formula evaluation tool allows you to go through each operation in a complex equation to find the step or steps causing the issue.

Example: Finding the Source of a #DIV/0 Error

Select the cell with an error. Then, from the Formula tab, select “Evaluate Formula”.

When you click “Evaluate”, Excel evaluates the underlined expression.

Continue clicking “Evaluate” until you find the error. Here, the source of the division by zero error is clear.