Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Losing Data in a Shared Workbook.

Losing Data in a Shared Workbook

Written by Allen Wyatt (last updated June 11, 2020)
This tip applies to Excel 2007, 2010, 2013, and 2016


Karen described a problem in which a shared workbook, stored on a company network, periodically loses all the data it previously contained. In trying to track down the problem Karen did tests where she and her coworkers tried to open the file at the same time and save it at the same time, but all the tests left the data intact. Still, she reports that there are times when the workbook data is simply erased; wiped clean.

This problem is, perhaps, a prime example of why many people refuse to trust shared workbooks in Excel. Many folks have related experiences where data has become corrupted, information is overwritten, or data just outright disappears. All the problems were traced to the fact that a workbook was shared, and the problems went away when the sharing was turned off.

This leads to the first suggestion: don't share the workbook. If you "unshared" it, then only a single person can open the workbook at a time, which reduces complexity and decreases the chances of corruption. If the data contained in the workbook is extensive and it must be shared among multiple users, consider converting to a program that is designed for better data integrity in a multi-user environment, such as Access.

If this is not possible, consider turning on Track Changes in Excel (an option available when you share the workbook) so that a change history is maintained. This allows you to see who last changed a workbook. If you open the workbook and find the data missing, checking the change history may actually disclose that the data was deleted—probably inadvertently—by a user.

Of course, it is possible that the change history may not provide the information that you hope it will. The reason that sharing a workbook can lead to data corruption is the way in which Excel allows users to work with data. In a program such as Access, data is fetched and worked with on a record-by-record basis. While the record is in use by a user, no other user can make changes to the record, but they can make changes to other records.

In Excel, the entire workbook is transferred to the user's computer, not just a single record from the data in the workbook. In a shared-workbook scenario, this means that multiple copies of the workbook are actually open at the same time, and Excel is charged with resolving potential conflicts in data. Consider the following scenario:

  • User 1 opens the workbook, which means that a copy of the workbook now resides in his system memory.
  • Ten minutes later User 2 opens the shared workbook. This version, now residing in the memory of User 2's system, does not contain any of the changes done during the last 10 minutes by User 1.
  • Five minutes later User 2 saves the shared workbook, but continues working.
  • Two minutes later User 3 opens the shared workbook. This version is the one that User 2 just saved and doesn't include anything done by User 1 or any additional changes made by User 2 since last saving.
  • Thirteen minutes later, all three users save their versions of the shared workbook and exit Excel.

In this thirty-minute scenario, can anyone tell which of the users' changes are saved in the final version of the workbook? Which changes should be saved? When you consider the ramifications of such a scenario (and this scenario is not uncommon), then you can see why many people suggest not using Excel in a shared environment.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12107) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Losing Data in a Shared Workbook.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Inserting a Dynamic Word Count in Your Document

Need to know how many words are in your document? You can use the NumWords field to add that statistic, dynamically, to ...

Discover More

Capturing a Screen

Windows provides a way of "capturing" the image on the screen into the clipboard. You can then paste the image from the ...

Discover More

Opening Sites in a Browser

You can store all sorts of information in a worksheet, including Web addresses. If you want to open those addresses in a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Speeding Up Large Worksheets

If your worksheet gets large enough, you may notice a severe slowdown when it is recalculated. This tip provides some ...

Discover More

Disabling the F1 Key

Tired of hitting the F1 key by mistake and pulling up the Help system? Here are a couple of ways (one drastic and one not ...

Discover More

Controlling Automatic Backups

Excel can make backups whenever you save your workbook. If you want to turn the feature on or off, this tip explains how ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2020-06-11 06:00:54


If you have Office 365, pop your workbook onto SharePoint or OneDrive and send sharing links to your co-workers.

You don't need the Share Workbook button on the Review tab - the workbook can be opened by multiple users and each user can see almost real-time what colleagues are doing. Changes you make are saved automatically to the central copy every few seconds.

As I understand it, this does away with the "multiple copies" problem described by Allen.

There are some limitations - major changes such as deleting worksheets may be blocked when multiple users have the workbook open.

2017-08-23 09:41:23


Hello again. I wanted to share what I have found that may be an issue.

I have several workbooks stored on my shared external drive. Only myself and one other person has access to these files that are also password protected. Information seems to get erased/not saved when the documents are in "Compatibility Mode" using Microsoft Excel and/or Microsoft Word. These issues happen randomly and without consistency. I have noticed that once I convert the file, the issue of lost data no longer happens. Here is how to convert the file from Compatibility Mode...

Open up a document and look at the top ribbon to see if it is in “Compatibility Mode”.

If so, click on File and then click on Convert. A pop up window will appear and then click Yes. The document is no longer in Compatibility Mode.

Not sure if this "fix" above is working because myself and the other person who has access to these files on my shared external drive have the same version of Microsoft Office, but I would imagine that this helps.

2017-08-22 04:33:08


At work we use excel spreadsheets as a rota system, it is saved on the company intranet and around 15 people have access to it although I am primarily the one who makes any changes to it.
For a long time now I have been convinced that there is something odd going on i.e. corrections and amendments that seem to revert back to previous editions. I have always put this down to my own error as I cannot 100% guarantee that I made these amendments, even though I am pretty sure I did.
It is interesting to read your thoughts on sharing on the intranet; do you think the same would apply to a shared network drive?
The problem is not multiple users amending the same file simultaneously, as not more than one person can work on the rotas at any one time. Our master template gets altered and ‘saved as’ to create our weekly rotas so often I just started to wonder whether these multiple overwrites could be causing a problem?

2016-08-05 09:05:41

Marla Fusilier


The excel workbook automatically saves every 5 minutes. Not sure exactly what you are referring to. Please provide more detail. Thanks.

2016-08-04 12:18:42



I developed a project some years ago. Data was entered into a form, calculations made and then the data values copied to a list. Each time the data was copied, the workbook was saved (a bit like a database).

Perhaps you can have something like this, alternatively, you hhave have a macro in each book to save it say every five minutes using Now + timer


2016-08-03 09:20:40



Thanks for your response. I am not sure what our IT department has in mind for the next step; however, with the yearly license cost of Office now, I am not sure that they will want to attempt trying another version. Have a great day.

2016-08-02 14:23:54


I have no idea of what is causing it. Mistery! What i can say, try another version of Office, or maybe Libreoffice, for a couple of weeks, just to see if the corruption occurs.

2016-08-01 10:59:02



Thanks for responding. Yes, I have actually made a new template with my personal computer that is not on our company server. Note: My personal computer runs the same version of Microsoft Office "Home and Business 2013" as well as the same operating system "Windows 7 Professional". After composing a new template, I then copied it to my work computer and have been using this template with each new project. The workbook is NOT shared... it is password protected and only myself and my assistant have the password to modify these files.

2016-07-29 07:47:02


Marla, good morning. I want to help you, if I can. These workbooks are shared? Did you already tried to work on a completely new file, to see if data gets lost?

2016-07-28 17:30:18

Marla Fusilier


I have a very similar issue as Karen; however, my problem is a password protected workbook, stored on a company network, periodically loses data it previously contained. The company I work for is running Microsoft Office Home and Business 2013. I have had our IT attempt to resolve this issue; however, they cannot figure it out. This issue is now happening in our Word program documents as well. My position here is Document Control and I work in Excel spreadsheets daily (Vendor Document Registers) as well as Word for cover pages, section sheets, etc. My spreadsheets were being saved to our company servers and only myself and my assistant has the password to modify these VDR documents. In an effort to try and resolve the issues of lost data, we purchased external drives and now the spreadsheets are being saved on my external drive. This is still not resolving the issue of lost data. Is anyone else experiencing this kind of issue? If so, please respond with a resolution.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.