Excel formula dependency troubleshooting Exaple

Most formulas in Excel use data from one or multiple cells and process them step by step. Changing the content of even a single cell leads to the automatic recalculation of the entire chain of values in all cells on all sheets. Sometimes these chains are short, and sometimes they are long and complex formulas. When the calculation results are correct, we don't pay much attention to the structure of the formula chain. However, when the calculation results in errors or when we receive an error message, we attempt to trace the entire chain to identify where an error occurred. We need to debug formulas to step by step check their functionality.



Analyzing Formulas in Excel

To trace all calculation steps of a formula, Excel offers a built-in tool that we will examine more closely.

Enter a formula into cell B5 that simply sums values from several cells (without using the SUM function).

Now, let's trace all the calculation steps and the content of the summation formula:

1. Go to cell B5, which contains the formula.
2. Select the tool: "Formulas" - "Formula Auditing" - "Evaluate Formula." The "Evaluate Formula" dialog box will appear.

3. In this dialog box, periodically click the "Evaluate" button while observing the progress of the calculation in the "Evaluation" window.

The underlining under the links informs us where values will be returned with each click on "Evaluate."

To analyze the next tool, we will use a simple credit calculator in Excel as an example:

To find out how we arrived at the result of calculating the monthly payment, go to cell B4 and select the tool: "Formulas" - "Formula Auditing" - "Trace Precedents."

Now we can clearly see the values that the monthly payment formula is working with. Next, we need to find out how these values were generated. For this, first go to the cell with the first value, C2, and again select the "Trace Precedents" tool.

As a result, a graphical scheme of the Excel formula calculation chain has been formed.

Note. To clear the scheme, select the "Remove Arrows" tool.

Useful Tip. If you press the keyboard shortcut CTRL+` (apostrophe above the Tab key) or select the "Show Formulas" tool, you will see that to calculate the monthly payment, we use three formulas in this calculator. They are located in cells B4, C2, and D2.

This approach significantly helps to trace the calculation chain. Return to normal mode by pressing CTRL+` again.

How to Remove Formulas and Keep Values in Excel

Now let's see how to remove formulas in Excel while keeping the values. When sharing a company's financial reports with third parties, you might not always want to reveal the calculation methods. The simplest solution in this situation is to share a sheet that contains only values and not the formula.

On the sheet, in cells B5 and C2:C5, there are formulas. Let's replace them with the final values of the calculations.

Download

Press the keyboard shortcut CTRL+A (or click on the intersection of row numbers and column headers in the top left corner of the sheet) to select all the contents.

Now, right-click the selection and choose the "Paste Special" option (or press the CTRL+ALT+V shortcut).

In the dialog box that appears, select the "Values" option and click OK.

As a result, the sheet no longer contains formulas, only the values of their calculations remain.