Control Your Accent

BY J. CARLTON COLLINS

Q: When reviewing Excel workbooks that contain hundreds of formulas, I often find the review process time-consuming and frustrating. Can you provide suggestions that would make this process easier?

 

A: An obvious solution for making the workbook review process easier is to ensure that all data and formulas are well labeled and that the source of all data and the logic behind complex calculations are well documented. Assuming this is the case, in all editions of Excel use the Ctrl+` key combination to display the workbook’s formula view. (The accent mark key is located below the Esc key. The accent mark looks like a backward apostrophe and is technically referred to as a “grave.”) This view allows you to see all formulas at a glance for easier readability, traceability and diagnosis. Press Ctrl+` again to toggle back to normal view. In addition, you can use the Trace Precedents tool to insert arrows that establish the flow of data through the workbook’s formulas, as shown below.

 

To add data flow arrows in Excel 2007 and 2010, select a formula in your workbook that you want to evaluate and, from the Formulas tab, click the Trace Precedents tool in the Formula Auditing group. Click the Trace Precedents tool again, as needed, to insert additional data flow arrows to continue tracing the data flow backward to the original source data. In Excel 2003, the Trace Precedents tool is located in the Formula Auditing toolbar, which appears when you press the Ctrl+` key combination or when you select View, Toolbars, Formula Auditing from the Excel 2003 menu.

 

More from the JofA:

 

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos

SPONSORED REPORT

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out