by Allen Wyatt
(last updated January 11, 2020)
Robert has lost way too many spreadsheets due to the "Too Many Formats" error message. He wonders if there is a way to monitor the running total in real time of the number of formats I have already created that will serve to give me a heads up, so to speak, of how close I am getting to the maximum allowable.
Getting the "Too Many Different Cell Formats" error message has been discussed, briefly, in a different ExcelTip:
The problem can be vexing for those using large, complex workbooks or when using workbooks that have been extensively edited over long periods of time.
Unfortunately, there is no way to count the number of custom formats that Excel thinks are being used in a workbook. The reason is that Excel considers a format as any "unique set of formatting elements that are applied to a cell." That covers a lot of territory. You might think that VBA could access such formats as a collection, but that is not possible; Microsoft just doesn't make the format count available.
This means that the only way to avoid the problem is to take some sort of preventative measure. The best we've been able to find is the XLStylesTool, which you can find here:
The tool is free easy to use. Run it periodically and it should minimize the problem, if not completely remove it.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13724) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
As the limits on what you can store in Excel have increased, so has the need to consider how to make your workbooks and ...Discover More
Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather ...Discover More
Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.