Excel Toolbox: Multiple users in one worksheet

 Today’s post represents an item I researched for a client.  I was looking for ways for them to make their day-to-day process more efficient.  This is an easy solution to many users of one worksheet. 

Excel shared workbooks provide a simple, flexible way for a group of people to collaborate on a list or other data-driven project. With a shared workbook, you can design your workflow around familiar Excel features.

Shared workbook projects are easy to adapt to your group’s organization. Unlike a database, an Excel shared workbook doesn’t require that one user take on all the administrative chores. You can choose whether you want to be in charge of granting permissions and administering the project, or you can share management of the workbook equally among workgroup members.

This post describes the kinds of projects where shared workbooks are effective, and provides an overview of how to prepare and manage a shared workbook.

When to use a shared workbook

Shared workbooks work well for tracking status, collecting and summarizing information, and compiling lists. Use a shared workbook when you want to assign each user an area of a worksheet to keep up to date, and everyone needs to see what everyone else is doing. You can also allow users to add onto the end of a list.

An accounting manager who created a shared workbook has different employees assigned to accounts in different regions. As employees determine that payments are overdue, each can add a row to the worksheet. If one employee is responsible for working on 30-day overdue accounts, that employee can update column C as late notices are sent out and payments are received. Another employee who sets up payment schedules with each customer who has a 90-day overdue account could update column E. Everyone can enter comments, identified by red triangles in column A, to provide status information. The accounting manager keeps the workbook onscreen and monitors progress by getting automatic updates of everyone’s changes every few minutes.

Scenarios where a shared workbook might not be the best approach include large, complex worksheet models where the structure and formulas change frequently or take shape over time. Workbooks of this type are easier to maintain when there is a single owner, or users take turns editing and updating the information.

Preparing a workbook to be shared

When you plan a project around a shared workbook, there are several issues to consider while creating and setting up the workbook you’re going to share.

Set up features that can’t be changed after sharing

Several Excel commands and features become partially available or unavailable after you share a workbook. For example, if you include a PivotTable report in the workbook, you’ll need to make all the layout changes and customizations to the report before sharing the workbook. After the workbook is shared, users will be able to view the report but not change it. Similarly, any conditional formatting or data validation rules that you set up before the workbook is shared will continue to work, but you won’t be able to change the settings.

Decide how much change tracking data to keep

Excel can track information about each change to a shared workbook, including who made the change and what kind of change was made. When information is deleted, change tracking records a copy of the deleted data so that you can retrieve it if necessary. By default, Excel keeps 30 days of change history, deleting any information older than 30 days whenever a user closes the shared workbook.

You can specify an interval much longer than 30 days; however, the size of the workbook can grow very large over time and as a result the workbook can become slow to open and save. Alternatively, you can archive the change information periodically, or if you don’t need to track changes or find that you aren’t using change tracking after the workbook has been shared for a while, you can turn this feature off.

Decide whether you want to limit user access

You can use Excel’s protection features to determine who can access which areas of a shared workbook and what features are accessible, but you must set up protection before you share the workbook. If you don’t protect a shared workbook, anyone can override anyone else’s changes, and anyone can stop sharing and edit the workbook exclusively, which disconnects the other users and deletes any stored change history. You may want to omit protection in projects where users share administrative duties.

Protection for a shared workbook is applied in layers. First you apply any worksheet protection you want, then protect the workbook, and lastly protect the sharing and change tracking. Once you protect the workbook, you can’t change worksheet protection; and once you protect the sharing and change tracking, you can’t change either workbook or worksheet protection.

Worksheet protection lets you grant different access for each user by name. You can allow a user to edit only ranges you specify, or allow users to perform some types of commands but not others. Workbook protection affects all users and determines whether sheets can be inserted, deleted, hidden, and unhidden. Protecting the sharing prevents the workbook from being removed from shared use and prevents the change history from being deleted.

Provide instructions for your users

You can put information about how to use the shared workbook right on the worksheet, where it’s easy for users to find. You can use comments, text boxes, or features like data validation to provide instructions about what kind of data you want entered and where it should go.

