Financial models in Excel come in various types, including financing, valuation, investment, etc., with each model containing complex formulas. Sometimes it can be difficult to understand how the formulas interact with each other. Therefore, a tool is needed to understand the formulas in Excel better. In Excel, this tool is the menu Audit Formulas / Trace Dependents or Trace Precedents. Formula auditing in Excel can help finance professionals to understand how the model works. The audit formulas function in Excel offers a very useful tool to understand better how the cells are linked to each other and how the model logic works.

How to audit and trace dependencies of formulas in Excel? In this article, we will explain how you can trace dependencies between cells via the auditing trace dependent and precedents in Excel.

Audit Formula In Excel By Tracing

Company ABC is conducting a 3-year Sales projection. Assume the worksheet below presents the details of its calculations.

Assume you want to understand and audit formulas in the Excel worksheet.

1. How did the financial model arrive at Gross Profit?
2. How did the financial model calculate Sales?
3. How was the Cost of Sales calculated?

You can simply click on the cell one by one to see the formulas or use Excel’s Show Formulas tool to display the formulas instead of the results.

Steps to use Excel’s Show Formulas tool:

1. Go to the Formulas Tab
2. Under the formula auditing in Excel group and click the Show Formulas button.

This allows you to hide the results and view the cells and formulas only.

Tip: Click the Show Formulas button again to display the results and hide formulas.

But this appears confusing and lacks clarity, especially for financial models with multiple and complex formulas. All in all, this may not be helpful for formula auditing in Excel.

Formulas and cells in financial models are often intertwined, and Excel provides many tools to make sure these incorporate the correct values. There are 2 formula auditing in Excel tools that help identify what type of role cells play in a formula.

• Trace Precedents in Excel
  • Precedent Cell. Cell or range of cells that provide data or value to a formula
• Trace Dependents in Excel
  • Dependent Cell. Cell or range of cells that reference or rely on data or value from other cells.

Let’s see how Trace Precedents and Trace Dependents in Excel help us better understand the formulas and calculations done in a financial model.

Trace Precedents In Excel For Formula Auditing

To trace and display cells that provide data or values to another cell, use the Trace Precedents in the Excel button.

Steps on how to use Trace Precedents in Excel

1. Select the cell to trace or audit.
2. Go to the Formulas Tab
3. Under the Formula Auditing in Excel group, click on Trace Precedents
4. Follow the dots and arrows displayed

Each dot corresponds to a precedent cell, while the arrowhead points to the end value or the selected cell.

1. Blue Dots and Arrows. Indicate the cells that contain no errors.
   1. Red Dots and Arrows. Indicate the cells that contain errors
   2. Black Dashed Lines and Arrows. Indicate the cells are contained in a separate worksheet or active workbook.
2. Continue auditing formulas in Excel by clicking the Trace Precedents in the Excel button again and again.

How Do You Trace Precedents in Excel?

To Trace Precedents in Excel, simply follow the dots and arrows displayed.

Using the Company ABC’s financial model, we can trace precedent cells in the Excel worksheet by first selecting the cell to audit. This serves as the starting point.

Since you want to understand how Gross Profit was computed, the selected cell is Cell E5.

To view the precedent cells, go to the Formulas tab and under formula auditing in the Excel group, click on Trace Precedents in the Excel button.

Cells E3 and E4 (represented by the blue dots) are precedent cells of cell E5 (represented by the arrowhead). In other words, the Sales and Cost of Sales provide their values to the Gross Profit formula. So, any changes to Cells E3 and E4 will affect Cell E5.

If you click on the Trace Precedents in the Excel button for the 2^nd time, Excel further displays other precedent cells.

Continue following the blue dots and arrows.

Cells E7 and E9 are precedent cells of E3.  That is, Sales Volume and Price per unit provide their values to calculate Sales.

Cells E8 and E10 are precedent cells of E4. Sales Volume and Cost per unit provide their values to calculate the Cost of Sales.

If you click on the Trace Precedents in Excel for the 3^rd, 4^th, and 5^th time, Excel further displays other precedent cells and the different levels of formula at work.

