From the course: Excel 2016: Advanced Formulas and Functions

Using Auditing tools

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Using Auditing tools

“

- As you work with formulas in Excel, you become sensitive to the idea that if a cell changes, a formula that uses that cell of course reacts immediately. And sometimes changing the content of a cell causes not just one other cell to change, but multiple cells. How we do we know, for example, in this worksheet here called Auditing Tools, if we change Cell B3, what impact will that have on other cells? Maybe you've discovered already there's a formula in Cell B7, I'm double-clicking it, we can see a reference there to B3. Could also see this in the Formula Bar. We know if we change B3, there's likely to be a change in this cell based on the formula, and it's likely to happen on other cells. And furthermore, what happens if there's a formula that relates to Cell B7? If Cell B3 is going to have impact on Cell B7, the other cells that work off of B7 that have formulas that relate to B7, they too are going to change. So how do we check all the dependencies of a cell? Clicking Cell B3, going to the Formulas tab on the ribbon, in the Formula Auditing group, there's an option called Trace Dependents, show errors that indicate which cells are affected by the value of the currently selected cell. Trace Dependent, we see that immediately. But don't stop there, keep clicking Trace Dependents repeatedly. If you have a speaker on, at some point you'll hear a beep, otherwise the arrows will stop appearing. So, a change in Cell B3, is going to have potential impact on all these other cells that have arrows in them. Well that doesn't necessarily mean that every one will change, it depends upon the natures of the formulas. But all of them have formulas that one way or another relate back to Cell B3. And of course, many of these cells have formulas that get data from other cells as well. As you look at this, something else unusual has occurred, there's a dashed line here. It appears to be coming out of Cell G5, and it's pointing to an icon out here. Think of that icon as being another worksheet. If you double-click the dashed line, as I'm doing right now, brings up a dialog box, and says that in this file, in another sheet called Update Values, in Cell A13, is a formula that gets data from Cell G5 in this worksheet. And G5 has a formula in it that gets data from Cell B3. We can double-click this or click it and click OK, either way, this will take us to the other sheet. Watch the sheet tabs at the bottom of the screen change, we're on a different sheet right now called Update Values, and here's the cell A13, lower left corner, I'm double-clicking it, that's a formula that's getting data from Cell G5 in that worksheet that we just came from. So I'll press Escape, move leftward back to the Auditing Tools sheet. A change in Cell B3 will impact not only the cells referred to on this worksheet, but also the cell on the other worksheet. Now, without that feature, you would never know. You could look at this worksheet for days, years, whatever, you will never know just by looking at this worksheet that there's a formula elsewhere that's getting data from this sheet. So that's certainly a handy tool. If we want to be able to check the dependencies of other cells, the first order of business probably is Remove these arrows, or I'll have a huge number of arrows on the screen. Now almost important, sometimes maybe more important, is the idea that you've got a total and you'd like to know where the total is getting its data from. So the opposite of dependents is referred to as precedents. I need to use this in the quarterly report, I need to use this number, but I need to know where it's coming from. I've got a formula here that gets data from either Cell L11 or K27, but where do those cells get their data from? And so on, and so on. So working in the opposite direction, Trace Precedents. I'm clicking it once, only two cells are referred to, but I'll keep clicking it just as we did with Trace Dependents, repeatedly. And here, working in the reverse direction, we see any of the cells that have blue dots in them, if altered, could impact our total on the bottom. And one of our cells here, it's slightly different in this situation in Cell F13, this is getting data from another worksheet. Now here we do see this, and we do see the formula that it refers to, the other worksheet, so a little bit different than our example with Trace Dependents, but once again we do see we've got a situation where a cell here is getting data from other cells. And one of the other cells that's impacting the total here is coming from another sheet. And so here too, when we see this dashed line, we'll double-click it, and we could click this, click OK, and go see the other formula here. There it is right there. Now, this is simply a raw number, but it's referred to by the formula on the other worksheet. So once again, I'll go back to the other worksheet, Auditing Tools, bottom of our screen. So I think you can see the incredible value of these two tools. We just saw Trace Precedents, and before that, Trace Dependents. Let's Remove the arrows. And by the way, there are two keystroke shortcuts here that are occasionally helpful, they don't show us arrows, but they do highlight cells. I'll go back to Cell B3. If I'd like to highlight all the dependent cells on this worksheet, I can press Ctrl Shift ) I'm doing that right now, and all those cells, although they don't have arrows or lines in them, I could apply a color, these cells have a dependency on Cell B3. And in the opposite direction, if I go to Cell L27, and I'd like to find the precedents, without those lines and arrows, I'll use the keystroke shortcut Ctrl Shift ( this highlights all the cells that are preceding, so to speak, or if altered could impact the total in Cell L27. Neither of these keystroke shortcuts by the way recognizes any relationship between cells on other worksheets that might be involved. So ultimately, it's not as good as the feature that we saw earlier for both of these, Trace Precedents and Trace Dependents, the ones that show the lines, but the keystroke shortcuts do help in a pinch. If we have no other external references, they're just fine. So different techniques here for tracing dependents and precedents in a worksheet.

Contents