Maximum Number of Cell Formats

[Jim Allen]

My company uses very large spreadsheets
to confrom to goverment requirements. Recently we have run into a Warning
message when we try to insert a new column. It says we've used up all the
cell formats, so we can't add any new cells. The Microsoft documentation
seems to indicate that the maximum number of cell formats is 4000.

I guess we're hitting that limit and Excel won't let us enter anymore data.

How do I increase the max number of cell formats?
How do I find out how many I've used already?
How do I delete ones I'm not using (Excel seems to "hang on" to all the cell
formats we've ever generated)?

Thanks.

Jim Allen

 

[Earl Kiosterud]

Jim,

I don't think you can. The way I understand it is this. When you do
formatting to a cell, Excel internally creates a thing called a style, that
contains all the formatting called for in that cell. If you use the same
formatting in another cell, it uses that same style. In your case, it has
run out of styles it can create. If you can use the same formatting
combinations in some of the currently formatted styles, you may be able to
economize on styles. Also, be sure to remove any rows/columns beyond your
used cells you're not really using, in case they're using up styles. I think
www.contextures.com has the procedure for that

 

[CyberTaz]

Hi Earl-

I was intrigued by this information. I admit I had never given the issue any
thought, but was surprised that it works that way. Apparently it pays to be
consistent with formatting in order to avoid the conflict, but I'm sill
unclear on a couple of points:

This 4,000 "cell style" limit is on a per workbook basis, correct?

Does defining Styles (as in Format Menu) & Custom Formats help lessen the
likelihood of running into this limit?

Why would a book (even with the full 4,000 combinations used) disallow
inserting columns or rows as long as the cells in them are formatted by one
or more of the allowed 4,000? As I understand it, no new cells are actually
inserted (16,777,216 is the max), they are simply 'moved' from elsewhere, so
they must be carrying existing format, right? Wrong?

TIA for any additional enlightenment |:>)

Jim,

I don't think you can. The way I understand it is this. When you do
formatting to a cell, Excel internally creates a thing called a style, that
contains all the formatting called for in that cell. If you use the same
formatting in another cell, it uses that same style. In your case, it has
run out of styles it can create. If you can use the same formatting
combinations in some of the currently formatted styles, you may be able to
economize on styles. Also, be sure to remove any rows/columns beyond your
used cells you're not really using, in case they're using up styles. I think
www.contextures.com has the procedure for that

-- (e-mail address removed)

 

[Earl Kiosterud]

Jim,

The 4000 limit is per workbook. Check out article 213904 at the microsoft
knowledge base, www.support.microsoft.com

I don't know why you get that message when you insert a column. The
inserted column has the same formatting as the prior column, so one would
think it'd use existing formats. Make sure you don't have stuff all the way
to the last column (even if you think they're empty). www.contextures.com
has a procedure for that.

 

[Matthew B.]

Jim,

Check out QAid. Peter is a very intelligent gentlemen who has developed a
great little program that will identify all the format combinations.

www.quarrell.demon.co.uk/QAid/

Matthew