Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Too many different cell formats - Excel

Too many different cell formats - Excel

Too many different cell formats - Excel

I have a complex Excel work book model which now gives the following error message when a cell format is changed: "Too many different cell formats" I guess the answer to this problem is to simplify formatting in the workbook. The difficult problem is that I saved a version of this model, closed the file and on trying to reopen it, I get the same message and cannot open the file.(Even after rebooting the PC)

Any one got any ideas on how to resarect the corrupt file ?

RE: Too many different cell formats - Excel

From the microsoft KB (Q213904):

In Microsoft Excel 97 or later, when you format a cell or a range of cells, you may receive the following error message:

Too many different cell formats.
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.
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.
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.

No luck on how to open such a thing, however.
There is another article that deals with Excel issues when you created the xls from Access (Q157495)

Hope this helps



RE: Too many different cell formats - Excel

I don't have any ideas on opening it.  As a last resort to aid in recreating it, create a new workbook and enter the following into cell A1:

Copy this fomula to a range large enough to encompass the entire old workbook.

You can then edit the cell using search and replace to retain only the desired info.

It's messy, but it's easier than re-entering everything even if you know what it was.

Good luck!

RE: Too many different cell formats - Excel

I posted the earlier replay too hastily.  Upon trying it, it fails miserably.  If it gives anyone else an idea,, good luck.  Sorry!

RE: Too many different cell formats - Excel

Have you tried something like:


as a means of referencing the file?  I'd guess that it would still have problems, because Excel still needs to be able to open the referenced file.


RE: Too many different cell formats - Excel

Thank you all for your tips and ideas on "Too many different cell formats". I have simplified the model, standardising & reducing the nomber of formats for future use. I rebuilt the corrupted file and put the loss down to experience.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close