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.
July 10th, 2009 at 7:11 am
Hi
Will Office 2007 Service Pack 2 offer any improvements on shared workbooks?
Regards
July 10th, 2009 at 8:11 am
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?
July 10th, 2009 at 11:09 am
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.
July 30th, 2009 at 1:54 pm
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?
July 30th, 2009 at 10:38 pm
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.
September 2nd, 2009 at 1:15 pm
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...
April 26th, 2010 at 9:18 am
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
April 26th, 2010 at 2:30 pm
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.
April 27th, 2010 at 4:03 am
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?
July 21st, 2010 at 3:58 am
Is there any workaround on this shared excel scenario? Can Access solve the problem of data entering?
August 18th, 2010 at 9:51 am
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.
August 18th, 2010 at 10:35 am
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.
August 20th, 2010 at 10:41 am
Thanks Chris, your comments should help those who need to share a workbook, and want to avoid as many problems as possible.