Excel: Formula Dependency

While working with Excel files created by various people, you will encounter times when you need to find from where the data gets populated. This may not specifically be from the previous cell where another Formula or Value is. But rather each cell where data can change to affect a specific Value.

Excel has Auditing features specifically for this purpose. These can be found on the Excel Ribbon under the Formulas Heading -> Formula Auditing.

Trace Precedent

This shows arrows that indicate which cell affects the value of the currently selected cell.
In other words, it works backward finding everything that affects that specific Cell.

An example would be, you are doing an Inventory of the Board games you own. You want a list with the potential value. Creating the Spreadsheet you find that the total value does not look right but the values you entered are correct.

Trace Precedent

To see where the values are coming from you can click on Cell: F8 then click on the Trace Precedents. This will show you the immediate cells that Contribute to the cell value with Blue Arrows. By Clicking in again it will continue on the next section of cell that affects those cells Data.

You can do this until there is no more cell displayed that could affect the data of this cell or until you find what you are looking for. As per the Example, you can see that somehow the formula is looking at the wrong cells to calculate the Values and quickly fix the problem.

Trace Precedent


When referring to data, not on the same sheet you will find a stripped line showing the Value get affected by data on another sheet or book. You can double click on the Stripped line to show you where the data is and browse to the data. As per the example, if you click on B2 it shows the Data on the Games sheets will affect the value of B2

Trace Precedent


Trace Dependents

Show arrows that indicate which cells are affected by the value of the currently selected Cell.
In this case, it works Forwards finding everything that will be affected by the specific Cell.

Example, if B3 Changes price, both F3 and F4 will change. Showing you which cells will be affected when changing the value. If you continue to click Trace Dependents, it will continue for each of those cells until no other cells are affected.

Trace Dependents


********************************************************

If you liked what you read Please Share.
I’d love it if you followed me on YouTube and Facebook.

Also, feel free to subscribe to my posts by email.
Donations for the site can be made here.
Thanks for reading.

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *