QAid –– Excel workbook analysis to deal with an urgent problem

Copyright Peter J. Quarrell, 2009. All rights reserved.

 

The "Too many different cell formats" Excel message
If it was not recommended to you by a fellow sufferer, it is very likely that you have found this web page by entering “Too many different cell formats” in a search engine.  In that case, you have probably recently encountered that error message while developing an Excel workbook.  You have discovered that the workbook is now completely unusable, and you are deeply worried.  If so, you can start to relax!  Help is available. It is effective and valuable.

One of the nastiest disasters that may occur with a large Excel workbook is to hit the limit (of about 4000) on the number of different cell formats one workbook can have. (You are unlikely to have hit this limit when using Excel 2007. That version increased the limit to 64K, and it should only report the error if a workbook has hit the limit under a previous version of Excel and is then reopened under Excel 2007).

(You may also have arrived here after coming across another Excel message telling you that “No more new fonts may be applied in this workbook”. Although this web page is not specifically aimed at your needs, you may find it helpful).

Index  

 

Cell format combinations

A typical 'too many cell formats' error message

The results of this error are unexpected and unpleasant.  It nearly always requires considerable work to neutralise them.  This situation arises from the way in which Excel economises the storage of cell format information.  It is described in Microsoft's Knowledge Base article 213904).  That article defines a cell format combination 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".

When you reach the limit, the workbook usually becomes completely unmanageable.  The three inconvenient choices range from worst to least bad:

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

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.  A convenient way to deal with this very real problem is to use QAid, my Excel workbook analysis and repair software.  If you prefer to do without it, this web site also gives useful advice on how to manage without it, although the work involved in that would be lengthy and frustrating.

 

How QAid helps the combinations problem
You need to reduce the problem workbook's number of format combinations, and get rid of at least a hundred of them to get it out of the danger zone.  Each combination has values for each of 40 different aspects of cell formatting.  If your computer has (like mine) over 700 different fonts loaded and 1000 possible Excel number formats, there are at least 1044 different combinations available.

QAid deals with the problem by making a detailed list of all the different format combinations in the workbook.  This list records how often each combination is used, and the address of each one's first example.  Clearly, once you have that list, the most efficient way of reducing the number of combinations is to get rid of the unique ones (each of which occurs in only one cell in the whole workbook).  You get rid of them by changing each unique format to a (very similar) format which is already in use elsewhere.  The search is simple, but it takes a long time because every cell in the workbook's Used Range must be examined.  Once it is complete, the list is sorted so that the least-used (usually unique) combinations are at the top.

Then the repair process can begin.  It enables you to:

  • Select a unique cell format which you would like to eliminate;
  • Examine a set of up to 11 candidate formats that have the fewest differences from the unique format, and select one as most suitable to replace the unique format;
  • Eliminate the unique format by copying the selected candidate format to its cell;
  • Update the results workbook to reflect the change;
  • (As long as there is still a risk of the error recurring), automatically save the target and results workbooks after each unique cell is eliminated, to minimise the risk of damage caused by hitting the 4000 limit again.

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.  Any method of dealing with this problem means that you must sacrifice some aspect(s) of your workbook's appearance.  However, the QAid approach means that you retain your essential control of the subjective judgements involved.

 

Why QAid?
QAid meets your urgent need for help when you have just come across the "too many different cell formats" disaster.  It is 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.  QAid can do a range of other analyses of your workbook. These can support a wide range of improvements in its efficiency and accuracy.  They are described below.  But the main reason for most enquiries is its ability to deal with the "too many different cell formats" message.

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.

Back to top       Index   

 

Commercial details
A single-user QAid licence is available at a price of 110 GB pounds.  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.  If you replace the computer (or replace or repartition its C: drive), 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; it can be emailed back to me with a request for a new copy and an explanation of the reason why it is needed.  There is normally no charge for a new copy, although a charge of 20 GB pounds per copy may be invoiced if new copies are requested too frequently or with insufficient explanation.

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
  • Version 12, Excel 2007 (but see the earlier note).

