Excel Tip – Defaulting to Non-Compatibility Mode

72 Comments

1. As I am using this method described above I found that new workbooks still open in the old format. I’m guessing this is a registry setting that our IT organization has in place so I will begin my research into how to undo this and post my results shortly.

2. Update – I’ve discovered that this is somehow related to my user profile. I’m not sure if it’s a group policy or local setting but when I do a ‘Run As’ for Excel and use a different user account that has never logged onto my laptop before, I get the default view in Non-Compatibility mode…

3. I found out today that this was a result of my companies group policy settings. After talking to the IT guys I was able to have them change this for me since there was no reason.

   This of course comes after completely blowing away my profile and recreating it on my laptop…

4. Thank you very much for this useful tip which worked for me. It was getting to be quite annoying.

5. We have the same problem at my company. Thanks for your detective work.. Could you supply the group policy setting your IT guys had set ?
   Thanks a lot.

6. Thank you!

   This helped a frustrated man who was working way past midnight. (4am)

   You saved my night! =) I was able to copy+paste formulas after these tips.

   Chris

7. Thank you so much for this tip. I had searched & searched, but could only find the tips that showed how to change the default save as. This absolutely worked for me, and I am super happy! Thank you again!

8. Thank you! Spot-on!

9. This worked perfectly! It is much easier/cleaner than any other tip I’ve seen.

10. Thanks!

    This was a very precise and clean tip. Worked very well too.

11. Yes!!!!!!

12. I found all you need to do is change the default save-as format as .xlsx

    Excel options -> save -> top box — change to “Excel Workbook”

13. Hi Jim,

    Yep, that will work in most cases. In my case my company had a group policy that overwrote that change every night. This solution was a workaround their group policy. Great tip though!

14. The problem I have with this solution is that now whenever I open a new instance of excel ‘Book1’ opens. In other words, if I don’t have excel open and I open any excel file I also open an arbitrary file named Book1.xlsx….Any ideas on how to stop that from happening?

15. thanks a lot! it saved me a lot of time. this had been bothering me for some time now.

16. awesome. This is the first one I got to work without changing a bunch of things.

17. One of our gurus added this to the registry:

    HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0ExcelSecurity

    REG_DWORD “ExtensionHardening, Value=0”

    Flawless so far!

18. Awesome, thank!!!

19. Thank you so much!! This was extremly useful and the first really helpful tip i have found for this problem. Saved my night 🙂

20. awesome, succinct, sneaky and to the point

21. Just do the following:

    Go to Excel Options -> Save -> Save Workbooks ->Save file in this format -> Select “Excel Workbook”. The default is Excel 97-2003 Workbook”

22. If you open an old XLS file you will see this notation in the title bar. Save it to the new XLSX format, close Excel, restart Excel and open the XLSX file and the message will be gone assuming there are no other issues.

23. I just recently got a new computer and now have excel 2010, the only one in the office. We have a file that is used throughout the office and I am unable to save changes. The file is saved as Excel 97-2003. It always opens in the compatibility mode so I am also loosing half my functions. Please help.

24. Below is the error I recieve when I try to save my changes. The file also needs to be accessed by other people in the office that have an older Excel version. I am using Excel 2010 and it keeps opening in Compatibility mode.

    Erros were detected while saving (filename). Microsoft excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click Continue. To cancel saving the file, click Cancel.

25. Worked for me. Thank you so much, that was a very annoying thing to me.

26. awsome!!

27. Hi Linda,

    It sounds like you may want to leave Excel in compatibility mode when working on your workbooks. Unless you’re using some of the more advanced features you might be able to get away w/o the 2010 features so that your colleagues can collaborate with you using their versions of Excel. Feel free to reply to this thread if you have any additional issues on this topic.

    Thanks,
    Ben

28. I like this tip. I want to say that whether one saves a retro excel workbook as .xlsx or .xlsm depends on whether there are macros. I would like a way to open a .xls file in non-compatibility mode automatically, but I can see many reasons why Microsoft would be in a no win situation were they to do this.

    Oh well, more fun for the programmer in me.

29. This was AWESOME…Worked perfectly…Thank you.

