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 incorporate the proper values. One of those tools give 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. A cell that is precedent supplies its value to the formula in another cell. A dependent is a cell that contains a formula and has other cells providing values to it. Displaying a cell's precedents indicates which other cells provide the formula's inputs. Now you can see the two cells here, B3 and B4 in this simple example, but say you had a more complex formula, you can still use this technique to determine where the values are coming from.
So, to display precedents, you go to the Formulas tab and then in the Formula Auditing group, you click Trace Precedents. You'll see that we have cells B3 and B4 as advertised that are highlighted with a dot. There's a line with an arrow and it goes to the cell that contains the formula that uses those two values. So that is how you trace dependents. When you're done doing the tracing, you can go back up to the Formula Auditing group, click Remove Arrows and the arrows disappear. So let's see how it works for dependents.
On this worksheet, we have Bonds, Notes and Loan Interest and we have a Total. This is a company's monthly debt service for the year 2009. If you want to see which cells use the value from cell B3 as part of its formula, you can click cell B3, again, on the Formulas tab and in the Formula Auditing group, click Trace Dependents. You'll see that once again you have the blue circle. The line draws an arrow to the cell that uses the value as part of it's formula. To remove the arrow, you go up to the ribbon and click Remove Arrows.
Now tracing precedents and dependents also works for values that are on other worksheets. So, for example, here in cell B5, I have a loan interest and that is on the Interest worksheet, which is the one we looked at previously. If you want to see which cell contains that value, you would select it, click Trace Precedents. Tracer arrows can also show you when values have been drawn from other worksheets. So, for example, on this worksheet, we have cell B5, which draws its value from the Interest worksheet.
So, if you click cell B5 and then go up to Trace Precedents to see where it draws its value from, Excel shows you this indicator, which tells you that the value is drawn from another worksheet. It can also be from another workbook entirely, but in this case it's just from another worksheet in the same workbook. So, to display that value, you click the line, the Go To dialog box appears and you can select the location to which you want to go, click OK, and it takes you to the Interest worksheet, cell B6, which is where the value came from. Identifying a cell's precedents and dependents will help you discover and correct many formula inaccuracies.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97510 Viewers
80 Video lessons · 141053 Viewers
59 Video lessons · 59403 Viewers
52 Video lessons · 72725 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.