If you have access to a shared folder on the Internet or on an attached network, you can create shared workbooks that can be opened and used by several people simultaneously. This powerful feature allows you to distribute the responsibility for creating group tasks, such as revolving product inventories, incoming customer orders, or corporate mailing lists. Excel limits the ways in which you can modify a shared workbook. These limitations are detailed in the online Help under "Shared Workbooks, Limitations Of Shared Workbooks." But you can insert and delete rows and columns, modify worksheet cells, and sort entries based on one or more criteria. The following steps show you how to create and maintain a shared workbook.
If your workbook contains information arranged under uniform headings, you can set it up as an Excel database. See Chapter 22, "Managing Information in Lists."
To use a shared workbook, you need access to a shared folder on the Internet or a computer network. If you or your colleagues don't have access to a shared folder, ask your network system administrator how to get one or how to create one on your own computer.
To learn how to add comments to worksheet cells, see "Entering Comments."
To create a shared workbook that can be used by several users simultaneously, follow these steps:
After you save the workbook, the word Shared appears in the title bar between brackets, indicating that you're now editing a multiuser or shared workbook. As long as the Allow Changes check box is selected on the Editing tab, you won't be able to save formulas in the workbook or modify any cell formatting.
For the shared workbook to operate properly, your co-workers need to open the same copy of the shared workbook from a shared network folder, not separate ones from their own individual hard disks. Users will know the workbook is shared if the word Shared appears in the title bar when it's loaded in Excel.
Once a shared workbook is active, you can monitor it by choosing the Share Workbook command to find out who is using it. To see a list of the users working on the file, follow these steps:
Figure 18-6. To see who is working on a shared workbook with you, use the Editing tab of the Share Workbook dialog box.
To "turn off" the shared workbook feature and disable multiuser editing in a workbook, choose Share Workbook from the Tools menu, click the Editing tab, and deselect the Allow Changes check box.
Don't disable the Share Worksheet feature until each of your users has finished editing the workbook and has saved changes, or you'll lock them out of the file. Closing a shared workbook discards any revision information in the file and prohibits users from saving their changes to the multiuser copy of the workbook, even if you reopen sharing.
If users enter or change data in different cells, each change will be accepted automatically and updated in everyone's workbook as each user saves his or her workbook. The changes to the workbook coming from other users will be highlighted after each save. Moving the cell pointer over each highlight will open a window showing who made the change, as shown in the following illustration:
An interesting problem arises, however, when two or more users change the same cells in different ways. Whose entry for the shared workbook should Excel accept? You have two methods for resolving the conflict.
In the first method, the most recently saved workbook's values replace the values that were entered into the cells on an earlier save. Choose this approach when you feel confident that later changes are always more accurate than earlier changes, such as when entering order numbers or tracking inventory quantities.
The second method enables the user saving the shared workbook to review the conflicting cells and decide whose changes take precedence. The user saving the workbook can accept all his or her own changes, accept all of another's changes, or decide cell by cell. Choose this method if you want to review the accuracy of changes before accepting them, or if you want to give one person's changes precedence over those of another. These choices and other multiuser options are provided on the Advanced tab of the Share Workbook dialog box, shown in Figure 18-7. To customize how your shared workbook handles conflicts in a multiuser environment, use these settings.
When you're ready to examine the list of editing activities in a shared workbook, choose Track Changes from the Tools menu, and choose either Highlight Changes or Accept Or Reject Changes from the submenu. The Highlight Changes command displays a dialog box asking you to specify the editing changes you want Excel to highlight in the workbook, as shown in Figure 18-8. (If your workbook is not currently shared, you can also use the Highlight Changes command to start sharing it as a workbook.)
You can highlight changes that were made at a particular time, by a particular user, or in a particular worksheet range. When you click OK, Excel outlines in blue each modified cell in the workbook that matches your search criteria and places a small triangle in the upper left corner of each affected cell. To see how a highlighted cell was changed, place the mouse pointer over the cell, and Excel will display a comment box containing the user name, date, time, and substance of the edit.
If you want to step through the list of revisions in the workbook and either accept or reject them, use the Accept Or Reject Changes command on the Track Changes submenu. When you choose this command, Excel saves the workbook and then displays a dialog box asking for your search criteria.
Figure 18-7. Customizing how workbooks are shared using the Advanced tab.
Figure 18-8. To have Excel automatically highlight new edits in a shared workbook, use the Highlight Changes command on the Track Changes submenu.
As you do when you choose Highlight Changes, you specify the time, person, and location of the edits you're looking for using the drop-down list boxes in the dialog box. When you click OK, Excel displays the changes one at a time in the Accept Or Reject Changes dialog box. (If more than one user wants to modify a cell, Excel identifies each user and the edits they're requesting.)
To accept an edit and store it in the shared workbook, click the Accept button. To reject the change, click the Reject button. Some cells might have more than one edit, in which case you must click the edit you want to accept. (See Figure 18-9.) After you accept or reject an edit, Excel removes the revision highlighting from that cell.
Figure 18-9. Excel tracks each edit in a shared workbook and lets you accept or reject it. If a change made by another user is inappropriate, you can discard it.
Another method for consolidating changes in a shared workbook is to merge two copies of the workbook together using the Merge Workbooks command on the Tools menu. Merging workbooks is a useful technique when two users are working with slightly different copies of the same file and one user wants to incorporate all the changes the other user has made.
Merging workbooks is a one-step process— you simply choose the Merge Workbooks command and the file you want to merge with the active workbook, and Excel compares the two workbooks and copies any differences to the active file. Unlike the Accept Or Reject Changes command, however, the Merge Workbooks command doesn't give you a chance to compare or sort out the differences between the different copies. Its sole purpose is to update one copy of a workbook with another.
To merge two copies of a shared workbook, follow these steps:
The file you specify for merging must be a copy of the original file that has a unique filename. It must also be saved as shared.
Remember, the Merge Workbooks command doesn't give you a chance to accept or reject changes, so use it only if you want all revisions merged into your original file.