Workgroup Features
Sharing Workbooks
One of the most productive features in Excel is the capability to share and revise workbooks with groups of people. Frequently, projects involve several people inputting or verifying data in the same documents.
In Excel 95, you can share your workbooks with other users and track the changes that are made to the workbook; however, there are limitations. The File, Shared Lists command is used to share the workbook and determine who has the file open. With this command, you can enter data, sort data, and add rows or columns, but you cannot change cell formatting or modify formulas. If changes made to the file conflict, you can see a list of conflicts and determine which changes to accept or reject. You continue to have the option of routing the workbook electronically to other users for their review.
In Excel 97 and 2000, you have the ability to truly share a workbook. One person can edit one worksheet in a workbook, and someone else can edit another worksheet simultaneously. When one user saves the workbook, the other users who are sharing it will see the changes made by that user. Different people can access adjacent data in the same worksheet, such as the formulas on a worksheet and the column headings. The shared workbook feature replaces the shared list feature in Excel 95. There are now several options available when you need to coordinate workbook files with groups of people: creating a Binder file, sharing workbooks, tracking changes, and highlighting text.
NOTE
Only Excel 97 and 2000 support the shared workbook feature. If some users in your group are still working with Excel 95, they won't be able to open the shared workbook.
To share a workbook with other users, choose Tools, Share Workbook and the Share Workbook dialog box appears (see Figure 3.44). The Advanced tab contains sharing options, which control how changes to the shared workbook will be tracked. When a workbook is shared, the word [Shared] 1 displays in the title bar for the workbook.
){kind=link}
The Highlight Changes dialog box, shown in Figure 3.45, works with settings on the Advanced tab of the Share Workbook dialog box to control how changes to the shared workbook are tracked. You access the Highlight Changes dialog box by selecting Tools, Track Changes, Highlight Changes.
){kind=link}
Each time you view the shared workbook, you need to specify how you want to review the changes in the Highlight Changes dialog box. The settings are established only for the working session; the next time you view the file, you have to specify your choices again.
Some Excel commands are not available when a workbook is shared:
-
Inserting and Deleting—Blocks of cells cannot be inserted or deleted, although you can insert or delete entire rows and columns. You can insert worksheets, but cannot delete worksheets.
-
Formatting—You cannot apply conditional formats or merged cells after a workbook has been shared.
-
List Manipulation Commands—When you filter data in a shared workbook and select specific print settings, your settings are then saved independently of settings made by other users. Whenever you open the shared workbook, your personal settings appear. The Group and Outline, Subtotals, Tables, and PivotTable and PivotChart Report commands cannot be used when a workbook is shared.
-
Linked or Embedded objects—Charts, pictures, objects, or hyperlinks can't be modified in a shared workbook.
-
Macros—You can run macros that were created only before you shared the workbook. New macros cannot be created.
-
Password Protection—While a workbook is shared, you cannot create, modify, or remove passwords that protect the worksheets or the entire workbook. Any protection that is assigned to a worksheet or workbook prior to sharing the file remains in effect while the workbook is shared.
-
Scenarios—This command is not available while the workbook is shared. Even if you create the scenarios before sharing the workbook, you will not be able to access this command (and hence the scenarios) while the workbook is shared.
-
Data Validation—You can't modify or create data-validation restrictions while the workbook is shared. Any restrictions that are set up prior to sharing the workbook remain in effect.
-
Drawing—All the drawing tools are unavailable while the workbook is shared. Drawn objects such as arrows and text boxes added to the workbook are visible, but the drawn objects cannot be moved or edited while the workbook is shared.
You can overcome these limitations for most of these features by activating (or implementing) the features before sharing the workbook. If active, the features will be available to use after the workbook is shared.
When users make changes to your workbook, their edits are color-coded. When you rest the pointer over a cell that has been modified, information is displayed about who made the change, when the change was made, and what type of change it was.
TIP
Excel identifies who made what changes when in the shared workbook, based on the name of the user. To ensure accurate tracking, each user should establish his or her username before working on the shared book. Instruct each user to choose Tools, Options, click the General tab, and then type his or her username in the User Name box.
You can step through highlighted changes in a shared workbook and accept or reject each change. Information about how conflicts are resolved is now available on the History worksheet (called the Conflicts worksheet in Excel 95). The History worksheet is added to the shared workbook and maintains information about any changes that are replaced by other changes. You can track the history of changes to a shared workbook for a specified amount of time. When you want to view what changes have been made to the workbook, you can display a separate filtered History worksheet.
If you reject a change, you can get it back by finding it in the History worksheet for the workbook.
NOTE
When you remove a workbook from shared use, the history of changes is erased. When you share the workbook again, a new list of changes is created.
Users can attach their comments directly to cells. In Excel 5.0 and 95, these are called Notes. In Excel 97 and 2000, they are called Comments. Figure 3.46 shows a shared workbook 1 with several embedded comments 2. The new Reviewing toolbar 3 is part of the enhanced Comments feature. You can use this toolbar to make sure you've read all the comments by stepping through the comments 4 in a workbook in sequence.
){kind=link}
FIG. 3.44 Share Workbook dialog box.
FIG. 3.45 Highlight Changes dialog box.
FIG. 3.46 A shared workbook with embedded comments.
Merging Workbooks
Instead of sharing a workbook with other users, you can provide separate copies of a workbook to users and then merge the copies, along with any comments or changes, into one workbook file.
Before you distribute copies of the workbook for review and editing, there are things you must do prior to making copies of the file:
-
The workbook from which the copies are to be made must be a shared workbook.
-
All copies must come from the same workbook, but each copy must have a different filename.
-
Before the copies are made, the change history in the original workbook must be turned on. Then, the merge must be completed within the time period you specified to maintain the history. If you aren't sure how long the review process will take, make sure you enter a large number of days, such as 500. Setting the number of days in history can be done from the Advanced tab in the Share Workbooks dialog box.
-
The workbooks either must not have passwords or must have the same passwords.
When workbooks are merged, comments on a cell from different users are identified by individual usernames and merged into a single comment box. Choose Tools, Merge Workbooks to merge several workbooks (see Figure 3.47).
){kind=link}
NOTE
You can use the Merge Workbooks command with the My Briefcase feature to edit a copy of a workbook on your laptop and then merge your changes back into the copy of the workbook on your network or office desktop.
FIG. 3.47 Select Files to Merge into Current Workbook dialog box.
Displaying and Reviewing Embedded Comments
Attaching notes to cells is a feature that has been available for some time. In Excel 97, this feature was renamed Comments. Attaching comments to cells is especially useful in forms or templates inside which other people will be inputting data. Comments are a means of preventing errors and providing explanations in worksheets.
Figure 3.48 shows a worksheet with built-in comments. Comments can be attached to any cell. Excel places a red triangle indicator 1 in the upper-right corner of each cell that contains a comment. When you position the mouse pointer in the cell, the comment automatically appears on the screen. The author 2 of the comment is included with the comment and the new Reviewing toolbar 3 has been designed especially for creating and reading comments.
){kind=link}
To create or edit a comment, choose Insert, Comment.
TIP
The bold title in the comment is taken from the User Name box on the General tab of the Options dialog box. You can edit the text directly in the comment in the worksheet, making each title unique to that particular comment.
To print worksheet comments, choose File, Page Setup, and select the Sheet tab in the Page Setup dialog box (see Figure 3.49). Comments can be printed on a separate page 4. If you want to print the comments directly in the worksheet, you must first display the comments by choosing the Comment and Indicator setting on the View tab of the Options dialog box.
){kind=link}
NOTE
The Page Setup dialog box must be accessed from the File menu, not through the Print Preview screen, to choose one of the Comment print options.
FIG. 3.48 Use comments to annotate a form or template file.
FIG. 3.49 Page Setup dialog box.