Collaborating in Excel

Advanced Training for Microsoft Office Excel 2003

Sharing and Comparing Workbooks

1	To begin turning on workbook sharing, change tracking, and change history, click the Tools menu, and then click Share Workbook.
2	To turn on workbook sharing, in the Share Workbook dialog box, select the Allow changes by more than one user at the same time check box.
3	To view default settings for change tracking and change history, click the Advanced tab, and in the Track changes area, notice that change history will be kept for 30 days. To accept the default settings, click OK.
4	To save the workbook, in the Microsoft Excel message box, click OK, and to close the workbook, click the File menu, and then click Close.
5	To begin opening two reviewed versions of the workbook, on the Standard toolbar, click the Open button, and then in the Open dialog box, double-click the Sales folder.
6	To select the desired workbooks to be opened, click AdDetailsDavid, hold down the CTRL key, click AdDetailsKim, and then click Open.
7	To compare the two reviewed workbooks side by side, click the Window menu, and then click Compare Side by Side with AdDetailsKim.
8	Notice that both workbooks can be seen side by side. To close the workbooks after comparison, hold down the SHIFT key, click the File menu, release the SHIFT key, and then click Close All.
9	To open the master workbook, on the Standard toolbar, click the Open button, click AdDetailsKaren, and then click Open.
10	To compare and merge workbooks, click the Tools menu, and then click Compare and Merge Workbooks.
11	To select the files to merge, click AdDetailsDavid, hold down the CTRL key, click AdDetailsKim, and then click OK.
12	To begin highlighting the changes made to the workbook, click the Tools menu, point to Track Changes, and then click Highlight Changes.
13	To highlight the changes made by everyone, clear the When check box, and then click OK.
14	Notice that the changes made by Kim and David are highlighted in the workbook. To save your changes, on the Standard toolbar, click the Save button.

Managing Comments

1	To begin inserting a comment, click the Insert menu, and then click Comment.
2	To enter text in the comment box, for purposes of this exercise, please press SPACEBAR, and wait as Remember to figure in our two new lines is entered in the comment box.
3	To view the comment indicator for cell D3, click cell F5.
4	To view a comment box, position the pointer over cell D3.
5	To insert a comment in cell F5, click the Insert menu, and then click Comment.
6	To begin locating all cells with comments, click cell G7, click the Edit menu, and then click Go To.
7	To select cells with comments, in the Go To dialog box, click Special, and then click OK.
8	To clear the selection of the comments, click cell D8.
9	To begin editing an existing comment, click cell F5, click the Insert menu, and then click Edit Comment.
10	To select part of the comment text, hold down the SHIFT key, and click to the left of 2006.
11	To edit part of the comment, press SPACEBAR, and wait as 2005 is entered for you in the comments box, and then click cell D5.
12	To delete a comment, click cell D3, click the Edit menu, point to Clear, and then click Comments.

Tracking and Managing Changes

1	To end the review, on the Reviewing toolbar, click End Review, as indicated, and then in the Microsoft Excel message box, click Yes.
2	To clear the change history and remove the workbook from shared use by reviewers, in the Microsoft Excel message box, click Yes.
3	Notice that the Reviewing toolbar has been removed. To turn on change tracking in the workbook, click the Tools menu, point to Track Changes, and then click Highlight Changes.
4	To track the changes to the workbook while editing, in the Highlight Changes dialog box, select the Track changes while editing check box.
5	To track all the changes to the workbook, clear the When check box.
6	To highlight the changes on the screen, verify that the Highlight changes on screen check box is selected, and then click OK.
7	To save the workbook and close the Microsoft Excel message box, click OK.
8	To make changes to the workbook, verify that cell D3 is selected, and for the purposes of this exercise, press SPACEBAR to have a value typed for you, and then press ENTER.
9	To view the comment about the details of the change, point to cell D3. To make another change, verify that cell D4 is selected, press SPACEBAR to have a value typed for you, and then press ENTER.
10	To save the changes to the workbook, on the Standard toolbar, click the Save button.
11	To display the Highlight Changes dialog box, click the Tools menu, point to Track Changes, and then click Highlight Changes.
12	To display an itemized record of the changes to the workbook since it was last saved, in the Highlight Changes dialog box, select the List changes on a new sheet check box, and then click OK.
13	Notice that a new worksheet named History has been created in the workbook. To display the Sheet1 worksheet, click the Sheet1 tab, and then click cell A1.
14	To begin accepting or rejecting the changes, click the Tools menu, point to Track Changes, and then click Accept or Reject Changes.
15	In the Select Changes to Accept or Reject dialog box, verify that the When check box is selected and that Not yet reviewed is displayed in the When text box. To display the first change, click OK.
16	Notice that the Accept or Reject Changes dialog box is displayed with the first change described in the body of the dialog box. To accept the change, click Accept.
17	To reject the second change and restore the cell to its previous value, click Reject.
18	Notice that the value in cell D4 has been removed and the History worksheet has been deleted. To save your work, on the Standard toolbar, click the Save button.

