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.
===========================
Filed under: Excel
Comment from Jan Karel Pieterse
Time: November 18, 2008, 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.
Comment from Jon Peltier
Time: November 18, 2008, 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.
Comment from Debra Dalgleish
Time: November 18, 2008, 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.
Comment from Christopher Lynch
Time: November 18, 2008, 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.
Comment from Debra Dalgleish
Time: November 18, 2008, 3:14 pm
Christopher, good point, and Word can probably make better tables!
Comment from Roger Greenwood
Time: November 19, 2008, 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.
Comment from Jan Karel Pieterse
Time: November 19, 2008, 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
Comment from Mark Ryan
Time: November 19, 2008, 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.
Comment from Mariusz Cendrowski
Time: November 19, 2008, 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
Comment from sam
Time: November 20, 2008, 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….
Comment from JP
Time: November 21, 2008, 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.
Comment from Debra Dalgleish
Time: November 21, 2008, 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.
Comment from Giff
Time: November 25, 2008, 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.
Comment from Debra Dalgleish
Time: November 25, 2008, 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.