In This Chapter
Security Features in Microsoft Excel
Shared Workbooks
Workbook Merging and Data Consolidation
Workgroup Review of Workbooks

See Also

• For a summary of new and improved features in Excel, see Chapter 2, "What's New in Microsoft Office."

• For information about supporting workgroups that are running more than one version of Excel, see Chapter 22, "Supporting Multiple Versions of Microsoft Office."

• For information about the structure of Excel and how its components fit together, see Chapter 36, "Microsoft Excel Architecture."

• For information about designing and implementing Web forms, see Chapter 25, "Web Support in Microsoft Office Applications."

This chapter explains how to administer features in Microsoft Excel 97 for Windows and Excel 98 for the Macintosh that are designed for sharing workbooks among users in a workgroup. It describes how the various workgroup features of Excel work and provides information for supporting them.

As a workgroup administrator, you might initiate or oversee workgroup security practices for protecting Excel workbooks. This section describes the options available in Excel for protecting workbooks. These options are independent of any additional security measures at the operating system level.

Excel incorporates the symmetric encryption routine known as RC4. RC4 is stronger than the encryption routine used in previous versions of Excel, known as Microsoft Office 4.x. Workbooks from previous versions of Excel are not as secure as password­protected workbooks in Excel 97 (Windows) or Excel 98 (Macintosh) format. For more information about Office encryption, see "Security Features in Office" in Chapter 34, "Microsoft Office Architecture."

Note   Strong encryption such as RC4 is banned in France. If a user's locale setting in Regional Settings on the Control Panel is set to French (Standard), that user is not able to open an Office document that is password protected. Nor can the user save an Office document with RC4 encryption. The user can, however, use XOR encryption by saving an Office document with password protection.

Excel supports three levels of workbook file protection. All three levels are controlled by the author of the workbook, who is the user with read­write access to a workbook. The three levels of workbook protection are:

• File open protection

  Excel requires the user to enter a password to open a workbook.

• File modify protection

  Excel prompts the user to enter a password to open the workbook read­write. If the user clicks Read Only at the prompt, Excel opens the workbook read­only.

• Read­only recommended protection

  Excel prompts the user to open the workbook read­only. If the user clicks No at the prompt, Excel opens the workbook read­write, unless the workbook has other password protection.

To protect a workbook file

1. On the File menu, click Save As.

2. Click Options.

3. To have Excel prompt the user to open the workbook read­only, select the Read­only recommended check box.

   – or –

   To require a password to open the workbook, type a password in the Password to open box, and then click OK.

   – or –

   To require a password to save changes to the workbook, type a password in the Password to modify box, and then click OK.

4. When prompted, type the password again, and then click OK.

5. Click Save.

Note   Because protected workbooks are encrypted, they are not indexed by Find Fast. For more information about document indexing and searching, see Chapter 26, "Finding Microsoft Office Documents on the Network."

In addition to protecting an entire workbook, you can protect specific elements from unauthorized changes. The elements you can protect are:

• Structure of a workbook

  Sheets in a protected workbook cannot be moved, deleted, hidden, unhidden, or renamed, and new sheets cannot be inserted.

• Windows in a workbook

  Windows in a protected workbook cannot be moved, resized, hidden, unhidden, or closed. Windows in a protected workbook are sized and positioned the same way each time the workbook is opened.

• Cells on a sheet

  Contents of protected cells cannot be edited.

• Graphic objects on a sheet

  Protected graphic objects cannot be moved or edited.

• Formulas on a sheet

  Protected formulas cannot be edited.

  Tip   You can also hide a formula so that it does not appear in the formula bar but the formula results appear in the cell. On the Format menu, click the Cells command, and then select the Hidden check box on the Protection tab. You must then protect the workbook file.

• Scenarios on a sheet

  Definitions of protected scenarios cannot be changed.

• The change history of shared workbooks

  Protected change histories cannot be cleared by the user of a shared workbook or by the user of a copy of a workbook that is to be merged.

To protect a specific element in a workbook from unauthorized changes

1. To protect cell contents including formulas, graphic objects, or scenarios, point to Protection on the Tools menu, and then click Protect Sheet.

   – or –

   To protect the workbook structure or windows, point to Protection on the Tools menu, and then click Protect Workbook.

2. Select the check boxes for the elements you want to protect.

3. To prevent others from removing protection from the sheet or workbook, type a password in the Password box and click OK, and then retype the password in the Confirm Password dialog box.

Caution   If you or a user in your workgroup assigns password protection to a workbook and then forgets the password, you cannot open the workbook, access its data in another workbook through links, remove protection from the workbook, or recover data from the workbook. Keep a list of your passwords and their corresponding workbook and sheet names in a safe place.

You can protect the change history only if it is not already password protected.

