Last week we covered the first three items in Excel’s “Formula Auditing” tools: Tracing Precedents, Dependents, Remove Arrows and Show Formulas in Excel’s “Formula Auditing” tools. Today we will wrap up the last three features.
I am sure you have never seen an “Error” popup box in Excel. That’s not true, unless you are an Excel Pro and have never made a mistake. I picked one that is easy to reconstruct for this column.
I created a formula that divides a number by zero. I know you all know why you can’t do that. Okay, you were taught, but you don’t remember why not either. In Excel if you do that you will get, “#DIV/0!” which (if you speak Excel you know) means you cannot divide any number by zero (0). If you’re not sure what that means you can click the “Error Checking” button. This button is found under the Formula Tab and then in the Formula Auditing command group.
Once clicked, you get several options; “Help on this error,” “Show Calculation Steps,” “Ignore Error,” “Edit in Formula Bar” and others you can check out. This should help you figure out the error made and give you some guidance as to how to best correct it. The “Help…” link is usually the best to aid me.
Also, keep in mind you can get this same help directly from the cell containing the error warning. You will get a little green triangle in the upper left corner of the offending cell. When you click on that cell you will see a yellow diamond with an exclamation mark in it. When you click on the exclamation mark you will get the same list of options you saw in the previous area and you may click on the one you choose.
Next is the “Evaluate Formula” button, which I am not that fond of; however, it may really pay off for you. It will basically walk you through a complicated formula step by step. This gives you the ability to evaluate how each part of the formula works and even if it does work the way you designed it to.
Finally, one of the neatest Excel tricks for auditing formulas: the “Watch Window” command. If you haven’t tried it before give it a shot now.
Let’s say that you are working on Sheet 3 in your workbook and want to view changes occurring in cell A10 on Sheet 1. This is hard to do until you use the “Watch Window” feature.
Navigate to the Watch Window button and click it. Now go to the sheet and cell you want to view and click on it. Click the “Add Watch” button at the top of the window and you will always see the Book and Sheet name along with the Cell Name, (if you have named it) the cell address, value and formula, no matter where you go with Excel. You will see any change reflected in the watch window where you can immediately deal with it or be advised what is happening.
Let me know if you appreciate these looks at Excel and if you would like to learn more, or if you would like to explore other Office products.
