ERROR message: "Too many different cell formats".

PhillySam · External Since: May 14, 2007 Posts: 1

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 · External Since: Apr 13, 2004 Posts: 464

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 · External Since: May 15, 2007 Posts: 2

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" wrote:

> 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
>
>

Jan Karel Pieterse · External Since: Apr 13, 2004 Posts: 464

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 · External Since: May 15, 2007 Posts: 41

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
"Jan Karel Pieterse" wrote in message

> 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
>

Frank-007 · External Since: May 15, 2007 Posts: 2

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

"Ronald Dodge" wrote:

> 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
> "Jan Karel Pieterse" wrote in message
>
> > 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
> >
>
>
>

Jan Karel Pieterse · External Since: Apr 13, 2004 Posts: 464

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 · External Since: Mar 03, 2010 Posts: 1

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.

"Jan Karel Pieterse" wrote:

> 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
>
>

Houston · External Since: Jun 01, 2010 Posts: 1

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" wrote:

> 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
>
>

Jan Karel Pieterse · External Since: Apr 13, 2004 Posts: 464

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