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.
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:
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.
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!
Today's PCs are more powerful than ever, but you can still have slowdowns when it comes to calculating large workbooks. ...
Discover MoreJumping to the last cell in a worksheet should be easy, but you may not always get the results that you expect. This tip ...
Discover MoreWant to be notified whenever your worksheet needs to be recalculated? Excel may already have you covered, as described in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
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:
Comments