Collaborating with Others

Advanced Training for Microsoft Excel 2002

Sharing a Data List

1	To begin sharing a workbook, click the Tools menu, and then click Share Workbook....
2	In the Share Workbook dialog box, in the Editing tab page, notice that the Who has this workbook open now pane lists only you, Catherine Turner. To activate workbook sharing, click the Allow changes by more than one user at the same time check box, and then click the Advanced tab.
3	Notice that the Keep change history for: box is set to 30 days, and the Ask me which changes win option is selected, which is fine. To accept these default settings, click OK.
4	To save the workbook and clear the message box that appears, click OK.
5	To close the AnnualSalesbyCategory workbook, click the Close button (black X in the top-right corner of the Excel window).
6	To share the AnnualSalesbyCategory workbook via e-mail, on the Windows taskbar, click the Start menu, point to All Programs, and click Outlook Express.
7	To compose a new mail, on the Outlook Express toolbar, click the Create Mail button.
8	To attach a file (the Excel workbook in this case) to the new e-mail message, on the Standard Buttons toolbar, click the Attach button.
9	In the Insert Attachment dialog box click AnnualSalesbyCategory, and click Attach.
10	Notice that AnnualSalesbyCategory.xls appears in the Attach header field of the new mail.To close the message without sending it, click the Close button in the top-right corner.
11	In the message box asking whether you would like to save changes, click No.
12	To close Outlook Express, click the Close button, in the top-right corner.

Managing Comments in a Workbook

1	To begin adding a comment to a cell, click cell D2 to select it.
2	Click the Insert menu, and click Comment.
3	To enter your comment, in the Comment box, type Check data, and then click cell D4.
4	To view the comment, move and hold the mouse pointer over the cell that contains the red comment flag, cell D2.
5	To begin editing the comment in cell D2, click cell D2, click the Insert menu, and then click Edit Comment.
6	In the comment box, double-click the word Check to select it, type Correct, and then click cell D4.
7	To view the edited comment, move and hold the mouse pointer over cell D2 again.
8	To delete the comment in cell D2, click cell D2, click the Edit menu, point to Clear, and then click Comments.

Tracking and Managing Changes in a Workbook

1	To turn on change tracking, click the Tools menu, point to Track Changes and then click Highlight Changes....
2	To track changes while editing the workbook, click the Track changes while editing check box.
3	To have Excel track all changes, the When, Who, and Where check boxes must be clear, so click the When check box to clear it, and then click OK.
4	In the message box warning you that the workbook will be saved, click OK.
5	To edit a cell, click cell D3, type 235000, and press ENTER.
6	To edit cell D4, type 300000, and press ENTER.
7	Notice that the changed cells are outlined in color, and a flag appears in the upper-left corner of each. To view information about the last change made, hold the mouse pointer over cell D4.
8	To save your work, on the Standard toolbar, click the Save button.
9	To have Excel list all the changes made to the workbook, click the Tools menu, point to Track Changes, and then click Highlight Changes....
10	To have the list of changes appear in a new worksheet, in the Highlight Changes dialog box, click the List changes on a new sheet check box to insert a check mark, and then click OK.
11	Notice that a new worksheet, named History, lists all changes made to the workbook so far. To return to Sheet 1, on the tab bar of the workbook, click the Sheet1 tab.
12	To accept or reject the changes made to the workbook, click the Tools menu, point to Track Changes, and then click Accept or Reject Changes....
13	Notice that the default settings in the Select Changes to Accept or Reject dialog box allow you to review all changes that have not yet been reviewed. To accept these settings, click OK.
14	Notice that the History worksheet has been deleted and the Accept or Reject Changes dialog box lists the first change.To accept the change, click Accept.
15	Notice that the second change appears in the Accept or Reject Changes dialog box.To reject this change, click Reject.
16	Notice that the value in cell D4 has been removed. To save your work, on the Standard toolbar, click the Save button.

Identifying Which Revisions to Keep

