Two Better Ideas

BY STANLEY ZAROWIN

Ali Allison, CPA, tax practice manager at Singer Lewak LLP in Los Angeles, had some useful ideas about the Tech Q&A item “Generate Duplicate Changes in Multiple Excel Worksheets,” which appeared in the December 2008 issue, page 104. The article is about keeping several similarly laid out spreadsheets along with an annual summary sheet. She suggests adding a “3D” reference for an overview. To do that, she adds, the summary worksheet should have a formula like =SUM(Jan:Dec!B5) to total all the cells in B5 from sheet Jan to sheet Dec. To get the 3D reference, type =SUM in the summary worksheet and then click on the first sheet (for example, Jan), and hold down the Shift key as you click on the last sheet (that is, Dec) and finally click in the cell to be totaled (that is, B5). And presto: an instant 3D reference.

 

And in the same column, regarding the item “A Quick Way to Erase Selected Data From a Workbook,” she points out that our suggestion to use GoTo, Special to identify and remove Constants is not enough. She explains that some accountants use Excel as a calculator and will enter, for example, =25+25+10. Since Excel will read that as a formula, it won’t select it as a constant and remove it. She suggests viewing the formulas with Ctrl-` (the key to the left of the 1 key) to see what’s left after the constants have been erased to see what else needs to be deleted.

 

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

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

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.