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.
