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.
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.
Thanks for explaining “Excel’s Too Many Different Cell Formats” problem cause so well.
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.
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!)
The problem description above applies only to Excel 2003. Excel 2007+ introduced another scenario that is covered in this thread: http://answers.microsoft.com/en-us/office/forum/office_2003-excel/too-many-different-cell-formats/5b2fdc26-ed5b-4459-8461-5d9d89ebe9a9. The thread includes references to even more resources that provide multiple ways to detect severity and fix this problem in both 2003 ans 2007+.
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
Hi Sorry I forgot to let you know it is Excel 2007
Thanks
Christine
Did you try to walk through some of the steps outlined in this tip?
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…
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
@Scott
Thanks, Scott.
I had tried that, too, but It didn’t work. :( The only thing that did was XLGeek’s XLStyles tool: http://sergeig888.wordpress.com/2009/10/13/sharing-useful-utilities/
XLGeek, can I be the President — no, Vice President! No, JUNIOR Vice President! — of your fan club?? ;)
Thanks!
Fixed thanks to this tool : xlsgenreduction.arstdesign.com
Works with XLS and XLSX files.
There’s also a free tool “XLcleaner” that works well for me:
http://sergeig888.wordpress.com/2011/02/03/adding-more-utilities/
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.
The suggestion to find the ‘blank’ cells and clear the formats worked like a charm. Thank you so much.
I saw this issue in Excel 2010. It caused me to lose formatting on cells when I downlevel saved my .xlsm file to .xls. The issue I ran into was due to a large number of styles associated with the workbook. I’ve written up a VBA macro that fixed it for me.
See http://stackoverflow.com/questions/2449755/too-many-different-cell-formats/8933399#8933399