Track changes in a shared workbook

By using change tracking, you can track, maintain, and display information about the changes that are made to a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.).

Example of change highlighting and details

In this article


Overview of change tracking

You can use change tracking to log details about workbook changes every time that you save a workbook. This change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) can help you identify any changes that were made to the data in the workbook, and you can then accept or reject those changes. Change tracking is especially useful when several users edit a workbook. It is also useful when you submit a workbook to reviewers for comments, and then want to merge the input that you receive into one copy of that workbook, incorporating the changes and comments that you want to keep.

How change tracking works

Change tracking is available only in shared workbooks. In fact, when you turn on change tracking, the workbook automatically becomes a shared workbook. Although a shared workbook is typically stored in a location where other users can access it, you can also track changes in a local copy of a shared workbook.

When changes are made in the shared workbook, you can view the change history directly on the worksheet or on a separate history worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.). Either way, you can instantly review the details of each change. For example, you can see who made the change, what type of change was made, when it was made, what cells were affected, and what data was added or deleted.

When you use change tracking, you should consider the following:

  • Change tracking differs from undo and backup    You might expect that change tracking creates a backup copy of the workbook before changes are made, or that you can undo any of those changes. Unfortunately, you cannot use the change history to back out of changes by undoing them or by restoring an earlier version of the workbook. However, the history worksheet includes a record of all deleted data so that you can copy that data back to the original cells in the shared workbook. Because change tracking is not designed to help you return to earlier versions of a workbook, you should continue to back up workbooks that have change tracking in effect.
  • Some types of changes are not tracked    Changes that you make to cell contents are tracked, but other changes, such as formatting changes, are not tracked. Some Microsoft Excel features are unavailable in shared workbooks, and therefore cannot be tracked.
  • Change history is kept only for a specific interval    When you turn on change tracking, the change history is kept for 30 days to keep the size of the workbook manageable. However, you can increase or decrease the number of days of change history that you want to keep as needed. To keep the change history indefinitely, you can specify a large number of days. You can also make periodic copies of the history information.
  • Change history is periodically deleted    Excel determines the change history that is kept by counting back from the current date. Each time that you close the workbook, Excel deletes any part of the change history that is older than the number of days that were in effect the last time the workbook was saved.

For example, if you are keeping 30 days of change history, and you open a workbook for the first time in two months, you will be able to view the history from two months ago. However, when you close this workbook, the history from the previous 30 days (31 to 60 days) ago is deleted.

Important    When you turn off change tracking or stop sharing the workbook, all change history is permanently deleted.

Ways to use change tracking

Excel provides the following ways to access and use the stored change history.

Top of Page Top of Page

Turn on change tracking for a workbook

  1. On the Review tab, in the Changes group, click Share Workbook.

The Changes group on the Review tab

  1. In the Share Workbook dialog box, on the Editing tab, select the Allow changes by more than one user at the same time check box.

Tip    This also enables workbook merging.

  1. Click the Advanced tab.
  2. Under Track changes, click Keep change history for and, in the days box, type the number of days of change history that you want to keep.

Note    By default, Excel keeps the change history for 30 days and permanently erases any change history that is older than this number of days. To keep change history for a longer than 30 days, enter a number that is larger than 30.

  1. Click OK and, if you are prompted to save the workbook, click OK to save the workbook.

Note    Turning on change tracking by setting options in the Highlight Changes dialog box (to display this dialog box, on the Review tab, in the Changes group, click Track Changes and then click Highlight Changes) also shares (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) the workbook.

Top of Page Top of Page

Highlight changes as you work

When you highlight changes as you work, Excel marks any revisions (such as changes, insertions, and deletions) with a highlighting color.

  1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

The Changes group on the Review tab

  1. In the Highlight Changes dialog box, select the Track changes while editing check box.

Selecting this check box shares the workbook and highlights changes that you or other users make.

  1. Under Highlight which changes, select the When check box and then, in the When list, click the option that you want.
  2. To specify the users for whom you want to highlight changes, select the Who check box and then, in the Who list, click the option that you want.
  3. To specify the worksheet area where you want changes to be highlighted, select the Where check box and then, in the Where box, type the cell reference of the worksheet range.

