Trace Precedents
Cells or group of cells that affect the value of the active cell
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
What are Trace Precedents?
Trace precedents are cells or groups of cells that affect the value of the active cell. Microsoft Excel provides users with the flexibility of doing complex calculations using formulas such as average, sum, count, etc.
However, the formulas may sometimes return wrong values or even give an error message that you must resolve in order to get the correct values. Excel makes it easier to resolve calculation errors by providing easy-to-use tools such as Trace Precedents that one can use to audit the calculations.
Summary
- Trace Precedents is an inbuilt feature in Microsoft Excel.
- The tool helps audit a formula in order to understand the relationship between the active cell and other cells.
- To access trace precedents, go to Formulas Tab > Formulas Auditing > Trace Precedents.
How Trace Precedents Work
Trace Precedents are used to locate the source of an error to understand how formulas are arranged before making changes to the Excel worksheet. Using the Trace Precedents tool in Excel helps analysts check cells for potential links to an active cell. Here is how to use Trace Precedents to trace the source of errors in an Excel workbook.
- Open the Excel worksheet that contains the formulas that affect the value of the cells you are analyzing.
- Scroll to the cell with the formula you need to trace and select the cell.
- On the Excel tab, go to the Formulas tab and locate the Trace Precedents tool under the Formula Auditing section.
- Next, click the Trace Precedents button and wait for Excel to audit the cells. If a blue arrow appears, it indicates that there are no errors on the current Excel worksheet. Click “Trace Precedents” again to continue auditing.
- If a black dash line appears, it shows that there are cells related to the active cell that is located in a separate workbook. The black dashed arrow points to a small icon. Double-click the black dash line to open a dialog box. The dialog box contains a list of the cells that are related to the active cell. Double-click any of the cells to open the separate worksheet and the specific cell.
Additional notes
If red arrows appear after running the precedents tool (instead of blue or black dash arrows), it means that the cells are the source of the error in the active cell.
When auditing the cells, click the Remove Arrows button under the Formula Auditing tool section each time you move to the next cell so that you can distinguish between resolved and unresolved cells. Excel does not remove the arrows automatically, which can cause confusion.
Practical Example: Trace Precedents
XYZ Limited uses Excel formulas to calculate revenue projections for each quarter for the year 2019, based on the previous year’s revenues. In the Excel worksheet below, cell F11 shows an error that needs to be resolved to get the total revenue projections for Q3. To get the source of the error, we can use the inbuilt Trace Precedents tool to audit the worksheet.
Select the cell you want to audit (cell F11 in our case), go to the Formulas tab, and then click the Trace Precedents button. The command will display blue arrows that show the specific cells that affect the selected cell. The arrow points to the cell with the formula that references to the precedents.
Clicking the trace precedents button once more shows more cells that affect the value of the active cell.
In our example, the error in F11 occurs because one of the cells selected when calculating the revenue projections was incorrect. We selected cell D11 and Cell F2. Cell F2 was incorrect since it does not contain a numerical value for the quarterly projection of Q3. The correct cells were D11 and E11.
The cause of the error was human error, where the person doing the calculations selected the wrong cell. To correct the error, simply edit the formula and select the right cell for getting the total revenue projection for Q3.
Once the error’s been resolved, you should remove the arrows manually by selecting the cell with an arrow and then clicking the Remove Arrows button under the Formula Auditing tool.
What Causes Excel to Produce a Beep Sound When I Click Trace Precedents?
If Excel produces a beep sound, it means that the Trace Precedents tool is already done with tracking the cells, and there is no precedent cell. It may also mean that the tool can’t track any further due to an untraceable item on the Excel worksheet.
Examples of items that are not traceable using the formula auditing tools include:
- Formulas in another closed workbook that reference to the active cell.
- References to pictures, text boxes, and embedded charts on the worksheet.
- References to named constants.
- Pivot table reports.
Excel Precedents Keyboard Shortcuts
There are specific keyboard shortcuts that can be used to track how formulas are linked within an Excel model. The shortcuts include:
Trace Precedents: Press “Ctrl” followed by the square bracket “[“. The shortcut takes you to the first cell used by the active cell with the formula. However, it does not draw arrows linking the cells related to the active cell.
Trace Dependents: Press “Ctrl” followed by the right-facing square bracket “]”. The keyboard shortcut takes you to the first cell that the active cell points to. However, it does not draw arrows to every cell that is affected by the active cell.
More Resources
CFI is the official provider of the global Financial Modeling & Valuation Analyst (FMVA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional CFI resources below will be useful:
