Formatting Error Message "Too Many Cell Formats". How Do I Fix ?

[Guest]

I am working on a big spreadsheet that requires a number of formatting
(shading, borders, etc). When I try to change a format it gives me the
following error message "Too Many Cell Formats". Then after awhile it locks
up and I have to get out of EXCEL and log back in. I use EXCEL 2000.

I have been doing this spreadsheet for more than a few months now and this
problem has just started to occur. Is there any way to fix it?

Thanks for your help

 

[Guest]

I googled "Too Many Cell Formats" and got...

A message about too many cell formats. A format in this
case means each combination of font characteristics.
ie. Font type, font size, font color, interior color, bold,
underline, pattern, number format, height, width, etc.
Reduce as much as you can. Eliminate unused custom
cell formats. The practical limit may be about 3K.
See Q163678 -- XL Err Msg: "Too Many Different Cell Formats"
http://support.microsoft.com/s­upport/kb/articles/q163/6/78.a­sp

Leo Heuser has a macro that you can find in the 7th issue
of the Excel Experts E-letter (or EEE) to remove unused
formats. The EEE newsletter is maintained by David Hager (MVP-Excel).
http://www.j-walk.com/ss/excel­/eee/eee007.txt

You would have found this same answer had you checked Deja archives
search "too many cell formats"
newsgroups: *excel*

David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davem­critchie/excel/excel.htm

HTH,

 

[Guest]

Leo Heuser's macro removes _number_ formats, and therefore is unable to
address the problem that (as KB article 215783 says) causes the "too many
different cell formats" message and consequent inaccessibility.

It's a commercial site, but you don't have to buy anything at all to get
quite a lot of useful advice on www.quarrell.demon.co.uk/QAid. The only way
I have found of getting to grips with a workbook that has been disabled by
this error is to open it, ignore the error, find a row or column that
contains at least one cell with a unique format combination (a format that
occurs no where else in the workbook) and delete that whole row or column
after making a note of its contents. Then save the workbook and exit Excel
completely. When you reopen it, the error message should not reappear. If
it does, repeat the process for another unique cell.

Then you have a workbook that is on the verge of crashing again, but at
least it isn't giving you the error when you open it. You can then have a go
at getting rid of more uniquely formatted cells using the advice given by
many correspondents and KB article 215783. Be careful; _any_ change in a
cell's format (even just reverting it to the default empty-cell format) seems
to start by adding the new format details to the end of the list of all
format combinations, then checking to see whether it's already on the list.
The list seems not to be tidied up as soon as the format is found to be in it
already, so you can easily find that getting rid of a unique format still
triggers the error message again. The only advice that really seems to work
is to save the file and exit Excel completely quite frequently when removing
unique formats. It is as if the final tidying of a workbook's list of 4000
is only done when Excel shuts down, though I can't think how that can be. By
the way I have one workbook that crashes when a new format combination is
added to the 4482 already there, but one of my correspondents has a workbook
that is close to the limit with only 2610 format combinations in it. So it's
not easy to forecast when you are about to fly into the ground!