To protect sharing and change history of a workbook

1. Point to Protection on the Tools menu, and then click Protect Shared Workbook.

   – or –

   If the workbook is not already shared, click Protect and Share Workbook.

2. Select the Sharing with Track Changes check box.

3. If you want to require other users to supply a password to turn off the change history or remove the workbook from shared use, type the password in the Password box, and then re­enter the password when prompted.

   This option is available only if the workbook is not already shared.

Excel allows many users to edit the same workbook simultaneously, or to edit copies of a workbook at different times. In both cases, a user with read­write access to the workbook can share the workbook by clicking Share Workbook (Tools menu). This section describes shared workbooks: multiple users editing the same workbook simultaneously. For information about workbook merging — users editing copies of a workbook at different times — see "Workbook Merging and Data Consolidation" later in this chapter.

When a workbook is shared, Excel keeps a change history of the workbook. The change history records who changes cells, when a change is made, and the previous values of an edited cell.

Shared workbooks have the following characteristics:

• Not all Excel commands and features are available.

  For example, you cannot delete a sheet in a shared workbook. For a complete list of limitations when using shared workbooks, see Excel online Help.

• Of the commands and features that are available when working with a shared workbook, not all are recorded in the change history.

  For example, formatting changes are allowed in a shared workbook, but not recorded in the change history. For a complete list of actions that are not recorded, see Excel online Help.

• History of cell revisions is maintained until the Track changes time value on the Advanced tab in the Share Workbook dialog box (Tools menu) expires. The history of revisions older than the Track changes time value is lost, but revisions within the Track changes time value are kept.

• Turning off workbook sharing clears the entire change history.

  Once cleared, the change history cannot be recovered. To protect the change history, use the Protect Shared Workbook command (Tools menu, Protection submenu). For more information about workbook protection, see "Security Features in Microsoft Excel" earlier in this chapter.

Tip   A workbook for which the change history has been enabled grows in size as the change history grows. To conserve disk space, you can enable sharing without enabling the change history. On the Advanced tab in the Share Workbook dialog box (Tools menu), select the Don't keep change history option under Track changes.

Some common uses of shared workbooks include:

• Sharing a list among several users.

  For example, a shared list could be a customer or parts list or a schedule. In this case, different users might need access to any part of the workbook at any time.

• Consolidating data stored on different worksheets within a single workbook.

  For example, different department managers might maintain their budgets on departmental sheets in a workbook, and a division vice president might consolidate the data onto another sheet in the workbook with the Consolidate command (Data menu).

• Summarizing data stored on different rows within a single worksheet.

  For example, different department managers might keep track of personnel headcount on specific rows in a worksheet, and a division vice president might summarize the data — apply an outline and subtotals or averages, for example.

Tip   Excel does not offer the same level of multiuser support as does Microsoft Access. For example, Microsoft Access supports user­level security, but Excel does not. If you have more complex needs for data entry, protection, or reporting than Excel can manage, consider using Access. For information about sharing data between Excel and Access, see "Sharing Information with Microsoft Access 97" in Chapter 27, "Sharing Information with Microsoft Office Applications."

When two or more users of a shared workbook make conflicting changes (for example, entering different values in the same cell), users can either review the conflicts or automatically save their own changes when they save the workbook. Users set this option for themselves in the Conflicting changes between users group on the Advanced tab in the Share Workbook dialog box (Tools menu). If users choose to review changes, they see a dialog box similar to the following when they either save a workbook to which another user has made conflicting changes, or when the Update changes time value is met.

Workbook merging and data consolidation have similar purposes: in both cases, you start with data from multiple sources and end up with a summary of the data in a single workbook.

Workbook merging combines multiple copies of a single workbook. It is better suited for workbooks in which multiple contributors edit any part of the data at any time, or in which a single user edits different versions of the same workbook on different computers. For example, a mobile user might copy a shared workbook to a laptop computer. While the user is gone, other users can continue to edit the shared workbook. Upon returning, the mobile user can merge changes into the original workbook.

Data consolidation combines multiple workbooks. It is designed for summarizing structured data, such as rolling up departmental budgets into a summary budget for a division.

Merging Workbooks

Merging workbooks is similar to working with shared workbooks, but instead of multiple users editing the same workbook simultaneously, multiple copies of a single workbook are reconciled. As with shared workbooks, merging requires you to turn on the change history with the Share Workbook command (Tools menu) before making copies and distributing the workbook. To merge workbooks, use the Merge Workbooks command (Tools menu).

You must turn on the change history in the workbook from the time you create copies of the workbook to the time you merge changes back into the original workbook. Specify the duration of the change history on the Advanced tab in the Share Workbook dialog box (Tools menu). If you do not know how long it will be before you merge changes, set the change history duration to a long period such as 1000 days.

