What causes the "Too many different cell formats" error in Excel

[Guppy]

This message appeared when I tried to copy a spreadsheet from one workbook
into another large workbook. I took the message literally, and reduced the
number of different cell formats on the sheet to a minimum and guess what, no
error message. Is there anyone out there who understands this error in
depth, so I can try to prevent this in future.

 

[FSt1]

hi
in excel versions 2003 and earlier, excel limits you to 4000 different cell
formats. in 2007, they increased this (due to the larger sheets) to 64000.
it is a ceiling and can't not be increased so the only sure fix is
......don't exceed the limit.
see this site for more details and possible fixes.
http://support.microsoft.com/default.aspx?scid=kb;en-us;213904

regards
FSt1

 

[Stephane Rodriguez]

The reason is a buggy implementation of Excel that keeps in memory all
duplicate style instances, eventually hitting the hardcoded limit. The
hardcoded limit is not known since you never know how many of those
duplicate styles are stored in the file or kept in memory.

This buggy implementation is not fixed in Excel 2007. But Excel 2007
introduces 4 times greater a hardcoded limit.

There is a tool (it's ours) that displays how many styles stored are
duplicates, and also lets you remove all those duplicates, eventually fixing
the problem that might occur during the open phase, during edit, or during
the save phase.

This tool is available here : http://xlsgenreduction.arstdesign.com/

Regards