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|
|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.