Unless the change history of the workbook is protected, a user can turn it off. If this happens, you cannot automatically merge that user's changes. the change history, use the Protect Shared Workbook command (Tools menu, Protection submenu). For more information about workbook protection, see "Security Features in Microsoft Excel" earlier in this chapter.

Consolidating Data

Consolidating data is similar to merging workbooks, except that it is not necessary to turn on the change history. Use data consolidation when you want to compile repetitive, highly structured data from several subordinate workbooks into one summary workbook.

When you consolidate data on multiple worksheets within one workbook, use consolidation by 3­D references and share or route the workbook. A 3­D reference includes a cell or range reference, preceded by a range of worksheet names, allowing you to analyze data in the same cell or range of cells on multiple worksheets within a workbook. Use 3­D formulas on the consolidation worksheet to refer to the data sources on the detail worksheets.

If you choose not to use 3­D references, consolidate data by consistent positions on a worksheet, or by the category of data. Whether you use 3­D references or position or category references, start consolidating data by clicking the Consolidate command (Data menu).

Excel includes several features that support adding comments to workbooks. If your workgroup has access to e­mail, users can also send or route workbooks to their co­workers.

Tracking Changes and Comments

As with Microsoft Word documents or Microsoft PowerPoint presentations, it is common for many members of a workgroup to revise or annotate an Excel workbook.

The commands on the Track Changes submenu (Tools menu) allow you to record, review, accept, or reject changes to cell values. When you click Highlight Changes (Tools menu, Track Changes submenu), Excel turns on both change history (if it was not already on) and workbook sharing. All changes to cell values are recorded and indicated by a small triangle in the upper left corner of changed cells. The triangles are color coded to distinguish among different users. Pointing to such a cell displays the most recent change made to that cell, similar to displaying a ToolTip for a toolbar button.

Viewing the Entire Change History The change history box of a cell shows only the most recent change made to the cell. To see all changes in the change history, select the List changes on a new sheet check box in the Highlight Changes dialog box (Tools menu, Track Changes submenu). Selecting this option adds a History sheet to the shared workbook that lists all changes recorded in the change history. The list is filtered, so you can easily review all changes that have been made to any cell.

To review the recorded changes, use the Accept or Reject Changes command (Tools menu, Track Changes submenu). This starts a process similar to reviewing revisions in Word: You review each change made to the worksheet, and accept or reject each one.

You can also record comments for any cell in a workbook. (In previous versions of Excel, these were called notes.) To create a comment, click the Comments command (Insert menu). A cell with a comment has a small red triangle in the upper­right corner. Pointing to the cell displays the cell comment in a box, similar to displaying a ToolTip for a toolbar button.

Sending and Routing Workbooks with Electronic Mail

Users in a workgroup who have e­mail capability can review workbooks by routing or sending them through e­mail, with each recipient adding comments to the workbook.

To route a workbook, the sender creates a routing slip, and then routes the workbook to the recipients either one at a time or all at once. The sender can track the status of the routed workbook as the recipients review it. After all of the recipients have reviewed the workbook, it is automatically returned to the original sender.

When sending a workbook through e-mail, the original sender does not create a routing slip. Whether the sender routes or sends a workbook, the workbook is received as an attachment in an e-mail message.

Windows users running one of the following e-mail programs can both route and send workbooks through e-mail:

• Microsoft Exchange (or another 32-bit e-mail program compatible with MAPI)

• Lotus cc:Mail (or another 16-bit e-mail program compatible with Vendor Independent Messaging [VIM])

Macintosh users running one of the following e-mail programs can both route and send workbooks through e-mail:

• Quarterdeck Mail

• Microsoft Mail

• StarNine Mail

To route a workbook to other members of your workgroup

1. On the File menu, point to Send To, and then click Routing Recipient.

2. If prompted, select the user profile you want to use in the Profile Name box.

3. In the Routing Slip box, click Address, select the recipients, and then click OK.

4. In the Message text box, type a message, select the Return when done check box, and then click Route.

Note   Macintosh users running other e-mail programs (for example, Microsoft Outlook Express, Eudora Pro, Eudora Light, and Netscape) can send workbooks through e-mail."

To send a workbook to other members of your workgroup

1. On the File menu, point to Send To, and then click Mail Recipient.

2. Follow the directions on the screen.

Note   If you are routing or sending a workbook to a user who has not yet upgraded to Excel 97 (Windows) or Excel 98 (Macintosh), first save the workbook in a format that the target version of Excel can open, such as Excel 5.0 format. For information about sharing workbooks among different versions of Excel, see Chapter 12, "Upgrading from Previous Versions of Microsoft Excel."

For more information about using Office applications with e-mail, see Chapter 28, "Working with Messaging Systems and Connectivity Software."