Too many cell formats

scotthannaford1973

New Member
Joined
Sep 27, 2017
Messages
33
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

Well-known Member
Joined
May 31, 2015
Messages
1,604
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

Well-known Member
Joined
May 31, 2015
Messages
1,604
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.
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top