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
===========================
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.
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!
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.
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.
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
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.