Too many different cell formats

I am running into the error message:

Too many different cell formats

Is there a solution to lowering the number of formats I
am using? Just trying to change them to make some
consistent gives me the same error message.

I tried running the search on the forums on my topic but
they have been disabled for a Microsoft upgrade.

Thanks!

One idea -

Rob Bovey's excellent Utilities add-in will list all the formats in use in
your workbook, allowing you to manually delete what isn't being used.

http://www.appspro.com/Utilities/ExcelUtilities.htm

You can also see the source code for the add-in, and could probably modify
it to delete the unused formats



"Craig" wrote:

> I am running into the error message:
>
> Too many different cell formats
>
> Is there a solution to lowering the number of formats I
> am using? Just trying to change them to make some
> consistent gives me the same error message.
>
> I tried running the search on the forums on my topic but
> they have been disabled for a Microsoft upgrade.
>
> Thanks!
>
>

Rob's utility deals with number formats. The larger issue in the "too many
different cell formats" problem has little or nothing to do with unused
number formats. It has to do with having too many combinations of
formatting elements actually in use.

Or to quote from MSKB 213904:

"This problem occurs when the workbook contains more than approximately
4,000 different combinations of cell formats. A combination is defined as a
unique set of formatting elements that are applied to a cell. A combination
includes all font formatting (for example: typeface, font size, italic,
bold, and underline), borders (for example: location, weight, and color),
cell patterns, number formatting, alignment, and cell protection."

I call each of the combinations referred to in the quote a "style". A
"named style" (that appears in the Format, Style list) is a subset of the
total "styles". In your typical large workbook, with lots of formatting,
the named styles are just a fraction of the number of total styles.

So, again, it's not unused number formats, but total "styles" used that is
the problem in "too many different cell formats". I don't think there is a
good answer to this problem. I guess that's why MS's solution is to "use
less formatting".<g>


--
Jim
"Craig" <[email protected]> wrote in message
news:[email protected]...
|I am running into the error message:
|
| Too many different cell formats
|
| Is there a solution to lowering the number of formats I
| am using? Just trying to change them to make some
| consistent gives me the same error message.
|
| I tried running the search on the forums on my topic but
| they have been disabled for a Microsoft upgrade.
|
| Thanks!
|

Some people have used OpenOffice to open the workbook, clean up some of the
formats, save it as a new name. Then excel can work with it better.

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

Craig wrote:
>
> I am running into the error message:
>
> Too many different cell formats
>
> Is there a solution to lowering the number of formats I
> am using? Just trying to change them to make some
> consistent gives me the same error message.
>
> I tried running the search on the forums on my topic but
> they have been disabled for a Microsoft upgrade.
>
> Thanks!

--

Dave Peterson

Craig,

Check out www.quarrell.demon.co.uk/QAid/. Peter is an intelligent guy who
has developed a great little program that will list all of the format
combinations. It has a price tag but it's worth it if you are in a jam.

Matthew

"Craig" wrote:

> I am running into the error message:
>
> Too many different cell formats
>
> Is there a solution to lowering the number of formats I
> am using? Just trying to change them to make some
> consistent gives me the same error message.
>
> I tried running the search on the forums on my topic but
> they have been disabled for a Microsoft upgrade.
>
> Thanks!
>
>