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.
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.
Step 2: Now, from the options under the Home tab, click on Clear to expand and select 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.
Step 2: Now, click on the Fill Color option. From the palette, select the No Fill option.
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.
Step 2: Now, from the context menu, select Options at the bottom.
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.
Step 4: In the COM Add-ins window that opens, check mark the box next to Inquire and the hit the OK button.
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.