Fix Microsoft Excel Error: Too Many Different Cell Formats

If you are using Microsoft Excel to store data, you can add a max up to 4000 unique cell format combinations to a spreadsheet in MS Excel 2003 version. However, if you are using a latest version of Excel, the max unique cell format combinations you can add is up to 64,000.


ADVERTISEMENT

But, when an Excel file reaches this maximum limit of cell format combinations, an error message will pop up saying, “Too many different cell formats“, thus preventing you from inserting formatting to a cell. You can also see this error when you try to paste cells and data copied from another excel file.

You must remember, that the unique formatting includes any kind of cell formatting like, adding numbers, inserting borders, changing, fonts, and even filling colors. All these and more could utilize the formatting limit.

Fortunately, there are quite a few ways to fix this error. Let’s see how.

 

Method 1: Remove All Kinds of Cell Formatting

 

Clearing all types of cell formatting in the excel sheet is what you can try the 1st thing.

 

Step 1: Open the said Excel file and press Ctrl + A keys together on your keyboard to select all the data. Now, go to the Home tab at the top of the file.

 

Excel File Ctrl + A Home Tab

 

Step 2: Now, from the options under the Home tab, click on Clear to expand and select Clear Formats.


ADVERTISEMENT

 

Home Tab Clear Expand Clear Formats

 

Now that you have cleared the contents, you can add new cell formatting to the spreadsheet easily. If problem persists, you can try the 2nd method.

 

Method 2: Make A Standard Font for All Cells and Clear Fill Colors

 

Clearing the cell formatting for the entire sheet can be drastic, especially if you want to keep some formatting. In that case, you can rather minimize the formatting as a quick fix. To do so, you can add a single font style to all the cells and clear all individual fill colors for all the cells. This will automatically decrease the amount of formatting.

 

Step 1: Press Ctrl + A keys together on your keyboard to select all the Excel cells and then go to the Home tab at the top of the spreadsheet (as shown in Step 1 in last method).

Now, click to expand the Font menu and select a new font that will be applied to all the cells.

For Instance, here we selected Arial.

Excel Ctrl + A Home Font Style Arial

 

Step 2: Now, click on the Fill Color option. From the palette, select the No Fill option.

 

Fill Color Option Palette No Fill

 

Now, all the cells will have the same font with no fill color formatting. You can now add your desired format in the cells. But, if this doesn’t work, try the 3rd method.

 

Method 3: Through Microsoft Excel Options

 

This method is applicable for those who are working with MS Excel via Office 365 Pro Plus. This is not valid for any other MS Excel version.

Talking of the solution, this indicates that even blank cells in the Excel sheet can have formatting. For instance, a blank column or a row can be set to have a standard font which will be applied to any data entered thereafter. Therefore, even the blank cells can increase the amount of unique cell formatting.

Here, we can resolve this by removing any added cell formatting from an Excel sheet by adding the Inquire option to the Inquire add-in to the spreadsheet. Let’s see how.

 

Step 1: Open the spreadsheet and click on the File tab on the top left.

 

Excel Sheet File Tab

 

Step 2: Now, from the context menu, select Options at the bottom.

 

File Tab Options

 

Step 3: In the Excel Options window, click on the Add-Ins option on the left side. Then, on the right side of the pane, go to the bottom of the page, up to the Manage section.

Now, from the drop-down next to it, select COM Add-ins and press the Go button next to it.

 

Excel Options Add Ins Manage Com Add Ins Go

 

Step 4: In the COM Add-ins window that opens, check mark the box next to Inquire and the hit the OK button.


ADVERTISEMENT

This will create an Inquire tab in the Excel sheet.

 

Step 5: You can now select the Inquire tab in the spreadsheet and select the Clean Excess Cell Formatting option under the Inquire tab.

 

Step 6: Now, here, choose to clear all worksheets in the spreadsheet and click on Yes to save the changes and exit.

 

This should help you add cell formatting into he spreadsheet without any error, but if this also does not work, try the 4th method.

 

Method 4: Open The Spreadsheet in An Alternative Software

 

There are umpteen alternative software applications around that can help you to add unique cell formatting to such type of Excel sheets easily. From Google Sheets to LibreOffice Calc, there are many other such reliable alternative software available online that support Excel file formats.

These software can help you to even add more cell formatting to the Excel sheet smoothly.

 

That’s about it. Try any of the methods above to add unique cell formatting to your Excel files easily, without any error.


ADVERTISEMENT


ADVERTISEMENT