I know that some, if not all, of you have experienced entering data into Excel spreadsheets only to find that the information that you entered “disappears” or you notice multiple “conflict” files created for the spreadsheet. While there are a number of possible causes, after researching the problem more I believe that the cause has been found (at least I’m 90% confident).

What causes Excel files to get overwritten?

Here’s a brief explanation of the operation and cause to help you understand. When Excel (or for that matter Word or PowerPoint) opens a file for editing or viewing it creates a special temporary file that acts as a “lock” file or “in use” file. Applications can look to see if this lock file exists, and if it does, then can respond with something like “file is in use by somebody else” or open the file in read-only mode. Maybe you’ve noticed some files on your system that look like ~$datafile.xlsx? That’s the lockfile. Excel creates that file in the same folder that the spreadsheet files is located and it uses the name of the datafile to create the lock file. So if you have a spreadsheet named “comps.xlsx” then the lock file will be named “~$comps.xlsx” and the file will be located in the same folder as the spreadsheet file.

Before we can get to the problem there is one more concept that you need to understand. Excel has two different major file formats. The “xls” extension is used to identify the binary file format used in Excel 2003 and prior. Excel 2007 introduced the open file format used in all Excel versions since 2007 and is identified by the “.xslx” file extension. This is important because Excel makes some decisions when opening a spreadsheet.

When you start Excel 2007 or later and open an xls file, Excel determines that the file is an older format Excel file and opens it in a special mode called compatibility mode. Have you noticed that at the top of your Excel file? That message is telling you that your version of Excel is working with a file format that is an old format. Everything seems to work OK, but there is something going on behind the scenes that is causing a problem; the special lock file isn’t created.

Because that lock file is not created there is no way for applications to tell that the file is in use. Therefore, someone else will be able to open the file and edit it. The last person saving the file will be the “winner” and their information will be saved. Depending on what type of computing environment you are running in the other person’s changes may simply be overwritten, or have conflict files created, or they may be forced to save the file in a new name. What’s important to note is one of the people in the file will lose their changes.

And to make matters worse, when Excel opens an old file in compatibility mode and you try to exit Excel, Excel will prompt you to save the changes even though you did not actually make any changes to the file – you just opened the file to see some information.

What the heck you say, I didn’t make any changes!

This happens because Excel itself has to make some changes to the file when it opens in compatibility mode, even if you didn’t make any changes. This can be really confusing because you don’t know if you should or shouldn’t save the file. If you don’t save the changes then there is no harm done, but if you do save the changes then it has the potential of overwriting real editing changes done by someone else.

Resolution to your Excel files getting overwritten.

Ultimately, what should you do? First, I recommend that you always convert the file to the latest version. That way the file will be in .xlsx format and will not have to be opened in compatibility mode. If there is the odd situation where you have to share the file with someone who is still on a very old version of Excel (hey, Excel 2003 is over 12 years old!) then they can either install a compatibility pack to be able to read the file, or you can save a copy of the file in Excel .xls format and send that copy to them.

How to convert an Excel file and get rid of compatibility mode.

  1. Excel conversion stepsConverting the Excel .xls file to the .xlxs format is very simple. Just follow these steps.
    1. First, be aware. If you have viewing of file extensions turned on in File Explorer then you will be able to see if it is a .xls file (plus, the icon for the file will be a little different than the icon for a .xlsx file.) If you open an Excel file, take a brief glance in the title bar and look for a notice that it is opened in “compatibility mode.”
    2. If the file is opened in compatibility mode, then click on File > Convert.
    3. You may get a couple of warning or notice dialogs telling you about the conversion change to the file. Just answer “Ok” or “Yes” to these notices. One of the notices will state that the file must be closed and reopened in order to apply the new file format. Please say “Ok”.
    4. After the conversion is finished take a look at the Excel title bar again.  You should see that the file is now a .xlsx file and the [Compatibility Mode] message is gone.  Save the file again to complete the process.

 

 

Any questions?  Please leave a comment.

Filed under: Microsoft ExcelUncategorized

Like this post? Subscribe to my RSS feed and get loads more!