This means that when you upgrade your version of Excel you will need a new copy of QAid.  As described above, this will be available on request, normally without charge.

Click this box to download the QAid demonstration copy



  Download point

A demonstration copy of QAid is available without charge.  This is free, but 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 left of this paragraph.  It contains the files that make up the demonstration version.  Alternatively, send me an email to the address shown at the foot of this page.



Back to top         Index  

 

 

Purchase
To buy a single-user QAid licence, you need to do two things:

  1. Use the demonstration version to create a QAid Installation File (QIF) and email it to me at You will have to type this email address, to avoid Spam
  2. Arrange to send me the payment of £110 GB pounds (for which I issue a receipted invoice).  You can do that by clicking this button
    With this button you don't have to log in or register with PayPal (use its Continue button to skip registration).
.   .  

 

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

QAid opening menu

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.  You should select one or more tasks, then click the 'Do selected tasks' button. Then the selected items are executed in sequence.

Back to top        Index   

 

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:

Progress Bar during QAid operation

The analysis can in some cases be lengthy.  This progress bar gives comfort by making clear that things are moving on.  The results sheet backdrop gives an up-to-date view of what sort of results are being produced.

When the main form's Exit button is clicked, the form disappears and the QAid workbook closes.  If the relevant checkbox was ticked, the results workbook remains open and can be worked on further.  Otherwise, the Excel window remains visible but empty.

Back to top       Index   

 

QAid's functions
The most important functions are the first two.  The first (which is always executed as it feeds other functions) summarises the whole workbook.  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.

Sheet Details button
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.

Format Combinations button
The format combinations list is by far the most important facility in QAid.  As far as I know, it meets users' needs better than any other software.  It lists all the different cell format combinations found in the target workbook, recording how many times each occurs, and where the first occurrence may be found.  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.

Conditions button
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).

All Formulas button
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.

Merged Cells button
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.

Named Ranges button
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.

Shapes button
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).

VBA macros button
This gives details of each VBA procedure's properties.  It also lists every reference to each routine, both in the VBA and in the OnAction properties of any 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.

Compare two workbooks button
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.

Remove blank VBA lines button
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 has an 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.

Back to top        Index   

 

Using the QAid results workbook
Each QAid results workbook contains one worksheet for each analysis that has been run.  There are no formulas on any of its worksheets.  The results workbook is saved with Excel's Calculation property set to Manual.

Every row of results contains all the reference information required to place it in context (eg sheet name, cell address, etc) even if several rows really refer to the same object.  This means that every table of results is suitable for the application of AutoFiltering.  It is sufficient to click any cell on the sheet, and then click Data — Filter — AutoFilter.  This applies filters to each headed column on the sheet.  While some columns contain so many different values that filtering is less useful, others can be used to assist further analysis.

You can also use the spare columns to the right of the results (or on a separate new sheet) to implement further calculations in support of the analysis, possibly filtering them as well.

QAid is a detail product, producing substantial amounts of information.  However, it makes this information available in a much more accessible form than it is within the workbook itself.  It uniquely fills a serious gap.  You can manipulate and analyse its results without modifying the target – it is a non-destructive testing system.  It should be used fairly regularly during the development of large or complex Excel workbooks for any application.  It can be used in preparing a workbook for general release to ensure that it is as efficient as possible, avoids built-in errors, and does not waste space.  In cases where a workbook will be used by people who were not involved in its development, it can be used to check that they will not be able to modify the workbook's structure accidentally, so that they and the developer can be sure there will be no disasters.

The subject of spreadsheet errors has been much researched.  A Hawaiian professor, Raymond Panko, is a leading authority on spreadsheet errors, and his papers contain alarming survey information and analysis.   QAid supports the sort of investigative action that can help attack several of the types of error that Dr Panko describes.

Back to top        Index  

 

