Too Many Different Cell Formats

[oliver]

I am running 2000 SP3 on a Vista machine and I have a large workbook with a lot of formatting. Apparently there is a limit I have exceeded and now I cannot even re-open the file. Error Message "Too Many Different Cell Formats". Is there anything I can do to get into this file and take out some of the formatting and reconfigure the layouts so I can manage this going forward? Do you think it might open with 2007 software?
Any advise appreciated.
Thanks, Oliver

[MWE]

I have had similar problems in xl2K. The problem normally relates to how much memory xl can grab when it tries to open the file. You might try opening the file with no other appls running or try opening the file on a machine with more memory.

You might also cross post this on an Excel site (vs a VBA site)

For what it is worth, all such problems disappeared when I upgraded to Office 2003

[oliver]

Thanks for the reply. I did try it on a 2003 version wirh more memory but it will not open. I was able to recover some of the data there with no formating, so I can at least rebuild the files. I searched other forums and could not find a solution. I still will give it a shot on a 2007 version. Thanks

[dalea]

Try this link. Excel in your version allows 4,000 different cell formats. Excel 2007 allows 64,000 different cell formats. So open in 2007; simplify your formatting and save as a 2000 workbook. From what I've read before this almost alway occurs because of charts in the workbook. You'd probably take a week out of your life manually assigning 4,000 different formats, but Excel is creating all kinds of different fonts, font sizes, colors, shading, etc. when it creates a chart. Hope this helps. :)

http://excel4audit.wordpress.com/2009/04/22/step-%E2%84%962-too-many-different-cell-formats/

[MWE]

dalea has a very good point, i.e., that the problem could well be charts. Virtually every time I ran into a problem in ol2K regarding sizes of things or number of things, it ended up being related to charts and graphs.

Further to his point about "fix it" programs ... there are many out there and most ofter some sort of free demo. Certainly would be worth trying.

Good luck.

[sandra412]

You get the error because the workbook contains more than 64,000 different combinations of cell formats. A combination is defined as a unique set of formatting elements that are added to a cell. A combination includes all font formatting (typeface, font size, italic, bold, and underline), borders (for example: location, weight, and colour), cell patterns, number formatting, alignment, and cell protection.

Want to know how you can fix this issue, then read this helpful post:

repairmsexcel.com/blog/how-to-fix-too-many-different-cell-formats-error-message-in-excel

[Paul_Hossler]

FYi -- that was a seven year old posting, but thanks for the link