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.
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).
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.
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 MrExcel.com. 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.