I have created a large financial spreadsheet in Excel 2002
for my job. It is very complex with many macro functions
and different cell formatting througout the workbook.

I was nearing completion and and began to consistently
receive a message that says "Too Many Different Cell
Formats". Microsoft's reponse to this is that the
workbook is too complex for Excel to handle and to
simplify it somehow. This workbook does (or will do) what
I need it to do and cannot imagine trimming it down. The
probem had become so bad that the workbook eventually
crashed and all of the formatting data was lost.
Fortunately our server was able to recover the prior days
copy, so I only lost the one days work.

Can anyone provide some advice on this? I getting very
frustrated. Yes this workbook is complex, but I am not an
Excel genius and believe I am only scratching the surface
of its capabilites. I am certain that other users have
created for more complex spreadsheets without experiencing
this problem. PLEASE HELP!

Leo Heuser has done some work in this area which you might be able to adapt:

http://groups.google.com/groups?selm...GP11.phx.gb l

http://support.microsoft.com/default...04&Product=xlw
XL: Error Message: Too Many Different Cell Formats


http://support.microsoft.com/default...22&Product=xlw
XL97: "Out of Memory" Error When You Open an Excel Workbook

--
Regards,
Tom Ogilvy

"Ron Green" wrote in message
...
I have created a large financial spreadsheet in Excel 2002
for my job. It is very complex with many macro functions
and different cell formatting througout the workbook.

I was nearing completion and and began to consistently
receive a message that says "Too Many Different Cell
Formats". Microsoft's reponse to this is that the
workbook is too complex for Excel to handle and to
simplify it somehow. This workbook does (or will do) what
I need it to do and cannot imagine trimming it down. The
probem had become so bad that the workbook eventually
crashed and all of the formatting data was lost.
Fortunately our server was able to recover the prior days
copy, so I only lost the one days work.

Can anyone provide some advice on this? I getting very
frustrated. Yes this workbook is complex, but I am not an
Excel genius and believe I am only scratching the surface
of its capabilites. I am certain that other users have
created for more complex spreadsheets without experiencing
this problem. PLEASE HELP!

It's not the complexity of your formulas or structure that is the issue;
it's purely a matter of the number of different cell formats you have used.

http://support.microsoft.com/default...04&Product=xlw

A cell format is any combination of formatting elements like typeface, font
size, italic, bold, underline, borders, cell patterns, number formatting,
alignment, and cell protection. You can only have about 4000 different
combinations.

There isn't any neat and simple solution to this problem I'm afraid. You
have to use fewer format combinations somehow. This might require you to
clear the formats from entire sheets or all sheets and then apply new
formats in a structured way. Some users use named Styles to control this
(Format, Styles). They establish certain styles and then format cells by
applying the style. And once a style is applied they do not format it
further.

I would add the Leo Hauser's work on this deals with have *unused8 custom
named styles. That is usually not a huge contributor to this problem is it
can easily exist with no custom styles having been created.

--
Jim Rech
Excel MVP