Excel has built-in tools that are very useful for troubleshooting formulas, even if the formulas are complex.

The example worksheet will calculate the force in the components of a structure made of a steel bar encased in concrete. There is a uniform load applied across the top.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

This kind of structure is statically indeterminate, so the spreadsheet uses the consistent deformation method to calculate the forces in the steel and concrete parts. However, it’s generating divide by zero errors:

Although you could go through this calculation manually to find the source of the errors, this section will demonstrate how to use Excel’s troubleshooting tools. All of the troubleshooting tools are in the **Formula Auditing **section of the **Formula **tab.

The first method is to trace the cell’s inputs using **Trace Precedents**. It’s possible that one of these cells is causing the error. Select the first cell with the #DIV/0! error and click “Trace Precedents” in the **Formulas **tab. Excel will draw arrows to the cells that are inputs:

One of these cells is actually zero, so there’s a good chance that cell is actually causing the divide by zero error. **Double-click **on the cell with the error to examine the formula. The term for the area of the concrete is in the denominator. So why is A_{concrete} zero?

Click “Remove Arrows” to clear this set of arrows. Then click the cell containing the zero for A_{concrete} and click **Trace Precedents**:

The area of the concrete section is a function of the steel diameter and the concrete diameter. **Double-click **on that cell to examine its formula. The formula subtracts the square of d_{steel} from the square of d_{concrete}. The diagram makes it obvious that the diameter of concrete must be greater than the diameter of steel, otherwise there isn’t any concrete. We could fix this error by entering a d_{concrete} greater than d_{steel}.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

There is another method to evaluate the source of errors in Excel called **Evaluate Formula**. This method works well for complex, nested formulas and ones that contain logical tests. It allows you to go through a formula, step-by-step, seeing what each part of the formula evaluates to.

**Select the first cell **containing the #DIV/0! Error. In the **Formula tab**, select **Evaluate Formula **from the Formula Auditing section. The Evaluate Formula window appears:

The part of the formula that will be calculated when you click **Evaluate **is underlined. If there’s a cell reference, it will insert the value of that reference. If it’s a calculation, it will perform that calculation. After you evaluate the first part, the second will be evaluated, and so on. By going through the formula step-by-step, it makes it easy to identify which part caused the error.

When you click **Evaluate**, Excel will replace F with the value from the spreadsheet:

Now the variable Asteel is underlined, telling you which value will be evaluated the next time you click **Evaluate**. Click **Evaluate twice**, and it will retrieve the values for both Asteel and Esteel.

Now that it has those two variables, the entire term is underlined. Click **Evaluate **again, and it will perform that multiplication. Click **Evaluate **again and it will move on to the terms in the denominator.

When Aconcrete is evaluated, it shows that variable is zero. Click **Evaluate **two more times to calculate the entire denominator. Click it once more to evaluate the division. This will generate the divide by zero error:

If you continue to click **Evaluate**, the error will propagate through the calculation.

The Evaluate Formula window and the tracer arrows allow you to find the reason for an error. However, anyone who uses this spreadsheet is allowed to enter values that could potentially result in an error. We’ll look at how to prevent that in the next two sections.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]