Chapter 4: Troubleshoot and Fix Formula Errors

Errors in simple Excel formulas are usually caused by syntax issues that can be easily fixed by correcting the syntax. For example, Excel may generate an error because a formula is missing a parenthesis and to fix the error you simply add the parenthesis to the syntax. On the other hand, a complex formula, for example, a nested formula with several levels, may not generate an error but fail to produce the expected result. This is called a logical error and they can be difficult to find.

Programming tools tend to have debuggers that can be used to step through the code to identify and fix logical errors. Fortunately, Excel 2019 provides several tools that you can use to step through complex formulas to troubleshoot and fix logical errors.

In this chapter, we will cover how to:

• Use Trace commands to trace the precedents and dependents in your formula values to identify the relationships between the results and cell references.
• Step through a nested formula one level at a time to see the evaluated result at each level.
• Use the Watch Window to display the value of a cell even when the cell is not in view.