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.
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
===========================
@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.
@VBA’s_mac_daddy
May be I’m too late, but I’m very interested in using/investigating your Shared Workbook.
Are you still able to send it out?,
let’s say to [email protected] ?
Thanks.
Hi. Jumping on the bandwagon here. @VBA’s_mac_daddy would also be interested in looking at your shared workbook.
Hope you’re still about. Please send to [email protected]
Thank you.
David,
Would you be so kind and sent to my email [email protected] your shared workbook.
The idea you describing sounds very interesting.
Thanks
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)
I would appreciate a copy of your 3 versions as well. Thanks in advance!
need to provide your email address if I am to send – email me on [email protected]
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!
Hi Hugo,
Unfortunately, I don’t have any way to contact VBA’s Mac Daddy, so we’re out of luck, unless he comes back for another visit.
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?
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.