will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Avoiding 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.
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.
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.
===========================
November 18th, 2008 at 1:17 am
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.
November 18th, 2008 at 7:23 am
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.
November 18th, 2008 at 12:41 pm
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.
November 18th, 2008 at 1:07 pm
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.
November 18th, 2008 at 3:14 pm
Christopher, good point, and Word can probably make better tables!
November 19th, 2008 at 1:56 am
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.
November 19th, 2008 at 3:13 am
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
November 19th, 2008 at 5:52 am
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.
November 19th, 2008 at 6:57 am
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
November 20th, 2008 at 8:36 am
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….
November 21st, 2008 at 10:47 am
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.
November 21st, 2008 at 9:53 pm
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.
November 25th, 2008 at 4:53 am
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.
November 25th, 2008 at 9:16 am
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.
April 13th, 2009 at 11:03 am
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.
April 13th, 2009 at 12:45 pm
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
‘=================
May 19th, 2009 at 5:29 pm
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?
May 19th, 2009 at 5:43 pm
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
July 1st, 2009 at 7:29 pm
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.