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 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.
Thanks Giff, that’s a pretty sad story. If they can’t (or won’t) use Access, they’d be better off having one person do all the updates, and lock it down for everyone else.
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.
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
‘=================
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?
Debbi, I don’t have any sample code for that. If you post your question in the Excel programming newsgroup, someone may be able to help. You can access the newsgroups through the Microsoft web site: Excel Programming Newsgroup