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.
- Open Excel (you should get Book1 [Compatibility Mode] by default)
- Save-As (F12) and save it as Book1.xlsx (the 2007 format) in the following location:
C:\Program Files\Microsoft Office\Office12\XLSTART
- 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?
Or dive in deeper and learn Data Analysis Fundamentals on Pluralsight.com