.NET4.0 version of the XLStylesTool is now available

.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.

About these ads
This entry was posted in Computers and Internet. Bookmark the permalink.

191 Responses to .NET4.0 version of the XLStylesTool is now available

  1. Pingback: Sharing useful utilities | XLGeek's space

  2. Teresa says:

    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.

  3. XLGeek says:

    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.

    • Teresa says:

      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.

  4. Teresa says:

    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!

  5. Pingback: How to use XLStylesTool resources | XLGeek's space

  6. mcgruffy says:

    Thank you so much for the tool! It is true life saver!

  7. Robert says:

    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.

  8. Pingback: MSFT released hot fix for Excel 2007 custom styles duplication | XLGeek's space

  9. Deanna says:

    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.

    • XLGeek says:

      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.

  10. tim says:

    I have same issue but my file is passwod protected. How I can remove password to use your utility.

    Thanks in advance

    • tim says:

      I mean to say vba code is protected. thanks.

      • XLGeek says:

        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.

  11. Pingback: You receive a "Too many different cell formats" error message in Excel | Microsoft Knowledge Base Wiki

  12. Jennifer says:

    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.

    • XLGeek says:

      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.

  13. Saikat Paul says:

    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.

  14. Brenda says:

    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!

  15. XLGeek says:

    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.

  16. Manup says:

    Hi ,
    I wnted help on resolving the issue with xls files.Please help.Currently all the formats are destroyed after opening the file.

  17. Manup says:

    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.

    • XLGeek says:

      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.

  18. Tio says:

    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?

    • XLGeek says:

      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.

  19. Steven says:

    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!

    • Steven says:

      Sorry – just found your other page with the console app – thankyou

    • XLGeek says:

      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.

      • Ed Haber says:

        This program has been great for fixing a ‘viral’ excel problem with unused styles propagating as users copy and paste data around. I think I am seeing a bug with the XLBulkCleanerSF. When I open excel files it sees the ~$filename.xlsx and thinks for a minute or so and then says it failed to write to that file. If i point the XLBulkCleaner at the same folder it works and correctly cleans the file on close and ignores the ~$ temp file.
        Thank you.
        -Ed

      • XLGeek says:

        Hi Ed,

        Thanks for the follow up. This shouldn’t be happening: the codebase is the same except for the buffer size and support for tree vs. single folder scan. I suspect that you may have a file in one of the subfolders (could be hidden or system) that makes the SF version barf. Would love to follow up. Windows logs will have an entry for the crash with the stack trace. I’d appreciate if you can share. I can reach out offline for an e-mail follow up if that is OK with you.

      • Ed Haber says:

        Yes. Please email me and I will give you additional information about the issue I’m having.

  20. Bro! says:

    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…

  21. Mauricio says:

    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!

  22. Michel says:

    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.

  23. Holly says:

    I love this! Saved me so much work. Thank you!

  24. Ste says:

    Excellent light-weight solution – Many Thanks

  25. Pingback: Microsoft Excel beschwert sich über “Too many cell formats” « think eMeidi

    • XLGeek says:

      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.

  26. Goodat2d says:

    Thank you very much! It worked prefectly

  27. Rory says:

    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.

  28. calg says:

    This was really usefull and fixed multple files for me.
    Many thanks for posing

  29. Ted Kennedy says:

    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!!!

  30. Leanne says:

    You made my Friday! This tool is awesome. Thank you for sharing.

  31. Rani Sowmya says:

    Got my issues fixed in a jiffy! This tool is like an amazing find and You are nothing short of a BLESSING!

  32. Kristy says:

    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.

    • XLGeek says:

      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.

      • Kristy says:

        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.

    • XLGeek says:

      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.

  33. Fer says:

    Works amazingly quick, and fixes one of the most annoying bugs in Excel…too many formats!

    Thanks!

  34. Dhanunjaya says:

    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?

    • XLGeek says:

      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.

  35. Michael says:

    Awesome utility!!! Saved me big time.

  36. Curt says:

    Thanks….glad I found this tool. I was going crazy!

  37. Alex says:

    Fantastic, THANK YOU.

  38. Bill Digges says:

    The utility worked great. Saved me a lot of time using work-arounds.

  39. Jeff says:

    Thank you very much! It worked great! I also appreciate the fact that you are not charging people for this.

  40. celia says:

    hey my exel error message says ‘ too many different sell format” how can i solve this?

  41. celia says:

    hey, apologies…typo ….my exel error message says ‘ too many different CELL format” how can i solve this? thanks

    • XLGeek says:

      What version of Excel are you using? If it is 2007 or newer XLStylesTool will fix your file. Have you tried using it?

      • celia says:

        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…

      • XLGeek says:

        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.

      • celia says:

        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

      • XLGeek says:

        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.

      • celia says:

        thanks XL Geek, I will keep back ups and also try to clean, cleaning sounds more complicated. Celia

  42. Chakra says:

    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?

  43. Chakra says:

    It worked like a charm. Thanks for a great tool.

  44. twinchenzo says:

    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?

  45. Ryan says:

    Thank you Thank you Thank you for making this tool. It saved my butt on a deadline today…

  46. KJ says:

    Great tool. Thanks for posting!

  47. CraigJ says:

    The utility worked great!!!! I have been messing with this problem for weeks,. Finally discovered your utility. THANK YOU!!!!

  48. boban says:

    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!

    • XLGeek says:

      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.

      • boban zarkovich says:

        Thanks for your help! Can I email you the file?

        Best regards,

        Boban

        ________________________________

      • boban says:

        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!

  49. Erick says:

    XL, Windows Live is blocked in my company, is there any other way I can get this tool?

  50. Kianoosh says:

    Thanks for this! It saved a lot of my hairs! :-)

    Worked like a charm!

  51. Alan Bellanger says:

    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.

  52. 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

  53. Gautham says:

    Thanks a lot for a wonderful tool. Saved a ton of time for me!!

  54. Pingback: Too many different cell formats error message.....

  55. smalley says:

    You saved the day!! Thank you so much. It worked great.

  56. Beth Hoffmann says:

    Our company only uses Windoes 7 – will this tool work? Thanks.

    • XLGeek says:

      .Net 4.0 is a relatively small download if you don’t have it and it works on Windows 7. You can use Silverlight version if you want minimal install and automatic upgrades.

  57. Ohi says:

    Wow!!!!!!!!!!!!!!!!!!!!!!!!!!! It worked lke a dream. Thank you so much!

  58. Ruffing says:

    I’m not a geek but wish I were, and the tool worked great! The how-to video and manual were very helpful. Thanks!

  59. superschan says:

    Thanks so much!!! Your tool works great, better than anything the million dollar IT department at work can suggest

  60. Kim Blackett says:

    HI XLGeek

    We’re having some problems with the corrupted file issue here at work. Looks like everyone is raving about your application and Windows Support is even linking to it so I was hoping to get my hands on a copy. However, our company does not permit access to Live or Silverlight. Would it be possible for you to email me a copy to the email address below (would be great if you could keep that from going public)?

    Any help is greatly appreciated!

    Thanks and regards
    Kim

    • XLGeek says:

      Your e-mail is never exposed. I sent the files. Hope they work in your highly restricted environment.

      • Kim Blackett says:

        Hi. I didn’t actually receive the files – I didn’t realise we’re that highly restriced. I’ve given my home email address. Can you please try sending through to there? Thanks again!

      • XLGeek says:

        Just forwarded original message to the new address. Let me know if it works. What OS are you running?

    • Kim Blackett says:

      Program received and looks to be working well on the files I’ve tested. Thanks so much. This is going to get rid of a whole lot of headaches! I’ll be sure to recommend to anyone else with the same problem.

  61. Millefolia says:

    Thanks from me, too! Some of my clients have files affected by this bug, and this is a g*dsend. Thanks again!

  62. Karmicdragon says:

    Just used your online tool. You saved my non-administrative-access, quarterly-statement-deadline-having tuchus.

    Thank you.

  63. Asif says:

    Thank you for this tool it has been very helpful. Is there a way in code that that can be done in Visual Studios 2010?

    • XLGeek says:

      I don’t quite understand your question. The main algorithm is patent protected. You have tools that support any client including MAC OS via Silverlight plus bulk file processing. Commercial use of the cleansing algorithm is not permitted.

  64. Chris says:

    Amazing! Thank you so much.

  65. TJ says:

    Thank you so much. This helped me – you are the best for providing this!

  66. Debbie Baird says:

    Hi XLGeek,

    I was having formatting issues with excel. I ran your software and found I had around 52,000 styes. After converting the file to normal everything worked perfectly for a while. However, when I now copy and paste data into my spread sheet the style count returns to 52,000 and my spreadsheet now longer works. Every time I run my file through the XLCleaner tool the styles reverts back to 0 until I copy over a single cell then. Any idea how to stop the styles from changing?

    • XLGeek says:

      Hi Debbie,

      Your source file from which you are copying from has bad styles in it. Also, you are most likely copying between two separate Excel instances. Even a single cell in that case will bring in all bad styles. Clean up the source file and start copying inside the same Excel instance.

  67. S.K. says:

    Hallo und 1.000 Dank !
    Ich habe ein umfangreiches Excel-Tool (noch in der xl2003-Version) beruflich im Einsatz mit denen
    ich umfangreiche Auswertungen erzeuge.
    Ca. alle 3 Monate werde ich ausgebremst durch die Limitierung bezgl. der Anzahl der Zellformate.
    Bisher habe ich mittels VBA immer allen Programmcode und alle unformatierten Daten in den Blättern in eine neue Datei “gerettet”. Alles leider immer noch mit hohem manuellen Formatierungsaufwand verbunden.
    Heute habe ich meine .xls-Datei mit xl2010 geöffnet, als .xlsm gespeichert, mit Ihrem Tool in Sekunden bereinigt und wieder als .xls gespeichert.

    Ergebnis: Benutzte Zellformate drastisch reduziert, Funktion wieder hergestellt.

    Nochmals 1.000 Dank – für mich ist das Tool die wichtigste Ergänzung seit Jahren.
    Viel Grüße aus Wolfsburg
    S.K.

    • XLGeek says:

      Thank you, including English translation:

      Hello, and 1000 thanks!
      I have professionally in the usage with which an extensive Excel tool (still in xl2003-version)
      I create extensive evaluations.
      Approx. every 3 months I’m thwarted by the limitation for the number of cell formats.
      So far I using VBA always all program code and all raw data into a new file in the “saved”. Everything unfortunately still with high manual formatting overhead associated.
      Today I opened my .xls file with xl2010, .xlsm stored, adjusted in seconds using your tool and saved as .xls.

      Result: Used cell formats drastically reduced function is restored.

      Again 1000 thanks – for me it is the most important supplement for years.
      Much greetings from Wolfsburg
      S.K.

  68. Rob says:

    Worked to remove corrupted excel files. If you have the C:\WINNT35.. corrupted excel styles this fantastic free tool will do the trick. THANK YOU SO MUCH for making this – I was at wits end with a new model I have spent weeks on that I didn;t want to stay corrupted or redo everything just to get rid of 2 stupid styles. THANK YOU THANK YOU THANK YOU

  69. Daniel says:

    Dear Sergey, thank you SO much for creating this elegant application. Saved me 2 days of brain killing work. Take care, D.

  70. Dan says:

    Where do I find the orignal XLStylesTool for .NET3.5? I clicked on the link above for original tool and it took me to the .NET4.0 page

  71. Liza says:

    Thank you so much for sharing this Utility. It fixed what would have been a very large headache for me, and my team. We have battled this error for months, and recreating our data is time consuming and doesn’t always fix the issue. This utility fixed the problem, and kept us moving forward instead of retracing our steps on our project. Thank you!!!!!

  72. SimpleSimon says:

    Thank you, thank you, thank you, thank you a centillion times over.

  73. Amy says:

    Hi, I clicked on the link above and it does not work. It is possible that my company does not permit access to Live or Silverlight. Would it be possible for you to email me a copy. I have .Net4.0 Client. Window 7. Thanks

    • XLGeek says:

      Sure. Will use your e-mail from this comment.

      • Amy says:

        Thank you beaucoup…..for super fast reply. Recevied file and it works beautifully. VOW! Is it ok for me to foward the file to my co-workers?

      • XLGeek says:

        Please try to distribute from my site if you can. I would like to know what the true demand is for this app and issue. The only things you can’t do is reverse engineer my code or use my tools commercially. The core algorithm is already protected by the US patent and EU patent is pending. Glad I could help.

  74. Hana says:

    Thank you for the utility..it saved me a lot of time!

  75. Jeff McAhren says:

    I’m troubleshooting troublesome workbooks that are used with an Excel add-in that is part of an Oracle software application. I’d like to look at the hidden styles to know if they are the cause of the problems is the add-in. Things like the count of styles, the style names, values, creators, etc.

    Can you give me a hint on how to do this?

    • XLGeek says:

      What skillset do you have: are you comfortable with Excel OM, VBA, OpenXML? What types of files are you dealing with: OpenXML or binary? Is it ok to contact you via e-mail?

      • Jeff McAhren says:

        I’m very comfortable with the XL OM and VBA, lots of coding experience in the distant past, but almost none with OpenXML. The files are are binary, xlsb. I’m looking for where the Add-In stores app specific metadata, like connection properties, point of view members, etc., etc. I feel like if I could review that information, I can find out why some workbooks are “breaking”.

        Yes, my email address is mcahren at gmail.

        Thanks!

  76. deepika says:

    Thanks ! the tool really helped :)

  77. Guy Ferguson says:

    Thanks very much for this tool. I just cleaned up a spreadhseet with 51454 Styles in it, of which 17 were being used. Took around 1.5 seconds to run. cheers form Australia

    • XLGeek says:

      Really appreciate your comment. Always glad to help Australians. All my Australian friends are really good people. Your country’s semi-pro surfer Billy Watson made a huge difference in my life last year. He taught me how to breath properly after a wipeout in bigger waves in Fiji. Super cool guy and incredible surfer. He just helped me out of the goodness of his heart. Probably saved my life in the long run. What goes around comes around. Cheers.

  78. paul says:

    Hi XLG
    Thanks for an incredible tool – had a file with over 60k styles – 8 in use – a couple of seconds later a new clean file was saved – brilliant!

  79. californian says:

    Microsoft said I should use you. ( http://support.microsoft.com/kb/213904/en-US ). Considering the years of work that went into the 79 worksheet dating pre-Excel 2003, I was extremely nervous. The program has features, lets you choose, and is awesome. 63500 styles, 58 in use. 2K names, 1K unused. Bam! Cleaned, done, file size cut in half, errors gone, 10+ years of bloat gone in under 10 seconds. All data is intact, formatting is exact. Thank you so much.

    • XLGeek says:

      Awesome. You should force all styles to Normal. It is particularly important for files that have been upgraded through multiple prior versions. There will be no loss in formatting, just a cleaner file.

  80. Steven Haas says:

    Used your SilverLight version to clean up our loan screening template which is the core of our loan origination operation. We originate about $3 billion in loans a year. Your utility completely solved our cell formatting error problem in seconds. Outstanding. Why aren’t you charging for this software. It is brilliant. Thank you.

  81. Michael says:

    Great utility! This fixed my error lickity split! I never knew I had so many different formats in my workbook. I didn’t lose any functionality after applying fix, and I have tons of vlookups and pivots all over. Thanks very much for developing this, and keeping it free!

  82. susan Kim says:

    I tried but it does not work.
    Next to the box, “Process File,” this “Excel File Cleanup Tool” shows “File contains computed data.”

    • XLGeek says:

      The error message most likely says: “File contains corrupted data”, not “computed”. Please make sure that the file you are trying to process has .xlsx or .xlsm extension – Open XML format. You are most likely trying to process .xls or .xlsb file – a binary format. You can explicitly save your file as .xlsx or .xlsm and try again. If you try to use a file with wrong extension in the Silverlight app it will tell you right there and then that it is binary. The “How to” post and the enclosed with the utility readme file explain what file formats are supported.

      • Susan Kim says:

          It is .xlsm file. Please let me know how to resolve this issue.  

      • XLGeek says:

        Please send me the file. It is either not true OpenXML file or is fully corrupted. I’ll check it out. I just sent you an e-mail.

      • XLGeek says:

        Please make sure your file is not password protected. File open password encrypts file parts making them look like gibberish to the tool and may very well be the cause of the error message that you see. The “how to” posts and enclosed with the tool ReadMe file explicitly call this condition out. Hope this helps to resolve your issue.

    • susan Kim says:

      Per your advice, I removed my password before uploading my file to the tool. And then my issue is resolved. Thank you very much!

  83. oscar riveros says:

    excelente!!!! muchisimas gracias por la herramienta tienes 5 estrellas de calificacion, pude arreglar mi archivo.

    muchas gracias!.

  84. XL_Learner says:

    This tool is awesome, cleaned up a messed workbook for me and saved my time.

    Thanks a lot.

  85. Brett Cooper says:

    This worked wonderfully! Thanks so much!

  86. Ray says:

    I thought all was lost with my too many cell formats death message until I found and downloaded this tool when nothing else worked and it cleaned the file in 5 seconds flat. The best freeware ever!

  87. Ken says:

    Why is it saying that the service is unavailable when I attempt to download the file (XLCleanerDotNET4.0.zip)?

    • XLGeek says:

      Looks like they are killing original SkyDrive links since MS is renaming it to OneDrive. Can you please try this and reply if it works for you:
      https://skydrive.live.com/redir?resid=53E1D37F76F69444!900&authkey=!AH5oeGVaWlMsFHA&ithint=file%2c.zip? I will update if it does. Really appreciate the fact that you sent me a message.

    • XLGeek says:

      Thank you for replying. There is service interruption due to the conversion of the SkyDrive to OneDrive by Microsoft. I checked and updated all links. I update my apps all the time to keep the algorithm current. My Silverlight version of the app is your safest alternative if something is down while Microsoft is finishing their conversion. If you are not downloading the tools from my site there is no guarantee that you are not getting a virus in your download or getting the latest version. The alternate site that you provided in your reply is located in Moscow, Russia and looks very suspicious. I can’t approve your comment for obvious reasons. Downloading executables from weird looking servers located in Russia, China, Korea, etc… is extremely unsafe. I would be running Offline Windows Defender full scan option if I were you.

  88. OMG this tool is amazing, I am an bit of IT challenged person and I was struggling with files provided from an unknown source to me and was having a lot of issues trying to work with them, my job is going to become SO easy now. THANK YOU FOR THIS TOOL!!!

  89. Paul says:

    Got linked to your tool from Microsoft’s own website. Apparently they feel that using your tool is much simpler than actually programming a fix for it themselves… In any case, it worked great on several sheets that had massive style formatting issues (yay for users formatting styles to infinity…), so just wanted to say THANK YOU SO MUCH for creating this!! :)

  90. Nick says:

    Fantastic tool ! Thanks

  91. Aaron says:

    Worked great for the accounting employee that was having trouble with an Excel file used month over month. Thanks much!

  92. Tom says:

    You’re awesome! Thanks so much for this.

    Reduced file size to a third and removed those infuriating “too many formats” messages. It didn’t even change the appearance of any of the worksheets.

  93. Nick Andersen says:

    Great tool! Curious if you have had any luck scripting with it.

    We have users who are experiencing the “excel cannot paste the data” issue (along with other excel related), the tool fixes the issue right away and they go on working. Only problem is…we cant anticipate what document they will need to run the tool on, and of course having to run it themselves is too much work.

    We would like to try some sort of scripting with this tool. Possibly batch file or VBS that we can run on their Excel Data drive and cleanup all excel documents in one swoop. Have you had any luck or experience with this?

  94. Pingback: Clear cell styles but retain formatting

  95. Leslie R Jones says:

    Thank you ever so much for this gift of an amazingly simple and effective tool. You’re a generous genius! I sent the corrupted file home to fix it because we cannot install unauthorized software on our workstations. I’ll turn to you in the future as well.

  96. Elisa says:

    Just downloaded the apps. I have been suffering from this demon for months and I now have the solution. You’re doing great keep it up !!!!

  97. Tanesja Youn says:

    I installed the Silverlight tool, but I don’t know how to access it so I can do the file clean up. Am I missing something?

  98. Giorgio says:

    Thank you so much for this utility program. I was getting mad trying to decipher the ultra techno solutions proposed by Microsoft guys and the lot. With your program just two clicks and problems solved.

  99. Erin says:

    Hi,
    Just wanted to say a big thank you for posting this tool. It just saved me weeks of work! Very much appreciated from a tech world newbie.
    Erin

  100. Amit says:

    Hello, I am trying to use the utility… but when I select the file it says ‘The process cannot access the file ‘Filepath”. WhenI try to access other files it works alright. Looks like something wrong with my file… but I dont know what to do. Can you please help. Thanks

    • Amit says:

      All sorted. The file I was trying to process was already open… duh!!… it works beautifully… thanks a ton!!!

  101. Dave Hills says:

    I can’t seem to get the xlstylestool to work for me. I’m using a 2009 MacBook Pro running OS-X 10.7.5. I’ve downloaded the XLCleaner and the XLCleanerDotNET4 from the OneDrive website. When I check if everything is installed properly, I get the message that I’m “…ready to use Microsoft Silverlight” and the version installed is Silverlight 5 (5.1.30317.0).

    However, when I launch the tool from http://xlgeekwebapps.cloudapp.net/default.aspx, click on the “Get Excel File” button, and browse and select the file, the tool starts “scanning file” but never reaches the end of this process. Even after several minutes, it is still “scanning file”. I tried again using a very small file with the same result.

    What am I doing wrong?

    Dave Hills

    • XLGeek says:

      Hi Dave, there have been 3 reasons for the behavior that you are describing: 1) file was already open in another program or was marked as read-only, 2) file was password protected, and 3) file size was extremely large – Silverlight has lower limit on the file sizes than normal 32 bit processes. Hope this info helps. If the root cause for the SL app spinning during the scan is different I would like to get a sample to dig in deeper.

      • Dave Hills says:

        Thanks for the prompt reply. I don’t think it’s any of the three causes you mentioned. The files are not open or password protected. I suspected it might be something to do with file size so I tested this. The file which suffers from the “too many formats” error is 21MB so I tried again with a file of only 55kb and had the same problem. How do I get a sample to you so you can investigate further?

      • XLGeek says:

        I will send you an e-mail and we will take it from there.

  102. Zabby says:

    thank you! worked liked a charm and very easily and quickly. much appreciated.

  103. Ondrej says:

    Thanks for the brilliant tool, saved me a LOT of work

  104. Penelope says:

    Thank you very much for providing this utility. We work with ridiculously large excel files that are added to from a number of different sources and they get out of hand very quickly. Occasionally they have to be converted into an older Excel format. & that can be problematic. After going in & trying to reduce the number of cell formats by hand (unsuccessfully) I found this and it fixed the problem amazingly quickly and easily. Thanks again!!!

  105. ITFachmann says:

    Very fast and very good, deleted 40,000 styles without any error in a second, did remain only 20 used styles. Thank you!!

    • XLGeek says:

      Thank you for the comment. You should consider forcing all styles to normal. 20 remaining custom styles is actually a lot. You will not lose any formatting if you choose to force all styles to normal. You will get the cleanest copy of the workbook. Custom styles are not necessary for normal usage. You may need them in some edge cases when they are leveraged in code, but that it very rare. Most likely you don’t need them.

  106. nreyes says:

    I installed the tool today, works like magic so far! Thank you very much.

  107. Cyndi says:

    THANK YOU SO MUCH for sharing this fabulous tool!!!!

  108. Shane says:

    good stuff, worked great for me.

  109. Bryce Andersen says:

    I have used your XLStyles tool for years and am deeply in your debt. I however am currently finding myself SOL. At work, I run Windows 7 and Office 2007, and I have a file which just recently started giving me fits. It is a .xlsb file and it isn’t letting me save the file into .xlsx or .xlsm to use the checker. It is giving me the “one or more formulas is longer than 8192 character bit” and I cannot for the life of me save the file as either of those formats. I have tried different versions, different computers, different users, nothing. I am at my wits end, any ideas on what the heck I can do?

    • XLGeek says:

      Hi Bryce, this can happen for many reasons. Sometimes paths of links to other workbooks explode in length, sometimes named ranges mis-behave, sometimes chart data series grow out of control. You can try looking into these three areas and if the file doesn’t get fixed, you can send it over to me if you want.

  110. Jess says:

    Hi there,

    Trying to download but when I click on the link it just says that IE cannot display the page. Is this just me or is there an issue? Desperate to get this as I can barely do any work at the moment.

    • XLGeek says:

      Unless your ISP explicitly blocks your access to Microsoft OneDrive or App Store there should be no issues: the services are up. If you still have issues let me know what OS you are on and I will e-mail you appropriate app. You can also try browser based Silverlight 5 version. Silverlight download is very small if you don’t have it. SL5 app link is in the original and SL5 app specific post.

  111. Sun says:

    Dear XL Geek
    Can you please help me recover 2 important xls that have been corrupted after a scan & epair of pendisk? they are password protected. Will forward to you with the files by email if you’d kindly help.
    Thank you in advance and with kindest regards, Sun

    • XLGeek says:

      I won’t be able to help with binary or password protected files. Their internals are not accessible to my apps.

      • Sun says:

        Even if I know the password to the files and I can forward to you to try? I have tried all methods online but I am not tech-equipped enough to understand half of what was suggested. Please help. thank you & regards, Sun

      • sergeig888 says:

        Can the files be opened? If not, it is file corruption. Even a single missing or extra byte in the encrypted file can make it completely unrecoverable. This has nothing to do with Excel, but rather the OS or external app like in your case corrupting the file. If you don’t have a backup you may be out of luck here. You may try disc recovery tools. Windows doesn’t really delete a file, so if the disk hasn’t been subjected to too many new writes you may get an older working version of the file.

  112. LeslieJones says:

    Love this program for accuracy efficiency and ease of use. I had an experience where a bad style was propagated to MANY Excel files!

  113. Mario says:

    Excellent tool! thanks :)

  114. Chris Boxall says:

    I would like to use XLStylesTool utility. Is it possible to download it without using an MS account.
    More specifically I do have a MS account but I do not want to associate my PC logon to my MS account. I have no need of the cloud and no other devices to link. I’m concerned that using a MS account to login (rather than a local account) will have effects on my PC that i do not want/need. If linking my local account to my MS account is essential to downloading the software then please point me to an article on the effects – and ability to disconnect – so i understand what i’m committing to.

    • XLGeek says:

      If you are on Windows 7 or below you won’t have to login. If you are on Windows 8+ you have no choice but use your MS account. What issue exactly do you have with accessing what version of the app?

  115. mmadurski says:

    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!

  116. XLGeek says:

    Glad it worked. I tried e-mailing you.

  117. Ashley says:

    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.

  118. XLGeek says:

    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.

Leave a Comment or Ask a Question

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