Sweeping formats under the rug


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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.