Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel includes a number of very useful tools you can use to ensure your formulas use the proper values. One of those tools gives you the ability to determine which cells are used in which worksheet formulas. Cells can play two roles in a formula: as a precedent or as a dependent. Displaying a cell's precedents indicates which other cells provide that formula's input. So let's say, for example, we have the formula here in cell D3. If I want to see which other cells provide this formula its values, other than looking on the formula bar, I can go up to the Formulas tab and then click Trace Precedents.
When I do, Excel outlines the cells that provide the values to the formula in cell D3. You can see that you have the anchor, or the circle, and then the arrow pointing to the cell. You can remove an arrow by clicking the Remove Arrows button, and that is right here on the Formulas tab, in the Audit Formulas group. So, I'll do that, and the arrow goes way. You can display a cell's dependents to show which other cells use the selected cell's value as an input in their own formulas.
So let's stick with cell D3, and if I want to see which other cells use its value, I can click, again, on the Formula tab, Trace Dependents. So there are two other formulas on this worksheet that use the value in cell D3, and that's those two here. Tracer arrows make it easy to identify where values are taken from, and they can even indicate values in other worksheets or other workbooks. So let me just remove these arrows here. Now you might wonder exactly from where I'm calculating the cost to replace a 10% loss of customers.
So let's take a look at the formula. I'll click the cell, and you see that I have the value in D3, right, multiplied by .01. That's correct. I'm calculating 10% of the total visitors, and then I'm multiplying it by this other value, sheet2, cell B1. So I want to find the cells that are used in that formula, so I will click Trace Precedents. So I get cell D3, which I saw earlier, and I also get this other line, this black line with a worksheet icon on it.
If I want to go to that, and in other words, if I want to see it, I can click the line, move my mouse pointer over it until it changes into a pointing arrow, double-click the line, and now the Go to dialog box appears. I can click the Reference. In this case, it's on sheet2, cell B1, and click OK. When I do, Excel takes me there. So it looks like my marketing department has determined that it costs $47.00 to acquire a new visitor. With that information, I understand more about the formula and how it calculated its value.
I can switch back to sheet1, and if I want, I can remove the arrows. One thing I should point out: if you want to remove precedents or dependent arrows, but you don't want to remove both, then instead of clicking Remove Arrows on the main body of the button, you can click the down arrow, and you can either remove all arrows, remove just the precedent arrows, or just the dependent arrows. In this case, I want to get rid of all of them, so I'll just click Remove Arrows. Identifying a cell's precedents and dependents will help you discover and correct many formula inaccuracies.
They can also help you understand the logic behind a formula you're seeing for the very first time.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91253 Viewers
80 Video lessons · 138210 Viewers
59 Video lessons · 57039 Viewers
52 Video lessons · 70682 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.