fbpx

Excel Tip – Defaulting to Non-Compatibility Mode

I was recently fighting with Excel because I was sick of it wanting to run in Compatibility Mode by default. Everything I found out there said that you simply need to change the option for Save-As to be the 2007 format. This only seemed to work on the workbooks I had open. If I closed and re-opened Excel it would be back in Compatibility Mode.

Remembering from my old days of hacking Excel 97 workbooks and creating templates with Macro's I recalled a way to change the default workbook that Excel would use when starting up. This is where the ‘ah-ha' happened.

Below are the steps you may use to force Excel out of Compatibility Mode when it starts up if changing the aforementioned Save-As method does not work for you.

  1. Open Excel (you should get Book1 [Compatibility Mode] by default)
  2. Save-As (F12) and save it as Book1.xlsx (the 2007 format) in the following location:

C:\Program Files\Microsoft Office\Office12\XLSTART

  1. Restart Excel

That's it! Excel by default will look in this location (based on where you installed Excel) for the default workbook it uses when starting up. Since you have saved it in the 2007 format Excel will no longer run in Compatibility Mode by default. It will only do this when opening .xls files that were saved in Compatibility Mode. For those, just save as .xlsx and you should be good.

Want to learn more about Excel?

Take my course on Excel Dashboarding

Or dive in deeper and learn Data Analysis Fundamentals on Pluralsight.com

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. This worked perfectly! It is much easier/cleaner than any other tip I’ve seen.

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

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

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

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

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

    HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0ExcelSecurity

    REG_DWORD “ExtensionHardening, Value=0”

    Flawless so far!

  14. Thank you so much!! This was extremly useful and the first really helpful tip i have found for this problem. Saved my night ๐Ÿ™‚

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. Ben,

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

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

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

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

  33. 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โ€

  34. 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 ๐Ÿ™‚

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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