Small Business

Minimizing Distractions in Microsoft Excel — Part 2

Written by David Ringstrom

In this article — the second in my series on minimizing distractions in Microsoft Excel — I’ll lead off with a discussion of those green triangles (in the upper left corner of cells) that tend to manifest themselves throughout your spreadsheets. Second, I’ll cover how to turn off the Paste Options icon and the ramifications of doing so.

In the first article in this series I shared techniques for turning off the Quick Analysis icon in Excel 2013 and later, in order to prevent Excel from unexpectedly zooming your spreadsheet, as well as techniques to overcome situations in which the Function ScreenTip caption obscures columns or cells.

Options for Handling Error-Checking Prompts

The green triangles that can appear throughout your spreadsheets are known as Excel’s error-checking prompts. I’ve had a love/hate relationship with these prompts from the first day I begrudgingly acknowledged that Excel had won the spreadsheet war at the expense of Lotus 1-2-3.

In those early days, I’d opt for the nuclear option on those prompts, as shown in Figure 1:

  1. Activate Excel’s File
  2. Choose Options
  3. Choose Formulas
  4. Uncheck the Enable Background Error Checking
  5. Click OK to close the Excel Options dialog box
Minimizing Distractions in Microsoft Excel

Figure 1: You can get rid of the green triangles entirely.

If you go this route you’ll never see those pesky green triangles again. However, my thinking has evolved over the decades with regard to this feature. It’s not that the feature itself is flawed; it’s simply that it cries wolf far too often for me. Therefore, my more nuanced approach these days is to finesse what gets flagged as an error, as shown in Figure 2:

  1. Activate Excel’s File
  2. Choose Options.
  3. Choose Formulas
  4. Uncheck the individual checkboxes in the Error Checking Rules section, such as these:
  • Numbers Formatted as Text or Preceded by an Apostrophe: Within the mix of spreadsheets that I work with there’s consistently data with leading zeros that has to be stored as text. Otherwise, Excel single-mindedly throws away the leading zeros because, well, you can’t do math with a number if it’s stored as text. I can only think of rare instances where I might ever wish to sum a column of Social Security Numbers, ZIP codes, CUSIPs, and other common identifiers that often have leading zeros.
  • Formulas Referring to Empty Cells: This can lead to a chicken-versus-egg situation in your spreadsheets. You create a template that a user will fill in later, and have formulas that refer to cells that at the moment are purposely blank. Yet Excel waves a flag, as if there’s a problem.
  1. Click OK to close the Excel Options dialog box
Minimizing Distractions in Microsoft Excel

Figure 2: Instead of getting rid of the green triangles entirely, you can select which rules get used to flag errors.

It may take you some time to develop an awareness of what error-checking rules are triggering unnecessary false positives for you, so there’s an interim step you can use whenever cells get flagged with the green triangle:

  1. Select a cell or group of cells that are flagged as potentially containing errors, as illustrated in Figure 3.
  2. Hover over the exclamation mark icon that appears to have a caption that summarizes the error.
  3. Click the arrow next to the exclamation mark to display a menu:
  • Typically, the first option on the menu will offer a potential remedy, such as “Convert to Number.” Bear in mind that doing so may solve one problem—eliminating the error flag—and cause another—discarding leading zeros. The first option that appears will vary based on the error in question.
  • Help On This Error is generally a red herring. You’ll usually be unceremoniously dumped at the top level of Excel’s Help file and left to your own devices on determining how to resolve the error.
  • Ignore Error gives you the ability to clear the error flag on a cell-by-cell basis. The process is the same for a single cell or a group of cells.
  • Edit in Formula Bar is self-explanatory.
  • Error Checking Options is a shortcut to the aforementioned area in the Excel Options dialog box that allows you to turn rules on or off.
Minimizing Distractions in Microsoft Excel

Figure 3: The Error Checking drop-down menu offers a variety of choices for a cell or group of cells that are flagged as having errors.

Regardless of the above choices, you can purposefully use these green triangles when auditing an unfamiliar spreadsheet, as shown in Figure 4:

  1. Activate Excel’s File
  2. Choose Options
  3. Choose Formulas
  4. Make sure that Enable Background Error Checking is enabled
  5. Enable all of the rules in the Error Checking Rules
  6. Click Reset Ignored Errors. This button resets ignored errors in the active workbook only, so you’ll need to repeat this step for any future workbooks that you wish to audit in this fashion.
  7. Click OK to close the Excel Options dialog box
  8. Activate Excel’s Formulas
  9. Click the Error Checking
  10. At this point you’ll either see a prompt that says the error check is complete, or an Error Checking dialog box that will allow you to move directly to each cell that Excel has flagged as containing a potential error.
Minimizing Distractions in Microsoft Excel

Figure 4: When using an unfamiliar sheet, you can reset previously ignored errors.

Turning Off Paste Options Icon

Another common distraction arises when you copy and paste data in Excel. As shown in Figure 5, a Paste Options icon appears each time you paste data into a worksheet. Although it’s easy to tune out this icon, it’s also easy to bump the Ctrl key on your keyboard to display a palette of options that you must press Escape twice to completely dispatch. The intent of this icon is to allow you to choose from a variety of options related to pasting. I personally opt for the Paste Special command when I need something other than a standard paste.

If you wish to turn off this icon, as shown in Figure 5:

  1. Activate Excel’s File
  2. Choose Options
  3. Choose Advanced
  4. Uncheck either or both of these checkboxes in the Cut, Copy, and Paste section:
  • Show Paste Options Buttons When Content is Pasted
  • Show Insert Options Buttons
  1. Click OK to close the Excel Options dialog box
Minimizing Distractions in Microsoft Excel

Figure 5: Turning off the Paste Options button also turns off the Auto Fill Options button.

There’s a catch to managing this particular distraction though, as you might solve one problem and create a new one. Turning off the Paste Options button also turns off the Auto Fill Options button which appears when you drag cell contents to other cells in the spreadsheet. When you drag in this fashion unwanted cell formatting, such as borders or colors, can get applied to the additional cells.

You can click the Auto Fill Options icon and choose Fill Without Formatting to keep the cell contents but discard the cell formatting. In this case you’ll have to weigh what’s worth more, turning off the Paste icon, or maintaining access to the Auto Fill Options icon. Fortunately you’re not making any permanent decisions with any of these on-screen distractions—simply reverse any of the steps in this series to put things back as they were.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

Leave a Comment