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”.
Newton Excel Bach, not (just) an Excel Blog 
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
LikeLike
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.
LikeLike
Pingback: 6 Year Report | Newton Excel Bach, not (just) an Excel Blog
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
LikeLike
I believe that this problem may have been fixed in Excel 2016 (Mac at least)
Bob J.
LikeLike
About time!
How is Excel on a Mac these days?
LikeLike