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.
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 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. 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.
-
Onscreen highlighting Excel can outline changed areas in a different color for each user and display the basic details as a comment when you rest the pointer over each changed cell. Onscreen highlighting is useful when a workbook does not have many changes, or you want to see at a glance what has changed.
-
History tracking Excel can display a separate history worksheet that provides a printable list of change details on which you can filter to find changes of interest. The history worksheet is useful when a workbook has many changes or you want to investigate what occurred in a series of changes.
-
Reviewing of changes Excel can step you through the changes in sequence so that you can decide whether to accept or reject each change. This method is useful when you are evaluating comments from other users.
Turn on change tracking for a workbook
-
On the Review tab, in the Changes group, click Share Workbook.
-
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.
-
Click the Advanced tab.
-
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.
-
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 the workbook.
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.
-
On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
-
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.
-
Under Highlight which changes, select the When check box and then, in the When list, click the option that you want.
-
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.
-
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
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. -
Make sure that the Highlight changes on screen check box is selected.
-
Click OK.
If prompted, save the workbook.
-
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.
-
On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
-
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.
View tracked changes
-
On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
Note If the Track changes while editing check box is not selected, Excel has not recorded any change history for the workbook.
-
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
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 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.
-
Accept and reject changes
-
On the Review tab, in the Changes group, click Track Changes, and then click Accept or Reject Changes.
-
If prompted to save the workbook, click OK.
-
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
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.
-
-
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.
-
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.
-
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.
View the history worksheet
-
On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
Note If the Track changes while editing check box is not selected, Excel has not recorded any change history for the workbook.
-
Under Highlight which changes, select the When check box and then, in the When list, click All.
-
Clear the Who and Where check boxes.
-
Select the List changes on a new sheet check box.
-
Click OK.
-
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.
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 | 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 | 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 | Existing array formulas continue to calculate correctly. |
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:
-
On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
-
Under Highlight which changes, select the When check box, and then in the When list, click All.
-
Clear the Who and Where check boxes.
-
Select the List changes on a new sheet check box.
-
Click OK.
-
Do one or more of the following:
-
Print the history worksheet. To do this:
-
-
Click the File tab, click Print, select the print options you want, and then click the Print button.
-
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.
-
-
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.
-
No comments:
Post a Comment