Auditing

- Excel has a number of auditing tools that let you figure out what's going on in a worksheet. A financial worksheet like this could be somewhat complex. Actually, this worksheet is not well-structured. Maybe the person who is designing it didn't quite finish it. It's difficult to figure out what's going on. It's certainly going to be helpful to find formulas. We can do that quickly and easily by going to the Formulas tab and clicking the option Show Formulas. As you click it, notice what happens. All column widths have been doubled. And more important is the idea that you are seeing formulas wherever they exist, for example, here in cell B7.

Now, the idea behind doubling the width of the columns is to give you a better opportunity to potentially see the entire formula. Some of these formulas are even wider than the actual column anyway. What you could do if you wanted to print this, you could click in the upper-left corner, double click a column boundary, any column boundary, and then possibly print this. Ctrl + F2 will give us a print preview. We could, possibly by showing the grid lines and the column headings of row numbers out here, use this as a source of documentation. Escape, right back here again.

Now, this is an on/off feature, Show Formulas, back and forth. It's also got a keystroke shortcut. If you slide over this button, you'll see the keystroke shortcut, but that symbol is so tiny, you might not even recognize it. Is that an apostrophe? Is it an accent of some kind? It's actually on a key that's typically above the tab key, below the escape key, to the left of the number one key on most keyboards. I've depicted it here in cell C1. It looks like this. Symbol on top is usually called tilde. The other one, if you look it up, is likely to be called accent grave just in French (mumbles) A lot of people don't know what to call that key.

I'm going to call it tilde. Ctrl + ~, without the shift key, will simply perform the same operation. Sometimes that's going to be faster than using the command off the menu. I find this shortcut to be invaluable as I work with certain financial worksheets. So ctrl + ~ doubles the width of columns as it exposes formulas, or takes us back to normal. Same as the button on the formulas tab, Show Formulas. Now, after working with this worksheet for a while, you may have realized that cell B3 is fairly important.

You'd like to know the impact of changing B3. What effect will this have on other cells? In other words, which other cells are dependent upon this? Now, certain cells will have formulas that refer to B3. Others will have formulas that refer to other cells that then refer to B3. If you attempt to do this manually, it's going to be a difficult challenge. What I can do is quickly and easily, from the keyboard, right now B3 is the active cell, I'm going to press ctrl + shift + ].

Right bracket key, typically, is two keys to the right of the letter P. And there we see all the cells, for the moment highlighted, that have a dependency on cell B3. Now, a more visual way of doing this, and it has one added feature that's even better too, is a feature on the Formulas tab called Trace Dependents. Now, if you click it once, you'll see cells, highlighted with arrows, that have formulas that refer to B3 directly. But don't stop there, keep clicking Trace Dependents.

If you have a speaker on, at some point you'll hear a beep here but at some point you'll see the lines stop appearing. All of these cells with arrows or dots in them have a dependency on B3. This also points out something you wouldn't know by going to every single cell. There's a dependency on another worksheet. That means either in this workbook or possibly in another workbook that's currently open if you see a dash line. So here's a dash line coming out of cell G5, I'm going to double click it, and it points out that there's a formula on another sheet here called Budget.

I could double click that if I wished, and it takes me to a formula right here. And there we see, here's the formula that's getting data from that cell G5 on the other sheet. Once again, when you're looking at this sheet, you would never know that there's a formula elsewhere that's getting data from here. So this is an added advantage. And, again, it makes you stop and think about what happens when you change B3, what kind of an impact will that have? And possibly, as in this case here, it will have an impact on another worksheet. Now, here too, you possibly could print this.

I'm going to press ctrl + F2 again. Maybe or maybe not use that as a source of documentation as well. Escape from here. If we want to check the dependencies of another cell, we would want to get rid of these lines, the arrows. We have the choice up here, Remove Arrows. Now, look in the opposite direction, so to speak, the opposite of dependents as we're talking about auditing, is precedence. We don't use that word in English as an opposite exactly so at first it might not hit home as you start to use it, or think of it. But let's say we're looking at cell L24 right here and we're thinking, "This is the number I'm going to put in to our report "and I want to make sure I know "where it gets its data from." So the opposite of dependents, precedence.

And here too, keystroke shortcut ctrl + shift + [. Left bracket key is one key to the right of the letter P on most keyboards. So there we are. All these cells that are currently gray, if altered, could impact our total in L23. By the way, the correct way to say this is "Could impact this." Depending upon the nature of certain formulas, we're not saying that necessarily every cell here that's gray, if we change it, would impact that. But likely, it would. We would have to go to every single cell to make sure that's true on all those cases if we wanted to.

And here too, we want to use the command because this will take us a step further than the actual keystroke shortcut. Trace Precedents, and as with Trace Dependents, keep clicking this, the speakers on, at some point you'll hear a bell. And here too, the added feature is the ability to point out, although we could've seen this by looking around the worksheet. In this case here, there's a formula in F8 that is getting data from another sheet. We could double click that and go check that up if we wished. And double click that. So we've got a formula here, lower right-hand corner there, that's where we're getting data from.

Now, on here, we don't see a formula at all because this is the source. So back to the sheet again. Again, I think you can see how valuable these tools are. I'm going to remove the arrows and talk about another feature. Sometimes you want to highlight just the cells that have formulas. Now, click a single cell. Oddly enough, if before using this feature, if you've got two or three or four cells highlighted, the feature will only look within the highlighted cells. In a counterintuitive way, if you click on one cell and then use this feature from the Home tab, Find & Select, Excel will look in the entire worksheet if you choose Formulas.

It will highlight all the formula cells. So if you have a range selected ahead of time, it will only look within the range. Single cell selected, it looks in the entire worksheet. And those are the cells that have formulas. Sometimes it makes good sense here to highlight these. We can go to the Fill Color bucket, this is on the Font group, Home tab. Choose a light color so that the numbers can be read through these, we could see what's going on there. So that's handy too. It's not dynamic though. In other words, if I click here and delete this, it stays that same color.

And if I write a new formula here, it's not going to change its background color. Let's get rid of these colors and show another way to do this by way of Conditional Formatting. And this will be dynamic. I'll click in the upper left-hand corner. If it's too late to do an undo, we could certainly come back here and choose No Fill, that way. I'm going to click in the upper-left corner to select the entire worksheet. A dynamic way of making sure that formula cells are always highlighted, from now on, automatically, is from the Home tab, choose Conditional Formatting and setup what's called a New Rule.

"Use a formula to determine which cells to format." Right now the active cell in the worksheet is A1 so I'll use this in the formula. Equal, and a new function, relatively new in Excel, as of Excel 2013, ISFORMULA[A1. Because I'm using A1, by inference, it means any selected cell will be used. I'm going to apply a format and I'll just fill it with a color like we did before, perhaps yellow. Again, a bright color that you can see the numbers through. Click OK. So any cell that's a formula is yellow.

If I delete this cell right here or erase it, I'll just press delete, it's no longer yellow. If I write a new formula here, equal this times two, it turns yellow. So that's a more dynamic way of doing it. It's just a question of whether you need that feature or want it, but I think you can see how valuable that would be certainly in some circumstances. So we've seen lots of different approaches here to auditing a worksheet. It's the kind of thing that if you try to do manually, many of the task that we saw would be unthinkable and quite lengthy. Many, many choices here, many of them on the Formulas tab, a few of them by keystroke shortcut.