Too many cell formats

[Guest]

I have a set of spreadsheets that I am unable to change the format of any
cells. When I try to change the background color, font or font size, an
error message pops up saying there are too many cell formats. I really want
to avoid having to re-create all of these spreadsheets. Please help. Thank
you.

 

[Chip Pearson]

Excel has a built in limit to the number of cell formats. You
have reached that limit. As the message indicates, you cannot use
any additional cell formats.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news[email protected]...

 

[Frank Kabel]

Hi
for dealing with too many cell formats have a look at the following KB
article
http://support.microsoft.com/default.aspx?scid=kb;en-us;213904

 

[Dana DeLouis]

Has anyone used the "Microsoft Excel Excess Formatting Cleaner Add-in" ??

http://www.microsoft.com/downloads/...6C-B873-48CC-B842-DA999C848C82&displaylang=en

If you use it, please let us know how it worked. :>)

HTH
Dana DeLouis

 

[Dave Peterson]

Some people have said that OpenOffice can open these files with no problem.
(then fix/reduce the number of formats, save & close, and try opening in excel)

http://www.openoffice.org, a 60-65 meg download or a CD

 

[Guest]

Dana,

I tried Microsoft's "Cleaner" but it didn't help. I have the same problem,
my spreadsheet has
grown over time and I'm bumping up against the 4000 cell format limit. Have
you found
any ways around it?

Jim Allen

 

[Myrna Larson]

Assuming the MS Cleaner works (I haven't tried it), this seems to be a
hardcoded limit, in which case there's no way around it. You'll have to remove
some formats.

 

[Max]

Maybe you'd like to check out these 2 previous posts by Leo Heuser, where he
provides 2 VBA subs to deal with it.

Sub DeleteUnusedStyles()
http://tinyurl.com/mmrd

Sub DeleteUnusedCustomNumberFormat­s()
[version 1.01]
http://tinyurl.com/37vz7