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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.