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
===========================
Hi, Im hoping you still have the Shared Workbook that can be sent out. If so would you please send to
[email protected]
Many thanks for reading.
For all those who read this page, I have a Shared Spreadsheet that several staff will enter their data throughout the day.
Is there a program or formula to add to the spreadsheet (or something easy) that will allow each person to open the spreadsheet up at the same time (as they work in different departments but need to add their data to the sheet) and when they save the sheet, it will not overwrite any other persons data entered if they opened the sheet and was editing on the same Cell/Row or Column and it Auto creates a new line for their work added.
Thanks for reading
Pugg
I have just setup a database for a small company using excel. We all have a common spread sheet which has a different name for each user so they can all have the same file open simultaneously. The data base is stored in a separate spread sheet which is opened and closed by each user. We also have another spread sheet which contains common tables of data. When someone logs on the tables automatically update to their file…so I can make changes offline without having to giver everyone a new copy. I use these tables to define access levels, users, test data, sample types etc. When one person wants to view a record the spread sheet is opened and a dump is performed for a range of records or the user can just search for a single record. If a user wants to change a record then a tag is applied to the record they wish to modify. Then when another user wants to view the same record they are prevented from doing so until the LOCK tag is removed….which occurs after they save the changes to the record or exit without making any changes. Each record has 105 fields and 75 of the fields contain about 30 pieces of data separated by file separators (a bit like CSV file but with my own unique separator). We can handle really very challenging information with data for formatting and rounding. Even can produce a series of rapid reports with individual signatures and different access levels which go straight to e-mail. Tool me about 3-4 months to setup at home and then have spent last year on and off making improvements. Don’t think access could handle what I have setup…was considering using access but think its not flexible enough. Have considered using sharing but have knocked this on the head after reading up on this. Am researching possibility of using CSV files for storing the database to improve access time. I currently have timers in the file opening to help multi use so will wait and try to open the file for a period if a lot of traffic.
Hi E J, I came across this blog while looking for an effective way to make my excel-vba application, a user-friendly and accessible to two (2) users which are the only ones needed to edit, input, modify etc the information contained in it. I got really impressed with all the features that you described in your post although, sadly you did not leave open any possibility to share that amazing outcome with us. Is there a chance that you would give us some hints or clues on how to get similar results on our excel workbooks? – thanks in advance for your kind attention. Best Regards, Charlie.
I work for MDX Technology Ltd. We have a commercial product, MDXT Connect, which includes functionality allowing rectangular ranges of cells to be ‘shared’. We don’t share formatting information, so our functionality is rather more limited than that provided by Excel shared workbooks – but we do share cell values and functions very quickly and efficiently. Our existing customers are mainly investment banks and hedge funds but we’re planning to create a separate product which is focused on general purpose sharing. More details can be found at http://www.mdxtechnology.com/products/mdxt-share. I hope you don’t mind me ‘advertising’ our product here, but I genuinely believe it could be of interest to your readers. We developed our sharing functionality for a client who was suffering badly from the problems with Excel shared workbooks, and they have been delighted with the results.
Mort here Debra from Dallas. Not sure how to phrase my observation / comment. These posts are most helpful, by the way! Here goes. It is not so much that I want to “share” a workbook, in the “share sense” of these posts, but that I want to be able to consolidate multiple additions/revisions from the same workbook that I have sent to several individuals. Is there a way to do that without “sharing” per se? Thank you, and others for that matter! Best week’s wishes!
Hi Mort, I haven’t tried anything like that, but you could take a look at the code in this post on the MrExcel forum.
It might give you some ideas on consolidating the data.
Hi Deb
I’ve visited this page before and appreciate you having created it, but as I dropped-by in passing once again, I have to stop and say that I agree with VBA’s_mac_daddy … I have virtually NO problem with fairly complex shared Excel 2010 workbooks … with the help of some decent VBA coding to support users’ interaction with the various worksheets within those books.
Within the design intent of the product, there is very little I have been unable to get Excel to do for me over the years (it doesn’t make my coffee yet!)
I’ve written two full legacy~SaaS ETL solutions, a large Requirement Traceability application, a workflow transformation application (using several key office apps including Excel), ‘poor-man’s’ performance monitoring applets, Financial data extraction & reporting solutions, a Test Management product (Yes that was a big “shared workbook” application) … you name it.
My harsh opinion is that nay-sayers of Excel capability have not spent sufficient time understanding how the product works and the massive capability contained within it, often with fairly simple VBA support, even to the extent of just recording a macro (‘wizard-built’ VBA!) in some cases.
That said, sometimes there are BETTER ways of solving things than with Excel! (e.g. a database … in some cases)
Sadly I do not have the time to respond to the many, many questions on these types of forum, but if people desperately need help with specific Excel problems, they are welcome to contact me through my Blog or Twitter, but be aware that my time is at a premium so my responses may be slow and bigger challenges may attract a to-be-negotiated fee to solve.
Hi, I’ve ended up here as I’m looking to see if there’s a reason why an Excel 2010 user hasn’t got frozen panes whereas an Office 365 user has. However the 365 user is forced to look at worksheets (correctly) hidden from the 2010 user – so it all evens out. I’m not having much joy so I’m going to do the analogue of turn it off and on again – make it single user then back to multiuser.
As a tool excel certainly appears to get used for tasks where another tool would be more appropriate – particularly databases. But, as has been pointed out many times here, there is a user familiarity and expertise with excel. That expertise, in one tool, does tend to lead to the “To a man with a large enough hammer, everything looks like a nail” scenarios though.