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

Losing Data in a Shared Workbook

Written by Allen Wyatt (last updated November 2, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003


1

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 (2998) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Automatically Formatting an ASCII File

Import a file from a source external to Word, and you can end up with a formatting nightmare. One big step is to remove ...

Discover More

Combining Documents

Need to combine quite a few text documents? A macro may be the easiest way to stuff them all into a single Word document.

Discover More

Adding Individual Styles to the Template

One of the things you can store within templates are styles. When you use styles, it is critical that you understand how ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Comparing Workbooks

Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the ...

Discover More

Duplicate Workbooks Opening

If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the ...

Discover More

Opening a Workbook as Read-Only

When you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This ...

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 five more than 3?

2020-02-12 09:08:39

Walter Schmid

i have multiple sheet in one excel document. However lately, all the data on one sheet completely disappeared and on another sheet some data
from a different sheet was transferred to a second sheet. I wonder why this is happening. I have never seen this problem with another document
that I have created.
Thanks for any advice.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.