30. I use Excel 2010 and I could just change the settings. Open Excel 2010 and click on

    File –> Options
    In the options window choose “Save” on the left menu
    Change “Save files in this format” from “Excel 97-2003 Workbook (*.xls)” to
    “Excel Workbook (*.xlsx)” and then click on OK.

    You should now be able to restart Excel in a normal (non-compatibility) mode!

    /Christoffer

31. I tried what you suggested but it made no difference. Any .xls file opened by Excel 2010 will automatically be opened in Compatibility mode. There is no way to force excel to open a xls workbook in NONCOMPATIBILITY (ie, simply one of the 2010 formats)

    THAT’s the functionality I would like to be able to see, preferably the same way other applications allow you to choose a mode in which to open a file while browsing for it, just prior to clicking Open.

32. In case not clear, there are such items as
    Open
    Open Read Only
    Open as Copy
    Open in Protected View
    as options in the drop-down near Open

    That would be the perfect place to add “Open in default application Save format”

33. While I am getting the same message about compatibility, when I click on Office, open, print + close are the only options not greyed out. So I cannot click on convert or save as- what can I do to operate the system.

34. You’re a genius. Thanks

35. Great!!! thanks Jim…. Unfortunately the option ben suggested not wrking well. Coz.. if u try to create new excel when the book1.xlsx is already opened, it opens as read only…thts annoying..

36. Thanks very much. It also works in Office 2010 (Office14)

37. Does anyone know how to convert 100 “compatibility Mode” files into individual, unique files?

38. This tip is WRONG!!!!!!!!!!!! Of course there is a way to do it properly (as others have said above). This is how you do it properly:-

    1) Open Excel
    2) Click on Office Button ( the coloured one in top left corner)
    3) Then in the next window click on Excel Options (bottom right)
    4) In next window click on Save (4th one down on left hand side)
    5) In next window towards the top where it says “Save files in this format” make sure the box next to it says “Excel Workbook” then click ok at bottom and that’s it. Now when you open Excel 2007 it will not be in compatability mode.

    Hope that helps!

39. Hi guys,

    I know this is an old post but I had the same issue after my company upgraded to Office 2010 from 2003. I found the solution to be changing the file in my personal folder e.g.

    C:Documents and SettingsuserApplication DataMicrosoftExcelXLSTART

    Thanks
    Ben

40. Ben,

    That did it. Saving a xlsm file to my user folder XLSTART file solved the problem. Thanks.

41. You are the guy with the answer!! Thank you. Why they even have that “Save files in this format” setting in Excel Options is unclear to me – since it does NOTHING!! And does not retain whatever you set there. Geez how frustrating! Thank you for this great tip that fixed the problem.

42. There’s one more step needed to save an old XLS as a new XLSX with more rows in the sheets. Create a new blank XLSX (outside compatability mode, getting there however works for you in the above). Then copy/move the old worksheets across to the new XLSX.

43. Thank you so much! Your tip works great!

44. Click the Office Button in an Excel worksheet. Go to Excel Options, click on Save and change the setting “Save files in this format:” to “Excel Workbook”. Click OK to save your changes and exit the workbook. Open a new workbook and VIOLA!…it should default to the Excel 2007 workbook format.

45. Excellent – worked perfectly for me.

46. Thanks for the tip.
    It helped me display morethan 65*** rows.

47. Yeah you have to be careful to do ‘save-as’ so you don’t overwrite your default workbook.

48. Hi Bill,

    Try the first basic way to do it, and if that doesn’t work try going through the steps I laid out in the blog post.

    Go to Excel Options -> Save -> Save Workbooks ->Save file in this format -> Select “Excel Workbook”. The default is Excel 97-2003 Workbook”

49. Hi Steve,

    This solution is for when the basic method doesn’t work due to a group policy in your domain which forces it to be in ‘compatibility mode’. I didn’t set out to write a blog that repeats the microsoft help menu 🙂

50. Thanks Justin,

    As mentioned before, the solution I posted here is to get around your companies group policy that prevents users from following the default way of changing this.

51. Awesome! Thanks!!!

52. Great… It really worked. Thank you Sir 🙂

53. I use a computer terminal and have no access to C drive or to the registery and when I change the Save Setting to .xlsx in Excel options it defaults back to .xls when I reopen Excel. Below is another work around for people using a restricted work computer.

    So my work around is to the following VBA code in the Personal.xls (or in my case Personal.xlsb)

    Private Sub Workbook_Open()

    Application.DefaultSaveFormat = xlOpenXMLWorkbook
    Workbooks(“Book1”).Close
    Workbooks.Add

    End Sub