Tip    You can also click the Collapse Dialog button Button image  at the right end of the Where box, and then select the range that you want to use on the worksheet. After you finish, click the Collapse Dialog button again to display the entire dialog box.

  1. Make sure that the Highlight changes on screen check box is selected.
  2. Click OK.

If prompted, save the workbook.

  1. On the worksheet, make the changes that you want.

Note    Some changes, such as formatting, are not tracked and therefore are not marked with a highlighting color.

Stop highlighting changes

When you no longer want changes to be highlighted, you can turn off change highlighting.

  1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
  2. In the Highlight Changes dialog box, clear the Track changes while editing check box.

Tip    You can also turn off change tracking to stop and remove change highlighting. For more information, see the section Turn off change tracking for a workbook.

Top of Page Top of Page

View tracked changes

  1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

The Changes group on the Review tab

Note    If the Track changes while editing check box is not selected, Excel has not recorded any change history for the workbook.

  1. To select the changes that you want to see, do the following:
    • To view all changes that have been tracked, select the When check box, click All in the When list, and then clear the Who and Where check boxes.
    • To view changes that were made after a particular date, select the When check box, click Since date in the When list, and then type the earliest date for which you want to view changes.
    • To view changes that were made by a specific user, select the Who check box and then, in the Who list, click the user whose changes you want to view.
    • To view changes to a specific range of cells, select the Where check box, and then type the cell reference of the worksheet range.

Tip    You can also click the Collapse Dialog button Button image  at the right end of the Where box, and then select the range that you want to use on the worksheet. After you finish, click the Collapse Dialog button again to display the entire dialog box.

  1. To specify how you want to view the changes, do the following:
    • To highlight changes on the worksheet, select the Highlight changes on screen check box.

Tip    This enables you to view the details about a change by resting the pointer over a highlighted cell.

  • To create a list of changes on a separate worksheet, select the List changes on a new sheet check box to display the history worksheet.

Note    This check box is available only after you turn on change tracking and save the file with at least one trackable change.

Top of Page Top of Page

Accept and reject changes

  1. On the Review tab, in the Changes group, click Track Changes, and then click Accept or Reject Changes.

The Changes group on the Review tab

  1. If prompted to save the workbook, click OK.
  2. In the Select Changes to Accept or Reject dialog box, do the following:
    • To accept or reject changes that are made after a particular date, select the When check box, click Since date in the When list, and then type the earliest date for which you want to review the changes.
    • To accept or reject changes that are made by another user, select the Who check box and then, in the Who list, click the user whose changes you want to review.
    • To accept or reject changes that are made by all users, clear the Who check box.
    • To accept or reject changes that are made to a specific area, select the Where check box, and then type the cell reference of the worksheet range.

Tip    You can also click the Collapse Dialog button Button image  at the right end of the Where box, and then select the range that you want to use on the worksheet. After you finish, click the Collapse Dialog button again to display the entire dialog box.

  • To accept or reject changes to the entire workbook, clear the Where check box.
  1. Click OK, and then review the information about each change in the Accept or Reject Changes dialog box.

Tip    The information includes other changes that are affected by the action that you take for a change. You can scroll to see all the information as needed.

  1. To accept or reject each change, click Accept or Reject.

Tip    The history worksheet records a rejection with "Undo" or "Result of rejected action" in the Action Type column.

  1. If prompted to select a value for a cell, click the value that you want, and then click Accept.

Notes    

  • You must accept or reject a change before you can advance to the next change.
  • You can accept or reject all remaining changes at once by clicking Accept All or Reject All.

Top of Page Top of Page

View the history worksheet

  1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

The Changes group on the Review tab

