Select the product you need help with

You receive a "Too many different cell formats" error message in Excel

Article ID: 213904 - View products that this article applies to.

This article was previously published under Q213904

Expand all | Collapse all

Symptoms

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

Too many different cell formats.

Excel encountered an error and had to remove some formatting to avoid corrupting the workbook.

In Microsoft Excel 2013, 2010, or 2007, files may produce the following error message:

Excel found unreadable content in the file

Other scenarios that are associated with styles are:

1. When opening a file all formatting is stripped out.

2. File size grows after copying and pasting between workbooks

3. Inability to paste:

Microsoft Excel displays

“Microsoft Excel can’t paste data”

All menu option for "Paste" or Ctrl-V keyboard shortcut are ignored and produce no results although clipboard is not empty.

Back to the top | Give Feedback

This problem occurs when the workbook contains more than approximately 4,000 different combinations of cell formats in Excel 2003 or 64,000 in Excel 2007 and higher. 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.

In Excel style counts may increase when copying between workbooks because custom styles are copied.

A workbook that has more than 4,000 styles may open in Excel 2007 and higher (because of the increased limitation for formatting) but result in an error in Excel 2003.

Back to the top | Give Feedback

Resolution

1. To prevent built in styles from being duplicated when copied make sure you have the latest updates for Excel installed from Windows Update.

2. The following KB articles allow disabling copying custom styles between workbooks in one instance of Excel when you move or copy a worksheet. Install and add registry key in the following article.

Unused styles are copied from one workbook to another workbook in Excel 2007

2553085 Unused styles are copied from one workbook to another workbook in Excel 2007

(http://support.microsoft.com/kb/2553085 /EN/ )

2598127 Unused styles are copied from one workbook to another in Excel 2010

(http://support.microsoft.com/kb/2598127/EN/ )

3. To cleanup workbooks that already have numerous styles, run one of the following:

Excel formats (xlsx, xlsm)

XLStylesTool

(http://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/)

Binary Excel formats (xls, xlsb) or Password Protected/Encrypted workbooks.

Remove Styles Add-in

(http://removestyles.codeplex.com/)

4. Simplifying the formatting in your workbooks. 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.
Use Styles to standardize the formatting used throughout the workbook.

NOTE: After you simplify or standardize the formatting in the workbook, save, close, and then reopen the workbook before you apply additional cell formatting.

Back to the top | Give Feedback

More information

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.

Back to the top | Give Feedback