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

Tax reform complicates year-end tax planning

Get your clients ready for tax season with these year-end tax planning strategies, which address how to make the most of recent tax law changes, such as the new deduction for qualified business income and the cap on the deductibility of state and local taxes.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.