Note    If the Track changes while editing check box is not selected, Excel has not recorded any change history for the workbook.

  1. Under Highlight which changes, select the When check box and then, in the When list, click All.
  2. Clear the Who and Where check boxes.
  3. Select the List changes on a new sheet check box.
  4. Click OK.
  5. In the history worksheet, click the filter arrows next to the column labels to find the information that you want.

Note    Saving the workbook hides the history worksheet. To view the history worksheet after saving, you must display it again by selecting the List changes on a new sheet check box in the Highlight Changes dialog box.

Top of Page Top of Page

Changes that Excel does not track or highlight

Change tracking records changes that you make to cell contents, including changes that are caused by moving and copying of data. Row and column insertions and deletions are also included.

However, Excel does not keep track of the following types of changes:

Untracked changes Alternatives
Changed sheet names Sheet name changes are not highlighted on the sheet tabs, but are tracked in the history worksheet.
Inserted or deleted worksheets Changes that are made to a worksheet after it is inserted are tracked, but the insertion itself is tracked only in the history worksheet.
Formatted cells or data None.
Hiding or unhiding of rows or columns None.
Additional or changed comments None.
Cells that change because a formula calculates a new value To find cells that change due to recalculation, you can use the auditing tools on the Formulas tab, in the Formula Auditing group.
Unsaved changes You can highlight changes as they are made, but the changes appear in the history worksheet only after they are saved.

Note    Change tracking is available only in shared workbooks. As a result, features that are unavailable for use in shared workbooks are not tracked as changes. You can access the following features only if you stop sharing the workbook.

Unavailable feature Alternatives
Insert or delete blocks of cells You can insert entire rows and columns.
Delete worksheets None.
Merge cells or split merged cells None.
Add or change conditional formats Existing conditional formats continue to appear as cell values change, but you can't change these formats or redefine the conditions.
Add or change data validation Cells continue to be validated when you type new values, but you can't change existing data validation settings.
Create or change charts or PivotChart reports You can view existing charts and reports.
Insert or change pictures or other objects You can view existing pictures and objects.
Insert or change hyperlinks Existing hyperlinks continue to work.
Use drawing tools You can view existing drawings and graphics.
Assign, change, or remove passwords (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) Existing passwords remain in effect.
Protect or unprotect worksheets or the workbook Existing protection remains in effect.
Create, change, or view scenarios None.
Group or outline data You can continue to use existing outlines.
Insert automatic subtotals You can view existing subtotals.
Create data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) You can view existing data tables.
Create or change PivotTable reports You can view existing reports.
Write, record, change, view, or assign macros You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another workbook that is not shared.
Add or change Microsoft Excel 4.0 dialog sheets None.
Change or delete array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) Existing array formulas continue to calculate correctly.

Top of Page Top of Page

Turn off change tracking for a workbook

Turning off change tracking deletes the change history. To keep a copy of this information, do the following to print the history worksheet or copy it to another workbook:

  1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

The Changes group on the Review tab

  1. Under Highlight which changes, select the When check box, and then in the When list, click All.
  2. Clear the Who and Where check boxes.
  3. Select the List changes on a new sheet check box.
  4. Click OK.
  5. Do one or more of the following:
    • Print the history worksheet. To do this:
  1. Click the File tab, click Print, select the print options you want, and then click the Print button.
  2. Click any other tab to return to your file.
  • To copy the history to another workbook, select the cells that you want to copy, click Copy (Home tab, Clipboard group) or press CTRL+C, switch to another workbook, click where you want to place the copy, and then click Paste (Home tab, Clipboard group) or press CTRL+V.

Tip    When you copy or print the history, you may also want to save or print a separate copy of the shared workbook. If the workbook continues to change as you or other users work on it, the history that you copied may no longer apply. For example, cell locations or row numbers in the copied history may no longer be current.

  1. On the Review tab, in the Changes group, do one of the following:
    • If the shared workbook is protected, click Unprotect Shared Workbook. Then enter the password, if you are prompted, and click OK.
    • If the shared workbook is not protected, click Share Workbook and then, on the Advanced tab, click Don't keep change history.

Top of Page Top of Page

 
 
Applies to:
Excel 2010