“Too Many Different Cell Formats” in Microsoft Excel: Causes and Fixes

too many different cell formats“Too many different cell formats” – it’s  a common problem for Excel users, but it is an easy problem to get around. When you save your Excel workbook, you may end up receiving the error “too many different cell formats”, and may wonder what you have done wrong and how to fix it. When you receive this error, you are not permitted to save the file, and risk losing all the work to the sheet or workbook. To make sure you know how to get around all the bugs and problems that can arise with Microsoft Excel, an advanced Excel course on Udemy can teach you what you need to know.

Microsoft Excel is great because it allows you to create spreadsheets and to more easily organize your data, whatever that data may be. Users also have a number of creative options for formatting their spreadsheets. This can make things easier to read and follow, but it may not be necessary and can cause you to receive the aforementioned error.

Microsoft Excel 2003 allows you to have about 4,000 different cell format combinations, and the 2007 version of Excel allows you to have 64,000 variations. This may seem like a lot, but it is important to think about all the unique characteristics available in formatting. A few of these characteristics include:

  • Fonts
  • Borders
  • Fills
  • Number formatting
  • Alignment

The font doesn’t just pertain to the font itself, but also to the size, whether it is bolded, underlined or italicized. The font also includes strikethrough, subscript, superscript, color, and more. Anything you can do to the word or number is a part of the count.

Borders can include which part of the cell includes the border (left, right, top or bottom), the color of the border and the thickness or weight of the border.

The fill is the color of the background cell. This may be one plain color, can include multiple colors, and even include a pattern. Each of these different things contributes to the combination count.

Number formatting also uses up combinations. There are many types of number formatting, including regular number, general, currency, date, time, accounting, percentage, scientific, text, fraction, custom or special. Changing the amount of decimal places will also affect the combination count.

The alignment of the cell will also change the combination count. Different alignments include top, left, bottom, right, centered, centered across the selection, orientation degrees, indentations, wrapped text, merged cells, text direction and shrink to fit.

If you have one cell with one word that includes the Times New Roman font with bold, italicized and underlined features, with 18-point font that is centered with a border on the left, right, top and bottom, you are using a lot of combinations.

Any combination of the formats mentioned above (plus many others), will count toward the limit of 4,000 or 64,000. However, if multiple cells use the exact same formatting, this will only count as one. This can be helpful, but if even one cell has one difference, then it does not count as one and all of those different formats will be counted in the limit.

If you have three sheets in one workbook, every sheet’s formatting will count toward the limit.

How to Fix the “Too Many Different Cell Formats” Error

If you are a beginner with Excel, you might try an introductory Excel course from Udemy to get a handle on the errors you may experience. If you are comfortable with Excel, you can fix these errors yourself. In essence, to fix the problem, reduce the amount of cell formats in your worksheet or workbook. This may be difficult at first glance, because the workbook looks very polished and professional. However, it is quite easy to simplify formats and will actually make the worksheet easier to read and more professional looking. Try these suggestions:

  • Use one or two standardized fonts. You may choose a different typeface for each worksheet, but instead, consider one typeface for every sheet. Also, choose a more standard font, such as Times New Roman or Arial, instead of fancy scripts.
  • Border use should be consistent. You do not need to put a border around every cell. Consider those pieces of data that are extremely important and use a border there. Also, consider one type of border for the entire document.
  • Stop using patterns and fill colors. In most cases, the use of a border or an underlined piece of data are enough to notify readers this is something important. Removing the colors and patterns will save you points with your boss and the Excel program. If you must use color to highlight something, consider using a light gray.
  • Numbers should be consistently presented. If you are creating something with many decimals, all decimals should have the same amount of numbers to the right, for example: 5.03. Two decimal places is usually considered proper and normal anyway. Also, consider providing dollar signs for every cell (remember, if every cell has the same formatting, it counts as one formatting combination).

One last thing you can do is to clear the format of blank cells. You may think this unnecessary, but even if the cell had been formatted and then data is removed, the formatting stays. To do this, click Edit on the menu, then click “go to”, then “special”, then “blanks”. Any cell that has no data will be highlighted. You can then click Next. On the “next” menu, click edit, clear, and then formats (or All).

This can be a fairly easy way to reduce the amount of cell combination formatting. However, use caution, because if you have filled the cells in a column or row to be a “border” between sections of the worksheet, it will remove that border. It can also be problematic if you have used the Center Across Selection formatting, which may be needed in the piece.

After you have removed the formatting of blank cells, you will want to save your file and close Microsoft Excel. Then you want to open Excel back up and open your file before adding anything new. This ensures the removal of formatting has been completed.

Copying Workbooks

If you will be copying between workbooks, you can add a lot of combinations because the styles and formatting is also copied. To stop built-in styles from being copied along with the copied workbook, you should use Windows Update on your computer to install the latest updates for Microsoft Excel. It is also possible to add registry keys that prevent copying built-in styles, but it is very dangerous to change a computer’s registry, and only a professional should do this.

No matter how advanced in Excel you are, there are always perplexing issues that come up. Udemy has a wide range of Excel courses, such as this detailed Excel 2010 course, to ensure that you know how to use the program you have on your computer. You should also consider taking a full Excel 2013 training course on Udemy to ensure that you know how to get the best out of this program, no matter how you plan to use it.