Protecting Workbooks and Worksheets

1	To begin adding password protection to a workbook, click the File menu, and then click Save As.
2	To set a password for the workbook, in the Save As dialog box, click the Tools menu, as indicated, and then click General Options.
3	To assign a password for opening the workbook in the Save Options dialog box, for the purposes of this exercise, press SPACEBAR to have the password typed for you in the Password to open box, and then click OK.
4	To confirm the password in the Confirm Password dialog box, press SPACEBAR to have the password typed for you in the Reenter password to proceed box, and then click OK.
5	To save your changes to the PriceList workbook, in the Save As dialog box, click Save, and then click Yes in the Microsoft Office Excel message box.
6	To verify the changes made to the workbook, click the Close button of the PriceList workbook, and on the Standard toolbar, click the Open button, click PriceList, and then click Open.
7	Notice that you are prompted for the password to open the workbook. Press SPACEBAR to have the password typed for you in the Password box, and then click OK.
8	To password-protect the Furniture worksheet, click the Furniture sheet tab, click the Tools menu, point to Protection, and then click Protect Sheet.
9	To assign a password in the Protect Sheet dialog box, press SPACEBAR to have the password typed for you in the Password to unprotect sheet box, and then click OK.
10	To confirm the password in the Confirm Password dialog box, press SPACEBAR to have the password typed for you in the Reenter password to proceed box, and then click OK.
11	To verify the protection, click cell A1, and press DELETE.
12	Notice the Microsoft Excel message box, notifying you that the cell is protected. To begin unprotecting the worksheet, click OK, click the Tools menu, point to Protection, and then click Unprotect Sheet.
13	To unprotect the worksheet by typing the password in the Unprotect Sheet dialog box, press SPACEBAR to have the password typed for you in the Password box, and then click OK.
14	To begin password-protecting a specified range of cells, click cell C4, hold down the SHIFT key, and then click cell C15.
15	To password-protect the cells, click the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
16	To create a protected range, in the Allow Users to Edit Ranges dialog box, click New.
17	Notice that the cell reference =$C$4:$C$15 is displayed in the Refers to cells box of the New Range dialog box. To assign a title for this cell range, press SPACEBAR to have the title typed for you in the Title box.
18	To assign a password, click in the Range password box, press SPACEBAR to have the password typed for you, and then click OK.
19	To confirm the password in the Confirm Password dialog box, press SPACEBAR to have the password typed for you in the Reenter password to proceed box, and then click OK.
20	Notice that the range is added in the Allow Users to Edit Ranges dialog box. To protect the sheet, click Protect Sheet, and then in the Protect Sheet dialog box, click OK.
21	To verify the protection, click cell C13, and press DELETE.
22	Notice that you can unlock the range by providing a password in the Unlock Range dialog box. To unprotect the sheet, click Cancel, click the Tools menu, point to Protection, and then click Unprotect Sheet.
23	To begin removing the protection from the Price range, click the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
24	To remove the protection from the Price range, click the row containing Price, click Delete, and then click OK.
25	To begin hiding the formula of a cell, click cell C15, click the Format menu, and then click Cells.
26	To hide the formula in cell C15, in the Format Cells dialog box, click the Protection tab, select the Hidden check box, and then click OK.
27	To apply the changes, click the Tools menu, point to Protection, click Protect Sheet, and then in the Protect Sheet dialog box, click OK.
28	Notice that cell C15 still has the value calculated by its formula, but the formula is not displayed in the Formula Bar, as indicated. To unhide the formula, click the Tools menu, point to Protection, and click Unprotect Sheet.
29	Notice that the formula of cell C15 now appears in the Formula Bar. To save your changes, on the Standard toolbar, click the Save button.