Technical requirements
QAid can be used with all Excel versions from Excel 97 onwards.  Its hardware requirements are the same as those of the installed version of Excel.  The program was designed on a display 1400 pixels wide, and looks best if the display is at least 1024 pixels wide.  If it encounters discontinued workbook elements that have somehow been retained from earlier versions (eg Excel4MacroSheets) it may sometimes produce unhelpful results or even terminate with a run-time error.  As you cannot edit or save the QAid workbook itself, multi-access problems are controlled by requiring that it will only open successfully if it is installed (in any folder) on a C:\ drive.  Each copy checks the identity of the computer it is running on, and will only run if it has not been changed.  When ordering a copy of QAid it is necessary to include a copy of the installation text file.  This can be produced by running either a demonstration copy of QAid or a copy that has become non-functional after being moved from another computer.

Back to top       Index  

 

More information
For more information, or to arrange a demonstration, contact Peter Quarrell at:
Grange Cottage, Carter Knowle Road, Sheffield, S7 2ED, UK
Telephone +44(0)114-255-0977
Email:
You will have to type this email address, to avoid Spam

 

Miscellaneous discoveries
My researches into the 4000 limit have produced a number of findings.  Some of them seem to indicate that the combinations data structure was added (to Excel 97) to speed up redrawing the display when it is scrolled or changes for any reason.  That data structure was very badly programmed. Among the findings are:

  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. A limit well below 4000 is apparently caused by duplicates in the list of combinations.  This might be caused by the amount of processing Excel must do to add a new combination to the list.  Perhaps that work is done only when your version of Excel has no other tasks to execute.  If you close and save the workbook the list of combinations may have had the most recent format changes added to it and not weeded out.  To support that idea, just when the limit is reached (whether below or above 4000) seems to depend partly on how long and complicated a modification session has been conducted since you opened the workbook and started work.

  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. No More New Fonts error messageExcel 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 seems to be less disastrous than the main 4000 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 format combinations 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.
Back to top        Index  





 

If you don't want QAid

 

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, and being reached unexpectedly by workbooks with many fewer different cell format combinations.  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.  The easiest thing to do then is to use the analysis and repair facility offered by QAid.  The rest of this section provides some guidance if you cannot afford to wait.

This process 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.

It will be helpful if you can disable some of Excel's automatic facilities. Before opening the problem workbook, open Excel's Visual Basic Editor window (Alt-F11), activate and get into its Immediate Pane (Ctrl-G), and there type:

Application.Calculation = xlCalculationManual (and press Enter)
Application.EnableEvents = False (and press Enter)

then revert to the Excel window and start work.

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.

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

Back to top       Index  

 

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.  The easiest way to proceed is probably to buy a QAid licence.

If you do not follow that advice, you have to eliminate the least necessary combinations.  This means you must sacrifice one or several of the most attractive aspects of your spreadsheet.  You may have to use fewer different fonts, use fewer different font sizes, or rule out a couple of the different styles or colours of borders or patterns that distinguish different types of cell.  Borders alone use up 18 of the 40 aspects of a format combination, so restricting the variety of borders can reduce the combinations considerably.  But you have to lose some of your workbook's artistic features if it is to become, and remain, usable.

The most practical approach is QAid's: to identify unique format combinations (that each occur only once in the whole workbook), and eliminate them by changing them to other formats that are already in use.  The only alternative, if you can't pinpoint several such unique combinations, is probably to cut the number of combinations en masse.  In my experience this may be most easily done, (because cell borders account for so many formatting aspects), by removing every cell border from the workbook.  Once you have done this, you can then create a simpler border system (using only one colour, a reduced number of thicknesses, and a very limited number of styles).  If you have made a sufficiently drastic reduction in border complexity, you may have taken the workbook out of range of the 4000 limit.  If not, you may run into it again.

Back to top        Index  

 

Index

  1. The "Too many different cell formats" message
  2. Cell format combinations
  3. How QAid helps the combinations problem
  4. Why QAid?
  5. Commercial details
  6. Demonstration version download point
  7. Purchase
  8. What QAid does
  9. The display
  10. QAid's functions
  11. Using the QAid results workbook
  12. Technical requirements
  13. More information
  14. Miscellaneous discoveries
  15. What to do immediatelyabout the 4000 limit
  16. What to do once you can open the workbook safely