Losing Data in a Shared Workbook
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.
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Leave your own comment:
Comments for this tip:
Shane 11 Jan 2017, 20:41
I have a complex excel file running our business using macros which I wrote over 10 years ago in excel 2003 it has been working perfectly on a shared basis forever without any problems, I upgraded the program into excel 2007 and
also works without problems. Now one of our machines is running windows 10 and excel 2016 and what a disaster!! The machine running 2016 un-shares its self, and leaves a message saying the file is so damaged as to not be repairable and wont allow a
"save as", it randomly formats cells and causes no end of drama. The other three machines running excel 2007 plod along perfectly regardless. Sometimes thing are better left alone - Cant wait for the opportunity to uninstall 2016
Chris 14 Dec 2016, 13:33
This problem, along with many of those mentioned in the comments, are not caused by Excel Sharing. They are caused by the file server.
Most Msft IT environments have multiple file servers, synchronized using a tool called DFS. DFS and Excel sharing should not be used concurrently.
The authors final example above will work fine. The system will see that rows/records already exist for each save, create non-conflicting records, and a cumulative list will be returned to the user. NOW, if these updates were on the same records (rows), then we have an issue. Sharing likely shouldn't be used in situations like this, excel is the wrong tool.
Summary. Don't use sharing if you have multiple file servers/sites, or if users will be editing the same old data at the same time. It works great when users are each adding records/rows however.
Gina 08 Jul 2016, 09:40
I am having the same problem as Jess. I am the only who can make changes to our spreadsheet, but anyone on the network can open a read-only version. In the last 2 weeks, I had issues where it wiped out data and reverted to an older saved version. This morning, I was locked out of the spreadsheet even though I had no other versions open. I had to re-save the spreadsheet under a different name. This is frustrating.
E-Z E 02 Mar 2016, 15:48
I had users who were entering data in a .csv that was using Excel to open by default. Although there was an obvious warning (below), then continued to save the file in .csv format. .csv will only save the active worksheet. Waive goodbye to your data in any other worksheet.
The warning was:
Some features in your workbook might be lost if you save it as CSV (Comma delimited). Do you want to keep using the format? Selecting No leads to the Save as screen. Selecting Yes (which is what my users selected) saves the file. However, upon reopening it, only one tab exists. All data in the other tab(s) is gone. I googled looking for a way to retrieve the data, but it’s gone. Obviously, the solution was to save the file into .xls or .xlsx format.
sue 03 Sep 2015, 06:01
we have a similar problem. it is becoming extremely frustrating trying to remember what information was input and if it is still there. I note the article mentions Access. I am a complete novice on Access, would it be able to do calculations? our spreadsheet calculates dates etc from a customer's initial referral and tracks the number of days till closure etc. would this be possible on Access?
Jim 22 Jul 2015, 09:01
I have 2010 Excel, I use the spreadsheet to sort customer information. I have 1 sheet for each state and about a thousand (avg) line for each state. The problem I have is that entries that I know were made later just disappear? I have lost worksheets that I recently saved, when I go back to look for it I find it in the recent opened but when I select it it's not there?
Thabet Muhsen 10 Aug 2014, 05:56
We almost solved the disappearing and corruption of shared Excel sheets by doing the following:
- Using Microsoft Server 2003 or 2012 R2 and only ( R2 ) for file server
- Using the same office 2013 32bit for all employees
Shared Excel will always have issues as it isn't built for intensive corporate use, I would recommend reviewing its use by IT departments if you have more than 20 users
Norbert 16 May 2014, 10:19
The most good advice is: Not need to use that looser function (shared excels)
Lex 29 Nov 2013, 09:01
We use a shared excel spreadsheet. On Excel 2010 it constantly deletes data becomes corrupt and in one instance the file seemed to delete itself.
I have been unable to identify any actual problem with anything we've been doing. I wonder if it could have something to do with network latency.
Kirsty 18 Mar 2013, 20:35
We do not share worksheets but we seem to be losing data inputted. We are not sure why we are losing integrity. Can anyone advise we would be loosing information or that information is being put into different cells when you re-open
Larry Boyd 14 Feb 2013, 08:58
My small office team has been sharing Excel 2003 worksheets for 4 years. WIN XP Pro Office 2003. We replaced 2 of 5 PC's upgrading to WIN 7 Office 2010. We have a file server still running WIN XP Pro. While training a new staffer yesterday, shared worksheets shared by Excel 2010 and Excel 2003 began locking and were corrupted and had to be replaced with a copy from backup. We tried "Save As"to the Excel '97-2003 format but the conflict continued between the Office 2003 and 2010 users. We tested and the conflict was resolved when users sharing the worksheet were accessing usung the same version of Office. Any input on this?
Sharepoint 21 Jan 2013, 13:39
Try sharepoint. There is a check-out / check-in feature that works the same as a book in the library. If someone has it checked out, noone else can edit it until they check it in. However, it leads to new issues, like "what if user 1 checked it out, and left for the weekend? User 2 just sits and waits" Not to mention all of the bugs assoicated with MS sharepoint on top of user error. I still dont have a good solution, as I am surfing now trying to find something better than sharepoint. Just wanted to share. Good luck.
Eric 03 Dec 2012, 17:28
We have a similar scenario in our company. However, we've got users that's got different versions of MS-Excel and different OS. The versions of Excel in use are 2003, 2007 and 2010.
While the OS being used are Win-XP, WinNT2003, Windows Vista and Windows 7.
It happened just today, that changes done by 2 users have gone all over the place when the shared file was saved. The "Resolve Conflicts" message did not appear. As we've got a huge file, it's hard to track which changes have been made by which user.
Can anyone advise me on this please.
Thanks & Rgds,
Rachel 12 Oct 2012, 11:55
I have just had a similar experience just yesterday, and again this morning. I made, and saved changes to an Excel file stored on a server. A few minutes later, the changes are gone.
This is extremely frustrating, and as I must share this file via the network.
Just in case, I work with Windows 7 and Microsoft Office 2010.
Jess 19 Sep 2012, 08:33
I have a similar scenario.
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?
I hope you have some suggestions.