Originally published at: How to trace precedents and dependents in Excel sheets | ONLYOFFICE Blog
If you often work with formulas and calculations in Excel sheets and feel tired of having to check them manually, you can take advantage of the Trace Precedents and Trace Dependents features. These tools are designed to graphically display and trace the relationships between your formulas and the corresponding cells. Read this article to find out more.
What are the Trace Precedents and Trace Dependents features in Excel?
Excel spreadsheets make it easier to perform complex calculations due to a set of standard formulas and functions, which come in handy in different situations. For example, using the AVERAGE function, you can quickly calculate the average value of a given range of numbers, and the SUM function is designed to add the numerical values in a range of cells in your Excel sheet.
However, when working with Excel formulas, you may sometimes face wrong values or error messages that you must correct to get the required values. Of course, you can manually check each cell and argument and fix all errors to get the correct results, but this will cost you a lot of time.
For your convenience, there exist special tools that you can use to audit your calculations with ease — Trace Precedents and Trace Dependents. The former are cells or groups of cells that are referred to by a formula of the active cell. The latter contain formulas that refer to other cells. Both tools help you to check your formulas for accuracy or find errors so that you can better understand the relationship between the active cell and other cells in your Excel sheet.
Let’s find out how to use Trace Precedents and Trace Dependents to graphically display the relationships between your formulas and the corresponding cells in Excel sheets using the example of ONLYOFFICE Spreadsheet Editor.
How to use Trace Precedents in ONLYOFFICE Spreadsheet Editor
Starting from version 7.5, ONLYOFFICE Spreadsheet Editor allows you to display and trace the relationships between cells and formulas with tracer arrows. Let’s take a look at a practical example.
The Excel worksheet below shows the number of gold, silver and bronze medals each country earned during a sports competition.
Here we use the SUM function to calculate how many medals of each type all the countries have won and the total number of medals. You can check all the applied formulas by clicking the Show Formula button on the Formula tab. As you can see, SUM is used several times in different cells.
Now you can easily check if all these calculations are correct and if there are any errors without having to check each formula. You just need to activate the Trace Precedents option.
To do so, you need to start by selecting an active cell. For the first country in our list, it’s cell G3. Select the target cell and click on Trace Precedents on the Formula tab. The blue arrow will trace all the cells that provide data to the SUM function in cell G3. This way, you will be able to understand that the current value in G3 is correct and displays the correct number of gold, silver and bronze medals the first country has won.
Using the same method, you can quickly check all 25 countries to make sure nothing has been missed, and column G displays the correct values for each participating country.
Additionally, using the Trace Precedents option, you can check the correctness of the values in cells D28, E28, F28 and G28. They represent the total number of medals. In our case, nothing is missing, the SUM functions are used correctly.
How to use Trace Dependents in ONLYOFFICE Spreadsheet Editor
Another useful feature that you can use to monitor the relationships between formulas and particular cells is Trace Dependents. By activating it, you will see all the cells that are affected by the active cell.
Let’s get back to our Excel sheet. Cell D3 shows the number of gold medals the first country, the USA, earned during the competition. Select this cell and click on Trace Dependents on the Formula tab. You will see two tracer arrows that will lead to cells D28 and G3 with the SUM function.
With the help of the Trace Dependents feature, you can easily check what formulas and functions reference to a particular cell in your Excel sheet.
How to remove tracer arrows in Excel sheets
When you are done with making sure your formulas are used correctly, you can get rid of the tracer arrows that appeared after activating the Trace Precedents and Trace Dependents options.
If you want to delete all the tracer arrows at once, you need to access the Formula tab and click Remove arrows. This action will remove everything.
You will see some additional options if you activate the drop-down menu next to the Remove arrows button:
- Remove Precedents Arrows
- Remove Dependents Arrows
Using one of the options above, you can remove only a certain category of the tracer arrows.
Get ONLYOFFICE Spreadsheet Editor
Easily display and trace the relationships between your formulas and the corresponding cells in Excel sheets online by creating a free ONLYOFFICE DocSpace account or offline by downloading a free desktop app for your Windows, Linux or macOS:
START IN THE CLOUD GET FREE DESKTOP APP
Discover what other features you can use in version 7.5 of the ONLYOFFICE suite: