Too many different cell formats

[reb24]

I have a huge model created in Excel and have to make many more enhancements
and additions to it. When I try to re-format a section with any type of
format option, I get the error: "Too many different cell formats" and Excel
won't allow any changes. Does Excel really have a limit as to the number of
formats it can have in a workbook? I've even tried clearing ALL formats on
each and every tab and then trying to re-format and get the same error.
Does anyone know why this is happening and how I can fix it?

 

[Dodo]

I have a huge model created in Excel and have to make many more
enhancements and additions to it. When I try to re-format a section
with any type of format option, I get the error: "Too many different
cell formats" and Excel won't allow any changes. Does Excel really
have a limit as to the number of formats it can have in a workbook?

You can find more about that here:

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

 

[Jim Cone]

r,

MS has this to say...
Jim Cone
San Francisco, USA

'--------------------------------
You receive a "Too many different cell formats" error message in Excel
View products that this article applies to.
Article ID : 213904
Last Review: April 13, 2005
Revision : 4.0
SYMPTOMS
In Microsoft Excel, when you format a cell or a range of cells,
you may receive the following error message:
Too many different cell formats.
CAUSE
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.

NOTE: If two or more cells share exactly the same formatting, they use one formatting combination.
However, if there are any differences in formatting between the cells, each cell uses a different combination.
RESOLUTION
To resolve this problem, simplify the formatting in the workbook.
For example, the following are suggestions for simplifying formatting:
• Use a standard font.
Using the same font for all cells reduces the number of formatting combinations.
• If you use borders in a worksheet, use them consistently.
NOTE: If you apply a border to the right side of a cell,
it is not necessary to apply a border to the left side of the cell that is to the right because the borders overlap.
• If you apply patterns to the cells,
remove the patterns by clicking No Color in the Patterns tab of the Format Cells dialog box.
NOTE: After you simplify or standardize the formatting in the workbook, save, close,
and then reopen the workbook before you apply additional cell formatting.
MORE INFORMATION
In most cases, the limit of approximately 4,000 different formatting combinations for a single workbook is sufficient.
This problem is likely to occur only when the workbook contains a large number of worksheets
that use different formatting, or when a large number of cells are all formatted differently.
APPLIES TO
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition
• Microsoft Excel 2004 for Mac
• Microsoft Excel X for Mac
• Microsoft Excel 2001 for Mac
• Microsoft Excel 98 for Macintosh


"reb24" <[email protected]>
wrote in message
I have a huge model created in Excel and have to make many more enhancements
and additions to it. When I try to re-format a section with any type of
format option, I get the error: "Too many different cell formats" and Excel
won't allow any changes. Does Excel really have a limit as to the number of
formats it can have in a workbook? I've even tried clearing ALL formats on
each and every tab and then trying to re-format and get the same error.
Does anyone know why this is happening and how I can fix it?

 

[reb24]

Thanks, that it very helpful to know!
Does the cell format limit of 4,000 apply to conditional formats too - not
the actual shading, font, etc. (because it's clear that it does apply there),
but I mean the formulas in the conditional format? - e.g., if the conditional
format is based on a formula where it looks at the values of other cells, and
the cell reference in that formula varies for each conditionally formatted
cell. Does that count as a different combination?

Also, is there a way to check how many different combinations of cell
formats you are currently at?

 

[Jim Cone]

r,

I don't know how Excel uses conditional formatting in determining the
cell format limit.
I do have my own opinion on spreadsheet formatting...
if you use more than 2 or 3 colors and 2 font sizes than that is too many.

Regards,
Jim Cone
San Francisco, USA

"reb24"
<[email protected]>
wrote in message
Thanks, that it very helpful to know!
Does the cell format limit of 4,000 apply to conditional formats too - not
the actual shading, font, etc. (because it's clear that it does apply there),
but I mean the formulas in the conditional format? - e.g., if the conditional
format is based on a formula where it looks at the values of other cells, and
the cell reference in that formula varies for each conditionally formatted
cell. Does that count as a different combination?

Also, is there a way to check how many different combinations of cell
formats you are currently at?