Applying the same steps to the rest of the years, you have a graphical presentation of the audited formulas in Excel. The Trace Precedents in Excel button allows you to backtrack through all the cells used in the formula to arrive at the end value.

How Do You Trace Precedents In Excel To Another Sheet?

Financial models in Excel are often composed of various worksheets. Trace Precedents in Excel button identifies cells found in a separate worksheet or active workbook. When a precedent cell is contained in a separate worksheet, Excel displays a black dashed line and arrow with a worksheet icon.

Steps for formula auditing in Excel by tracing precedent cells in a separate worksheet

1. Double-click on the Black Dashed Line
2. Select the Worksheet from the Go To dialogue box and click Ok. Excel will direct you to the separate worksheet containing the precedent cell.

Trace Dependents In Excel To Audit formulas

Instead of backtracking and tracing cells that provide data or value to another cell, Excel can audit the formulas in Excel worksheets in the opposite direction. Using the Trace Dependents in Excel button, Excel traces cells that rely on data or values from other cells.

Steps on how to use Trace Dependents in Excel to audit formulas

1. Select the cell to trace or audit
2. Go to the Formulas Tab
3. Under the Formula Auditing in Excel group, click on Trace Dependents in Excel button
4. Follow the arrows displayed to trace or audit

Each dot corresponds to a dependent cell, while the arrowhead points to the end value

1. Blue Dots and Arrows. Indicate the cells that contain no errors.
   1. Red Dots and Arrows. Indicate the cells that contain errors
   2. Black Dashed Lines and Arrows. Indicate the cells are contained in a separate worksheet or active workbook.
2. Continue auditing formulas in Excel by clicking the Trace Dependents in Excel button again and again.

How Do You Trace Dependents In Excel?

Similar to tracing precedents, to trace dependents in Excel, simply follow the dots and arrows.

We will continue using the same selected cell, Cell E5.

To view the dependent cells, go to the Formulas Tab, and under Formula Auditing in Excel, group click trace dependents in the Excel button.

Excel displays a dialogue box indicating no cells are relying on its value for calculating a formula. Simply, Cell E5 has no dependent cells, and any changes will not affect other cells.

If we select Cell E3 and click the trace dependents in the Excel button, we can see that Cell E5 is a dependent cell.

If we select Cell E4, the trace dependents in the Excel tool also display that Cell E5 is a dependent cell.

So, any changes made to the selected cells will affect the dependent cells.

Below illustrates how the trace dependents in Excel tool work when selecting the other remaining cells.

The trace dependents in the Excel tool also work for dependent cells contained in a separate worksheet or active workbook. Simply double-click on the black dashed line.

How Do You Remove Arrows In Trace Precedents and Trace Dependents In Excel?

Once you are done auditing formulas in Excel, you can remove the dots and arrows in 2 quick steps.

1. Go to Formulas Tab
2. Click on the Remove Arrows button under Formula Auditing in the Excel group. Alternatively, click the drop-down menu for more options.

Things To Know About Formula Auditing in Excel

• You will hear a beep sound when the Trace Dependents and Trace Precedents in Excel are done auditing the formula in Excel.
• Trace Dependents and Trace Precedents in Excel will not be able to trace the following:
  • When the selected cell refers to formulas contained in a closed separate workbook
  • References to named constants, text boxes, embedded charts, or pictures on worksheets.
  • PivotTable reports
• The Trace Precedents in Excel keyboard shortcut, CTRL + [, does not display arrows directing you to all the precedent cells. Instead, it highlights all precedent cells and takes you to the 1st precedent cell.
• Trace Dependents in Excel keyboard shortcut, CTRL + ], also does not display arrows for all the dependent cells but instead highlights the dependent cells and takes you to the 1st dependent cell.
• To audit formulas in Excel that result in errors, you can use the Error Checking button under Formula Auditing in Excel group.
• Another tool used to audit formulas in Excel worksheets under the Formula Auditing in Excel group is the Watch Window button. The window looks similar to a dialogue box and lets you view and monitor formulas.

Here’s a list of Impairment Test model templates that you can use as a reference for testing what you learned above: