How to track changes in Excel

This tutorial explains how to track changes in Excel: highlighting onscreen changes, listing changes on a separate sheet, accepting/rejecting changes, and monitoring the last modified cell. This is particularly useful for collaborative work, especially during final revisions.

Think of it like using a red pen on a printed document; Excel's Track Changes feature provides electronic review, acceptance, and rejection of edits. The Watch Window further helps monitor recent changes.

Excel Track Changes: The Fundamentals

Excel's built-in Track Changes allows review of edits within the worksheet or on a separate sheet, with individual or bulk acceptance/rejection of changes. However, keep these points in mind:

1. Shared Workbooks Only: Track Changes functions solely within shared workbooks, enabling simultaneous editing by multiple users. Note that sharing limits some Excel features (conditional formatting, data validation, sorting/filtering by format, cell merging, etc.).

2. Tables and XML Maps: If the Track Changes button is disabled, your workbook likely contains tables or XML maps, which are incompatible with shared workbooks. Convert tables to ranges and remove XML maps to enable the feature.

3. No Undo: Unlike Word, Excel's Track Changes doesn't allow reverting changes. It's a log of edits; you manually review and choose which to keep or discard.

4. Limited Change Tracking: Not all changes are tracked. Cell value edits are recorded, but formatting, row/column hiding/unhiding, and formula recalculations are not.

5. Default 30-Day History: Change history is stored for 30 days by default. While a workbook opened after 40 days shows a 40-day history, closing the workbook removes any history older than 30 days. This duration is customizable.

Tracking Changes in Excel

Let's explore enabling and using this feature:

Enabling Track Changes:

1. On the Review tab, in the Changes group, click Track Changes, then select Highlight Changes....
2. In the Highlight Changes dialog:
   • Check Track changes while editing. This also shares your workbook.
   • Set the desired time period (When) and user (Who). (The default settings are shown below.)
   • Select Highlight changes on screen.
   • Click OK.
3. Allow Excel to save the workbook.

Edits are highlighted in different colors per user. New changes are highlighted as you type. For shared workbooks, the List changes on a new sheet option becomes available.

Onscreen Change Highlighting:

With onscreen highlighting enabled, Excel shades changed rows and columns in dark red. Individual cell edits from different users are marked with colored borders and triangles. Hover over a cell for details.

Viewing Change History on a Separate Sheet:

1. Share the workbook (Review > Changes > Share Workbook > check Allow changes by more than one user at the same time).
2. Enable Track Changes (Review > Track Changes > Highlight Changes).
3. In the Highlight Changes dialog, configure the settings (recommended settings shown below), check List changes on a new sheet, and click OK.

A new "History" sheet lists tracked changes, including timestamps, authors, data changes, and acceptance status. Conflicting changes (multiple edits to the same cell) show "Won" and "Lost" actions. The History sheet only reflects saved changes.

Accepting or Rejecting Changes:

Go to Review > Changes > Track Changes > Accept/Reject Changes. Configure the dialog to select changes to review, then accept or reject individually or en masse. Accepted/rejected changes remain highlighted; disable Track Changes to remove highlighting.

Customizing Change History Duration:

1. Review > Changes > Share Workbook.
2. On the Advanced tab, set the desired duration under Keep change history for.

Disabling Track Changes:

Clear the Track changes while editing checkbox in the Highlight Changes dialog. This permanently deletes the change history. Save the History sheet separately if you need to retain it.

Tracking the Last Changed Cell:

Use the CELL function with the Watch Window. Enter =CELL("address") and =CELL("contents") in empty cells. Add these cells to the Watch Window (Formulas > Formula Auditing > Watch Window > Add Watch...). The Watch Window displays the last changed cell's address and value across open workbooks.

The above is the detailed content of How to track changes in Excel. For more information, please follow other related articles on the PHP Chinese website!