Too many different cell formats

It can happen to you too.

When working with a large workbook in Excel, at one not at all wonderful moment you do something completely harmless (adding a row or inserting a large fragment of cells, for example) and suddenly you get a window with the error “Too many different cell formats”:

Sometimes this problem occurs in an even more unpleasant form. Last night, as usual, you saved and closed your report in Excel, and this morning you cannot open it – a similar message is displayed and a proposal to remove all formatting from the file. Joy is not enough, agree? Let’s look at the causes and ways to correct this situation.

Why is this happening

This error occurs when the workbook exceeds the maximum number of formats that Excel can store:

  • for Excel 2003 and older – these are 4000 formats
  • for Excel 2007 and newer, these are 64000 formats

Moreover, the format in this case means any unique combination of formatting options:

  • font
  • fillings
  • cell framing
  • numeric format
  • conditional formatting

So, for example, if you styled a small piece of a sheet like this:

… then Excel will remember 9 different cell formats in the workbook, and not 2, as it seems at first glance, because a thick line around the perimeter will create, in fact, 8 different formatting options. Add to that designer dances with fonts and fills, and the craving for beauty in a large report will lead to hundreds and thousands of similar combinations that Excel will have to remember. The file size from it, by itself, too does not decrease.

A similar problem also often occurs when you repeatedly copy fragments from other files into your workbook (for example, when assembling sheets with a macro or manually). If a special paste of only values ​​​​is not used, then the formats of the copied ranges are also inserted into the book, which very quickly leads to exceeding the limit.

How to deal with it

There are several directions here:

  1. If you have a file of the old format (xls), then resave it in a new one (xlsx or xlsm). This will immediately raise the bar from 4000 to 64000 different formats.
  2. Remove redundant cell formatting and extra “pretty things” with the command Home — Clear — Clear formats (Home — Clear — Clear Formatting). Check if there are rows or columns on the sheets that are formatted entirely (ie, to the end of the sheet). Don’t forget about possible hidden rows and columns.
  3. Check the book for hidden and super-hidden sheets – sometimes “masterpieces” are hidden on them.
  4. Remove unwanted conditional formatting on a tab Home — Conditional Formatting — Manage Rules — Show Formatting Rules for Entire Sheet (Home — Conditional Formatting — Show rules for this worksheet).
  5. Check if you have accumulated an excess amount of unnecessary styles after copying data from other workbooks. If on the tab Home (Home) In the list Styles (Styles) a huge amount of “garbage”:

    … then you can get rid of it with a small macro. Click Alt + F11 or button Visual Basic tab developer (Developer), insert a new module through the menu Insert – Module and copy the macro code there:

Sub Reset_Styles() 'remove all unnecessary styles For Each objStyle In ActiveWorkbook.Styles On Error Resume Next If Not objStyle.BuiltIn Then objStyle.Delete On Error GoTo 0 Next objStyle 'copy the standard set of styles from the new workbook Set wbMy = ActiveWorkbook Set wbNew = Workbooks.Add wbMy.Styles.Merge wbNew wbNew.Close savechanges:=False End Sub

You can launch it with a keyboard shortcut. Alt + F8 or by button Macros (Macros) tab developer (Developer). The macro will remove all unused styles, leaving only the standard set:

  • How to Automatically Highlight Cells with Conditional Formatting in Excel
  • What are macros, where and how to copy the macro code in Visual Basic, how to run them
  • Excel workbook has become very heavy and slow – how to fix it?

Leave a Reply