Add in to remove Formats and Styles

I thought I would share a success story.

I have a soreadsheet that has been moved between Excel 2003 and 2007 several times and we use it as a major source of reference between a few companys. So as you might be able to image, over the years it has has hundreds of cut and pastes between it and others. Several of our workgroup only have 2003. Lately in attempting to save it as a 2003 file, when I open it up, all the formatting is gone. Major issue.

I would run the compatibility checker, get the message

Some cells or styles in this workbook contain formatting that is not supported by the selected file format.......

It would show three occurances (and no find or fix button, as shown in the help file)

In searching around, I found that Microsoft (sort of) suggests to remove unused custom cell styles. Sure, I could go through and right click each on and delete it, but even after 10 minutes oer so, I seemed to be getting nowhere.

I ran Jim Cone's Formats and Styles addin and it found over 4000 unused styles!. I new the sheet was jacked up, but over 4000? Anyway, deleted them and all is now rigt with the world.

While this sounds like an infomercial, it worked great. Thanks.

Steve,

You are welcome. and thanks for the story & feedback.
-
Jim Cone

Hi Steve,

A well know situation. I've created a set of tools for a customer to remediate files like these. The tool would have fixed this issue as well I expect.

One other thing I'd do is run my Name Manager on the tool and see what range names have gathered in there. COpying and pasting has the tendency of dragging range names with it into the file as well. Use the Unused range names filter to see which aren't in use and might be removed.