Avoiding Shared Workbooks in Excel

do not use shared workbooks in Excel

Occasionally a client asks me to create a shared workbook in Excel, so two or more employees can work in it at the same time. It sounds good in theory, but I always try to come up with a different solution.

There are many reasons for avoiding shared workbooks in Excel.

Note: See a newer version of this article: Shared Workbook Limits in Excel 2010

Your Thoughts on Shared Workbooks

Maybe you’ve had success with shared workbooks, and I’d love to hear about it in the comments. For me, the limitations far outweigh the benefits, and there’s usually another way to accommodate multiple users.

[Update] Your Comments

Thanks to everyone who left a comment below, detailing their experiences with Excel shared workbooks.

  • Be sure to read through those comments, if you plan to share an Excel workbook. They might convince you to find a different solution!
  • Or, if you have no choice in the matter, and have to use a shared workbook, you might find helpful tips in the comments, and workarounds for some of the problems they cause.

Too Many Missing Features

After you share a workbook, many of Excel’s features can’t be used. There’s a list of unavailable features for Excel 2003 on the Microsoft site, and in Excel’s help.

For example, you can’t add any of the following features, and in some cases you can’t even change the existing items:

  • Conditional Formatting
  • Data Validation
  • Lists
  • Protection
  • Pivot Tables

If you do need to create a shared workbook, check the list of restricted features, and make sure you have everything set up exactly the way you want it, before you share the file.

Test everything after you share the file, because things might not work the way they did before.

do not use shared workbooks in Excel

Alternatives to Shared Workbooks

What can you do instead? Find out exactly what the workbook’s purpose is, and why multiple people need to use it.

  • If users are entering data, they could get in and out of the workbook quickly, so another person has a chance to enter their data. Excel will notify the next user when the workbook is available.
  • If users need the workbook as a calculator, make the file read only, or save it as a template, so anyone can open a copy. Users can save the file with a different name, if they need to save their work.
  • If users enter data on separate sheets, create separate workbooks instead. Then, create a summary workbook to pull all the data together.
  • If users need to enter data many times throughout the day, a database might be a better option.

If you have other solutions, I’d like to hear them.

Note: See a newer version of this article: Shared Workbook Limits in Excel 2010

===========================

102 thoughts on “Avoiding Shared Workbooks in Excel”

  1. Wow Debrah, you’ve been way too kind about this “feature”.
    My advice: DON’T USE IT. EVER.
    I frequent the crashes/gpf’s newsgroup and I simply see too many complaints about loss of data, corrupted files, “unexpected” data, loss of the complete file, all related to shared workbooks.
    Excel isn’t a multi-user platform. Maybe it will be in the future, but it certainly isn’t now.

  2. I can echo Jan Karel’s statement: Don’t Use Shared Workbooks. If you need to share data, use a separate database to house the data.
    Debra left off charting from her list of shared workbook incapabilities. If I can’t do charts and pivot tables, then shared workbooks are less than useless.

    1. Our clients have been using both types of sharing (single book vs multiple book) for months, without a hitch. Re the loss of the ability to do charts and pivot tables, why are you using a shared workbook to show pivots and charts? Those are for DISPLAYING data– use a separate workbook for that. We use shared books for data-collection ONLY, which is their purpose. We keep it simple– no macros.

  3. Thanks Jan Karel, I didn’t realize that shared workbooks could also get corrupted so easily. That should convince everyone to avoid them.
    And Jon, how could I have omitted charts? Thanks for adding it to the list of things you can’t do in a Shared Workbook. It would probably be quicker to list the few things that you ARE able to do.

  4. If you are going to list the things you ARE able to do with a shared workbook then lets just call it Microsoft Word with Tables.

  5. We have used shared workbooks for many years now, and live with the problems described. But so far I have not found an affordable alternative. We use it to share details, in real time, of our production system. We do some calculations within the workbook, for resource loadings for instance, so a spreadsheet is ideal. It also needs to be simple for people to use who are not very computer literate – “click here” kind of instructions. I understand a database is the “proper” solution, but it would cost a lot more than a basic license for MS office, which most machines at work have already. Used by about 10 people at the same time. Works 99% of the time OK. OOo is nearly there now, but not quite as good yet. If you know of any other solutions I too would be pleased to hear about them.

  6. Roger:
    A database “only” costs you the development time, your systems do not need an Access installation to be able to use it, just the (Free) Access viewer.
    I’d still urge you to consider porting to Access.
    and you can hire Debra to build the database

  7. Excel is probably the most commonly known tool in the Office Suite for managing data. Best tool? There’s a long debate. From the standpoint of collecting information from various personnel throughout a company, Excel is the application everyone “knows how to use”. Low learning curve means lower cost.
    The add-in called Distributed Spreadsheet has helped a number of companies collect data from various parts of companies and then export the data to other systems. In this way, the reliance of workbook integrity is not an issue and the deployment costs are minmized.
    Sometimes the answer lies in combining tools rather than the same tool used from beginning to end.

  8. Hello.
    I agree that database is the best solution.
    I use Excel with MS SQL Server and it works great.
    Server stores the data and controls the limitations and Excel with VBA is a User Interfase and Reporting Tool.
    You can use free MS SQL Server Management Studio Express.
    Mariusz

  9. When you want multiple users to interact with an excel file – use it in a “Database” mode.
    Create a Template which can add and edit records in a database (Excel/Access etc) stored in a network folder with proper access.
    Create a Reporting application which can pull data from the database.
    Excel 2000 has a very interesting addin called Template wizard with Data tracking which allowed users to add data to a database from an excel template. Instead of improving it MS decided to discontiue it from XP onwords….

  10. At my office, we did the shared workbook thing a lot. It was quick and dirty, and we didn’t need the advanced features. Just a few people working remotely who needed to edit a few cells on a common workbook.
    We did end up with a few corrupted workbooks, but fortunately there were nightly backups, so the data loss was minimal. But the sad part is the rationale behind it all. They didn’t want to buy more Access licenses (getting IT to do installations is a rude welcome to the bureaucracy), and paying someone to write the code that would write the information to a database (or create anything more than a rudimentary solution) was out of the question. All I got were blank stares when I suggested alternatives to shared workbooks.

  11. Sam, I remember that Template wizard, and lots of people found it useful, so I don’t know why it was dropped from later versions.
    JP, at least you your office wasn’t expecting miracles from the shared workbooks, but it’s pretty bad if even a very simple workbook gets corrupted when shared.

  12. I did some contract work for a major car manufacturer a couple of months back where they used a Excel to maintain a long list of parts. They decided to share this workbook, but within a week it was a mess.
    Why? Well the multiple users were not only updating information row by row but also inserting and deleting rows, so when one user saved changed the whole structure of the document (that everyone else was using) was overwritten. They asked me to have a look at salvaging the week’s worth of work, I laughed and pointed them at Access.
    Unfortunately the team leader was unfamiliar with Access and insisted they find a way to ‘make Excel work right’. I left about a month later and the team was still working on a solution for a problem that could have been solved in Access in 15mins.

  13. Can comments be edited if you share a workbook in excel 2007? after the workbook is saved, I have not been able to edit existing workbooks.

  14. Mark, you can’t edit existing comment text, but you can add to the end of a comment, if the sheet is unprotected, or if it’s protected with users allowed to edit objects.
    Or, you could use a macro to remove the existing comment and create a new comment with the same text. Then, edit the new comment before you save the workbook.
    ‘================
    Sub CreateNewCommentWithText()
    Dim strCmt As String
    On Error GoTo errHandler
    With ActiveCell
    strCmt = .Comment.Text
    .Comment.Delete
    .AddComment
    .Comment.Text Text:=strCmt
    End With
    exitHandler:
    Exit Sub
    errHandler:
    MsgBox “Could not edit comment.”
    Resume exitHandler
    End Sub
    ‘=================

  15. can you tell me how to write a macro that will copy text from a cell in excel, open a hyperlink in the adjacent cell to a word doc and then paste the contents of the cell into the word document?

  16. If you just want data, how about everybody having the same workbook on their hard drive and writing to a text file on a shared drive?
    It might be useful for something like simple HR records, perhaps a timesheet or a survey. Everybody can add/edit data with VBA, the person who “owns” the file can open/use it when necessary. The caveat is all edits must be done beforehand to ensure when the file is opened/used, so that data is up to date.

  17. On the subject of shared workbooks
    If a car manufacturer sold a car with A?C but it did not work, would it be allowed to sell the car
    Its suprising Microsoft bundle shared workbooks but then advise dont use it, maybe they should put a 12 user limit on shared workbooks then on the 13th person deny access and prompt for MsAccess or SQL?

  18. Gary –
    Microsoft does not advise not to use shared workbooks. We are independent users who have been burned by shared workbooks.
    You should also remember that SP2, like all service packs, corrects small but very visible issues that have not been around for umpteen versions of a product. Shared workbooks have been around for a long time, and the problems are not very visible unless you have actually tried the feature.

  19. I’ve got a shared workbook that people at my company are updating constantly all day long. I need a long term solution to make this functional, b/c it keeps getting corrupted. If I move this information to Access, can users update info in real time?

  20. Jaime, almost anything would be a better solution than using a shared workbook.
    You could post a question in an Access newsgroup, describing what you’d like to do with your data. Someone there may be able to help you decide if Access is the best solution.
    You can access the Microsoft newsgroups through the Microsoft web site.

  21. Wow, This is not what I want to hear!
    I just spent some months working on a shared workbook application and am introducing it to the rest of our company.
    I’ve used shared workbooks for years now. My work around to the corruption problem is to backup daily before using it and then again whenever you run a potentially destructive macro or do any heavy data manipulation. So far I haven’t had any “unexpected” corruption issues in some years now.
    What drives me to the shared approach is the need for users to copy the shared workbook template and use it for their own projects. Projects are different enough that users need flexibility in how they use the tool. Users aren’t very good at figuring out exactly what they need the “database” to do till they need to do it. The spreadsheet allows them to do things manually that may not be automated yet.
    Typically the spreadhseet is unshared when reports, extra sheets, and pivot tables are needed. It may be that the unsharing cleans up the spreadsheet as well as far as corruption goes…

  22. I just spent 2 months in creating a excel – vba application……………
    I am starting to have some issues when i share my workbook…
    i am getting errors,,,when users save their work at about the same time.
    i want to know, what the problem is? that they try to put data in the same cell, or the saving function that is causing the errors?
    and also the application that excel have to solve the conflicts, is there any possibility to not lose one of the users data?
    and how hard is to transform vba coding to access?
    thanks ,, i tried so hard for this application 🙁

  23. When you set a workbook to be shared, Excel creates a hidden worksheet that contains all the changes, who made them and when. This file can grow rather large and do so quickly. Yo may notice this happening if you use the “Share” feature a lot. File size can create its own problems. If you opena shared file and no longer need to have the file in share move, I highly recommend turning off the share mode on the file.
    Shameless promotion: Sharing & Merging Excel workbooks can be easy. Hit my domain for more info.

  24. Is there anything i can do to avoid as much as possible the clashes in my workbook? i am using user input forms to create my projects informations and all the changes can be done through the user forms………
    i have an option that every project is entered into the next availiable line(empty) but i can still can’t avoid the conflict if 2 users press save almost at the same time…..
    is there any vba coding that can help me in achieving this?
    i tried to assign an empty row before the user create the project.///////////// but no luck…….
    any other idea?

  25. I have had extensive experience of shared workbooks over the last 5 years & my Works currently use 4 of these applications. I agree with previous comments about the fragility of this type of spreadsheet application. However if you find that other, more suitable alternative software is not available to you, you can minimise a lot of problems, especially those of data clashes & file corruptions. Never, NEVER allow users to input, edit or delete data from a “common” data input area or data table in Excel – you may as well give up & go home now if you do that! Instead, create seperate & user-restricted data input areas, edit/amend areas & data table areas for each & every user. You will also need to write data input validation routines, VB code to perform additional validation & standardised write, amend & delete routines. Users viewing of the data areas can be achieved out of harms way by using a protected sheet for a data array & that is all they can access – NOT the “real” data.
    It’s a lot of work & expertise required & if you don’t have the wherewithall to do this, then don’t touch shared workbooks!! (Otherwise it’s great fun if you are an Excel freak!). So far, the only problems we have experienced are those which are common to all files & applications on a network. As for reports, pivot tables etc. then write these in a seperate workbook, out of the way, and leave the report workbook on exclusive access basis.
    Please don’t think I am suggesting that everyone start trying to use shared workbooks – they are hard work. I would recommend alternatives wherever possible, but it IS possible to get a reasonable result if you have to.

  26. Oh – another item to watch out for are “ghost sessions”. Excel has a nasty habit of retaining data of sessions which terminated unexpectedly, such as when your server or network has a problem. Excel does not delete these sessions in its Share Workbook dialogue, but will check them all each time the file is opened, saved or closed & I suspect the file becomes more vulnerable because of this. These ghost sessions can become quite numerous at times of network problems – I have counted 26 of them on an application. Go in & delete these ghost sessions periodically or, more quickly if there are a lot of them, remove the file from shared use totally & then put it back on a shared basis.
    Like I said, best to use some other database application if you possibly can.

  27. I have a single shared workbook that has crashed and become corrupted sporadically, and it typically happens more to remote users (sharing workbook over WAN) than local. Questions:
    1) Chris’ comment said to avoid using a “Common data input area”. What does this mean? Different worksheets? Distinct ranges within a worksheet? Our users don’t enter into the same cells, but they do enter on the same worksheet. Would simply having each enter on their own worksheet minimize the corruption/crash problem?
    2) Is a linked workbook a better solution (each person enters into their own unshared workbook on the common network drive and all are linked to a master workbook in the same network location)? Do linked workbooks have any stability issues similar to shared workbooks? What other disadvantages would this cause?

  28. Hi Jim, I have used seperate user restricted areas on the same sheet & I think they work as well as as using seperate user restricted sheets for input areas – a matter of preference which you use I think, or what your application needs. If I knew EXACTLY why shared workbooks have a vulnerability to crashing then I’d know more than I do! I have strong suspicions though & they all centre around the file saving process & on a heavily used workbook & network, especially on a WAN with remote users, I think there are conflicts between users saving in the same “save time frame” & I have seen some users application lock up because of it & data not getting saved. I’m sure those “ghost” sessions I mentioned increase the save time frame for each user. Eventually, sods law says you will get 2 or more users saving their files / updating each others files in the same time frame which must increase the chances of a conflict or crash. Short of writing some code to control each users saves, I have resorted to having my users ensure that they do not save their work at the same time. Not very neat, but I don’t get many crashes since I instituted that regime. Harder to implement if you have remote users of course.
    I think the suggestion to use a linking master workbook is good & I have some master files like this that use data arrays of the input-user file data (arrays use less resources than cell formulae to pick up external data.) It really depends on how & what purpose you are using the master data file for, as to whether this may be a better solution – I think you should minimise the conflict problems that shared workbooks can have however. If you are using the master file continually, with arrays, you would have to regularly use the UPDATE LINKS feature each time you wanted to see current data (again that is dependant on how often users save their files), but I guess that is no more work than saving a shared workbook master file in order to force updates from the user files. The users of course would not be able to see the full application data if the master file is on exclusive use setting. If they don’t, then linked workbooks may be the better option.
    I think we have to accept that Excel is not the best medium to use for multi users data input etc. Excel is clever, but it’s not a universal remedy I’m afraid & databases are much better for multi user applications, that’s what they were designed for. Sorry that this doesn’t give a nice neat & tidy solution & I don’t pretend that I know all of the answers, but hopefully the bits I can offer are of some help to users, or spark off some more input from readers on the subject – even if to say that something I’ve said isn’t right!! I don’t mind. Regards.

  29. Chris,
    Thanks for the thorough answer. Your comments match my experience. Our corruption occurs when multiple users are actively using the workbook. Unfortunately it is a deadline driven application where all of the users (3 people) have 1 day each month to enter and save their data.
    The long term solution is to enter the data in our corporate forecasting tool, so there is light at the end of the tunnel, but some additional development work needs to occur to get there. I’ll investigate linked workbooks in the meantime. If there is no data input in the master workbook, it may make sense to share that one and have the individual workbooks in exclusive mode since they wouldn’t need to save it. That brings to mind another idea – setting up the Master workbook as a template (.xlt), then each user would be opening their own version – since all the data is linked, they should be able to see the application and not corrupt the original. Not sure what downsides that could have (having multiple open workbooks referencing the same input workbooks via links), but I may try it out and post my findings.

  30. Jim, glad to have been of some small help & good luck with your template idea. Regarding setting up a linked-file master workbook on a shared basis, might it be better to set it up as exclusive-use and Read-Only with a password protection. That way if anything goes awry with the read only file the original is left intact. Again it depends on how you need users to use & access the full data. Good luck & I look forward to hearing what happens.

  31. I have a client using shared workbooks wondering why sometimes the person in the workbook shows up as “System Adminstrator” rather than the actual name of the person. This presents a problem when they want to contact the actual person in the workbook. It seems to happen more with 2007 files than 2003 files. Does anyone know the answer.

  32. Ed, firstly, I’m not a System Administrator, but as I understand it, when Excel is originally installed on a server I believe the server automatically enters, as default, the user name of the person doing the installation – usually someone logged in as “System Administrator”. This name will stay like that for all susbsequent users of Excel until those individual users change this ID. The chosen name is then saved under that individual user’s “System User Profile” on the server & will appear on all Excel files they author, save or open on a shared use basis.
    In Excel 2003, open Tools, Options, go to the General tab & you will see a name in the “User” box at the bottom. This can be changed to the users name. If you are using Excel 2007 then the user maybe hasn’t done this yet. Click the Office button at top left & select Excel Options, Popular Tab & you will see the same feature at the bottom. To test this, in both Excel versions, save the file & close Excel then re-open the file. Go to the General or Popular Tabs in Options & you should now see the changed name. On shared workbooks this is the name Excel displays for those users with the file open. Hope this helps.
    As I say, I’m not an administrator & I may have a few technicalities wrong, but it works for me!

  33. I have a shared workbook in Excel 2002 on our company network that’s used by about 12 people through out the day across 3 shift. Nothing is protected but there generally shouldn’t be any update conflicts as each person in a shift have their own cells assigned to them. It’s been in use for about 3 years now and at first there was major problem with data corruption. I was periodically getting calls where the entire workbook was empty. Eventually I narrowed the issue down to a handful of users using a lower service pack of Excel. Most of us were on SP3 while a handful were still on the original install. The data gets lost when one of these “non-SP3” users attempted to save their edits. They get no error message, but when they close out the workbook becomes empty.
    Now, I have a second shared workbook created. I protected and shared the workbook and locked down the cells using the edit ranges feature. I have 3 groups of users with 3 people in each group. Although there’s only one primary data entry person in each group (the other 2 are backup).
    HOPEFULLY (fingers crossed), it’ll run smoothly. I remembered the nightmare experience I had with my first shared workbook, and had suggested using Access for instead. But all the users are familiar with Excel and non of them have any experience with Access. The linked workbook sounds like a good alternative if this doesn’t work.

  34. I have 4 users using a shared workbook in Excel 2007. They also have Windows 7 on their computers. We used to have some issues with the workbook locking up when we were using 2003 but not like it’s been since we migrated to Office 2007 with Windows 2007. The thing locks up constantly….it stopped for a couple of weeks after an auto windows update and we thought it fixed itself but another auto windows update was downloaded last week and we are back to square one. This is a spreadsheet that allows the sales people to see their saleable inventory and users update as inventory is sold so the saleable inventory is kept current throughout the day. I’ve wracked my brain for an alternative but I can’t see how Access would work…This spreadsheet is linked to other workbooks that contain our actual inventory.

  35. i have a protected sheet and in that some cells are unprotected for feeding the data which i want the users not to copy or delete or edit,please give me the suggestion

  36. I have actually found a solution to my problem with our spreadsheet locking up…I have modified my workbook so it doesn’t have as many external links. It used to link out to the price list every time a product appeared in the workbook. It now only links out for the first instance of a product’s appearance. Any time after that that the same product appears in the workbook it links to the first instance in the same workbook so with far fewer external links it is working much much more efficiently.
    Everywhere I have external or internal links I have the cells protected so that data cannot be overwritten by users. I have a worksheet with calculations where they can affect their saleable inventory before it gets posted to their sales sheets…this helps keep them from overselling in case of possible production problems etc.

  37. There’s an alternate way to do shared editing, which may eliminate all the corruption problems people are reporting.
    Instead of storing the workbook in one location for several users to edit, you distribute separate copies of the shared workbook to each user. Then, after their edits are done, they send the changed workbooks back to you, and you merge the workbooks together. (this is NOT the same as “Consolidation”). Then, you never have the situation where two people have the same copy open at the same time.
    To merge multkple books together, click Compare and Merge Workbooks. To add the Compare and Merge Workbooks command to the Quick Access Toolbar, click the Microsoft Office Button Button image, click Excel Options, and then click Customize. In the Choose commands from list, click All Commands, select Compare and Merge Workbooks, click Add, and then click OK.
    You should use worksheet protection, to ensure people don’t do things that can mess up your merge, such as adding or deleting rows.

    1. Johny. The problem with linking files among them is that the time response in Excel is so bad at times that it would be worst than even trying to share the file. An apparent solution is to try to manipulate sectors of the spread sheet and it works until you need protection on the sheet, which is a huge downfall and dissapointment on Microsoft. The only visible option would be to have a mastersheet where all data is consolidated, and some how administrate users coming to the workbook so they are directed to a zone in the workbook where no protection, or special tools lost by the sharing option is required. Then once is finisthed, put back the data changed or updated into the centralized file. I have done similar type of things in the past and although they are pretty demanding in macros and design, they are for sure a safe option. Of course, with good Access knowledge, you may sure find another solution but this would requied to pay for the Access licences por user.
      ACA

      1. Aca, i don’t know what you mean by “linking files among them”. Who said anything about linking? i don’t know what you mean by “time response in Excel is so bad”. What time response? i don’t know what you mean by “it works until you need protection on the sheet…” Sharing the way i described DOES include protection. I don’t know what you mean by “… which is a huge downfall.” What about protection is a downfall? Works fine for me. True some functionality is lost in a Shared workbook, but for our purpose the benefits outweigh the drawbacks. Our clients have been using both types of sharing (single book vs multiple book) for months, without a hitch. (btw, might be a good idea to avoid using the word “consolidate” in this context, since consolidate has a special meaning in Excel). -Cheers!

    2. I have often had problems merging sheets with defined names on them. The formulas in the parent workbook don’t know where to point when re-integrating imported copies of the same worksheet they had been pointing at. And the sheet that is moving often has formulae pointing to other sheets in the workbook, and copying the sheet or moving it into the master workbook leaves those pointing back at the other file. Basically, merging workbooks has always been a nightmare for me. Sorry this is off topic a bit.

  38. Used one of Debra’s VBA solutions for making Excel drop-downs larger by adding a Combo Box. Worked like a charm-Genius!! But, when I share the workbook, the combo-box solution no longer works. I didn’t notice VBA being one of the things lost in shared workbooks. Anyone have an idea if there is a solution or workaround. The file needs to be shared because I need to track changes and it’s possible more than one user will be in there at the same time. Please advise.

  39. Hi
    Not sure if this has been covered, but having major issue with printing shared workbooks – different computers (all macs, but different versions of Excel) print different page breaks, headers ad footers etc. It’s a calendar/planner, with twelve monthly worksheets, which automatically transpose onto a Yearly summary sheet.
    And no, before you ask, the differences are not relative to the versions – one Excel 2004 user will experience a different print out to another Excel 2004 user with the same workbook. They all have checkboxes cleared under ‘Include in personal view’. I’ve gone round and tried to configure all machines the same way for this particular workbook, but impossible to get them all to print the same way
    any ideas – apart from the obvious – get a database – and yes we do have Filemaker and am considering reworking it.

  40. Richard,
    Not sure how this impacts printing, but I found I needed to delete all custom views after changing any layout (hidden columns etc.) in order for everyone to see the worksheet in the same way. In 2007 it is found in the View ribbon. Not sure where to find this in 2004. It may not help your printing problem, but it’s worth a try.
    Jim

  41. we have no corruptions in our shared books. maybe it’s because they are short-lived– we use them to collect data just for one month, then the book is retired. we keep ’em simple-no macros, charts, pivots, etc– we use them for data-collection only. After collecting all our data, we pull that into an access database and generate whatever reports are needed. For our needs, Shared books works great.

  42. That is the thing, for basic access without controls, passwords, and knowing who access what and when, probably sharing is ok, but when it comes to high level sharing access, then things are different and they simply don’t deliver what they are made for. It is like a medication that would fix your stomache but damage your leber, no really that good. Thanks aniway for your help.

  43. Shared workbooks can be made to respond just as reliably as an access DB. I have done so, with more than 8000 SLOC. It took 2 months to build, but it works flawlessly, logs errors, tracks changes, many conditional formats, popups including calendars and userforms that are very aesthetically appealing. One thing to avoid is a large file size and worksheet formulas. I use macros to do everything. And it’s sweet. Even hyperlink creation is possible; and who needs conditional formatting when a macro can handle everything. Even better, I have written code that accepts userform inputs to create new custom macros that filter and display data as necessary. No need for Pivots when you can filter and copy data from one xlVeryHidden sheet to a visible sheet, and no one is any the wiser. So much that is possible, if you really want to get it to work.
    I may be willing to share thoughts, modules and functions with anyone who is interested.
    This workbook does it all, and it never crashes.
    For all the “experts” that state this and that negatively of shared workbooks–well, suck it.

    1. I am trying to set up a workbook where multiple users may need to edit in the same data ranges on some sheets, and yet allow only one editor at a time on other sheets. I suspect I need to record user & network machine details and then asign prority rights from userform menus (so only allowing one person to do one type of task at a time, with option for other users to try again after a couple minutes, and then get the workbook to resave itself & update before and after each task). So I am interested in your approach, please email me at [email protected] thank you

  44. Sharing workbooks works quite well, providing you remove free will from the user. Over the past year or so, I have constantly updated a shared workbook. Slowly but surely removing all routes by which a user could enter data incorrectly or generally screw things up. Now, entering data involves userforms and lots of code to avoid errors and multiple entries in the same cell. It works very, very well and is totally secure. I like Access also and think it is a better tool for the job, but most people do not like Access and do not know how to use it. Hence my work securing shared wookbooks.

  45. I got a little excited recently when I saw that Excel had this shared write feature. Overall, this discussion is discouraging and a wet blanket that will make me reconsider proposing that our group switch to using sharing for one file for now, and others in the future if the first one works well. Please understand: that is not to be critical of the people who have had bad experiences (the word “corrupt” gives me pause) and report them here. I’m better off knowing this than not knowing. Thank you all for taking the time to relate your experiences.
    I wonder if our circumstance is such that we can avoid many of the problems reported here, especially the ones going back to about three years ago. What do you think? …
    – We’re all on Win 7 with Office/Excel 2010.
    – The file we want to use this to start out with is one that is used monthly to track the status of the process used to produce bills for our our customers.
    Ours is a low price / high volumn business — millions of purchases per month. However, the file we want to share is just 5 sheets that track the status of customer groups and report creation as they pass thru the process. Typical end size of the spreadsheet: about 220K.
    – We start the process each month with a fresh copy of a template .xlsx, and we’d have no reason to convert the template to being shared. Instead, we’d clone/copy from the template each month and make the clone sharable. So there’s no exposure to the ravages of one instance of the file being used and growingindefinately.
    – There are five of us who would write to the file, but only three in any given month. Two of the roles are shared by pairs of people for the safety of redundancy, and they alternate months. So, there are no overlaps between the two people who are doing it a given month — the members of one pair never change the cells of the other pair.
    But, there are a striking number of steps to the process, so people are in and out of the current file quite often over the course of four days or so. Plus, we recently added some tracking sheets that are such that you’d really rather park yourself in the file for most of the day than have to go in and out many times to allow others to save their changes. Opening read-only and wawaiting exclusive access doesn’t cut it. And we’s rather use one file — the bosss prefers to monitor one file,
    – After a planned dry run in the next few days with just two of us trying Excel sharing for the first time on an unrelated project/file, we’ll consider going live with it for the next billing process. (Actually, I’ve tried it out myself using two logins on a copy of the billing xlsx while learning aout this. That was fine, but just a toy exercise.)
    – The billing sheet is pretty simple. It’s over whelmingly just a matter of entering a “Y” (meaning a task is done) or entering a number in some cases. The fanciest it gets is conditional formatting, which we wouldn’t need to change dring the process. It doesn’t even havea any sums, formulas, macros or VBA.
    The process and file are pretty stable over the long term, and definitely stable WRT sharing’s functionally restrictions.
    – If this works out well, then we’d be tempted to use it for tracking testing, especially when we test major, weekend Oracle upgrades with about 15 testers entering the pass/fail results for the tests they own, and occasionally a comment when appropriate. Just those small things “pass” or “fail” and maybe a comment in a test field. Oh, and a few functions to give the runing pass/fail/not-yet-run counts.
    – There would be virtually no conflict/collision resolution to do in either example. Each person has their assigned part of the billing process and their designated test cases. I’ve done these for about 4 years, and it’s hard to think of any cells in either case where more than one person would have occasion to overwrite someone else’s cell.
    What do you think? At this point, with Win 7, Excel 2010 and simple worksheets, how conderned should we be that this could backfire on us?

  46. > So, there are no overlaps between the two people who are doing it a given month — the members of one pair never change the cells of the other pair.
    That was jumbled. Clarification: Each month, it’s either Eric or Patrick doing one part of the process and either Carol or me doing another part. There’s also Renee every month doing a third part. There are several other people involved in the overall process, but they don’t touch the spreadsheet. So, a typical rotation would be: January: Patrick, Carol & Renee;
    February: Eric, Paul & Renee;
    b>March: same as Jan:
    April: same as Feb; etc.

    So:
    – not many users,
    – very few, probably no, change conflicts,
    – small file,
    – file lifetime = about 1 week;
    – current software (Win 7; Office 2010).

  47. We use Shared Workbooks because it allows us to operate collectively without having to have a database solution designed, built, tested, installed and trained on. We can create the whole process in 10 minutes and be up and running. Everyone who knows excel can use immediately.
    As to the excel functionality we lose. If you need to do some of those things that you cannot, you just unshare the file, put in what you need, and reshare the file. If you are doing pivot tables or charts or whatever, you just copy of the file and produce what you want. But we rarely need those things for the shared workbooks files. That is not their purpose.
    Shared Workbooks have the big problem of getting corrupted. It is going to happen. Copying the file with copy and paste or saving it to a new name do not help anything. Here are two ways to minimize the problems:
    Do not keep history period. If you need to be able to track who has changed what, then your application needs something more powerful than Shared Workbooks.
    You can watch the file size grow. It is an exponential process. So, when it starts to increase in size, it will increase in size faster and faster. Before this gets out of hand do the following:
    Highlight all of the TABS
    Use the Worksheet Move or Copy command
    Check Mark ‘Copy’.
    Choose New Book in the drop down.
    Save the New Book to the name of your choice.
    We do this every friday night on our Shared Workbooks.
    You will be amazed.
    I have one workbook that is 12 Meg. When it blows up it exceeds 200 Meg.
    When we do the move and copy procedure above. It takes it back down to 12 Meg.
    You obviously have to do this before it says it is corrputed.
    So, everyone is right. Share Workbooks is not very stable. A database solution or any of the other fixes mentioned are great – if you have the IT staff or your own staff can build those solutions, and it is cost effective for the application you are trying to accomplish.
    However, there are many things that Shared Workbooks can do cost effectively, as long as you realize the limitations.

    1. Easier than copying the sheets is to unshare & then re-share; which removes the history bloat. To get an idea when to prune, one can use a rough calculated value to estimate the expected file size from the number of entries to compare to the actual filesize (divide one by the other and await until say x10). Provided there are down-times when no one needs access, an on-sheet autoshape button links to VBA code that checks the number of currently shared users is 1 (i.e. oneself) and then unshares and reshares the file – takes but 1 minute:
      If need set any sharing attributes, then these must be applied after the file has been reshared, i.e. the file gets saved 3 times in the process.
      If ubound(ActiveWorkbook.UserStatus,1) >1 Then Goto lquit ‘number of users should be just us !
      If Not (ActiveWorkbook.MultiUserEditing) And Not (ActiveWorkbook.ReadOnly) Then GoTo lquit ‘must be currently shared and not read-only
      ActiveWorkbook.ExclusiveAccess ‘ un-share (SAVED#1)
      ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, AccessMode:=xlShared ‘re-shared (SAVED#2)
      ‘set certain shared features…
      ActiveWorkbook.AutoUpdateFrequency = 10
      ActiveWorkbook.PersonalViewListSettings = False
      ActiveWorkbook.PersonalViewPrintSettings = False
      ‘… and a final SAVED#3
      Application.DisplayAlerts = False
      ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName
      .lquit

  48. @VBA’s_mac_daddy
    May be I’m late, but I’m very interested in using/investigating your Shared Workbook.
    Are you still able to send it to somewhere :-),
    let’s say to [email protected] ?
    Thanks.

    1. copies of shared workbook with sharing rest button emailled (3 versions, simple button, a little table estimating the developing bloat, and one show list of all users currently editing the file)

  49. Hi Debra,
    seems, that a lot of people are VERY interested in VBA’s Mac Daddy’s Shared Workbook.
    Seems too, that he is not responding to us.
    Do you see a possibility, to share this workbook to our small community?
    Maybe, someone of us was successful in getting this source of expertism.
    I hope!

  50. I have a new issue with my Shared workbook and am wondering if anyone has an explanation or cause. We have a shared workbook that is used by 5 users who all sit in the same room. The workbook displays sellable inventory and they edit it each time they sell something to keep the sellable inventory current for the other users. The problem is that occasionally a salesperson’s version of the workbook will disconnect from the others and even though it will still be displayed as “shared” on his desktop it won’t be sharing with other users. The only way we find out is when someone oversells the inventory. Then we have to go back and try to figure out when it disconnected and how much the disconnectee posted as sold during that period. Luckily this doesn’t happen very often but it’s a pita when it does. Any ideas?

    1. Depending on how often the 5 users are adding new entries and whether users enter directly into the worksheet or via a userform, have the workbook re-save itself before each transaction is added. This effectively updates the workbook ensuring each user is looking at a current version (rather than making edits that will edit-conflict resolved at the end of the day when the workbooks are closed).
      Obvious caveats: a) this adds a delay for each transaction, not a problem if workbook smallish, and time for each user is not too pressing
      b) might need to have workbook then check it is on the list of users sharing workbook.

  51. Can I just ask, can multiple users be in one database document then, updating it from their different desks and there will be no conflicts????

  52. Is it really possible for multiple users to be in one database updating it, adding deleting info AT THE SAME TIME and there will be no conflicts whatsoever?

    1. The short answer is
      –Yes, a database can handle “many” concurrent updates without conflicts. —
      There are limits (usually well documented), but a database should also deal with such limitations “gracefully” (for instance declining to commit the update in a saturation condition) rather than “ungracefully” (losing data).
      Your mileage may vary. (Cue discussions of what’s a “REAL” database and what is not…)

  53. Pingback: Excel file
  54. Since three years before working in network infrastructure, all is well but recently got one error in shared excel workbook 2003. dono what is the solution. the problem is , shared workbook working good in all systems but its unexpectedly stopped working for single user,but other excel working good in same folder. we tried all other ways as in microsoft website, but still same problem . is ther any solution?

  55. Hi everyone
    I have made a system of 15 types of applications (interfaces) with VBA and used excel 2010 shared workbooks (17 workbooks) for reading-writing data, it is a very large project with >200 worksheets and about 190,000 lines of VBA codes, in fact the system calculates the fees of our products (Prosthesis and Orthotics) and many other data, such as working hours of personnel by product production not by the time they spend in the clinic.
    it is really amazing that after 2.5 years I encountered the “locked by another user” problem. all the users open the workbooks as read-only all the time and only when they want to write into the workbook it is reloaded as writable for a few milliseconds and then closed and reopened as read-only. for all this time I had no such problem till the data workbooks got bigger (smallest = 19,000 records for only the reception workbook, having 26 columns) and the saving time extended.
    at the cashier desk we have 2 cashiers and their data workbook is very large, now it takes 1.5 seconds to save the workbook (the data workbooks are binary workbooks “.Xlsb”) and if one is writing on it the other encounters the message, if she/he is trying to save some data at the same time. I am a physician and not a software expert but I have become familiar with excel and VBA as I have encountered most of the problems posted on the internet (such as UserForms with >500 controls and many other huge problems).
    assuming that all the users open workbooks as writable only when saving some data (a few seconds), I think if we make a loop to check if a workbook is Locked by another user and let it check until the other user’s application closes and reopens the workbook as read-only (in fact unlocks the workbook), then VBA can exit the loop and continue the writing code.
    I will try it in a few days and may be the problem can be solved, and the users won’t see the annoying message!
    thanks for reading the long story!

  56. I have a workbook with multiple worksheets. I need specific people to be able to log in and specific data from one worksheet to populate for viewing, while the rest of the data remains hidden and the other worksheets remain hidden. The people will not be able to do anything other than view the data. There is only one person that maintains the workbook.
    Help?

  57. I have applied the excel template (Project Tracking) and have been trying to share it with my co-workers so everybody can update the file. It doesn’t let me to do it. System asks me to convert it to normal range which I did. It also told me to remove XML maps which got none. Now I really don’t know how to do with it. Please help…

  58. Hi Debra,
    i am searching for only one workbook in a screen vba very badly. ( total no. of workbooks opened = one)
    Because of excel vba run from another workbook will pull all details from existing workbook ( book2 is open and we will pull details from Book1 by using code as ” Sheets(“sheet1″).Cells.Copy ” to other book.
    To avoid the above instance,
    Kindly help me in this regard.

  59. Hi, Im hoping you still have the Shared Workbook that can be sent out. If so would you please send to
    [email protected]
    Many thanks for reading.
    For all those who read this page, I have a Shared Spreadsheet that several staff will enter their data throughout the day.
    Is there a program or formula to add to the spreadsheet (or something easy) that will allow each person to open the spreadsheet up at the same time (as they work in different departments but need to add their data to the sheet) and when they save the sheet, it will not overwrite any other persons data entered if they opened the sheet and was editing on the same Cell/Row or Column and it Auto creates a new line for their work added.
    Thanks for reading
    Pugg

  60. I have just setup a database for a small company using excel. We all have a common spread sheet which has a different name for each user so they can all have the same file open simultaneously. The data base is stored in a separate spread sheet which is opened and closed by each user. We also have another spread sheet which contains common tables of data. When someone logs on the tables automatically update to their file…so I can make changes offline without having to giver everyone a new copy. I use these tables to define access levels, users, test data, sample types etc. When one person wants to view a record the spread sheet is opened and a dump is performed for a range of records or the user can just search for a single record. If a user wants to change a record then a tag is applied to the record they wish to modify. Then when another user wants to view the same record they are prevented from doing so until the LOCK tag is removed….which occurs after they save the changes to the record or exit without making any changes. Each record has 105 fields and 75 of the fields contain about 30 pieces of data separated by file separators (a bit like CSV file but with my own unique separator). We can handle really very challenging information with data for formatting and rounding. Even can produce a series of rapid reports with individual signatures and different access levels which go straight to e-mail. Tool me about 3-4 months to setup at home and then have spent last year on and off making improvements. Don’t think access could handle what I have setup…was considering using access but think its not flexible enough. Have considered using sharing but have knocked this on the head after reading up on this. Am researching possibility of using CSV files for storing the database to improve access time. I currently have timers in the file opening to help multi use so will wait and try to open the file for a period if a lot of traffic.

    1. Hi E J, I came across this blog while looking for an effective way to make my excel-vba application, a user-friendly and accessible to two (2) users which are the only ones needed to edit, input, modify etc the information contained in it. I got really impressed with all the features that you described in your post although, sadly you did not leave open any possibility to share that amazing outcome with us. Is there a chance that you would give us some hints or clues on how to get similar results on our excel workbooks? – thanks in advance for your kind attention. Best Regards, Charlie.

  61. I work for MDX Technology Ltd. We have a commercial product, MDXT Connect, which includes functionality allowing rectangular ranges of cells to be ‘shared’. We don’t share formatting information, so our functionality is rather more limited than that provided by Excel shared workbooks – but we do share cell values and functions very quickly and efficiently. Our existing customers are mainly investment banks and hedge funds but we’re planning to create a separate product which is focused on general purpose sharing. More details can be found at http://www.mdxtechnology.com/products/mdxt-share. I hope you don’t mind me ‘advertising’ our product here, but I genuinely believe it could be of interest to your readers. We developed our sharing functionality for a client who was suffering badly from the problems with Excel shared workbooks, and they have been delighted with the results.

  62. Mort here Debra from Dallas. Not sure how to phrase my observation / comment. These posts are most helpful, by the way! Here goes. It is not so much that I want to “share” a workbook, in the “share sense” of these posts, but that I want to be able to consolidate multiple additions/revisions from the same workbook that I have sent to several individuals. Is there a way to do that without “sharing” per se? Thank you, and others for that matter! Best week’s wishes!

  63. Hi Deb
    I’ve visited this page before and appreciate you having created it, but as I dropped-by in passing once again, I have to stop and say that I agree with VBA’s_mac_daddy … I have virtually NO problem with fairly complex shared Excel 2010 workbooks … with the help of some decent VBA coding to support users’ interaction with the various worksheets within those books.
    Within the design intent of the product, there is very little I have been unable to get Excel to do for me over the years (it doesn’t make my coffee yet!)
    I’ve written two full legacy~SaaS ETL solutions, a large Requirement Traceability application, a workflow transformation application (using several key office apps including Excel), ‘poor-man’s’ performance monitoring applets, Financial data extraction & reporting solutions, a Test Management product (Yes that was a big “shared workbook” application) … you name it.
    My harsh opinion is that nay-sayers of Excel capability have not spent sufficient time understanding how the product works and the massive capability contained within it, often with fairly simple VBA support, even to the extent of just recording a macro (‘wizard-built’ VBA!) in some cases.
    That said, sometimes there are BETTER ways of solving things than with Excel! (e.g. a database … in some cases)
    Sadly I do not have the time to respond to the many, many questions on these types of forum, but if people desperately need help with specific Excel problems, they are welcome to contact me through my Blog or Twitter, but be aware that my time is at a premium so my responses may be slow and bigger challenges may attract a to-be-negotiated fee to solve.

  64. Hi, I’ve ended up here as I’m looking to see if there’s a reason why an Excel 2010 user hasn’t got frozen panes whereas an Office 365 user has. However the 365 user is forced to look at worksheets (correctly) hidden from the 2010 user – so it all evens out. I’m not having much joy so I’m going to do the analogue of turn it off and on again – make it single user then back to multiuser.
    As a tool excel certainly appears to get used for tasks where another tool would be more appropriate – particularly databases. But, as has been pointed out many times here, there is a user familiarity and expertise with excel. That expertise, in one tool, does tend to lead to the “To a man with a large enough hammer, everything looks like a nail” scenarios though.

  65. Hello, I have been using this function:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, “Packet_temp”, Fileandpath, False
    where Fileandpath is a previously defined variable for the file location.
    In my code, I am importing over 1600 excel files, and for some of these (about 100) the rows are imported out of order (ie. row 3 is listed as a recorded before row 2).
    Any idea why this could be happening?
    Thank you

  66. Basically I am a VBA programmer, when several copies of the program should write the same file, even if the writing operation takes only 1-2 minutes, it can occur that the different instances of the program want to do it at the exact same time. It is not allowed, because only one instance of the file can be opened for editing at the same time.

    I just implemented a workaround. Before opening the shared data file, the program tries to create a lock file in a dedicated folder. If there is another lock file in the folder, it means somebody has already created a lock file and probably editing the data. In this case, my program instance has to wait (entering into a checking loop) until the other user’s lock file will be deleted, after writing and closing the shared file. At this moment, my instance can create my own lock file, open, edit, close the data file and delete my lock file. The lock file has no content, the name of it is the username of the user, so it could be known who is locking the data file.

  67. I use shared workbooks when a bunch of people have to add infomation to a basic workbook and using separate workbooks for each person is not practical.

    In my case, the workbook is a requirements matrix provided by a customer for a product they want us to develop. I need to send the matrix back to the customer after various people in the company have added information. If I break it into separate files per person I have to reassemble at the end and that’s a nuisance.

    So far shares workbooks work ok for me, but some of the limitations, specifically inability to paste pictures or merge cells, are really annoying.

    To overcome the danger of someone overwriting the file, I am using a free utility called RealTimeSync in combination with a batch file (https://datatofish.com/backup-file-timestamp/) that copies the file to backup location and adds a timestamp to the filename, to save a backup copy of the file each time it is saved.

  68. As I wrote earlier, I am successfully using shared workbooks when I need to enable multiple people to add data to the same spreadsheet.

    My problem is that sometimes I need functions that don’t work in shared workbooks, such as pasting pictures or merging cells.

    Is there a way to unshare the workbook then reshare after making the changes, without losing the change history?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.