Excel Formula Dependency Troubleshoot Step-By-Step

Excel is equipped with tools for tracking formula dependencies. You can find these tools on the "Formulas" tab under the "Formula Auditing" section. Let's delve into the details of these tools.



Trace Precedents Tool

Which Formulas Values Are Transmitted To

Prepare a worksheet with formulas as shown below:

Initial Formulas for Example

Check which cells are used as data sources for calculating the result in cell F3.

  1. Select: "Formulas" - "Formula Auditing" - "Trace Precedents" to see the data sources for F3.
    2. Arrows of Dependency Links
  2. To trace the complete dependency chain and find out where the data in cells C3 and D3 comes from, select "Trace Precedents" again.
    3. Dependency Links Diagram
  3. Remove the displayed arrows in the source value scheme using the "Remove Arrows" tool.
    4. Clearing Dependency Arrows

Note: Similar arrows are displayed when selecting the "Error Checking" option from the expanded menu.

Trace Dependents Tool

On the same worksheet, check which formulas use the content of D3.

  1. Go to cell D3.
  2. Select "Trace Dependents."
  3. Press the "Trace Dependents" tool again to continue the chain scheme.
Data Chain Which Formulas Values Are Transmitted To

Remove the displayed arrows again using the "Remove Arrows" tool.

Error Checking and Trace Tool

With this tool, you can determine the cause of erroneous values in cells after calculating the formulas.

To evaluate the effectiveness of the "Error Checking" tool in action, let's simulate the following situation. Suppose we have a formula on a worksheet that cannot find the required value within the range of cells and, as a result, displays the #N/A error.

Error Control

Perform the following actions:

  1. Select "Formulas" - "Formula Auditing" - "Error Checking." Excel will immediately move the cursor to the first cell containing an error.
  2. In the "Error Checking" dialog box, choose an action you would like to take with this error. For instance, the "Skip Error" button will automatically search for the next erroneous value.

You can also trace the dependency of error occurrence by using the "Trace Error" tool:

Link Tracking in Errors

download file Download

Now it's clear how the formula refers to the data source, which returns an error in its cell during the calculation process.