Formula dependencies in Excel

• Influencing cells
• Remove arrows
• Dependent Cells
• Show formulas
• Checking for Errors
• Formula calculation

Formula dependencies in Excel allow you to display relationships between formulas and cells. The example below will help you understand formula dependencies quickly and easily.

=ROUNDUP(C10/C12,0)

=ОКРУГЛВВЕРХ(C10/C12;0)

Influencing cells

As you can see in the picture above, you have to pay $96. To display arrows indicating which cells are used to calculate this value, follow the instructions below:

1. Highlight a cell С13.
2. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Trace Precedents (Influencing cells).

   

   Result:

   

   As expected, the total cost of the order and the size of the group are used to calculate the cost per person.

3. Click on again Trace Precedents (Influencing cells).

   

   As expected, a list of various costs is used to calculate the total cost.

Remove arrows

To remove the arrows, do the following:

1. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Remove Arrows (Remove arrows).

Dependent Cells

To display arrows that indicate which cells depend on the selected cell, follow the instructions below:

1. Highlight a cell С12.
2. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Trace Dependents (Dependent cells).

   

   Result:

   

   As expected, the cost per person depends on the size of the group.

Show formulas

By default, Excel shows formula results. To show formulas and not their results, follow the instructions:

1. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Show Formulas (Show formulas).

   

   Result:

   

Note: Instead of clicking on Show Formulas (Show formulas), you can press the keyboard shortcut CTRL + `. Key «`» located above the key Tab.

Checking for Errors

To check for common errors that creep into formulas, do the following.

1. Enter the value 0 in the cell С12.

   

2. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Checking error (Checking for errors).

   

   Result:

   

   Excel finds errors in a cell С13. The formula tries to divide numbers by 0.

Formula calculation

To debug a formula by evaluating each part individually, do the following:

1. Select cell С13.
2. On the Advanced tab Formulas (Formulas) section Formula Auditing (Formula dependencies) click Evaluate Formula (Compute formula).

   

3. Click on Evaluate Formula (Compute the formula) four times.

   

Excel shows the result of the formula: