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

by Allen Wyatt
(last updated June 11, 2020)


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


Getting Big Macros to Run

Troubleshooting an Excel macro when it causes the entire computer to freeze can be a tedious affair. This tip provides ...

Discover More

Fixing Odd Sorting Behavior

When you sort data that contains both numbers and text, you may not get exactly the result that you expected. To know ...

Discover More

Use Filenames that Sort Properly

When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Making Pane Settings Persist

When you freeze panes in a worksheet, those panes should persist even though you save the workbook and reload it. There ...

Discover More

Excel Self-Tests

Need to find out how good you are with Excel? Here are some places you can check out to quiz yourself.

Discover More

Getting Stock Information into Excel

Excel is a great tool for analyzing financial information. If you want to do some analysis using stock information, it is ...

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