If you work a lot with formulas in Excel, you know that the value of one cell can be used in a formula in many different cells. In fact, cells on another sheet can also reference this value. This means that these cells are dependent on another cell.
Keep track of dependents in Excel
If you change the value of that single cell, it will change the value of any other cell that references that cell in the formula. Let’s look at an example to see what I mean. Here we have a very simple sheet where we have three numbers and then we take the sum and average of those numbers.
Let’s say you wanted to know the dependent cells of cell C3, which has a value of 10. In which cells will the values ??change if we change the value of 10 to something else? Obviously this will change the sum and average.
In Excel, this can be visually seen by tracking the dependents. You can do this by going to the Formulas tab, then clicking the cell you want to track and then clicking the Track Dependent button.
– /
When you do, you will immediately see blue arrows going from that cell to the dependent cells, as shown below:
You can remove arrows simply by clicking a cell and clicking the Remove Arrows button. But let’s say you have another formula on Sheet2 that uses the value from C1. Can you track dependents on a different sheet? What can you! This is how it will look:
As you can see, the black dotted line indicates what looks like a leaf icon. This means that there is a dependent cell on another sheet. When you double-click the dotted black line, the Go To dialog box appears, allowing you to navigate to a specific cell on that sheet.
So for the dependents, that’s pretty much everything. It’s hard not to talk about use cases when talking about dependents because they are very similar. Just as we wanted to see which cells are affected by the C3 value in the above example, we may also want to see which cells are affected by the G3 or H3 value.
As you can see, cells C3, D3 and E3 affect the value of the sum cell. These three cells are highlighted with a blue rectangle with an arrow pointing to the sum cell. It’s pretty simple, but if you have really complex formulas that use complex functions too, then there might be a lot of arrows moving all over the place. If you have any questions, please leave a comment and I’ll try to help. Enjoy!
–