Too many cell formats

[scotthannaford1973]

Hi

I have a huge spreadsheet that has evolved over a while and which also included worksheets copied in from other workbooks. As a result, we keep getting the "Too Many Different Cell Formats" message meaning we are unable to apply any additional, new formats to cells without formatting.

I have downloaded XLCleanerDotNET4.0 which will remove ALL formatting, but as that would mean about a day's work to apply the formatting we do want to keep, I am loathe to use it (but will if needed).

Is there any way to remove any unused cell formatting / style which doesn't affect the formats/styles that I am using?

cheers!

 

[Jeffrey Mahoney]

I have run up against that before. Most of the problems arise from trying to format entire rows and entire columns when you only have a few hundreds rows and maybe a hundred columns.

First try this:
Delete all your unused rows below your data set. Delete all your unused columns to the right of the data. Save the workbook and reopen it. You may have to do that on multiple sheets.

Jeff

 

[Jeffrey Mahoney]

Check if you have a ton of conditional formatting. After setting Conditional Formats, moving data around can create copies that are affecting smaller ranges. Highlight your entire data set and go into Conditional Formatting rules management to check how many rules you have.