Excel consulting

Excel help

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.

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. 

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!







Home ] Return ]