Sharing Workbooks: A Working Exercise

A shared workbook allows several people to edit the workbook simultaneously, even across the Internet. Among other uses, shared workbooks can be particularly useful for managing lists that change frequently. For example, if the people in your workgroup each handle several projects and need to know each other's status, the group could use a list in a shared workbook where each person enters and updates a row of information for each project.

The original author of a workbook can share that workbook by turning on the Sharing Attribute as shown below:

It should be noted that some Microsoft Excel features can be viewed or used but not changed once the workbook is shared, therefore you may want to set up these features before you share the workbook. The following features can't be changed after a workbook is shared:

·         Conditional Formats
·         Data Validation
·         Charts
·         Pictures
·         Objects
·         Drawing objects
·         Hyperlinks
·         Scenarios
·         Outlines
·         Subtotals
·         Data Tables
·         PivotTable reports
·         Workbook and worksheet protection
·         Macros
·         Merged Cells 

When you share the workbook, you can customize some sharing features. For example, you can decide whether to keep track of changes for the default 30 days or for a longer or shorter amount of time. The Share Workbook dialog box is shown below:

After you open a shared workbook, you can enter and change data as you do in a regular workbook. In the screen below for example, the user has changed cell A7 to read “Columbus” and cell C7 to read “January”.

Please note that a few aspects are different from working in a regular workbook. Specifically:

1.   Merged Changes - Each time you save the shared workbook, the Excel workbook is automatically updated with any changes that others have saved since the last time you saved. If you want to keep the shared workbook open to monitor progress, you can have Microsoft Excel update you with changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.

2.   Resolving conflicts - When you save changes to a shared workbook, another person who's currently editing the workbook might have saved changes to the same cells. In this case, the changes conflict, and a conflict resolution dialog box appears that allows you to decide whose changes to keep. This is what the resolution dialog box looks like:

3.   Personal views - Excel saves a custom view of the shared workbook for you that includes things like which worksheet you have displayed and your zoom settings. By default your view includes any filter and print settings you make, or you can specify that you want to use the original filter and print settings. Each time you open the shared workbook, Excel displays it with your view in effect, so that each user can have his or her own settings.

All users with access to the network share have full access to the shared workbook, unless you use the Protect Sheet command (Tools menu, Protection submenu) to restrict access.  The users who will edit the shared workbook need Microsoft Excel 97 or later.

Once a Share workbook is enabled, Microsoft Excel will maintain and display information about how a worksheet has changed. The “Tracking Logs” will compile details about workbook changes each time you save a workbook. You can use this history to understand what changes were made, and to accept or reject revisions. This capability is particularly useful when several users edit a workbook. It's also useful when you submit a workbook to reviewers for comments, and then want to merge input into one copy, selecting which changes and comments to keep. To access this functionality, choose TOOLS – TRACK CHANGES as shown below: 

When you view the change history the information includes the name of the person who made each change, when the change was made, and what data was changed, either directly on the worksheet or on a separate History worksheet. The History worksheet also how conflicts were resolved. 

Change tracking is available only in shared workbooks (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users. You must have Excel 97 or later to modify a shared workbook.)

Some types of changes are not tracked such as formatting changes. The history is kept only for a set interval   When you turn on change tracking, the history is kept for 30 days. This limit keeps workbook size manageable. You can increase or decrease the number of days of history to keep. If you want to keep the history indefinitely, you can specify a large number of days, or you can make periodic copies of the history information.

Excel determines what history is kept by counting back from the current date. Each time you close the workbook, Excel erases any part of the change history that is older than the number of days in effect the last time the workbook was saved. For example, if you're keeping 30 days of change history, and you open a workbook for the first time in two months, you'll be able to view the history from two months ago. However, when you close this workbook, the history from 31 to 60 days ago is deleted. If you turn off change tracking or stop sharing the workbook, all change history is permanently deleted.

Excel can step you through the changes in sequence using a dialog box that lets you decide whether to accept or reject each change. This method is useful when you're evaluating and working with comments from others.

I find this to be a very useful tool not only for edits made by multiple users, but also when I am the only person editing the workbook. In this case Excel keeps tracks of the changes I have made, and lets me reject or approve them later as part of a formal review process. I hope this feature works well for you too.

If you want to give this a try yourself, you can download my example Excel 2002 file here:

http://www.exceladvisor.net/sub/creative/web-shared.xls

- END -