Hi, I'm Matthew MacDonald the author of Excel 2007: The Missing Manual and I'm going to show you how to use Excel's tracing feature.
Tracing is a way to dissect a formula in an Excel worksheet. Using tracing you can find out what cells are being used by a formula. You can also do the reverse trick and find out what formulas are using a particular cell. Best of all Excel shows you with nifty arrows that appear right over top of your worksheet data.
Before going ahead it's worth going taking a look at what a precedent and dependant are. Consider the super simple formula shown here in cell three. It says, "A1 + B1." This formula takes the value from cell A1 and adds it to the value from cell B1 and then displays the result. These two cells, A1 and B1, are precedents. In other words, they supply the values for my formula.
You can place a formula like this virtually anywhere in an Excel worksheet. The cell where you place it becomes the dependant. In this example the formula is in cell C3 so cell C3 is a dependant of A1 and B1. That's just a fancy way to say that C3 relies on the values in A1 and B1 in order to perform its calculation.
Excel's tracing feature lets you see a graphical representation of these precedent and dependent relationships in the form of blue arrows that show up right on your worksheet. To see tracing in action start by moving to the cell that contains the formula. Here I'm using cell C3 that has my simple addition formula.
Then head to the formula's tab of the ribbon and click "trace precedents." Excel displays solid blue arrows that link the precedent cells to the formula that uses the precedents.
Here I see two arrows, one for each precedent in the current formula. One arrow points from A1 to C3 and the other arrow points from B1 to C3. In other words, C3 uses two precedents, A1 and B1.
This example isn't that impressive because the formula I'm tracing is very simple. But if your worksheet is stuffed with numbers and formulas the tracing arrows can really help you pick out the cells that are important to a specific formula.
Next we'll look at a more realistic example. This worksheet calculates student grades based on two tests. Here I'm looking at cell H2 which calculates the final grade for Edith Abbott. Using the "trace precedents" button I can hunt down the direct precedents of cell H2. As you can see, H2 calculates the grade based on the test percentages that are stored in cells C2 and F2.
Because the two arrows overlap they appear as one arrow but you can clearly see two circles, each of which represents the starting point of an arrow. There's one on cell C2 and one on cell F2. If I want, I can dig deeper into this formula. All I need to do is click the "trace precedents" button again to see if the precedents cells have their own precedents.
Here this shows me that the two test percentages are calculated by taking the test score and dividing it by the total. In other words, the mark of 31 and a 40 earned 78 percent for Edith on the test and that's then taken into account in the final grade.
There's actually no limit to how many times you can click "trace precedents." As long as there are more precedents Excel adds another level of arrows every time you click "trace precedents."
Excel also lets you trace the precedents for a bunch of different cells at once. All you need to do is more to another cell and click the "trace precedents" button again. For example, I can move to Grace DeWitt's grade, click "trace precedents" and I'll see how that's calculated. Now I see Edith's arrows and the arrows for Grace all at once which can make for a bit of a tangled worksheet.
At any point you can remove a single level of arrow by choosing "remove precedent arrows" in the "remove arrows" menu or you can clear everything by clicking just "remove arrows" which is what I'll do now.
So far I've been using the trace precedents feature to see what cells a formula uses. Excel also allows me to go the other way. In other words, I could head to a specific cell and see what other cells have formulas that use that cell. This is called the trace dependence feature because it allows you to find what cells depend on another cell.
For example, if I moved a cell B12 I can click "trace dependence" to find the formulas that use this number. As I expect it shows me that every student's test score is calculated using this total.
Finally Excel's tracing tools also work with formulas that are broke which is useful in trying to solve weird formula problems. For example, imagine I type in something that clearly doesn't make sense like a total test score of zero. Now my worksheet is filled with errors. To get to the bottom of the problem I can diagnose any one of these arrows.
For example, if I head to cell H2 I can find out where the error started by clicking the "trace arrow" button. Now Excel traces all the precedents that led back to the error using blue arrows. The Excel uses red arrows to indicate how the errors spread. In this example two blue arrows show the precedents of cell C2 where the error occurred. The error then spread to cell H2 and then finally to the current cell H15.