Auditing errors in a worksheet can sometimes be tougher than creating the worksheet from scratch, especially if the worksheet involves complex formulas that refer to multiple cells that in turn contain formulas too.
To ease the pain of error auditing, some spreadsheet softwares provide ways to trace the source of the errors.
For example, Excel provides the ‘Trace Precedents’ and ‘Trace Dependents’ buttons.
Unfortunately, the Google Sheets Trace Dependents and Trace Precedents feature has not yet been included.
However, there are alternative ways.
In this tutorial, we will go over some alternative ways to trace dependents and precedents for your formulas in Google Sheets.
- Using the Formula bar
- Using the Find and Replace feature
- Using an add-on
Table of Contents
What are Precedents and Dependents?
Precedents of a cell are the cells that affect its value. So, precedents for a cell that has a formula are those cells that the formula references.
In the following image, the precedents for cell C2 are cells A2 and B2.
Dependents of a cell, on the other hand, are cells that are affected by it. So, dependents for a cell, say A2, are all those cells that contain a formula referencing cell A2.
In the above image, the dependent for cell A2 is cell C2.
A single cell can sometimes serve as both a precedent and a dependent.
Why Trace Dependents and Precedents?
If you have large datasets that involve multiple formulae referring to multiple cells in multiple sheets, you will find your work getting increasingly complex. This may result in an increased risk of errors, invalid data, or incorrect formulae.
Tracing precedents and dependents helps trace back errors, so you can exactly pinpoint the cell or formula that is causing the error and correct it.
You can also use this technique to ensure that all the formulae in your worksheets are referring to the right cells and values. This helps greatly reduce the risk of incorrect formulae, so that you can be more confident in the results returned by them.
How to Trace Dependents and Precedents in Google Sheets
Tracing dependents and precedents requires you to obtain the full stack of cells referenced by a given cell’s formula. It is easier to do this if you have a graphical view of the cells being referenced, so that you can quickly trace back to the source that may be causing an error.
In Google Sheets, there’s no direct menu or button that can help you trace dependents and precedents of a formula, but there are ways around this limitation. Let’s take a look at three such ways.
To demonstrate these ways, we are going to use the following spreadsheet:
In this worksheet, we get an error in cells E15, A15, H15 as well as cells B2, B7 and N2.
To understand where these errors originate from, we need to trace back to their precedents. We also need to trace the dependents of these cells so we know which other cells are affected by them.
Â
Using the Formula Bar to Trace Precedents in Google Sheets
Tracing precedents of a function is quite simple.
Let’s say you want to trace the precedents for the formula in cell H15 to find the source of the error. All you need to do is click on the cell and click on each cell reference in the formula bar.
You will notice all the precedents of the formula get highlighted in the respective colors of the cell references in the formula.
For example, the image below shows what happens when you click the reference $A$2:$N$6 in the formula bar. Since this range is displayed in purple inside the formula bar, all the cells in this range are displayed in purple, with a purple dashed line surrounding the entire range of cells.
Similarly, when you click on the reference to the range B7:D7 in the formula of cell A15, you will notice all the cells in this range surrounded by a yellow dotted line.
Using this Google Sheets feature, you can also trace back to see which cells are affecting the result in cells E15, H15 and A15.
For example, since cells B7 to D7 are precedents to cell A15, the error could be coming from the #ERROR in cell B7. This can then be traced back to cell B2 using the same technique.
Using Find and Replace to Trace Dependents in Google Sheets
The Find and Replace feature of Google Sheets provides quite a quick and easy way to trace dependents of a cell.
For example, now that you know that cell B2 is erroneous, you might want to know which other cells are dependent on cell B2. For this you can open the Find and Replace window by pressing CTRL+H from your keyboard and typing the cell reference B2 in the Find field.
Make sure the checkbox next to ‘Also search within formulas’ is checked, so that Google Sheets checks all the formulas for a reference to cell B2.
Press Find to track the dependent cells one by one. You will find that the cells N2 and B2 are directly dependent on cell B2, and that is why these cells show errors.
One important thing to note is the above two methods only help track the immediate precedents / dependents of a cell. If the precedent cell in turn depends on other cells or if the dependent cell in turn is used in other cells, then the errors might not be that easy to track.
An easier way would be to use a Google Sheets add-on. In the next section we will discuss how to do this.
Using an Add-on to Trace Precedents and Trace Dependents in Google Sheets
You can use Google Sheets add-ons to extend functionality of Google Sheets. These are third-party applications that you can integrate into your sheet to get specialized facilities.
Click here to read about other add-ons on Google Sheets.
An add-on that is going to make it really easy to track dependents and precedents in Google Sheets is the Formula Tracer, by Casabase Software.
This add-on lets you track both direct and indirect formula precedents and dependents for a selected range. The application also works with named ranges and importrange.
You can install this add-on for free from the Google Workspace Marketplace, as follows:
- From the ‘Add-ons’ menu, select ‘Get add-ons’.
- This opens the Google Workspace Marketplace.
- In the search bar, type ‘Formula Tracer’.
- Select the Formula Tracer app from the app thumbnails that appear in the search result.
- Click Install.
- Follow the instructions until you get the message ‘Formula Tracer has been installed’.
- Click Done.
- Close the Google Workspace Marketplace window.
- You should now see ‘Formula Tracer’ added as a menu option under the add-ons menu.
Now whenever you want to trace a precedent or dependency, you can simply select Formula Tracer->Open from the Add-ons menu. The application will open as a side menu on the right.
You will notice there are two buttons:
- Find Precedents
- Find Dependents
Simply select the formula you want to track and press the appropriate button from the sidebar.
Let us try to trace back to the source of the error in cell H15. So select cell H15 and press the ‘Find precedents’ button from the Casabase Formula Tracer sidebar.
The formula tracer will show an easy-to-read tree-like structure with all elements of your formula and their sources. The tree displays the cell you want to trace as the parent node and the references that make up the formula in that cell as children, or descendants of the node.
These descendents are again displayed as parent nodes to cell references that make up their formulas, and this goes on.
When you hover over a node, you can see the formula in that cell. When you click on a node, it highlights the cell in your worksheet that it corresponds to.
As can be seen from the above Precedents tree, the root of the problem is at cell B2, which has the wrong formula parameters. Fixing this value is going to fix all the errors in the worksheet, as shown in the screenshot below.
You can use the same system to track dependents of a cell.
The Bottom Line
In Google Sheets, the Trace Dependents and Trace Precedents features are not yet included.
However, in this tutorial we showed you how to trace precedents and dependents in Google Sheets using the simple formula bar and the Find and Replace feature.
We also showed you how to get a more detailed visual of your precedents and dependents using a Google Sheets add-on.
We hope this helps you audit and correct formula errors in your worksheet quickly and effectively.
