In this article we will cover step by step the options available to help resolve errors in Excel formula.
Next, we clicked Formulas → Trace Precedents:
You will now see a blue arrow drawn across the current row, where all of the data used by the currently selected formula appears. Each blue dot on this arrow corresponds to a value that is used in the formula, while the arrowhead points to the end value. In this particular example, you can see that cells A2, B2, and C2 are all used by the formula in D2 to calculate the end value:
The Trace Dependents command works the opposite way of Trace Precedents: it highlights all the items that depend on the value in the current cell. This command will not work for values in the Monthly Payment column (D) because it has no dependents. However, the Loan Period (B) column does have dependents.
If we Click cell B4 and then click Formulas → Trace Dependents:
Another blue arrow will be drawn from the selected cell, pointing towards the dependent cell, which in this case is the formula in cell D4:
As you can see in the images, the Trace Precedents and Trace Dependents commands can be used at the same time.
To remove the arrows, click Formulas → Remove Arrows (drop-down arrow):
Clicking on the Formulas → Remove Arrows command directly will remove all arrows from the worksheet. The drop-down command allows you to remove all arrows, remove only precedent arrows, or remove only dependent arrows:
For this example, we have clicked Remove Arrows. All arrows on the current workbook will be removed:
To do this, click Formulas → Show Formulas:
All formulas will now be displayed in full. This command will also expand the column widths for all columns so that formulas are easier to read:
Click Formulas → Show Formulas again to return to the default view that shows calculated values and returns columns to their normal widths.
If you are using later versions of Excel, you can also use the function FORMULATEXT.
The syntax for FORMULATEXT is
=FORMULATEXT(reference)
where reference is the cell containing the formula that you want to display.
A quick reminder for those that have forgotten, you can edit the formula in a selected cell by pressing F2. When a formula is in edit mode, the corresponding cells in the workbook are color coded. This makes the formula easy to debug.
When you are entering a formula or in edit mode, you can check for accuracy as you go along. Highlight any part of the formula in the formula bar and press F9.
This will calculate that part of the formula for you and show you the result in the formula.
However using F9 comes with a heath warning. You must press ESC to return the formual to the original state. If you press enter when in F9 mode, the formula will accept the calculated value and not the original formual.
To use this command, first select the cell that contains the formula that you would like to work with. For this example, we have selected cell D2:
Next, click Formulas → Evaluate Formula:
This action will open the Evaluate Formula dialog box, which will show the formula that will be evaluated. Click Evaluate to step into the first part of the formula:
In this case, you can see that “A2” in the formula was replaced to show “.06” (that cell’s actual value). Click the Step In button:
Clicking the Step In button will dive one level deeper in the formula, just as the Show Precedents/Show Dependents command would backtrack an additional level. Here, the value of B2 is shown in a separate text area. Click Step Out to hide the separate text area and progress to the next step:
Now, the next value is highlighted and the option to step in is available again. Click Evaluate to continue:
Continue clicking the Evaluate button to step through all the calculations one at a time. Stop when all the values of the formula are displayed and the entire formula is underlined. Click the Evaluate button once more:
The end result of this formula will now be displayed:
Clicking Restart will start the process over from the beginning. Click Close:
To view and manage these settings, click File → Options:
Then, click the Formulas category. Options to help you construct formulas and check them for errors can be found here:
If you hover over the option button, you will see a ScreenTip describing the error. Clicking the option button itself will show commands for resolving the error and modifying error checking options:
Click inside the worksheet to close the menu.
Run an error check by clicking Formulas → Error Checking:
When the Error Checking tool is launched, it will immediately search your workbook for any formula errors. Any errors that are found will be listed one at a time. In this example, an error will be found in D2:
Cell D2 contains a naming error: the cell reference “A” is incomplete. You can use the buttons on the right to:
Click Next to move to the next error
If no other errors are found in the current worksheet, a dialog will be displayed. Click OK:
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.
