As per a clients question – I have used sheets in a file that I have previously moved and copied from other files. I continually got the error message: “Too many different cell formats”, and the data became corrupted when someone else tries to open the file. So what are the options with regards how to remove too many cell formats in Excel?
So I re-created the file from scratch, and have no external links, but still get the “Too many different cell formats” message.
Table of contents
The Problem of too many cell formats
The issue here is not related to external links but more to merging various spreadsheets. As per Microsoft support, the issue is how many different format styles does Excel need to remember, with the emphasis on different.
So if 100 cells are formated as Arial 10 in bold, this counts as 1 format to remember. But if 40 are underlined, and 20 are italic then there are 3 formats to remember. If you use Arial but your mate prefers Courier and you have copied his sheet in, Excel now needs to remember all of those styles as well.
Excel 2003 could only hold 4000 styles. More recent versions can remember 64 000 styles. Although this sounds like a lot, with over a million rows and 16000 columns, and with users tendency to highlight an entire row or column to apply formatting, you can quickly run into problems. The most likely cause though is moving/ copying sheets from other spreadsheets into a common spreadsheet. Even if the differences are only minor, Excel needs to remember all your combinations of font, size, colours etc.
Some Methods to ‘clean’ the cells
So the solution is simple. Reduce the number of unique format styles in your spreadsheet!
Easy if you have a hundred row spreadsheet. Not so easy if you are working on a 50 MB monster that has imported sheets from other files and computers.
The Easiest- Excel Inquire Tool
The easiest and cheapest is probably the free Inquire tool that comes with Excel 2013 and later.
If you have it loaded it, you will see in the Inquire tab a Clean Excess Cell Formatting button.
It pretty much does what it says. However, you could still have problems as it will not change your custom formats. So you may need to do some manual work
Use the GOTO Special tool
An underutilised tool is the Goto special tool. This tool can be used to highlight all cells that exhibit a certain characteristic e.g. contain text, or formula or number OR blanks. Using this you can quickly highlight a vast number of cells and change them to have the same format.
So perhaps you can highlight all text and make it Arial size 11. All constants and formula that end up as numbers can be Arial 9 etc.
Although a manual way, at least it gives you a chance to re assess the look of your spreadsheet.
A useful feature is the blanks. Using Goto Special you can highlight all blanks and perhaps clear all so that they are the same format.
Change ALL font and size
If you are happy with it, highlight the sheet and change all the font to be the same and the same size. Do this to all the sheets and use the same font and size.
Format the numbers the same
Numbers should probably only have a few styles, but it is so easy just to add a few decimals here and there to better see a number. Try and settle on standard number styles.
Colours/ Fills/ Patterns
Each colour/ fill or pattern adds to the number of style combinations. Try and avoid using too many colours. We prefer to put a colour on the input cells as there are generally less inputs than calculations in our spreadsheets.
Delete unused columns and rows
Just in case Excel is trying to remember millions of cells outside you active area, it is always useful to delete all columns and rows that are outside your key area. To do this make the deletions, save the file, close the file and re open.
Related
Impact of font type in Excel numbers copied to Word or PowerPoint
Safely delete sheets and avoid REF errors
Tracing the source of DIV and VALUE errors
Stop Excel users from inserting/ deleting rows and columns
Speed up the opening or closing of an Excel file that recalculates