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


10

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

MORE FROM ALLEN

Inserting Cells in a Table

You can enlarge a table by adding cells where they are needed. Just pick where you want the cells inserted, then use the ...

Discover More

Replacing with a Subscript

The Find and Replace capabilities of Word are quite powerful. One type of replacing may not seem possible at ...

Discover More

Multiple References to an Endnote

Adding endnotes in a document is easy in Word. If you want to create multiple references to a single endnote, you can do ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Mouse Scroll Wheel Doesn't Work with Some Worksheets

When you use the mouse wheel, the normal behavior is to scroll vertically through your worksheet. If the mouse wheel ...

Discover More

Slowing Down Mouse Selection

Ever tried to select a range of cells using the mouse, only to have the cells scroll by so quickly you can't make the ...

Discover More

Not Enough System Resources

When you are using Excel, it can be frustrating to receive a cryptic error message that indicates the program cannot ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 2 + 8?

2020-06-11 06:00:54

Paul

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

Marla

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

shanu

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

Peter,

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

Peter

Marla

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

HTH
Peter


2016-08-03 09:20:40

Marla

Adryan,

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

Adryan

Marla,
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

Marla

Adryan,

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

Adryan

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

Hello,

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
Subscribe

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.