54. A slight tweak to this method: save it as a template (Book.xltx) rather than a workbook (Book1.xlsx). This means it will load in Excel as “Book1” rather than “Book1.xlsx”.

55. Alternatively, you could use the following:

    Private Sub Workbook_Open()

    Application.DefaultSaveFormat = xlWorkbookDefault

    End Sub

    In this scenario, it is not necessary to have the “book1.xlsx” saved to the XLStart folder. I found that to be annoying, because whenever I opened a different workbook, book1.xlsx automatically opened as well. Your version may work better for you, since you do not have access for the C: drive. The above worked perfectly for me to open in xlsx.

    Dan

56. Thank you so much. This has made using Excel that much easier without having to constantly change it out of compatibility mode.

57. I am converting excels files from 2003 to 2013, however when I finish converting the files to 2013 version and changing all the links to 2013 version files and save the files verifying that no compatibility issues. When I open the same file again it come up stating that where are compatibility issue even when all files linked are on version 2013.

    I have noticed when I open all the linked files is comes up that there are no compatibility issues. Is there a way that the compatibility setting can be set so that I don’t have to open all the files to eliminate the compatibility situation when the file is being opened without going through the process of opening all the linked files any time the file is opened?

58. For some reason just one of my excel files were having compatibility issues. In this one file it had (compatibility mode) at the top when you opened it. All of my sheets were gone. Only the most current was available. This was not happening to any other of my docs that I have. I followed the above steps and it a) didn’t help with the one excel file that was having problems and b) now ALL of my files are like that. How can I reverse or delete these changes?

59. You can use a free tool from Microsoft called OFC (Office File Converter) to convert all your Word, Excel, PPT into the newer format (.docx, .xlsx, or .pptx).

60. Thanks 🙂

    Do you have a way to do the oposite? I need to force it out of “protected view” mode. I have unchecked all the “trust center” boxes and still it opens all excel files (old and new) in protected view where I can’t do anything.

61. I am also have the same issue. I just got a new computer with window 8 and downloaded office 2013. I put my files on a hard drive to bring them to my new computer. When I tried to open them last night after I installed 2013 I got the same message. Permission view. Very frustrating when I need to work on file. Any help would be appreicated. I see so many feeds but nothing on permission issues.

62. I found the solution on another website 🙂

    You need to create a folder: Put everything related to everything you are going to work on or with, into that folder.
    Now open Excel -> go to “file” -> go to “options” -> go to “trust center” -> go to “trust center settings” -> go to “TRUSTED LOCATIONS” and click Add new location. Where it says Path: write the entire path to the new folder.
    Check the box beside “Subfolders of this location are also trusted” and click OK.
    Now close excel.
    Go to the folder you made, where all your work is. Open the excel file you want to work with and it should be good 😀

    Let me know if anything here is unclear.

63. THanks for the info back. I was able to follow it. I opened a file from my documents and got ‘excel cannot access “FIle”. The document may be read only or encrypted.

64. I figured out that it was my documents that I was trying to get from my external hard drive. Once I used them from Carbonite, I could open the right up. I will have to see what the issues is there, but seems to be working good now. Thanks for your help.

65. Oh, I don’t know about that but glad you got it working 🙂

66. Try opening it from the new folder you made not ‘my documents’.

    Otherwise you should be able to right click on the file -> go to properties and at the bottom of the window, there should be a checkbox for “read only” which you can put on or off.

    Sorry for the late reply, I don’t check my gmail often.

67. Hello, this worked great! only thing is that i found that i also have an office12 and office14. Only in this last folder is where the XLSTART exists… i used this one and it worked!

68. I’ve done this, but every time I restart Excel it defaults back to xls. Anyone know how to keep it as xlsx permanently?

69. Works like a charm. Thanks a million times!!!

70. Thank you! This is maddening, especially since when I open a new excel document, it is usually to run a database query that frequently returns well over 65,000 lines and crashes and burns in a 97 document. Your solution worked like a charm.

71. You absolute Beauty!

72. I had to automate a program that created an excel spreadsheet and when saving would pop this up, this worked great and really will save some time.

Comments are closed.