.Net4.0 version is uploaded here: XLCleanerDotNET4.0.
Documentation:
Refer to the XLStylesTool user comminity contributed information and “how to” video.
Windows 8:
XLStylesTool for Windows 8 in the Windows App Store.
Web Browser Application Option:
If you are using a Mac, don’t want to or can’t download .Net, don’t have time to setup SkyDrive, use browser based Silverlight version of the XLStylesTool.
Background:
Unlike other tools, MS Excel add-ins or VBA macros that deal with excessive styles that rely on MS Excel object model, XLStylesTool uses Office Open XML file format based deep cleaning algorithm (patent protected). This means that it works 100+ times faster and is the ONLY option that can get rid of hidden and built-in corrupted styles inaccesible to other tools.
About 20% of all users that downloaded the original XLStylesTool to date also clicked on .Net3.5 Client Profile download link provided in one of the comments. .Net3.5 Client Profile runtime is necessary to run the application. The download size is ~255MB. Compare it to the .Net4.0 Client Profile download size which is only 41MB. I recompiled XLStylesTool against .Net4.0 and if you don’t have .Net3.5 on your machine yet it makes sense to go with the smaller .Net4.0 Client Profile Download.
Please Leave Comments:
Same ask as before: please leave comments if you used the utilities. All application features, optimizations and new utilities downloadable from this blog are the result of the user community feedback starting with the original post. User comments and content contributions make this project more valuable to the worldwide user community.
Recent Comments
Too many different c… on .NET4.0 version of the XLStyle… XLGeek on Adding more utilities Konami on Adding more utilities XLGeek on Adding more utilities Connor on Adding more utilities
Pingback: Sharing useful utilities | XLGeek's space
Hey! I’m trying to use your utility on a workbook that I broke yesterday afternoon! We have a very large workbook with cells formatted a million different ways, apparently. When I moved some sheets from another workbook into this one, my formatting disappeared. When I try to run your utility, I see a message that says “File contains corrupted data” next to the Process File button. Do you know a way to fix this? Or work around it? I’ve been googling my fingers off, and trying many different things. Your utility sounds like my best option, if I can get it to work.
Hi Teresa, there may be couple of reasons for this behavior:
a) the file is not in OpenXML format: i.e., it has *.xls (old binary format) or *.xlsb extension. You can do “Save as” *.xlsx or *.xlsm (if file contains macros) to address this.
b) the file has password to open it. In this case document part that contains format definition is encrypted, so the tool can’t read it. You can temporarily remove the password to open the file. Read-only password is OK.
c) one or more of the document parts are corrupted. This can only be determined by disassembling the file which I can help with. Sometimes it is possible to fix this by tweaking part contents by hand.
If none of the suggestions above work for you I would be more than happy to look at your file. You can reply to this comment with your contact e-mail (I won’t approve it, so no one will see it) and I will send you my e-mail address to send over your file. You can delete all numeric data from it.
Thank you for writing me back so soon!
It is an .xls file. I opened it and saved it as an .xlsx, ran the utility, and then checked the file. The formatting is still missing. When I check Cell Styles, I still see all the individual styles that have been added.
No password, so that’s not an issue. I did notice that when I saved the file as an .xlsx, the size dropped from over 2,000 KB to only 371 KB. I realize that the .xlsx format uses less space, but this appears to be a bigger drop than is typical.
Please make sure you check “Commit changes and open in Excel” checkbox and click “Process File” button again.
Your utility works beautifully for us. I want to thank you for creating it, and for putting comments about it out there on so many tech blogs so that I could find it!
I work in an environment that is a mix of Office 2007, 2003, and 2000. So even though I and my most immediate coworkers are using Office 2007, we have to save our Excel files at a lower version in order to share them with the rest of our department and the organization. We are also currently in the middle of a major system upgrade that will affect every department, so we are currently creating and sharing much larger workbooks than we have in the past. This has created a “perfect storm”, in which the limits of Excel have caused files to crash, and formatting to be lost. The file crash which sent me to your blog involved a workbook that we have been using and updating for well over a year now. By the time it crashed, there were more than 9,000 different cell format styles. I believe that the problem had been building up for a while, because your utility shows me that a previous version of the file from late February has 1434 different styles. The most insidious part of this is that Excel will act like everything is perfectly fine and normal when you save and close. No error messages, and nothing to indicate that anything is wrong. The problem comes when you try to reopen the file. And so you don’t know that you are in trouble, until you are really in trouble. It’s sneaky that way!
I had the opportunity to use your utility again this morning. A coworker had a very small, simple (but important!) workbook crash on her just this morning, all because she copied and pasted a small amount of data from another workbook where the styles had gotten out of control. Luckily, she came to someone who was involved with troubleshooting my particular file crash, and so the problem eventually came to my attention and I was able to help her.
I’m going to alert the rest of my coworkers about this problem and the solution. And I’ll also get in touch with our network engineers about the possibility of running your batch utility on our network, so that we won’t have to worry about this issue any more. Thanks again, XLGeek! Keep up the good work!
Pingback: How to use XLStylesTool resources | XLGeek's space
Thank you so much for the tool! It is true life saver!
About 4 years ago my company all got dual monitors, and everyone found that opening Excel files in separate processes would allow them to have different files on different windows. Thanks to me (and yes, I regret it…) I found that I could modify the file type advanced properties by adding a %1 and un-checking use DDE would force all files to open in their own process. Everyone loved this… It took about a year before we started noticing that some xls files would lose all formatting.
Our fix at the time was to migrated to Excel 2007! But, within 6 more months even those started to have issues… It was at this time that I found out about styles corruption! We learned that if you take a corrupt Excel file and copy a single cell (even an empty one) and then paste into another Excel file (even a brand new one) ALL corrupt formatting would transfer over! Now, this only seemed to happen when the files were open in separate processes. If both files were open in the same window/process the copy/paste would be quick and no corruption would occur. But when in separate processes a simple copy/paste could take 10-20 seconds while it copied over 600kb, 60,000 extra styles. Insane!
For the past 2 years or so I have used ASAP Utilities for their capability of cleaning extra styles; however, in the past 6 months I realized two things.
1. It took a very long time on large Excel files.
2. It missed some styles…. By looking at the xl\styles.xml file, I could see some styles were being completely missed! These styles had weird attributes “Hidden=1” and “CustomBuiltIn=1.” From here I tried to create my own style cleaner in VB.Net, and found that nothing that I used could even see these nodes with those specific attributes.
So the next step was to look into XML parsing and doing this myself… Something that I was on my way to doing but always ran into issues. Different Excel developer forums had never heard of this issue – one person going as far as saying that if I had 40,000 styles I set some sort of record!
I had all but given up when I found this site by accident the other day. This tool is not only amazing at finding all styles, it does so extremely quickly. Something that would take ASAP Utilities 5+ minutes and all of my processor would take seconds using this utility… And the resulting file size was smaller!
Sergeig888, you are awesome to provide this utility free of charge to help reduce this seriously annoying problem. Your name will be praised in my department for saving us time, and preventing people from having to re-do work because of lost formatting.
Pingback: MSFT released hot fix for Excel 2007 custom styles duplication | XLGeek's space
Hi,
We have a large, ~20 tab, Excel 2007 .xlsm file. Periodically, we have had a problem with cells randomly changing into dates. It happened again today, although the formatting change only affected headers this time. Finally did some research and discovered the problem with Excel Styles. (Which we’ve never knowingly used.) After running the utility, the file no longer shows any styles brought in from other spreadsheets. The utility did not fix the headers, although I reran the utility four or five times and I’m pretty sure I followed the instructions correctly. So I manually corrected the headers and reran the utility again. I’m hopeful that the file will stay uncorrupted this time. Thank you for making this available.
Hi Deanna, this behavior makes perfect sense: this app will remove unused styles as well as the style property associated with the cell if you choose “Force all cell styles to ‘Normal’” (<-highly recommended for best results and probably the best option for your situation). Cell formatting will not be altered at all. That is the objective of my app: get rid of bad styles, stabilize the file, but keep all formats intact. If at the time when you ran the utility the cell in question was formatted as date and had say "Date&Time" custom style applied to it then when the processing is done original cell formatting will remain, but the style property may be removed if you forced all to Normal. Please make sure you don't have any Excel add-ins installed. These usually are responsible for re-introducing bad styles back into the cleaned up workbooks and messing up cell formatting. Thank you very much for sharing your experience.
I have same issue but my file is passwod protected. How I can remove password to use your utility.
Thanks in advance
I mean to say vba code is protected. thanks.
Protected for viewing VBA project even if it is digitaly signed will not have any impact on how the utility works. As long as the file is in Open XML format (.xlsm since it has VBA) and the file is not password protected utility will process just the formatting part and won’t touch VBA part (or parts if your code is signed) at all. My utility will work only if your file is in Open XML format with .xlsx or .xlsm extension and is not password protected: password protection encrypts file contents which includes format definitions used for custom styles. All binary files: e.g., .xls or .xlsb also won’t work. I won’t be able to help you hack the password for obvious reasons. Utility will not be able to open password protected file or binary file. Hope you have an option to save the file off as .xlsm.
Pingback: You receive a "Too many different cell formats" error message in Excel | Microsoft Knowledge Base Wiki
I’ve run the utility on my file and get an Excel File Cleaner Error message: “Unrecoverable Styles Corruption”
Any ideas? It is an xlsx file and it is not password protected.
Unfortunately your file has reached the maximum limit of custom styles count on the last save and now is fully corrupted. I hope you have a recent backup.
This is a great utility you created!! I had been tearing my hair in trying to fix this issue with a pretty large file that had a large number of styles being used and it kept crashing on me.
This works great! Like everyone else, this has been a problem for a long time, and we could never figure out how to clean it, and others we have seen cleaned down to the font formatting, and that did not work at all. This one was quick, easy, and brought the exact results expected. Thank you!
Is SkyDrive access a problem for some users? It is free for me to publish the tools and free for world wide users to download. Please leave a comment if you have issues. I’ll consider creating a mirror if it is a big deal. I saw someone creating a copy of the tool on some site in Asia last week. That is bad: that file will never get updated and the apps on this site get upgraded periodically.
Hi ,
I wnted help on resolving the issue with xls files.Please help.Currently all the formats are destroyed after opening the file.
Binary file formats: .xls and .xlsb are not supported. You need to upgrade to .xlsx to use the tool. See converting .xls files to .xlsx post.
The file is in xls format and is very important .If i open it,a command box opens with excel found unrecoverable contents in the file,do you want to recover the contents” and if I click yes,all the formats are deleted.Even if I convert the file to xlsx,the fromats are lost.
Please help.
I’m afraid your .xls file got completely corrupted on last save. Most likely last save was in Excel 2007. You definetely have corrupted styles problem.
Hello,
I tried to run XLStylesTool.exe to clean up format in my file, however I can’t run it. The error message is “To run this application, you first must install one of the following versions of the.NET Framework:v4.0.30319″. Anyone can help me with this please?
Hi Tio, the link to Microsoft .Net4.0 client profile download is available in the post above. The error message that you get means you have some version of .Net installed on your PC. If you are using Windows 7 you already have .Net3.5 and can use original version of the XLStylesTool without the need to install .Net4.0. I synced all features between the two apps. In rare cases .Net3.5 needs to be activated on Windows 7 (part of Windows features under Programs and Features in the Control Panel). You can also try browser version of the app. It will require very small Silverlight 5 run-time download and will produce the same results as WinForms app. Let me know if you need any further assitance.
Thanks for this great utility – is there any way to run this from a command line or in any kind of batch mode? I need to clean 2250 files!
Sorry – just found your other page with the console app – thankyou
Hi Steven, glad you found it. This ask came up a bunch of times from enterprise users. Last year I added updated version of the bulk cleaner with support for subfolders (make sure you get XLBulkCleanerSF.zip). I can’t imagine your files are in a single folder. Although I increased memory buffer size to its absolute maximum of 64K it may happen that at 2250 files you may overflow. Please pipe console output into a text file and analyze the list to make sure that some files didn’t get skipped. The relevant to your case feedback I got so far was from the user with 500+ files and at 64K buffer it wasn’t a problem: all files got processed. Let me and everyone on this thread know how it went for you. You are on the higher end and it is very interesting case. Thank you.
Hello!!
You are the Man!!, I am more than grateful that I find this page, You save tons of hours of work with this application, Thanks for share the knowledge and share this useful tool…
Hello!
Thanks for sharing this. It seems it has worked for most of the people. Not for me. I arrived here because I am facing both Excel issues you described in your first post: “too many cell formats”, and “I can’t copy/paste cells from one workbook to another”. I downloaded your NET4.0 app and the .NET4.0 Client Profile using the links you provided and run the app in the two workbooks. I made sure to toggle on the four options, including “commit changes and open in Excel”. The files seem to be cleaned, yet they did not open after the app finished processing the files. At the end of each run, an Excel window popped up, but the file did not open. I open manually the files, save them again and tried to copy/paste from one file to the other, but the problem persists. Paste Special does not work at all, and “normal” (values) paste only works once. The next time I try to paste, destination cells are empty . Any idea on what is going on? Thanks!
Hi Mauricio, do you by any chance have Skype add-in installed: http://community.skype.com/t5/Windows/Skype-add-in-causes-issues-with-Excel/td-p/426765? If not, I would like to connect with you offline and look at the files that have a problem. Please reply with the Skype check. I will contact you directly if the problem is not related to skype.
Thank you for posting this valuable tool. I thought I was faced with rebuilding all corruputed files when I found this. Now hours of productivity have been given back to my company.
I love this! Saved me so much work. Thank you!
Excellent light-weight solution – Many Thanks
Pingback: Microsoft Excel beschwert sich über “Too many cell formats” « think eMeidi
Can someone please translate this article? Based on what I understood there is a push for VBA. Please stay away from VBA – it can’t access hidden styles and styles with low ids. You are better off with the Open XML based clean up.
Thank you very much! It worked prefectly
Hello – I just want to express my sincere thanks for your help. I have had issues with “too many cell formats” on an excel dump I was doing and I have consulted so many support people to try and figure this out. This tool solved it and couldn’t have been more stright forward. Again, thanks.
This was really usefull and fixed multple files for me.
Many thanks for posing
This tool is a god-send! I was running into limits on several spreadsheets, and I suspected it had something to do with copying things from other spreadsheets. But I didn’t want to re-create some very elaborate spreadsheets from scratch.
I stumbled across this utility, and it appears to have completely solved the problem.
THANK YOU, THANK YOU, THANK YOU!!!
You made my Friday! This tool is awesome. Thank you for sharing.
Got my issues fixed in a jiffy! This tool is like an amazing find and You are nothing short of a BLESSING!
This works great! We have been dealing with this huge file with so many issues. There were days were I would 30 seconds before the file would respond after I hit enter. Also, copying and pasting was a nightmare.
One question though, I would like to understand if you are able to obtain any of the data that runs through this app.
Hi Kristy,
Thank you for the feedback. I don’t think I understand your question. Are you concerned with your data privacy? All versions of the XLStylesTool (even browser based) accessed or downloaded from the locations listed in my blog posts work on the client machine in memory and the memory is released as soon as scanning and processing are done. Not a single byte gets stored in the file system or is transmitted outside. Please see Privacy Policy for Windows 8 XLStyles Tool application in the related post. It applies to all versions of the tool. Or are you interested in leveraging my code or tool functionality in some other app? I already covered typical enterprise scenarios: e.g., console based version of this app actively monitors specified file share and automatically cleans up files saved on that share displaying appropriate stats.
I am concerned about privacy. I just wanted to ensure that this app doesn’t download my data.
I have been working on the file since I made the post earlier. It is not an understatement when I say this saved me hours of time.
I absolutely understand your concern and that is why there are detailed data privacy disclosures in the Silverlight and Windows 8 App version blog posts. Please be assured that your data is not transmitted anywhere by my app. Some people even asked if the tool was a virus. Seriously, would I put my name on a virus and would Microsoft include it in the KB article? As long as you download the apps from the links on my site you are getting official versions of the tool. Productivity wise the highest stat that I got from a user in Finance department was an estimate of 20 saved days per calendar year per person! They actually created a PowerPoint presentation to teach their team members on how to use the tool. These guys had a ton of old huge workbooks that they were upgrading to new versions of Excel for almost 15 years.
Works amazingly quick, and fixes one of the most annoying bugs in Excel…too many formats!
Thanks!
Hi,
We are using excel (.xls) files as test data files using HP-Quality Center in our Automation testing, but now a days when an excel is open from QC for any modifications we are getting “Too Many Different Cell formats” message popup and the change is not getting reflected back. According to Microsoft this is happening because of the Excel 2003, it supports only 4,000 different combinations of cell formats. As we are using many different files across different projects, so it is difficult to change/ upgrade the test data files (excel) from 2003 to 2007.
Will this ‘XLStylesTool’ helps us from this without any further problems?
XLStyles Tool will work only on .XLSX or .XLSM file. You can always bulk update .XLS files to .XLSX/.XLSM. There is a post in my blog with all necessary resources.
Awesome utility!!! Saved me big time.
Thanks….glad I found this tool. I was going crazy!
Fantastic, THANK YOU.
The utility worked great. Saved me a lot of time using work-arounds.
Thank you very much! It worked great! I also appreciate the fact that you are not charging people for this.
hey my exel error message says ‘ too many different sell format” how can i solve this?
hey, apologies…typo ….my exel error message says ‘ too many different CELL format” how can i solve this? thanks
What version of Excel are you using? If it is 2007 or newer XLStylesTool will fix your file. Have you tried using it?
microsoft office exel 2003… I dont know what happened , but after getting nowhere I tried using the backup copy of the spreadsheet & everything seems fine again , dont know if it will occur again …, will be good if you can let me know what to do if it does
P/s: Thanks a lot, XL Geek, I have never tried asking help this way before & I didn’t know if anyone will reply at all, like a mystery , dont know if you are half way across the globe or nearby…
Hi Celia, Microsoft Knowledge Base article explains what is causing this error in Excel 2003. Chances are your problem is caused by the excessive style count as well. I have seen spreadsheets that exceeded the 4,000 unique formats maximum limit primarily in test labs, and very few in the real world. Your backup copy most likely is close to the limit. If you have a friend with Excel 2007 that can help you save your workbook as xlsx or xlsm and clean it with my tool it would get you the best results.
thanks XL Geek, for the solution- I will follow yr instructions when it happens again. I have been copying whole worksheets over from another spreadsheet , so maybe that’s the reason. maybe deleting those copied worksheets will help? But thanks a lot …cannot thank you enough . Celia
Typically it is copy/paste action between two or more instances of Excel that causes this. Please keep lots of backups if you choose not to clean up your workbooks. If the workbook blows up there is no way to get the formatting back without a backup file.
thanks XL Geek, I will keep back ups and also try to clean, cleaning sounds more complicated. Celia
Hi XL Geek,
I am using Excel 2010 and I am getting the too many cell formats error. I’ve never seen this error. I don’t want to lose my formatting. What should I do? How do I use your tool? should I just run it?
It worked like a charm. Thanks for a great tool.
XLStylesTool saved me hours and hours of work. I can’t even thank you enough for your generosity!
I tried to reach you via Facebook to discuss possibility of making a localisation for this tool, but didn’t manage to. Could you please e-mail me on the matter to [edited for privacy] at gmail.com?
Thank you. Will e-mail in the morning US time.
Thank you Thank you Thank you for making this tool. It saved my butt on a deadline today…
Great tool. Thanks for posting!
The utility worked great!!!! I have been messing with this problem for weeks,. Finally discovered your utility. THANK YOU!!!!
Hi XLGeek,
I downloaded your utility based on a suggestion from a forum about the weird excel bug that randomly converts all columns to date format (http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-2007-spontaneously-formats-entire-work-book/1ae533af-ecee-41fa-866a-2590343a111d?page=2); I ran it on the file in question, but it didn’t help…Any ideas/advice? Much appreciated!
That means your issue is not related to corrupted styles, but something else. I can contact you via e-mail and look at your file. I may be able to spot what is wrong with it.
Thanks for your help! Can I email you the file?
Best regards,
Boban
________________________________
Sergei, I can’t thank you enough!!! I really appreciated your help, and I feel really empowered by the way you explained the solution to me! You knowledge is great and so is your kindness!
XL, Windows Live is blocked in my company, is there any other way I can get this tool?
Hi Erick, can you use Silverlight 5 version: http://xlgeekwebapps.cloudapp.net/default.aspx? If not I can e-mail the zip package to you if your company allows zip attachments.
The Silverlight link worked, thanks a lot, great tool!
Thanks for this! It saved a lot of my hairs!
Worked like a charm!
Just wanted to say thanks. My workbook was to the point of being unable to format any cells and opening/saving was taking forever. Your app cut the workbooks size in half and is fast. Thanks much.
A comment, as requested – It Worked! Apparently approx 55,000 Styles Max ID, 11 Style Node Count, size of file went down from a Mb to 600k. Not dramatic, but saved the file.
Thanks!
Petra
Thanks a lot for a wonderful tool. Saved a ton of time for me!!
Pingback: Too many different cell formats error message.....
After working on the problem a bit longer, I had to use the utility on the Excel workbook I was moving tabs into (in addition to the workbook I was moving tabs from). This solved my issue. Thanks a lot!
Glad it worked. I tried e-mailing you.
This was on the old file. I had started moving everything over to a new workbook of which I am about half way there and I did get the clean up to work on that file. Still couldn’t get it to work on the old file, still says device attached to the system is not functioning.
Hi Ashley, the error message doesn’t really make sense. Which application is showing it? The text of this message relates to old VB6 dll which is not used in the Excel styles tool. I have never seen what you are reporting before. Can you please share your PC specification: OS type and version, what versions of .Net are installed, Office version? As an alternative you can use Silverlight 5 version of the XLStyles Tool. It was created specifically for people that have issues with .Net, the hardware or are on IT lockdown.