Clearing excess formats

I have recently been getting messages about “too many different cell formats”, particularly when saving  a 2007/10 format file as Excel 97-2003 format.  Some searching revealed that the source of the problem was a bug that caused unused styles to be copied when copying data between different worksheets.

The problem (and resolution) is described at: You receive a “Too many different cell formats” error message in Excel.

This provides a download for an add-in to automatically remove the custom formats: Remove Styles Add-in

and a couple of links with information on the style-copying problem, with a “hot-fix” download to fix it:

2553085 Unused styles are copied from one workbook to another workbook in Excel 2007

2598127 Unused styles are copied from one workbook to another in Excel 2010

I have now run the Remove Styles add-in (once I found the new “Remove Styles” button on the Home tab), and it seems to work as advertised.  I have yet to try the “hot-fix”.

This entry was posted in Excel and tagged , , . Bookmark the permalink.

6 Responses to Clearing excess formats

  1. excel1star says:

    hi I have a utility that uses the XML under pinnings for excel xlsm and xlsm it strips out all styles except for normal at my job at a hedge fund I encountered workbooks with 60,000 styles I actually wrote it as a batch process I have cleaned the styles from over 150 workbooks in under 5 minutes regards Alfred

    Sent from my iPhone

    Like

  2. Bob Jordan says:

    I too got the excess styles situation with nearly 65000 styles and the no more formats error message. It also severely impacted on performance with a full calculation taking over 20 seconds.
    This happened on a Mac OSX 10.6.8 using Excel 2011 and the unused style removers failed for me. The reason for failure was that the styles.Count attribute was only 27 meaning that the unused styles were not apparent.
    I documented my successful attempts to manually edit the styles.xml file and remove the problem. I have since automated this process using Word vba. Check out:
    https://www.dropbox.com/s/v986cpcg2ksxgkz/Killing%20Style%20Excesses.docx

    I think it is about time the this problem was addressed at source level.

    Bob J.

    Like

  3. Pingback: 6 Year Report | Newton Excel Bach, not (just) an Excel Blog

  4. excel1star says:

    I posted the manual steps to clear extra styles on Excel Black Belts Linkedin Group:
    Steps to manually remove excess styles from xlsx and xlsm workbooks
    step 01 download and install notepad++
    step 02 add .zip to your_workbook.xlsx.zip or to your_workbook.xlsm.zip
    step 03 open the zip (do not unzip it)- just right click on the workbook and choose open
    step 04 open the xl folder
    step 05 copy the styles.xml file to another another folder
    step 06 open the styles.xml with notepad++
    step 07 under the search menu – replace “<" with "\n<" make sure to choose the extend option and replace all
    step 08 under the view menu – fold all
    step 09 enter alt+shift+1 to view the first level of xml objects
    step 10
    carefully replace the xml string that begins with "”
    with the following:
    “”
    and then replace
    carefully replace the xml string that begins with “”
    with the following:
    “”
    step 11 under the search menu – replace “\n<" with "<" make sure to choose the extend option and replace all
    step 12 save the modified styles.xml
    step 13 copy the styles.xml and paste it back into the original xl folder.
    step 14 accept the modified styles.xml
    step 15 close the zip folder
    step 16 remove the zip from the workbook name
    step 17 you are done!
    Regards
    Alfred Vachris Chair NYPC Excel SIG

    Like

  5. Bob Jordan says:

    I believe that this problem may have been fixed in Excel 2016 (Mac at least)

    Bob J.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s