Fix for Too Many Cell Formats in
Excel
This is not a problem that you want to have, but sometimes
it can sneak up on you when you have a lot of different worksheets with a lot of
different colors, border, fonts, etc. You should read the
Microsoft article on this problem. This
is a problem in XL2003 and earlier Excel versions. If you have users of
your file with those versions of Excel, then you need to correct the
problem.
Quote from the Microsoft article:
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.
Fix for the problem:
I have only needed to fix this problem one time - so far. Microsoft
recommends that you simplify the formatting etc. (see article). The
problem is that Excel sometimes does some weird things when you get very close
to the limit. What might work on a less formatted file will not
necessarily work. Things that would normally reduce the number of formats
used can actually increase the number of formats used on these borderline files.
I have wasted quite a few hours trying to reduce the
number of formatting combinations in a file by:
1. take a formatting combinations used more than once in the file
and using this to
2. replace formatting combinations that were only used 1 time in the file.
Sometimes it works, sometimes not. More often NOT on
the borderline files.
My best success was to strip all formatting from the file,
and save with a new file name. Then, you need to re-open the file so that
Excel can re-count the number of available formatting combinations. This
is when the new file starts behaving a little better.
Next, you can use the format painter to paste CONSISTENT
formats from the old file to the new file. Copy the formats from one sheet
or cell range to the other (within the SAME WORKBOOK) as often as possible to
make sure that are not creating new formats. You might also create some
styles to aid in faster formatting of the new workbook sheets. Note, the
OLD file probably did not have CONSISTENT formatting from one sheet or range to
the other. Simple is better.
DRAWBACK:
This can take lots of time to re-format everything manually. You will also
need to save, and re-open the file each time that you hit a formatting limit so
that Excel can re-count the available formatting combinations.
I used VBA macros to reduce the number of formats used and to eliminate some
unnecessary formatting while automatically re-formatting the clean file.
This saved some time, but was still time consuming. I have been able to
get an extra 1000 free formatting combos using this method.
Contact us if you would like a quote to repair your
file.
ANOTHER POSSIBLE SOLUTION:
Reduce the number of formats in XL2007 using the replace single use formatting
combinations above. It might work in 2007, assuming that the file is more
stable because of the increased number of available formats. I have no
idea if this would work....
Good Luck!
