Too many cell formats

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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,830
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top