"TOO MANY DIFFERENT CELL FORMATS" ERROR MESSAGE ON EXCEL 2004

[Steve_Evans]

I have created a work book that contains 29 sheets some of which are two A4 pages long (50 pages in total). I am unable to complete the last sheet because after two or three operations I keep getting a "Too many cell formats" error message, if I quit Excel and reopen the workbook I can then complete a couple more operations before the message reappears. After quitting and reopening the worksheet a few times the worksheet finally refuses to open at all but the same error message appears. Is there a maximum file size for a workbook or is it some other problem?

 

[Bob Greenblatt]

I have created a work book that contains 29 sheets some of which are two A4
pages long (50 pages in total). I am unable to complete the last sheet because
after two or three operations I keep getting a "Too many cell formats" error
message, if I quit Excel and reopen the workbook I can then complete a couple
more operations before the message reappears. After quitting and reopening the
worksheet a few times the worksheet finally refuses to open at all but the
same error message appears. Is there a maximum file size for a workbook or is
it some other problem?

Which version of Excel? The specifications and limits in Excel Help show a
limit of 64,000 unique cell formats and styles for Excel 2008 and 4,000 for
Excel 2004. Sounds like a lot to me, but maybe not to you. Are you likely to
be reaching this limit? How much RAM is on your machine? Do the sheets have
a lot of charts? Are the charts scaled? I think that the formatting limits
counts for fonts in the charts too. So even if the charts use the same font
and size, but are scaled differently, this may count against the limits.

 

[Steve_Evans]

Thank you Bob, that's given me something to think about.

A rough estimate of the cells contained in the workbook is 75,000. There are no charts, there are some logo graphics and the pages are formatted to replicate grey scale versions of paper forms. I'm only using two fonts but I suppose three different sizes, three style variations and three colours of each when entered into cells whose size, alignment, background colour, borders, formulas and protection are also defined may well have pushed the 4,000 cell format to the limit.

I don't think that ram is the problem since the workbook is less than 900k and even though I have been using an old G3 laptop with only 192MB it is running OSX 10.3.9 with Mail being the only other open application. However I can test this next week when I receive delivery of a new MacBook Pro with 2GB of ram.

Since I wanted this workbook to be used on a PC as well I have had to get Windows XP and Excel 2007 to run on the MacBook Pro, just so I can resize the rows and columns to make the workbook pages look and print the way they should. Hopefully Excel 2007 will also have the same 64,000 cell format variation as Excel 2008 and I can test your initial theory.

 

[Bob Greenblatt]

Thank you Bob, that's given me something to think about.

A rough estimate of the cells contained in the workbook is 75,000. There are
no charts, there are some logo graphics and the pages are formatted to
replicate grey scale versions of paper forms. I'm only using two fonts but I
suppose three different sizes, three style variations and three colours of
each when entered into cells whose size, alignment, background colour,
borders, formulas and protection are also defined may well have pushed the
4,000 cell format to the limit.

I don't think that ram is the problem since the workbook is less than 900k and
even though I have been using an old G3 laptop with only 192MB it is running
OSX 10.3.9 with Mail being the only other open application. However I can test
this next week when I receive delivery of a new MacBook Pro with 2GB of ram.

Since I wanted this workbook to be used on a PC as well I have had to get
Windows XP and Excel 2007 to run on the MacBook Pro, just so I can resize the
rows and columns to make the workbook pages look and print the way they
should. Hopefully Excel 2007 will also have the same 64,000 cell format
variation as Excel 2008 and I can test your initial theory.

Yes, Excel 2007 has the same 64,000 limit for cell formats and styles.