Trace Precedents and Trace Dependents

If I change the value of this cell, how do I know which cells it will affect in my workbook? I’m in the formula bar and I want to quickly find the cells that make up the formula? I’m reviewing a workbook and need to understand quickly how the cells in the workbook work together?

These are just some of the questions that I’ve been asked over my career and which can easily be solved by the Trace Precedents and Trace Dependents buttons. They therefore shave a lot of time off workbook review and are consequently an ideal tool for auditors, finance managers … pretty much anyone who needs to do review work over an Excel workbook.

How do I use it?

Job one of course is find the cell that you want to interrogate and select it. From there go to the Formulas tab and you’ll find both options in the Formula Auditing section of the ribbon. 

Trace Precedents will point you to the cells that participate in the formula that make up the cell you’ve clicked on.

Trace Dependents on the other hand will show you all the formulas where the cell you’ve clicked on participates. Very handy for figuring out what will happen if you change the information in the active cell.

What will happen when I select either option?

It depends!

If the cells that the button highlights are on the same worksheet then you’ll get a blue line, from the active cell leading to the participating cells and with little blue circles on the participating cells. If the participating cells are not in the same worksheet then you’ll get a dotted line with a little table symbol. Click on the dotted line and you’ll be presented with a “Go to” menu with all the participating cells references. You can click on any one that you want to investigate and then the go to menu will take you to that cell.

How do I get rid of the pesky arrows?

Once you’ve got the information you need then go back to the Formulas tab, back to the Formula Auditing section of the ribbon and select Remove Arrows. You’ll need to do this in every worksheet where there is an arrow.

Play Video