Excel 2007: Too many different cell formats

[Mark Gould]

Hi

We have a spreadsheet which was created in Excel 2003 (.xls) and then saved
in Excel 2007 as a .xlsx.

I am aware of the 4,000 style limit in Excel 2003 from KB213904 however I
understand this has been increased to 64k in Excel 2007.

Given that I don't believe I have 4,000 cell styles and certainly not 64,000
styles why am I still gettting this error and how can I clear it.

I suspect the whilst the spreadsheet is saved as a Excel 2007 workbook, it
still thinks it is a Excel 2003 file format?

How can I convert the underlying structure of the file. Copying and pasting
would not work as there are charts etc.

With thanks

 

[Jan Karel Pieterse]

Hi Mark,

Given that I don't believe I have 4,000 cell styles and certainly not 64,000
styles why am I still gettting this error and how can I clear it.

We're not talking about 4000 different cell styles, but about 4,000 different
cell format *combinations*.

So any time you change a single element of a cell format, for example the font
color, you add a new cell format combination.

You can see how many unique combinations there are in your xlsx file.

- Open the xlsx package by renaming it to .zip.
- Find the xl folder
- locate the file Styles.xml and open it.
- Now look for an entry called "cellStyleXfs", it should have a count.

The only way I know to get that count down is by rationalising the cell
formatting: less border combinations, less different font characteristics,
etcetera.

Regards,

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

 

[XLGeek]

Hi Mark,

Many people seem to run into this problem in both 2003 and 2007. In Excel
2007 most often the issue is related to the exessive number of unused often
corrupted styles and not so much cell unique cell format combos (you are
right: hard to imagine someone can actually hit over 64,354 combos). I wrote
a utility to fix XL2007 OOXML files that can also be saved down to XL2003.
Here is the link to the blog post:
http://sergeig888.spaces.live.com/bl...9444!534.entry

Requres .Net3.5 and MS Excel 2007. Will fix xlsx or xlsm files. Won't work
on binary xlsb or password protected files (read-only passwords are OK).

No need to run the risk of further corrupting your file by using Open Office
like some people recommend in other Excel forums. Utility is free and comes
"as is".

 

[XLGeek]

Fixing URL:
http://sergeig888.spaces.live.com/blog/cns!53E1D37F76F69444!534.entry