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

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.

SPONSORED REPORT

Getting leases in line

ASC Topic 842 is a relatively simple standard that can mean profound changes for organizations with leases. This report examines what makes this standard challenging and describes new ways for CPAs to add value.