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

Why cybercriminals are targeting CPAs

This free report expands on the most commonly found scams, why education and specialized IT knowledge help to lessen security vulnerabilities, and why every firm should plan carefully for how it would respond to a breach.

PODCAST

How tax reform — and Excel — are changing the CPA Exam

Mike Decker, the vice president of examinations at the AICPA, discusses changes being made to the exam as a result of tax reform — and about how Excel will now be available for use on the test.