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.
===========================
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.
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.
Our clients have been using both types of sharing (single book vs multiple book) for months, without a hitch. Re the loss of the ability to do charts and pivot tables, why are you using a shared workbook to show pivots and charts? Those are for DISPLAYING data-- use a separate workbook for that. We use shared books for data-collection ONLY, which is their purpose. We keep it simple-- no macros.
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.
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.
Christopher, good point, and Word can probably make better tables!
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.
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
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.
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
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....
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.
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.
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
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.
Hi
Will Office 2007 Service Pack 2 offer any improvements on shared workbooks?
Regards
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?
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.
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?
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.
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...
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 :(
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.
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?
Is there any workaround on this shared excel scenario? Can Access solve the problem of data entering?
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.
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.
Thanks Chris, your comments should help those who need to share a workbook, and want to avoid as many problems as possible.
I have a single shared workbook that has crashed and become corrupted sporadically, and it typically happens more to remote users (sharing workbook over WAN) than local. Questions:
1) Chris' comment said to avoid using a "Common data input area". What does this mean? Different worksheets? Distinct ranges within a worksheet? Our users don't enter into the same cells, but they do enter on the same worksheet. Would simply having each enter on their own worksheet minimize the corruption/crash problem?
2) Is a linked workbook a better solution (each person enters into their own unshared workbook on the common network drive and all are linked to a master workbook in the same network location)? Do linked workbooks have any stability issues similar to shared workbooks? What other disadvantages would this cause?
Hi Jim, I have used seperate user restricted areas on the same sheet & I think they work as well as as using seperate user restricted sheets for input areas - a matter of preference which you use I think, or what your application needs. If I knew EXACTLY why shared workbooks have a vulnerability to crashing then I'd know more than I do! I have strong suspicions though & they all centre around the file saving process & on a heavily used workbook & network, especially on a WAN with remote users, I think there are conflicts between users saving in the same "save time frame" & I have seen some users application lock up because of it & data not getting saved. I'm sure those "ghost" sessions I mentioned increase the save time frame for each user. Eventually, sods law says you will get 2 or more users saving their files / updating each others files in the same time frame which must increase the chances of a conflict or crash. Short of writing some code to control each users saves, I have resorted to having my users ensure that they do not save their work at the same time. Not very neat, but I don't get many crashes since I instituted that regime. Harder to implement if you have remote users of course.
I think the suggestion to use a linking master workbook is good & I have some master files like this that use data arrays of the input-user file data (arrays use less resources than cell formulae to pick up external data.) It really depends on how & what purpose you are using the master data file for, as to whether this may be a better solution - I think you should minimise the conflict problems that shared workbooks can have however. If you are using the master file continually, with arrays, you would have to regularly use the UPDATE LINKS feature each time you wanted to see current data (again that is dependant on how often users save their files), but I guess that is no more work than saving a shared workbook master file in order to force updates from the user files. The users of course would not be able to see the full application data if the master file is on exclusive use setting. If they don't, then linked workbooks may be the better option.
I think we have to accept that Excel is not the best medium to use for multi users data input etc. Excel is clever, but it's not a universal remedy I'm afraid & databases are much better for multi user applications, that's what they were designed for. Sorry that this doesn't give a nice neat & tidy solution & I don't pretend that I know all of the answers, but hopefully the bits I can offer are of some help to users, or spark off some more input from readers on the subject - even if to say that something I've said isn't right!! I don't mind. Regards.
Chris,
Thanks for the thorough answer. Your comments match my experience. Our corruption occurs when multiple users are actively using the workbook. Unfortunately it is a deadline driven application where all of the users (3 people) have 1 day each month to enter and save their data.
The long term solution is to enter the data in our corporate forecasting tool, so there is light at the end of the tunnel, but some additional development work needs to occur to get there. I'll investigate linked workbooks in the meantime. If there is no data input in the master workbook, it may make sense to share that one and have the individual workbooks in exclusive mode since they wouldn't need to save it. That brings to mind another idea - setting up the Master workbook as a template (.xlt), then each user would be opening their own version - since all the data is linked, they should be able to see the application and not corrupt the original. Not sure what downsides that could have (having multiple open workbooks referencing the same input workbooks via links), but I may try it out and post my findings.
Jim, glad to have been of some small help & good luck with your template idea. Regarding setting up a linked-file master workbook on a shared basis, might it be better to set it up as exclusive-use and Read-Only with a password protection. That way if anything goes awry with the read only file the original is left intact. Again it depends on how you need users to use & access the full data. Good luck & I look forward to hearing what happens.
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.
There's an alternate way to do shared editing, which may eliminate all the corruption problems people are reporting.
Instead of storing the workbook in one location for several users to edit, you distribute separate copies of the shared workbook to each user. Then, after their edits are done, they send the changed workbooks back to you, and you merge the workbooks together. (this is NOT the same as "Consolidation"). Then, you never have the situation where two people have the same copy open at the same time.
To merge multkple books together, click Compare and Merge Workbooks. To add the Compare and Merge Workbooks command to the Quick Access Toolbar, click the Microsoft Office Button Button image, click Excel Options, and then click Customize. In the Choose commands from list, click All Commands, select Compare and Merge Workbooks, click Add, and then click OK.
You should use worksheet protection, to ensure people don't do things that can mess up your merge, such as adding or deleting rows.
Johny. The problem with linking files among them is that the time response in Excel is so bad at times that it would be worst than even trying to share the file. An apparent solution is to try to manipulate sectors of the spread sheet and it works until you need protection on the sheet, which is a huge downfall and dissapointment on Microsoft. The only visible option would be to have a mastersheet where all data is consolidated, and some how administrate users coming to the workbook so they are directed to a zone in the workbook where no protection, or special tools lost by the sharing option is required. Then once is finisthed, put back the data changed or updated into the centralized file. I have done similar type of things in the past and although they are pretty demanding in macros and design, they are for sure a safe option. Of course, with good Access knowledge, you may sure find another solution but this would requied to pay for the Access licences por user.
ACA
Aca, i don't know what you mean by "linking files among them". Who said anything about linking? i don't know what you mean by "time response in Excel is so bad". What time response? i don't know what you mean by "it works until you need protection on the sheet..." Sharing the way i described DOES include protection. I don't know what you mean by "... which is a huge downfall." What about protection is a downfall? Works fine for me. True some functionality is lost in a Shared workbook, but for our purpose the benefits outweigh the drawbacks. Our clients have been using both types of sharing (single book vs multiple book) for months, without a hitch. (btw, might be a good idea to avoid using the word "consolidate" in this context, since consolidate has a special meaning in Excel). -Cheers!
Used one of Debra's VBA solutions for making Excel drop-downs larger by adding a Combo Box. Worked like a charm-Genius!! But, when I share the workbook, the combo-box solution no longer works. I didn't notice VBA being one of the things lost in shared workbooks. Anyone have an idea if there is a solution or workaround. The file needs to be shared because I need to track changes and it's possible more than one user will be in there at the same time. Please advise.
Hi
Not sure if this has been covered, but having major issue with printing shared workbooks - different computers (all macs, but different versions of Excel) print different page breaks, headers ad footers etc. It's a calendar/planner, with twelve monthly worksheets, which automatically transpose onto a Yearly summary sheet.
And no, before you ask, the differences are not relative to the versions - one Excel 2004 user will experience a different print out to another Excel 2004 user with the same workbook. They all have checkboxes cleared under 'Include in personal view'. I've gone round and tried to configure all machines the same way for this particular workbook, but impossible to get them all to print the same way
any ideas - apart from the obvious - get a database - and yes we do have Filemaker and am considering reworking it.
Richard,
Not sure how this impacts printing, but I found I needed to delete all custom views after changing any layout (hidden columns etc.) in order for everyone to see the worksheet in the same way. In 2007 it is found in the View ribbon. Not sure where to find this in 2004. It may not help your printing problem, but it's worth a try.
Jim
we have no corruptions in our shared books. maybe it's because they are short-lived-- we use them to collect data just for one month, then the book is retired. we keep 'em simple-no macros, charts, pivots, etc-- we use them for data-collection only. After collecting all our data, we pull that into an access database and generate whatever reports are needed. For our needs, Shared books works great.
That is the thing, for basic access without controls, passwords, and knowing who access what and when, probably sharing is ok, but when it comes to high level sharing access, then things are different and they simply don't deliver what they are made for. It is like a medication that would fix your stomache but damage your leber, no really that good. Thanks aniway for your help.
Shared workbooks can be made to respond just as reliably as an access DB. I have done so, with more than 8000 SLOC. It took 2 months to build, but it works flawlessly, logs errors, tracks changes, many conditional formats, popups including calendars and userforms that are very aesthetically appealing. One thing to avoid is a large file size and worksheet formulas. I use macros to do everything. And it's sweet. Even hyperlink creation is possible; and who needs conditional formatting when a macro can handle everything. Even better, I have written code that accepts userform inputs to create new custom macros that filter and display data as necessary. No need for Pivots when you can filter and copy data from one xlVeryHidden sheet to a visible sheet, and no one is any the wiser. So much that is possible, if you really want to get it to work.
I may be willing to share thoughts, modules and functions with anyone who is interested.
This workbook does it all, and it never crashes.
For all the "experts" that state this and that negatively of shared workbooks--well, suck it.
Interested in your solution to the Shared Workbook problem....please email me at emarino@npvna.org if you are interested in sharing this with me.
Elena
Will do!
Sharing workbooks works quite well, providing you remove free will from the user. Over the past year or so, I have constantly updated a shared workbook. Slowly but surely removing all routes by which a user could enter data incorrectly or generally screw things up. Now, entering data involves userforms and lots of code to avoid errors and multiple entries in the same cell. It works very, very well and is totally secure. I like Access also and think it is a better tool for the job, but most people do not like Access and do not know how to use it. Hence my work securing shared wookbooks.