Tracking down cell dependencies across multiple worksheets and workbooks

- Worksheet troubleshooting and worksheet auditing are difficult tasks if performed manually. Excel has some keystroke shortcuts, some techniques for helping you figure out the implications, for example, of changing a cell's content. This is a particularly unwieldy worksheet. Got lots of different formulas in it, inner relationships. Maybe you've looked at it for five or ten minutes, you've come to realize that cell B3 is pretty important. And you're wondering if you change that, which other cells will change. So for example, you might click on cell E5, double click or look in the formula bar, it directly relates back to B3.

How about cell F12? That does too. How about cell E13? Well that doesn't say B3, but it does refer to B8. Does B8 refer to B3? Let's go to B8. Looks like that doesn't. But we could have some layers here of dependencies. Go down to this cell. That doesn't refer to B3 but it refers to E17, E18, does one of those refer back to B3? You start to gather the idea here that this is not exactly going to be easy.

That refers to B16 which is over here, and that refers back to B3. So the inner relationships become somewhat complex. A keystroke shortcut that will help is control shift right bracket. The active cell is at B3, I'd like to know which cells in this worksheet depend upon this. Control shift right bracket. The right bracket key is typically two keys to the right of the letter P. And so we can see at a glance. Possibly you might want to add a color to these, you could do that if you wanted to, but we see at a glance, if B3 changes, all these others have formulas that either directly or indirectly refer back to B3.

So at least theoretically, any of these shaded cells could change if we change B3. But what about this thought: could there be formulas in another worksheet in this workbook that refer to this data? This feature didn't help for that at all. Now I know there's one, you wouldn't necessarily know that. And could there be another open workbook that has a formula that gets data from here? We can't figure that out by that keystroke shortcut either. In fact, as we look around here, we could click every single cell in this worksheet, in no way would we know if there's a formula in another worksheet that's getting data out of here.

Let's go back to B3. On the formulas tab in the ribbon, we do have the option Trace Dependents. Now some people when they use this, at least at first, click it once and that's it, and they see what's happening, they say, "Wow, that's a great feature." And it is. It shows us cells that directly refer to B3. In other words, in the formula here for example, in G5 there's a formula that refers to B3. But we don't know about cells that depend upon G5, where we are right now.

Let's go back to B3. When you click the Trace Dependents option keep clicking it. If your speaker's on you'll hear a beep, otherwise you'll see the lines stop appearing. And now we see there are lots of dependencies. And furthermore, there's a dashed line here. I'm going to double click the dashed line. This will show us dependencies on other worksheets or in other open workbooks if they exist. So in this Go to dialog box we see two references in this example.

The first one is to a different workbook that's open. In other words there's a formula in that other workbook that's getting data from cell G5 in this worksheet of this workbook. And also, within this same workbook, on a different sheet called FirstHalfReport, is a formula that's getting data out of here. Now we can double click either one of these, in fact we do it both. I'm going to first double click on the other reference in the current workbook. So we're in a file called Dependencies but in another sheet called FirstHalfReport.

So that's selected, I could either click OK or simply double click it, either way. And here's that formula, and as I double click it, you see what the formula does. It refers back to the other sheet, it's getting data from cell G5. You wouldn't know that in any other way unless you wrote the formula or you happened to have remembered it. Once again, going back to PricingCalculations. Buried in here, and for the moment it's a little bit hard to see. Let me temporarily remove the arrows here, and go to this cell.

Looking at this cell by itself, it has references to cells in this worksheet, so we don't know, unless we simply took the steps I took previously, that this cell is being used in another worksheet. This is cell G5, the other worksheet's over here, double click, it's getting data from cell G5, in that other worksheet in the same workbook. Remember, how do we find this out? We found it out by, initially we were at cell B3, we checked its dependencies, and we found that.

Now remember when I double clicked this dashed line before, there were two different dependencies. The other one is in a totally different workbook. And here too we can go to that workbook, which is currently open, and there it is. And there's a formula there. It's using the same data, but look at the reference. When we double click, what are we seeing here? This is getting data from cell G5 just like the other one, same worksheet, but we now see the actual name of the workbook. Remember this is a different workbook that's currently open.

So when you're checking these cross references, and I'll go back here, this can only work on worksheets within the current workbook or worksheets of workbooks that are currently open. If that other workbook was closed, we wouldn't see this dependency here. But again, think of the power of this because you cannot tell at a glance whether a formula here is being used elsewhere, and we can now. Now if that other file happened to be closed, we wouldn't know that there's a dependency, that file has to be open.

So, this is pretty powerful stuff when we think of how long it would take to do this manually. Dependencies, available on the formulas tab. We also see an option here called Trace Precedence, that's the topic for another movie.