Stop “Too many different cell formats” errors

Stop “Too many different cell formats” errors

Have you encountered a “Too many different cell formats” error message? This often occurs as a result of copying or importing worksheets from one workbook into another, because by default Excel also imports all the custom styles (cell formats) from the source workbook. Generally there are about 50 basic styles, but when worksheets have been copied or imported many times, the number of styles can increase up to a maximum of 64,000. This can double the size of a workbook and make it very slow to load.

So this error can be very frustrating, and very difficult to eliminate manually. Fortunately, as you can see from the composite Rainbow Pro screenshot above, we’ve just released an update for Rainbow Pro with a new function called Clear Formats, which in most cases will quickly eliminate the “Too many different cell formats” error. Clear Formats works by deleting all the custom (i.e. not built-in) styles; a colleague of ours has just used it to clean up a problem workbook, reducing the number of styles from more than 62,000 down to just 47! To check if you have this problem, you can run the Summary Report to see how many styles there are in the workbook (in cell B18).

It is generally quite safe to delete all the custom styles; this will not change the underlying data or formulas, and it will probably not even affect the visible formatting. However, if your spreadsheet is sensitive to formatting changes, you can use the File Save As function to save the cleaned workbook under a new name, and check it against the original.

This latest release of Rainbow also includes a couple of other minor improvements. One is that we’ve changed the default selection of Favourites for the Risk Map function, removing a couple of very slow options and adding others that are equally useful but run faster. As a result the Risk Map function (with default Favourites) now runs up to six times faster than before. And the other improvement is that we’ve eliminated some issues with the Save Format and Undo Changes functions, so these now work reliably as intended.

Sorry, comments are closed for this post.

Back to topAdministration