Error Checking (Alt-M,K,K)
Standard error checking rules automatically trap common errors. It’s important to understand why Excel looks down on these conditions, and not to ignore them. Error checking rules are like a seat belt. The intent of safe driving is never to need the seat belt, but always to fasten it just in case. It is far better to start with all error checks on while learning than to hide mistakes for others to find.
The rules are not just there to flag cells, though. They enable comprehensive audit of the sheet using the Error Checking tool, Alt-M,K,K. This tool is like a spell check. It brings up each offense and suggests a remedy, letting you fix it on the spot or review other options. This only works for errors that are set to be checked in options. The bigger the used range on the sheet, the more important this feature becomes.
The “auto-correct” feature is like a formula tutor. It exposes the specific cause and offers suggested ways to correct it, or to put you in edit mode and make you think about it. For example, if you have a number stored as text, it offers a button to convert it to a number; if you have a text date with a 2-digit year, it gives you two buttons, to convert XX either to 19XX or to 20XX. It is a worthy exercise to build a test sheet, try to create all or some of the errors, and see what the tutor suggests.
With experience you’ll get accustomed to robust design principles that prevent errors, like defensive driving. You might wish to disable a rule, for example, if you have a dashboard range with inconsistent formulas, or you use text-formatted numerals as indicators. Rather than disabling alerts, resolve the root cause, or ignore the error instance and own the fact that you have an inconsistent design. Because error checks are set in user options, other users can get alerts that you don’t get, unless you eliminate them in design.
Formula Auditing
Use the Error Checking tool as described above to test your work. Catch errors early to avoid rework. If you turned off any checking options, turn them all on again before running the check. The tool gives you access to change the rules during the check, but not to save them. To save rule settings, return to options, Alt-F,T,F, or go with the temporary settings, and resume checking.
Besides Error Checking, other tools in the Formula Auditing section of the ribbon enable tracing, watching and dissecting formulas. In addition to auditing your own projects, these tools are helpful to analyze someone else’s sheet or template. It’s worth the time to learn what they offer. They will help you understand the logic and potential risk of how the sheet is built. You might learn something you can reapply or avoid in your own work, or simply validate your own skills.
The Formula Auditing tools include Trace Precedents (Alt-M,P) and Trace Dependents (Alt-M,D), which link interdependent cells with arrows; Show Formulas (Ctrl+`) to toggle display from formula to result; Watch Window (Alt-M,W) to monitor select cells in a popup as you move and change other cells; and Evaluate Formula (Alt-M,V).
Evaluate Formula opens a dialog with the selected cell’s formula, and allows you to see the result step by step. To illustrate, in this example I have an input cell $P$3 named Diameter, and a formula Name Radius = Diameter/2. The output cell in $P$4 calculates the area of the circle with the formula, =PI()*Radius^2. The evaluator steps into the formula following operation precedence rules, underlining the portion being evaluated at each step, and opening a second pane for named ranges and formulas.
This can be a lifesaver when troubleshooting complex nested formulas, and a good practice for validation of any complex formula logic that you have so carefully constructed.
Comments