1	To merge three copies of a workbook, compare the changes made to each copy, and decide which revisions to keep, click the Tools menu, and then click Compare and Merge Workbooks.
2	In the Select Files to Merge Into Current Workbook dialog box, click AnnualSalesbyCategory_SalesManager, press and hold down the CTRL key, click AnnualSalesbyCategory_PurchaseManager, release the CTRL key, and then click OK.
3	To view change flags and colored borders around all cells that have been revised in any of the three copies of this workbook, click the Tools menu, point to Track Changes, and then click Highlight Changes.
4	To highlight all changes made at any time, in the Highlight Changes dialog box, click the When check box to clear it, and then click OK.
5	Notice that in the upper-left corner of two cells there are change flags, indicating changes to these cells. To save the merged workbook, on the Standard toolbar, click the Save button.
6	To accept or reject changes to the worksheet, click the Tools menu, point to Track Changes, and then click Accept or Reject Changes.
7	To view all changes that have not yet been reviewed, in the Select Changes to Accept or Reject dialog box, make sure that the When check box contains a check mark, Not yet reviewed displays to its right, and the Who and Where check boxes are unchecked, then click OK.
8	Notice that in the Accept or Reject Changes dialog box, the Select a value for cell F5 box contains a list of changes to the cell. To accept the value inserted by you, Catherine Turner, click the change starting with $265,000.00, and then click Accept.
9	To accept the next revision, changing cell F4 from $155,000.00 to $175,000.00, in the Accept or Reject Changes box, click Accept.

Protecting Workbooks and Worksheets

1	To begin protecting the workbook with a password, click the File menu, and then click Save As....
2	To view your options for saving the workbook, at the top of the Save As dialog box, click the Tools menu head, and then click General Options.
3	To specify the password required to open the workbook, in the Save Options dialog box, in the Password to open box, type ca1th, and then click OK.
4	To confirm the password that you have entered, in the Confirm Password dialog box, in the Reenter password to proceed box, type ca1th, and then click OK.
5	To finish saving the workbook, click Save.
6	In the message box warning that this workbook will replace the previous version of DailySalesbyHour in the Garden Company folder, click Yes.
7	To select the March worksheet for protection, click the March sheet tab.
8	To prevent users from modifying the current worksheet without first entering a password, click the Tools menu, point to Protection and then click Protect Sheet.
9	To specify the password that will allow a user to modify the worksheet, in the Password to unprotect sheet box, type ca2th, and then click OK.
10	To attempt to modify the protected worksheet, click cell D6, and type 2.
11	A warning box informs you that the worksheet you are trying to modify is protected. To close the warning box, click OK.
12	To remove the password and unprotect the March worksheet, click the Tools menu, point to Protection, and then click Unprotect Sheet.
13	In the Unprotect Sheet dialog box, in the Password box, type ca2th, and then click OK.
14	To select a range of cells for protection, click cell D20, press and hold down the SHIFT key, click cell J20, and release the SHIFT key.
15	To password-protect the selected range so that, even when the March sheet is protected, a user who enters the password for this range can edit the cells in this range, click the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
16	In the Allow Users to Edit Ranges dialog box, click New.
17	Notice that in the New Range dialog box, the selected range already appears in the Refers to cells box.To name the selected range and then specify a password to protect it, in the Title box, type Day 15, press the TAB key twice, in the Range password box, type ca3th, and then click OK.
18	To accept the new protected range, in the Allow Users to Edit Ranges dialog box, click OK.
19	To protect the worksheet again, click the Tools menu, point to Protection, and then click Protect Sheet.
20	To specify the password for worksheet protection, in the Password to unprotect sheet box, type ca2th, and then click OK.
21	To attempt to edit a cell within the protected range, click cell D20, and type 2.
22	To allow editing of the protected range of cells, in the Unlock Range dialog box, in the Enter the password to change this cell box, type ca3th, and then click OK.
23	To remove protection from the worksheet so that you can change the format of a cell, click the Tools menu, point to Protection, and then click Unprotect Sheet.
24	In the Unprotect Sheet dialog box, in the Password box, type ca2th, and then click OK.
25	To select cell Q38 which contains the Grand Total, click once to the right of the scroll box in the horizontal scroll bar, and once below the scroll box in the vertical scroll bar, and then click cell Q38.
26	To hide the formula in cell Q38, click the Format menu, and then click Cells.
27	In the Format Cells dialog box, click the Protection tab, click the Hidden check box to insert a check mark, and then click OK.
28	To protect the worksheet and finish hiding the formula in cell Q38, click the Tools menu, point to Protection, click Protect Sheet.
29	To protect the worksheet but allow it to be unprotected without typing a password, in the Protect Sheet dialog box, click OK.
30	Notice that cell Q38 still has the value calculated by its formula, but the formula no longer displays in the formula bar.To view the formula once again, click the Tools menu, point to Protection, and then click Unprotect Sheet.