• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 3. Editing Spreadsheets > Tracking Changes Made by Multiple Users

Tracking Changes Made by Multiple Users

If you work with a team to build, edit, and maintain a workbook or if your workbook passes through several hands on its way to completion, it can be useful to know who made which changes to the workbook content. By using Excel's Track Changes feature, you can see which person added which content, on which date, and at what time. You can also review changes others made, and keep only the changes you want.

Tip from

If you're a one-person editing team, you can track your own changes. Track Changes allows you to track not just who, but when a change was made, and that enables you to track your edits since your last save or after a certain date.

Enabling and Disabling Revision Tracking

Track Changes must be turned on before you can begin tracking the evolution of your worksheet. If it isn't invoked, the changes someone else makes to your sheet will blend in with your content, and only your personal recollections will determine what's been added or changed.

To turn on and use Track Changes, follow these steps:

  1. In the worksheet you want to track multiple users'changes in, choose Tools, Track Changes, Highlight Changes.

  2. The Highlight Changes dialog box opens (as shown in Figure 3.17). Click the Track Changes While Editing check box.

    Figure 3.17. Any edits or new entries made prior to turning on this feature will not be highlighted, nor will any data about their origin be saved or displayed.

  3. Select the amount and type of tracking you want to perform by clicking in the check boxes next to When, Who, and/or Where.

  4. Leave on the Highlight Changes on Screen option so that cells containing new or edited content will be marked as such onscreen.

  5. Click OK to close the dialog box and begin tracking changes.

Tip from

Use the Where option in the Highlight Changes dialog box to specify a range of cells in which to track changes. If a range is specified, Excel tracks changes only within that range.

Figure 3.18 shows a worksheet that has been edited by more than one user. The highlighted cells contain new/edited content, and the ScreenTip indicates who made the changes.

Figure 3.18. Cells containing changes contain a thin blue outline border with a small triangular change indicator in the upper-left corner.

Tip from

The List Changes on a New Sheet option in the Highlight Changes dialog box enables you to store a record of what's been added or changed in your workbook on a separate sheet, so that all changes can be seen in one place. Otherwise, you must scroll through the entire book to find all the changes your team has made.

This list contains only changes made through the date and time you enabled this feature. To update the list of changes, save the workbook and reenable this feature.

You can turn off the Track Changes feature by opening the Highlight Changes dialog box and removing the check mark in the Track Changes While Editing option box. A prompt appears to inform you that your workbook will no longer be in shared mode, which you must confirm by clicking Yes to turn off Track Changes.

Accepting and Rejecting Changes

After others have made their changes to your workbook, you can review the changes, keeping those you want and discarding those you don't:

  1. Choose Tools, Track Changes, Accept or Reject Changes. The Select Changes to Accept or Reject dialog box appears. See Figure 3.19. (Excel might ask you to save your workbook first. If so, click OK.)

    Figure 3.19. Choose whose changes to review, the time interval for which to review changes, and the range of cells for which to review changes.

  2. Decide what to review:

    • In the When area, choose to review either changes you haven't reviewed yet, or changes made after a certain date and time. When choosing a specific date, Excel places the current date in the box. To change the date, type a new one, separating month, day, and year with either a dash or a forward slash. To add a specific time, type one after the date, separating the two with a space.

    • In the Who area, choose to review changes made by all reviewers, all reviewers but you, or any specific reviewer.

    • In the Where area, choose the range of cells to review. If you leave this blank, you'll review the entire worksheet.

  3. Click OK to begin reviewing changes. When Excel finds the first change, it highlights the cell on the spreadsheet with a marquee, and then describes it in the Accept or Reject Changes dialog box (Figure 3.20).

    Figure 3.20. Excel describes the change and lets you choose to accept or reject it.

  4. If more than one change was made to the cell, Excel lists them all. Click the one you want to keep.

  5. Click Accept to keep the change or Reject to keep the cell's original value.

  6. Excel finds the next change. You can continue to accept or reject each change in turn. You can also click Accept All to accept all the remaining changes in the workbook, or Reject All to keep each remaining changed cell's original value. You can also click Close at any time to stop the review process.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint