The Mystery of Excel’s Too Many Different Cell Formats

If you have ever received the “Too many different cell formats” error message in Excel, stop what you’re doing, take a look around your workbook and ask yourself “are all these cell formats really useful?”  After you answer the inevitable “no”, smack yourself in the back of the head for creating such a mess!  Just kidding, don’t actually smack yourself, but apologize to your co-workers for making them put up with your “creativity”.

The reason why you got this error is because an Excel file can only have approximately 4,000 different combinations of cell formats.  At first this might seem like a lot, but think about all the unique formatting characteristics that can be applied to a cell.

  • Fonts: including the font, font size, bold, italic, underline, strikethrough, superscript, subscript, color, etc.
  • Borders: including which side of the cell has a border (top, left, right, or bottom), border color, border thickness (or weight), etc.
  • Fills: including fill color, and patterns.
  • Number formatting: such as General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, or Custom.  Not to mention the number of decimal places shown.
  • Alignment: Top, bottom, left, right, centered, centered across selection, indented, orientation degrees, wrapped text, shrink to fit, merged cells, text direction, etc.

Any unique combination of the above cell formats counts towards the 4,000 limit, however if several cells share exactly the same formatting, it only counts as one.

For example, assuming that all other formats are the same, a 3×3 cell “boxed” with a border going around it would have nine different cell formats, considering that no cell in the group would share the same side border formatting (see below).

Top & Left Top Top & Right
Left None Right
Bottom & Left Bottom Bottom & Right

 To fix this, simplify the formats of the cells in your file, by using some of the suggestions below:

  • Use only one or two standard fonts.  For example, if you want your headings to be bold, and the data to be in a regular style, do so consistently.
  • Use consistent borders in your worksheets.  Maybe just box in headings and your data points to make it easier for your files users to read.
  • Clear out unnecessary fill colors and patterns.
  • Make sure your numbers are consistently presented (i.e. percentages are shown as such with the same number of decimal points, dollars are shown in the currency, or accounting format, etc.).

Also, consider clearing the formats of blank cells.  To do this, click Edit | Go To | Special | Blanks.  This will highlight all cells that do not contain data.  Next, click Edit | Clear | Formats (or All).  This will clear all the blank cells of any formatting that have been applied to them.  Be careful however, as this could have unintended consequences if you’ve filled the cells in a row or column to act as a “border” between sections in your worksheet, or if you’ve used the Center Across Selection formatting.

After done simplifying your cell formats across your entire file, save, close, and then re-open before adding any new cell formats.

Comments

  1. Cindy Balster says

    I’m wondering if anyone has noticed this being a problem more so now with the release of Excel 2007? We are running into problems where a file is created in Excel 2007 but used & updated by others who have Excel 2003. When the people with the lower version go to open the file they receive the “Too many different cell formats” error message.

  2. Graham Hensley says

    Thanks for explaining “Excel’s Too Many Different Cell Formats” problem cause so well.

  3. Mr. Fong says

    We had the same message with one of our users with Excel 2003. I solved the problem opening the file in Excel 2007 and seved it as Excel 2003 file and with a different name. Then I opened the file again in the original PC with Excel 2003 and the problem was solved.

  4. Clay Wisner says

    I’ve just run into this for the first time. The spreadsheet is a product comparison matrix with a LOT of photographs, diagrams, colored backgrounds, etc over several tabs. I’ve been told by the creator of the spreadsheet that it was saved as a “Excel 97-2003 worksheet”, but I certainly cannot open it. (yay!)

  5. christine says

    My Excel sheet has frozen with the error message “too many different cell formats”. I cannot get rid of the message and cannot save or go into anything else so that I can check the formats/edit anything etc. I don’t want to close the programme as my work has not been saved yet.

    Mega Mega Help please.

    Thanks Christine

  6. Dima says

    Aaarrrrrggghh!!!

    I can’t get around this problem & I need to be able to use this Excel 2010 file in Excel 2003. I’ve tried deleting all the other worksheets I wasn’t usign in the file & I tried clearing all the formatting. Then, when I try to add in any new formatting to help me navigate the data (eg. filters) I get that blasted “Too many different cell formats” message again!! WHY??? :(

    Please help…

  7. Scott says

    It must be that there are still cells formatted even if they don’t appear to be. For example, you can have several cells to be formatted as different fonts, text sizes, text colors, etc. and with no content in them they’ll all look the same.

    Try starting fresh. Copy and paste your DATA ONLY into a new workbook by selecting the data, clicking copy, selecting the cell in the new workbook where it should end up, and click paste formulas assuming there are formulas. Otherwise paste values would work just the same.

    Then go ahead and start to lightly apply new formatting as necessary (header rows, total columns, etc.) to make your spreadsheet a little more visually appealing, but not so colorful that it gets confused for Mardi Gras! @Dima

  8. XLGeek says

    The bugs that lead to “Too many cell formats” error message in Excel 2007+ have been addressed: http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-excel-2007-custom-styles-duplication/

    Note that the bug fixes will not remove pre-existing styles related file corruption. Open XML based tools (available for free) are the only option that can remove elements inaccessible to the Excel object model based tools: e.g., bad styles that disguised themselves as built-in, hidden styles, etc… Open XML based cleanup means 100% styles corruption free files.

  9. Lroi says

    The suggestion to find the ‘blank’ cells and clear the formats worked like a charm. Thank you so much.

  10. Marco Ladino says

    Why to live with this annoying limit?
    these limits do not exists in LIBREOFFICE, an opensource office framework solution,
    full compatible with ms-formats (XLS,XLSX.DOC,DOCX,PPT,PPTX,etc..),
    and works with all ms-windoze versions..
    Supports very huge number of formats and rows without troubles (1MEGA++ rows),
    and is free, without ads..try it..! http://www.libreoffice.org

  11. Barbara Wiseman says

    The ASAP utilities has a function to remove all unused styles on the sheet area. Seems to work well if you get the error when the file is open.

  12. Angry person says

    My spreadsheet tells me this problem even when I don’t have excessive formats i.e. no colored cells at all

  13. Renee C says

    I’m not an IT person but I did not have this issue until my pc was upgraded from Windows XP to Windows 7. I have spent several hours and days on the phone with my companies IT support with little to NO help and no resolution to solve. A co-worker heard I was having issues and knew exactly what the problem was and exactly how to fix. Here is what they did. I hope this helps as I’ve had no issues since.

    Start
    All Programs
    MicroSoft Office
    Right Click MicroSoft Office Excel ( I use 2007)
    Click Properties
    Compatibility
    Check – Run this program in compatibility mode for:
    Select whatever you use if not already selected. In my case this showed Windows XP and needed to be Windows 7
    OK
    You will need to restart your computer but first I would update all of your MicroSoft Office software if needed.

    Good Luck!

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>