|C H A P T E R 30||Part 5 Using Microsoft Office Throughout Your Organization||Microsoft Office Resource Kit|
|Workgroup Features in Microsoft Excel|
In This Chapter
Security Features in Microsoft Excel
Workbook Merging and Data Consolidation
Workgroup Review of Workbooks
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 to help protect Excel workbooks. This section describes the options available in Excel for helping to protect 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. The level of security in workbooks from previous versions of Excel is not as effective as in the 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. The author of the workbook, who is the user with readwrite access to a workbook, helps control all three levels. The three levels of workbook protection are:
Excel requires the user to enter a password to open a workbook.
Excel prompts the user to enter a password to open the workbook readwrite. If the user clicks Read Only at the prompt, Excel opens the workbook readonly.
Excel prompts the user to open the workbook readonly. If the user clicks No at the prompt, Excel opens the workbook readwrite, unless the workbook has other password protection.
To help protect a workbook file
– 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.
Note Because these 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 helping protect an entire workbook, you can help protect specific elements from unauthorized changes. The elements you can help protect are:
Sheets in a protected workbook cannot be moved, deleted, hidden, unhidden, or renamed, and new sheets cannot be inserted.
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.
Contents of protected cells cannot be edited.
Protected graphic objects cannot be moved or edited.
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.
Definitions of protected scenarios cannot be changed.
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 help protect a specific element in a workbook from unauthorized changes
– or –
To help protect the workbook structure or windows, point to Protection on the Tools menu, and then click Protect Workbook.
You can help protect the change history only if it is not already password protected.
To help protect sharing and change history of a workbook
– or –
If the workbook is not already shared, click Protect and Share Workbook.
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 readwrite 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:
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.
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.
Once cleared, the change history cannot be recovered. To help 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:
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.
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).
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 userlevel 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 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 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 3D references and share or route the workbook. A 3D 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 3D formulas on the consolidation worksheet to refer to the data sources on the detail worksheets.
If you choose not to use 3D references, consolidate data by consistent positions on a worksheet, or by the category of data. Whether you use 3D 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 email, users can also send or route workbooks to their coworkers.
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 upperright 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 email capability can review workbooks by routing or sending them through email, 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:
Macintosh users running one of the following e-mail programs can both route and send workbooks through e-mail:
To route a workbook to other members of your workgroup
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
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."