"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".

When you reach the limit, the workbook usually becomes completely unmanageable.  

The three inconvenient choices range from worst to least bad:

Save it and close it.  The next time it is opened, it is in the same unmanageable state.  Worst of all, the last good saved version has just been overwritten.   Save it under another name.  When that new version is reopened, it will be just as unmanageable, but you will not have overwritten the last good saved version.  This new saved version is almost worthless.  It is very likely to hit the 4000 limit again, and you are unlikely to be able to extract from it the changes that were made since the last good saved version.   Close the workbook without saving it.  This loses all the work done and changes made since it was last saved.

Surprisingly, option 3 is the least troublesome (although it may mean losing some hours' work).  You will, however, have been very lucky if you managed to choose it.  Options 1 and 2 are worth very little.  You are most likely to have followed option 1.  As a result, your workbook (the only copy you have) is unmanageable.  Instructions on how to rescue that unmanageable workbook are given in the next section.

My researches into the 4000 limit have produced a number of findings:

1. It is definitely possible to hit the limit well before reaching a figure of 4000 different formats.  The lowest figure that a QAid user has found so far was just above 2500.  I have also found a case with well over 4400.

2. Just when the limit is reached (whether below or above 4000) varies.  It seems to depend partly on how long and complicated a modification session has been conducted since you opened Excel™ afresh and it read in a previously saved version of the workbook (which was saved with the 4000 limit unreached).  Saving and closing a workbook that has not yet reached the limit does not seem to affect this – it must be saved and closed, and Excel™ must be closed and reopened if the workbook is to get as far away from the 4000 limit as possible.

3. Conditional formats (which cover only Border, Patterns, and some aspects of Font) do not affect the 4000 limit.  I have a test workbook which has so many format combinations that adding one more immediately generates the "too many different cell formats" message.  It quite happily accepts the addition of a substantial number of new conditional formats without triggering the message.  There is a separate limit for conditional format combinations, which is the 2050-row limit described in another Knowledge Base article 215783.  That limit seems to operate in a less damaging way, and experiments so far suggest that it can apply at a level far above the quoted 2050 rows.  I would be pleased to hear from anyone who has more information about it.

4. Based on that, if you cannot bear the thought of your workbook having to lose any of the rich formatting that created this error, you might be able to use conditional formats to retain all your format combinations.  The strategy would be to use normal formatting for most aspects of cell formats, but set no borders at all.  Then in up to 2050 rows of cells that need borders, specify them in conditional formats, using a condition that is always satisfied.  The main drawback would be a relatively imperceptible slowing down of screen refreshing.

5. Excel™ has a separate hidden limit of just over 500, on the number of different font formats (covering only the font aspects such as Name, Size, Style, etc).  Hitting this limit does not seem to be so disastrous as the 4000 main combinations limit, since you can continue to work on the workbook after it has refused to allow the addition of a new font combination.  (Knowledge Base article 215573 refers to this message, but it covers a different situation where an Excel 2000 worksheet with over 10 chart objects with titles is copied several times and hits the fonts limit).

6. Knowledge Base article 213904 contains one piece of preventative advice which indicates a clear lack of understanding of the Excel object model:

   “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.”

   While eliminating borders can be a great help in reducing the number of combinations, simply arranging to place borders only on left and top edges has no effect at all, since they automatically get added to the corresponding edges of the adjacent cells.  A web site belonging to Mr Clay Johanson claims authorship of Knowledge Base article 163678, the original of article 213904, along with another 1249 Knowledge Base items in his 8+ years at Microsoft.

What to do immediately about the 4000 limit
Unfortunately, an Excel™ workbook becomes almost completely unusable when it hits the 4000 limit, and the immediate problem is damage limitation.  The 4000 limit is made worse by being a moving target.  It has actually been reached unexpectedly by workbooks with fewer than 3000 different cell format combinations, and I have a workbook which has over 4400 different format combinations and hits the limit when one more new format is added.  So, even for users who are aware of the 4000 limit, hitting it is usually an unexpected disaster.

This advice may be too late to be any help to you:
Do not save the workbook after hitting the limit.   Instead, quit without saving, or save the workbook under another name.

This at least makes sure that the version that you had at the beginning of the session remains in existence.  If you did save the workbook after hitting the 4000 limit, you will usually find that it immediately screens the "too many different cell formats" message again when you reopen it, and repeats the message when you attempt to change a format.  The workbook seems to be effectively unusable.  You must start by getting it into a condition from which it can be rescued.

This first step is difficult, since almost any attempt to change the format of any cell seems to trigger a recurrence of the error message.  So far, I have found only one good and effective way to get round this:

To get the workbook into a state where it does not repeat the error message:
find a row or column containing a (unique) cell with a format not repeated anywhere else in the workbook,   note the row or column's details (so that you can restore it later on),   delete the whole of that row or column completely.   save the workbook and exit Excel™ completely.

Although it will take some time, you can use QAid to help you find a uniquely formatted cell so that you can carry out the first of those steps (for QAid disables the target's event-trapping mechanisms and switches off Automatic Calculation, and only reads the workbook's contents).  If you follow the rest of that advice, the error message will usually not reappear next time you open the workbook.  If it still occurs, you should repeat those steps for another row or column that meets the same definition.  Once the error message does not immediately appear, the workbook is mendable.  One problem that remains, however, is that it is very close to hitting the 4000 limit again.  You must arrange to save it very frequently as you work on repairing it (but not if it hits the limit again).  Then you can always be sure of being able to restart from a recent good saved version if disaster strikes again.

In my experience it is quite possible to hit the 4000 limit again while repairing the workbook, even though you are reducing the number of different cell formats.  The list of cell format combinations seems not to be immediately reduced when a unique format is eliminated.  Even saving and closing the workbook seems not to alter the list’s space requirements.  To keep the list of formats away from the 4000 limit while repairing the workbook, you have to regularly close it and end the Excel™ session as well.

Once you have got the workbook so that it can be opened without triggering the error again, you can move on to the next stage of rescue. It is nearly always possible to repair a workbook then. (However, getting to this point is so laborious that it may be best to revert to the last error-free version and restart from there).

What to do once you can open the workbook safely
Now the workbook can be opened, you can only avoid an early recurrence of the "too many different cell formats" error by taking steps (before making many other changes) to reduce the number of format combinations.  You need to get rid of at least a hundred.  I have come across no other tool that can, like QAid, identify all the format combinations in an Excel workbook.  The list it produces records how many times each combination occurs in the whole workbook.  It is sorted in increasing order, with unique and rarely-used combinations appearing first.  This means you can change the formats of individual unique cells so that they become identical to other cells.  This reduces the number of combinations in the easiest possible way.

This Elimination system reduces the amount of laborious work involved in implementing your decisions on what to eliminate, but still leaves you firmly in control of the decisions themselves.  You are obliged to sacrifice some aspect(s) of your workbook's appearance, but QAid does not take away your essential control of the subjective judgements involved.

QAid's format combinations function takes several hours to run for a large target workbook (and a few days for the very largest workbooks). This is because every cell in each worksheet's Used Range has to be examined to identify its combination of 40 formatting aspects, and place the result in a list which records up to 4000 combinations.  Conditional formats do not apparently contribute to the number of combinations that make up the 4000.

Why QAid?
QAid meets your urgent need for help when you have just faced the "too many different cell formats" disaster.  It provides an analytical tool which can prevent a recurrence.  It also supports other aspects of the ongoing quest for workbook efficiency.

QAid provides systematic methods for hunting Excel™ errors and controlling workbook efficiency and housekeeping.  It does this by providing useful analyses of the target workbook's structure.  These are recorded in different sheets of a separate results workbook which it creates.  The QAid product itself takes the form of a separate relatively small workbook. It contains VBA components that do the work.  It writes to a separate results workbook, containing a series of worksheets that give the detailed analysis.  The full range of analyses which QAid can do is described below.  But the main reason for most enquiries is its ability to deal with the "too many different cell formats" message, and this web site focuses on that.

Dealing with a "too many different cell formats" crash is a laborious process, but you can use QAid's list to get the workbook into good condition.  This minimises the risk of hitting the error again.  You probably worked out that something like this approach would be necessary when you first encountered the error message, but realised that it would be difficult to identify the unique cells that can be changed with least effort.   QAid makes it all possible.

Commercial details
A single-user QAid licence is available at a price of 110 GB pounds (plus VAT where appropriate).  The files required are delivered by email, and users must undertake that no copies will be given or sold to any third party.  To ensure this, each copy is created for a specific computer and version of Excel™.  If you change either of these, a new copy will be required.  When QAid is started after such a change, it will decline to work but will create an installation request file on request.  This gives the new system details and can be emailed back to me with a request for a new copy and an explanation of the reason why it is needed.  If a new copy is requested within less than 12 months after one has been provided, a charge of 6 GB pounds may be invoiced.

QAid is only effective if your installation is Excel 97 or later.  It can analyse workbooks saved by any version of Excel™ on any computer.  Currently supported versions are:

• Version 8, Excel™ 97
• Version 9, Excel™ 2000
• Version 10, Excel™ XP or 2002
• Version 11, Excel™ 2003.

This means that when you upgrade your version of Excel™ you will need a new version of QAid.  As described above, this will be available on request, with a nominal charge of 6 GB pounds (plus VAT where appropriate) if it is less than 12 months since a new copy was last provided.

 A stripped-down demonstration copy of QAid is available without charge.  This is restricted to analysing only about a quarter of the active cells in a target workbook.  As a result, while it gives clear examples of the analyses available, it is of very little practical use in dealing with a workbook which has just hit the "too many different cell formats" limit.  The demonstration copy can also create the installation request file that records the system details required for a full copy.  To try the QAid demonstration immediately, download the self-extracting Winzip file by clicking on the large green-on-yellow box to the right of this paragraph.  It contains the files that make up the demonstration version.  Alternatively (eg if you need an Excel 97 demo), send me an email to the address shown at the foot of this page.

What QAid does
To access QAid's functions, you choose from a shopping list of options on its opening form:

Before they become accessible, you have to start by clicking 'Select workbook' and choosing a target workbook.  This makes the 'Select workbook' button disappear, changes the form's heading to include the workbook's full pathname, and enables QAid's main functions.  Once at least one function has been selected, the 'Do selected tasks' button is enabled.  When it is clicked, the selected items are executed in sequence.

The display
When an option is being executed, the relevant results worksheet appears as a backdrop.  It scrolls to keep visible the latest values being recorded in it.  A progress display (which can be moved aside if necessary) appears on top:

QAid's functions
The most important functions are the first two.  The first summarises the whole workbook, and the second supports the process of dealing with the "too many different cell formats" error.  However, the remaining 9 options provide analyses or perform tasks that are very useful to the serious Excel™ developer.

This gives details of each sheet, including counts of several different cell types, hidden rows and columns, and other objects like Shapes, on-worksheet charts, and comments.  (Some of the worksheet cell counts rely on Excel's Worksheet SpecialCells property, which can give misleading counts of some cell types.  A merged area of 12 cells, with a Logical constant in its top-left cell and blanks in the rest, is quite capable of adding 12 to the total of each category).  For chart sheets, the list includes the chart title, its type (there are 73 different types), the number of series on the chart, etc.  Since this option covers hidden and VeryHidden sheets as well as visible ones, it gives a useful overview of the workbook's broad structure.  This option is always reported whenever QAid runs.

The format combinations list is by far the most important facility in QAid.  It is not (as far as I know) provided by any other software.  It lists all the different cell format combinations found in the target workbook, recording how many times each occurs.  The most productive way of cutting down the number of different formats is to combine some of the ones which are unique or rarely used, and so this list provides an agenda for productive action.  This part of the results workbook also has an Elimination tool which eases the work of removing unique formats.  The Elimination tool is carefully designed to leave you in control of the decisions about which format features you will sacrifice.

The detailed conditional formats list can be used to spot opportunities to simplify conditions or remove them if necessary.  (For example, applying conditional formats to a merged area normally stores a copy of the format on each cell in the area, although only the top-left cell's conditions actually apply; space can be saved by unmerging such an area, removing the conditional formats from the other cells, and remerging the area again).  This option gives fuller information than the conditional part of the previous one, in that it lists every conditional format, rather than simply summarising and counting the different conditional format combinations.

The formulas option is a valuable aid to improving a workbook's efficiency.  The list may be bulky, but it highlights cases where often-used lengthy elements can be replaced by names or user-supplied VBA functions.  (This may require the calculation of adjacent columns to record the presence of significant strings in formulas).  Excel's Filter function provides a particularly useful way of managing the size of this list (see below).  One column (useful if the workbook must control user intervention) highlights any case where a formula exists in an unlocked cell.  The list also supports workbook review aimed at eliminating errors.  The formulas are reported in both A1 and R1C1 format.  Although the former makes it easier to see what is going on in a formula, the latter makes it easy to compare two formulas to see whether the cells they refer to are located in the same relative positions.

The merged cells list records the cells in each merged area, identifying hidden and redundant information.  When several cells are merged together, Excel™ makes the hidden ones retain their properties (in case they are needed if the area is later unmerged).  This can lead to the retention of unwanted information (which increases workbook size, and adds unneeded cell format combinations), so this list provides an agenda for housekeeping action, and highlights anomalies.

Names are a useful Excel™ facility, which can reduce the complexity of formulas and make possible some useful methods.  To help ensure that names are used efficiently, this option makes a detailed list of the names in use.  It gives details of the ranges referred to by each name.  It indicates whether each range has a variable scope (using a formula), or is fixed.  The list also includes hidden Names.  These are created by various Excel™ facilities (particularly the Solver Add-in) and persist even if the facility is not used again.  The option also lists all references to each name (in cell formulas and chart properties).  It is potentially large, and may cause one name's results to take up more than one row.  (The references found may include mistakes, if names are chosen carelessly – to save time, formulas are not parsed to exclude names within their text strings).  This references list indicates which names are currently unused, or so little used that they could be replaced by direct references.  This makes it possible to keep the workbook's names under control.

This gives details of each shape, with types, linked cell addresses (if any), OnActions (the identity of any macro which runs when the shape is clicked), hyperlink addresses, and visibility.  The list gives useful support for shape-related developments.  It also provides an essential input to the option which lists VBA routines and references to them (see below).

This gives details of each VBA procedure's properties.  It also lists every reference to each routine, both in the VBA and (if the Shapes list has been created) in the OnAction properties of the Shapes.  This is a valuable aid to efforts to simplify or rationalise the workbook's procedural structure.  As with the references list for names (see above), the references may include mistakes if procedure names are chosen carelessly.  For example, if one procedure's name is entirely contained within another's (eg "CellReset" and "CellResetFormat"), the first one's references will include all the cases which should only appear in the second one's.

This compares the target workbook with another, and lists the differences on a cell-by-cell basis.  At the start you can choose whether to compare cell formulas only (the default), or to include cell value differences as well.  If workbook versions are retained at each stage and this tool is used regularly, it can record the details of each stage of the workbook's development.  This can help pinpoint unexplained changes in a complex workbook's behaviour.

Many programmers use blank lines to break code up and to improve its legibility.  If you prefer to do without them, however, this option will remove them.  Since the VBA Editor tends to insert some blank lines when code is imported, or transferred from one Excel™ version to another, it can be very helpful.  This option, and the next (which minimises workbook size) are the only two which involve modifying the target workbook.  In both cases the process begins by saving a backup copy of the unmodified target to ensure that it remains available in the event of system disaster of any sort.

The Excel™ VBA Editor seems to have a rather individual approach to the storage of a workbook's VBA routines, and the size of the file can sometimes increase unexpectedly when it is saved.  This tool acts to counteract this by compressing the storage of the workbook's VBA – and has no effect if there is no VBA.  At the end of the process all the VBA code has become uncompiled (which also saves space, in exchange for an imperceptible increase in overhead time when each VBA routine is first executed).  In a large workbook with plenty of VBA, this option can achieve a total saving of several hundred kilobytes.