Auditing formulas by identifying precedents and dependents

- Excel includes a number of very useful tools that you can use to ensure your formulas summarize the proper values. One of those tools gives you the ability to determine which cells are used in which worksheet formulas. In this movie, I will show you how to use tracing arrows both to identify cells that provide values to another formula and to indicate which other cells use values from a given cell. This workbook contains visitor information that might be of use to our hotel owners. If you take a look at the formula in cell D3, you see that it's the Sum of Visitors and the values that it uses are the range B4 to B15, so these cells here.

If it were not clear which cells were used or if the cells were in a variety of different parts of the worksheet, then I might want to use tracer arrows to identify which cells provide values to the formula in D3. To do that, I can go to the Formulas tab of the ribbon and with cell D3 selected, go over to the right side of the ribbon tab and click Trace Precedents. Clicking Trace Precedents identifies which cells provide values for Sum of Visitors.

And you can see that there is a dot and it indicates that all of the cells within the blue bordered region provide values for the formula in D3. If you want to remove your arrow, you can go back to the Formula tab and click Remove Arrows. In the same way, you can display a cell's dependence which shows which other cells use the selected cell's value as an input. So in this case I'll click cell D3, here it is already clicked, and then again on the Formulas tab, I'll click the Trace Dependents button.

So I can see that cell D3 is used in the formula in cell D9. And I can see that that cell also uses the value in D6. Tracer arrows make it easier to identify from where the values used in a formula are taken, and they also indicate values in other worksheets or even in other workbooks. So let's say that I click cell D9 and then click Trace Precedents. You can see that I have the value from cell D3, but I also have a black line with a dot that indicates that there is another worksheet that contains a value used in this formula.

And if I click the line, double click it, you can see that the Go to dialog box appears. I'm given Tracing.xlsx and then Sheet2 as the reference. I'll go ahead and go to that and there is the value that is used in the other formula and that is the Cost to Acquire a New Visitor. Identifying a cell's precedence and dependence will help you discover and correct many formula inaccuracies. They can also help you understand the logic behind a formula if you're seeing it for the first time.