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

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

bsullinsExcel Tip – Defaulting to Non-Compatibility Mode
  • http://BenSullins.com bsullins

    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.

  • http://BenSullins.com bsullins

    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…

  • http://BenSullins.com bsullins

    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…

  • Gehan

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

  • Steve

    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.

  • Chris Fleming

    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

  • Marisa

    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!

  • Don Martin

    Thank you! Spot-on!

  • Ron Wallace

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

  • DJ

    Thanks!

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

  • Brian Mitchell

    Yes!!!!!!

  • Jim

    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”

    • http://BenSullins.com bsullins

      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!

    • Marc

      You’re a genius. Thanks

    • Krishna

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

  • Chris

    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?

    • http://BenSullins.com Ben Sullins

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

  • shankar menon

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

  • oliver

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

  • The Atomic Punk

    One of our gurus added this to the registry:

    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\

    REG_DWORD “ExtensionHardening, Value=0″

    Flawless so far!

  • Mike

    Awesome, thank!!!

  • Torodd

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

  • Tracy Dougherty

    awesome, succinct, sneaky and to the point

  • Ashu

    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”

  • Greg

    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.

  • Linda

    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.

  • Linda

    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.

    • http://BenSullins.com Ben Sullins

      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

  • Paul

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

  • Joze

    awsome!!

  • http://www.vbacreations.com Bill Benson

    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.

  • Kbubbles

    This was AWESOME…Worked perfectly…Thank you.

  • CvS

    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

  • http://www.vbacreations.com Bill Benson

    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.

    • http://www.vbacreations.com Bill Benson

      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”

      • http://BenSullins.com Ben Sullins

        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”

  • Ger

    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.

  • Marcel Heijboer

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

  • Brandt Peterson

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

  • Steve

    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!

    • http://BenSullins.com Ben Sullins

      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 :)

  • Ben

    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 Settings\user\Application Data\Microsoft\Excel\XLSTART

    Thanks
    Ben

  • jim

    Ben,

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

  • Jessica

    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.

  • http://bensullins.com/excel-tip-defaulting-to-non-compatibility-mode/ Katherine

    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.

  • Jo

    Thank you so much! Your tip works great!

  • http://NewTip Justin

    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.

    • http://BenSullins.com Ben Sullins

      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.

  • Josh

    Excellent – worked perfectly for me.

  • sekk

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

  • Aaron

    Awesome! Thanks!!!

  • Ashish Sharma

    Great… It really worked. Thank you Sir :)

  • Nick

    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

    • Dan Timper

      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

  • Duncan

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

  • Melodie Adams

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

  • Fforde

    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?

  • Jenn Rush

    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?

  • http://www.facebook.com/sunk818 sunk818

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