Sweeping formats under the rug

BY J. CARLTON COLLINS, CPA

Q: I received an error message in Excel stating that my workbook had “Too many cell formats, Excel encountered an error and had to remove some formatting to avoid corrupting the workbook.” What would cause this type of error, and how do I avoid it in the future?

A: Copying and pasting data between Excel workbooks with multiple formats and styles duplicates these formats, so they can accumulate over time. Excel 2003 limits the maximum number of differing formats to 4,000, and Excel 2013, 2010, and 2007 limit them to 64,000. Exceeding these limits produces the error message you received. (Examples of separate formats include fonts, font colors, borders, cell colors, patterns, bolding, italics, underlines, etc.)

In Excel 2013 Professional Plus, you can remove excess cell formats by running the Clean Excess Cell Formatting tool from the Inquire tab, as pictured below.

 

(This may require you to first activate the Inquire tab by selecting File, Options, Add-Ins. Then, next to the Manage dropdown option, select COM Add-ins and click Go. In the resulting COM Add-Ins dialog box, select the Inquire checkbox and click OK.)

If you have this problem in Excel 2003, Microsoft recommends that you download and run the Remove Styles utility, which is available for free at removestyles.codeplex.com. If you have this problem in Excel 2013, 2010, or 2007 and you do not have access to the Inquire COM add-in, Microsoft recommends that you download and run the XLStylesTool utility, which is available for free at tinyurl.com/kfynmlz. To avoid this problem in the future, upgrade to Excel 2007 (or higher), copy and paste data as text when possible, or periodically run the tools described above.

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.