A Quick Way to Erase Selected Data From a Workbook

BY STANLEY ZAROWIN

Each year I take some of my prior-year Excel workbooks and delete all the old data except for the formulas—a tedious process. Is there a better way to do this?

Excel has a tool for that. Highlight the area of the worksheet you want cleared. In this case, since the data is scattered throughout the entire worksheet, press Ctrl+A. However, if you want to save your labels, just highlight the range you want cleared. Don’t worry, this won’t erase your formulas. Now press F5, which brings up the Go To menu, and then click on Special, evoking the Go To Special screen (see screenshot on right). Since you only want the numbers erased—the constants—click on the Constants radio button. Click OK. If you want comments removed also, return to the Go To menu and click on that button.

Now press the Del key, and the worksheet is immediately ready for next year—with all the constants removed but all the formatting and formulas in their proper place.

 

 

 

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.