Tracking Changes in Your Worksheet

When a workbook is set up to be shared, you and the others involved in sharing the workbook can make as many changes to it as you want. Then you can use the Accept or Reject Changes feature to accept or reject the various changes.

Excel lets you review shared workbooks by tracking the changes you and others make to a shared workbook. There are two ways to track changes:

• Onscreen

• On a printed copy

Accepting or Rejecting Changes

The To Do steps demonstrate how to accept or reject changes in a shared workbook. You start by making a change to a value in a cell so that you can accept or reject it.

To Do: Accept or Reject Changes in a Shared Workbook

1. Click cell G5, type 37000 , and press Enter. This entry makes a change to the value in cell G5.

2. Choose Tools, Track Changes, Accept or Reject Changes. Excel prompts you to save the workbook.

3. Choose OK to save the workbook. Excel opens the Select Changes to Accept or Reject dialog box, as you see in Figure 23.3. Notice that the When box is selected and shows that the workbook is not yet reviewed.

   Figure 23.3. The Select Changes to Accept or Reject dialog box.

   

4. Select the Who check box. In the Who box, you should see Everyone . The other options are Everyone but Me and a list of users who are sharing the workbook with you. For this exercise, leave the Everyone option selected and the default option ( Not Yet Reviewed ) in the When box.

   

   If you want to review changes by all the users, clear the Who check box.

   
   

5. Select the Where check box. In the Where text box, the current cell G6 is highlighted. Click cell G5 on the worksheet. This step enters the cell reference in the Where box and indicates where you made a change to a value in a specific area on the worksheet. You should see $G$5 in the Where text box.

   

   If you want to review changes made to the entire workbook, clear the Where check box.

   
   

6. Click OK. In the Accept or Reject Changes dialog box, read the information about the first change (see Figure 23.4). The information also contains any dependent changes that are affected by the action you take for this change. If necessary, use the scroll arrows to see all the information.

   Figure 23.4. The Accept or Reject Changes dialog box.

   

   To accept the change and clear its marquee, click the Accept button. To undo the change, click the Reject button. If Excel prompts you to select a value for a cell, click the value you want and then click the Accept button.

   For each change, click the Accept or Reject button; alternatively, click the Accept All or the Reject All button to accept or reject the rest of the changes.

7. Click the Accept button. The Accept or Reject Changes dialog box disappears because there are no more changes to review.

   

   You must accept or reject a change before you can advance to the next change.

   
   

When two people work on a shared workbook, if person 1 makes a change to a cell and saves the workbook and then person 2 also saves a copy of the workbook, person 2 sees any changes that person 1 made. The changes are highlighted with a comment indicator in the workbook that shows person 2 the changes that person 1 made, and the changes are accepted as a matter of course.

Tracking Changes Onscreen

When you share workbooks, you can track changes made by users over a number of days. Excel uses the History sheet to show a complete list of changes to the workbook. The History sheet includes the names of the users who made the changes, data that was deleted or replaced , and information about conflicting changes.

To track changes onscreen, you need to ensure that the options in the Track Changes section on the Advanced tab in the Share Workbook dialog box meet your needs.

To prevent change tracking from being removed in a shared workbook, choose Tools, Protect, Protect and Share Workbook. In the Protect Shared Workbook dialog box, click the Sharing with track changes check box to put a check mark in the box. Then click OK.

The next To Do exercise helps you track changes onscreen in the My Database workbook.

To Do: Track Changes Onscreen

1. Choose Tools, Track Changes, Highlight Changes. The Highlight Changes dialog box appears (see Figure 23.5). Note the check box is selected for Track changes while editing. This also shares your workbook. When you turned on workbook sharing, Excel turns on the Track Changes feature.

   Figure 23.5. The Highlight Changes dialog box.

   

2. In the When drop-down list, you can choose

   • Since I Last Saved

   • All

   • Not Yet Reviewed

   • Since Date

   To show a history of all the changes, click the When check box to clear it. Notice that the All option appears, indicating that all changes will be tracked.

3. In the Who drop-down list, you can choose

   • Everyone

   • Everyone but Me

   • Each User 's Name Individually

   Click the Who check box to clear it.

4. In the Where drop-down list, select the cells that will be shown. If you leave this item blank, all cells are included. You want to leave it blank for now to show a history of the changes. Click in the Where check box to clear it. This step also clears the Where text box.

5. Choose List Changes on a New Sheet. This option controls where changes are displayedin this case, onscreen. The option Highlight Changes on Screen is also selected, which tells Excel to display the changes directly on the worksheet.

6. Click OK. Excel inserts a History sheet in the workbook to show a history of the changes, as shown in Figure 23.6. Also, Excel highlights the changes on the screen and shows comments where changes were made.

   Figure 23.6. The History sheet.

   

   The History sheet shows you a detailed record of changes made to a sheet, based on the changes that fit the criteria you selected in the Highlight Changes dialog box (When, Who, and Where).

7. Click the Sheet1 tab. Take a look at cell G5. A dark blue border surrounds the cell, and a blue triangle appears in the upper-left corner of the cell. The border and triangle indicate the cell has been changed.

8. Point to cell G5. A comment explaining the change that was made pops up next to cell G5 (see Figure 23.7).

   Figure 23.7. Changes shown on the worksheet.

   

Tracking Changes in the Printed Copy

You have seen how Excel indicates the changes onscreen and in the History sheet. But those aren't the only places you can track and monitor your changes. Why not print a hard copy of the History sheet? Printing a History sheet is handled just like any other print job.

To print this special sheet, click the History sheet and then click the Print button on the Standard toolbar. Excel prints the History sheet for you. Now you can track changes on a printout. You could punch holes in the printed History sheets and store them in a binder to help everyone keep track of the changes.