Choose an accessible location with sufficient space

When you share the workbook you’ll want to save it to a network server or other location where your intended users can open it. Don’t store the shared workbook on a Web server; users won’t be able to edit it there (shared workbooks are saved in Excel format, not HTML). If you’re planning to track changes, make sure there’s enough space available for the file to grow as change history accumulates.

Plan to keep backup copies

Although change tracking can help prevent loss of data, it’s no substitute for backing up the workbook. Make sure you create backup copies of a shared workbook as you would for any workbook that’s in regular use.

Managing a shared workbook

Administration of a shared workbook starts with educating your workgroup about working in the workbook together, then monitoring and managing any problems with conflicting changes or disconnects.

What to tell your users about working in the workbook

Let users know that they must save the workbook for others to see their changes and to see changes from others. Users can each customize several aspects of the shared workbook: they can save and update automatically at intervals they set; they can retain their own filter and print settings or use those established when you share the workbook; and they can display or hide highlighting that identifies their changes and those made by others. 

Dealing with conflicts and unwanted changes

When two users working at the same time try to save changes that affect the same cell, one of the users gets the Resolve Conflicts dialog box and must choose which change to keep in the workbook. You may want to give users some advance information about how to avoid these conflicts or how you want them resolved.

All users of the shared workbook have equal authority to resolve conflicts. If you’re tracking changes, you can review the outcome of any conflict by displaying the History worksheet.

You can also use change tracking to identify and back out changes that were made by mistake or you don’t want to keep. For example, an employee Roger might have deleted some entries from an aged receivables workbook on Tuesday, the day payment was received, instead of flagging the entries as received and waiting for the checks to clear the bank. As Roger’s manager, you could use change tracking to get the deleted entries back. Display the History worksheet, filter it to find all changes made by that employee on the date of receipt, and look for the deletions:

Once you find deleted data, you can copy it from the Old Value column back to your worksheet.

Dealing with disconnected users

Any user can disconnect any other user from a shared workbook at any time. Users can also become disconnected because of server or network access problems. A user who is disconnected won’t be able to save changes in progress to the shared workbook. However, if change tracking is turned on, the user can save the workbook with a new name and then merge the changes back into the original shared workbook. Users who want to take a copy of a shared workbook offline to work on it can use the same approach to merge their changes back in later.

Monitoring progress

You can check who is currently editing a shared workbook and use automatic updates to keep an eye on changes as they happen. When you display the Share Workbook dialog box (Tools menu, Share Workbook command) for a shared workbook that’s in use, the Editing tab lists all users who currently have the workbook open. Use the Update changes section of the Advanced tab to get periodic updates as often as you like. Keep in mind that you’ll see only changes that users have saved; Excel won’t show you changes that users have not yet saved.

Taking the workbook offline for maintenance

Sometimes you may need to remove a workbook from shared use so that you can restructure it, make changes to settings and use commands that are available only when the workbook isn’t shared, or grant permissions to new users by changing protection settings. When you stop sharing a workbook, you disconnect any users who are currently working on the workbook, and you permanently discard the change history. Because the change history is deleted, disconnected users won’t be able to merge their changes back in, even if you re-share the workbook.

Because of these consequences, you’ll want to plan and schedule maintenance so as not to inconvenience users. You’ll want to make sure all users have merged in any changes and closed the workbook, and you’ll want to copy or print the change history if you’re tracking changes, before you stop sharing the workbook.

What if your project outgrows the shared workbook?

Over time, if you keep change history continuously, your shared workbook can become so large that it’s slow to load. If your worksheet is becoming too large to use efficiently, find information in, and maintain; if managing user access becomes time consuming for the number of users of the workbook; or if you or users are constantly having to resolve conflicts, consider converting the workbook to a Microsoft Access database. Access is a good choice for any worksheet list that has become so large and complex that it’s difficult to maintain, because Access gives you true multi-user capability and security features along with several options that can help you to maintain data accuracy and integrity. Access is designed to allow you to organize large amounts of data for efficient storage and retrieval.