Spreadsheets: Spot the Math Error at a Glance

It’s not always easy to spot mathematical errors embedded in Excel formulas, so here’s a handy – and quick – trick to shed a light on the situation.
Bill JelenAugust 10, 2011

Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here and post your query or tip at the community center on the right.

Reader Karlo B. wins a copy of Slaying Excel Dragons for submitting the tip for this article that will help you to quickly spot spreadsheet errors.

Say you are reviewing a spreadsheet, such as the one presented in Figure 1 (below). There is a math error in this spreadsheet, but it might be pretty hard to spot just by looking at Figure 1.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

Fig. 1

But there is a tried-and-true way to make the job of identifying the error easy. To use this trick effectively, you will have to locate the grave accent key on your keyboard. The grave accent is a backward apostrophe used in the French language (`). If you are using a U.S. keyboard, it is usually just under the escape key, paired with the tilde character used in the Spanish alphabet (see Figure 2).

Fig. 2

When you press Ctrl and the grave accent, you toggle into Show Formulas mode. In this mode, Excel shows you all of the formulas in the spreadsheet at once. In Figure 3, you can quickly scan the SUM formulas and see that some creative sales manager decided to hit the $5 million budget using a plug number in cell F14. Unfortunately, the $301,000 was the never allocated back to the various regions.

Fig. 3

To exit Show Formulas mode, press Ctrl and the grave accent key again.

If you are using Excel 2007/2010, you can toggle in and out of Show Formulas mode using the Show Formulas icon in the Formula Auditing group of the Formulas ribbon tab.

Bill Jelen is the host of His 33rd book on Excel, Learn Excel 2007-2010 From MrExcel, includes 511 more tips like this one. Win a book if your question or suggested tip is chosen as the topic of a future column. To send a query or tip, either click here and use the community center on the right or simply click on Bill’s byline at the top of this article and send an e-mail note.