ERROR message: "Too many different cell formats".

[PhillySam]

I received this error message followed by a total corruption of my workbook.
I have a saved copy but I would like to avoid this problem in the future.

What am I doing that is causing this to happen? How can I fix this?

When it refers to the cell format does that include everything or just
certain cell attributes: cell size, merged cells, wrap text, background
color, font color, font style, font size, format ($$, Date, etc.), formulas,
???.

Using MS Office 2003 with all latest updates.

 

[Jan Karel Pieterse]

Hi PhillySam,

I received this error message followed by a total corruption of my workbook.
I have a saved copy but I would like to avoid this problem in the future.

What am I doing that is causing this to happen? How can I fix this?

When it refers to the cell format does that include everything or just
certain cell attributes: cell size, merged cells, wrap text, background
color, font color, font style, font size, format ($$, Date, etc.), formulas,
???.

It includes ALL formatting and any cell with a unique combination adds to the
total number of different cell formats.

You should simplify your workbook's cell formatting considerably to avoid this
warning in the future.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Frank-007]

For those of us developing Excel workbooks that hit this 4000 limit, is there
a way to convert or port the file to ASP.net?

 

[Jan Karel Pieterse]

Hi Frank-007,

For those of us developing Excel workbooks that hit this 4000 limit, is there
a way to convert or port the file to ASP.net?

I know there are tools that claim they can port Excel models to other systems,
but I doubt if that would solve your problem. Having more than 4000 unique cell
formats is a situation you should avoid in the first place.

Make sure you use styles in your workbook extensively and rationalise your
formatting. No doubt it can be simplified and thus the problem be
circumvented/prevented entirely. It might even make your file look better!

There are two words I try to apply to my formatting:
1. Simple
2. Consistent

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Ronald Dodge]

I'm with you there Jan as when I do format my stuff, I usually do it with
some sort of meaning behind it. Often times, when you have that much
formatting taking place, it gets to look too busy and thus not so user
friendly in those cases as things don't stick out as much.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Frank-007]

Ronald,

My workbook has a consistent look and feel on every worksheet, and the same
font throughout the workbook. Unfortunately, it has 64 worksheets and is
over 2.2 megs (with VBA macros). Is there any way for Excel to warn you when
you are close to the limit? My file has not failed to open, but as I try to
eliminate cell formatting, I get the error message "too many different cell
formats". If I "clear all formats" , its going to take me six weeks to add
formatting to each worksheet.

Frank

 

[Jan Karel Pieterse]

Hi Frank,

My workbook has a consistent look and feel on every worksheet, and the same
font throughout the workbook. Unfortunately, it has 64 worksheets and is
over 2.2 megs (with VBA macros). Is there any way for Excel to warn you when
you are close to the limit? My file has not failed to open, but as I try to
eliminate cell formatting, I get the error message "too many different cell
formats". If I "clear all formats" , its going to take me six weeks to add
formatting to each worksheet.

Well, you can save the file as HTML. then open it in a text editor and count
them <smile>. Seriously, the HTML route might help a bit, because it rids the
file of obsolete custom number formats and such.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Handong Chen]

Some times you just can't avoid it when Excel decides to add more formats. We
had an Excel file created in 2003 and has been working well for years. After
we upgrade to Excel 2007 and save it as xlsm, suddenly, this error message of
"Too many different cell formats" starts to pop out. I would say Microsoft
needs to look into this issue and make sure there is no bug.

 

[Houston]

I really do not believe there are over 4,000 formatting choices in my one
sheet (7 cols; 430 rows) 2007 file. I receive the file from an outside (my
company) source, and try to copy the data (less titles etc.) and paste into
new spreadsheet. That's when either nothing pastes in or I get the "too many
different cell formats" error. This has to be a Microsoft bug. So far the
only solution I have is to retype the data. Ughhhhh!

 

[Jan Karel Pieterse]

Hi Houston,

I really do not believe there are over 4,000 formatting choices in my one
sheet (7 cols; 430 rows) 2007 file. I receive the file from an outside (my
company) source, and try to copy the data (less titles etc.) and paste into
new spreadsheet. That's when either nothing pastes in or I get the "too many
different cell formats" error. This has to be a Microsoft bug. So far the
only solution I have is to retype the data. Ughhhhh!

I'd be willing to have a look if you email me the file (use email address as
listed at the bottom of my website).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com