Ed. note: this is third in an ongoing series to help you make the most out of Excel setting-by-setting. If you have a specific Excel demon to slay, you can get in touch for our resident white knight and Excel-slayer David Ringstrom to help you out.
If you’ve implemented the changes that I’ve recommended in Part 1 and Part 2 of this series then you’ve probably gained some efficiency in Excel. Today I’m going to focus on those niggling green triangles that are about as welcome in a spreadsheet as bedbugs in a luxury hotel room. Part 1 of the series shows how to get to the Excel Options dialog box in your version of Excel, and once you’re there, look for the Formulas section in Excel 2007 and later, or the Error Checking Tab in Excel 2003 and earlier (psst, hey, you Excel 2003 users, you do realize your software is obsolete now, right?).
The green tick marks that appear in in a spreadsheet are supposed to signify when Excel thinks you’ve made an error. The problem is many innocuous entries in a spreadsheet trigger false positives, such as when you force Excel to not drop the leading zeros from an account number, Social Security number, or a New England ZIP code. In such cases users often add a single quote before the number, as shown in Figure 1 to preserve the leading zero. Of course, doing so in turn triggers the green tick mark.
Figure 1: Adding a single quote before a number to preserve leading zeros triggers a green tick mark by default.
When said tick marks appear, you have several options:
- Simply ignore the clutter amidst your spreadsheet
- Turn off the tick marks on a situational basis
- Control the tick marks on a global basis
Obviously the first option requires no further explanation, so I’ll move on to the second item. As shown in Figure 2 above, an exclamation mark icon appears, which you can access in two ways:
- Hover over the icon with your mouse, make a quick jog to the right, and then click the arrow that appears so that you can interact with the menu.
- Use a keyboard shortcut, such as Alt-Shift-F10, use Alt-Menu Key.
Once the menu appears, you’ll be presented with several options:
- The first item on the menu is for display only, which informs you of the slight you’ve impinged upon Excel.
- The second item offers purported corrective action. Depending upon the context of the error, this option could be helpful, or could compromise the formula that triggered the slight in the first place.
- The third item on the menu takes you to Excel’s Help file, which sometimes is actually helpful, in other cases is oxymoronic.
- The fourth choice gives us the situational ability to ignore the error in one or more cells. This removes the green tick marks, but note that Excel will immediately taunt you again should you dare make that same infraction again.
- The fifth option allows you to edit the formula in the formula bar, but the error checking menu is the hard way to get there. Simply press F2 or double-click on a formula if you need to make an edit.
- The sixth option allows us to control the tick marks on a global basis. Select Error Checking Options for an express trip to the Error Checking section of Excel’s Options dialog box. At this point you have two options to consider:
The Nuclear Option: Clear the checkbox for Enable Background Error-Checking and you won’t see those green tick marks ever again— that is until you install Excel on a new computer.
The Nuanced Option: Clear the checkboxes adjacent to the rules that trigger the most false-positives in your spreadsheets. For me it’s the Numbers Formatted as Text or Preceded By An Apostrophe, as well as Formulas Referring to Empty Cells. In Excel, as in life, sometimes solving one problem often causes another. In any case, I’ve found that finessing the rules can turn the tide on the error markers, so that when they do appear onscreen I actually pay attention instead of tuning them out.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
