Excel: track changes

When you edit Excel workbooks in collaboration with others, it’s important to track the changes made and approve or reject corrections. For this purpose, Excel offers a “Track Changes” function, often referred to as the “tracking mode”. This function allows you to highlight changes visually, to log them in a tabular format, and to undo them later if necessary.

Tracking changes in Excel is closely linked to the approval of workbooks. Both functions are often used in conjunction. In newer Office versions, which are tailored to clouds, the function to approve workbooks is no longer provided because they are designed for collaborative working from the get-go.

You have two options to track changes in Excel. We’ll look at both of them here.

A simple option is to select: “Track changes while editing. This also shares your workbook.” and “Highlight changes on screen”. Now the changes are tracked in Excel and highlighted by a comment next to the cell. The workbook is automatically released. With this default setting, all other settings remain unchanged.

The table below shows additional settings and their function:

The following table provides an overview of “Advanced” settings.

How changes are displayed depends on your settings after you activate tracking mode in Excel.

If you uncheck the checkbox “Log changes on a new sheet” in the settings, the “Change History” worksheet will be removed.

In addition to tracking changes in Excel you can use the Excel function CELL to check which cell was changed last. Just enter the following formula into a cell:

Now you can view the address of the cell that was last modified. But beware that when you don’t specify a reference, the information is always output for the cell that was changed last.

Following the review, the changed cells are highlighted, even if you have accepted or rejected the change. Highlighting disappears when you turn off track changes in Excel.

If you no longer want Excel to track changes, proceed as follows:

1. Select the “Review” tab from the main menu.
2. Select “Track Changes” from the “Changes” section.
3. Click on ”Highlight Changes”.
4. Deactivate the checkbox that reads “Track changes while editing…”.

When you turn off track changes, the change history is deleted. However, you can save the history separately beforehand. To do this, you can “Log changes in a new worksheet” as described above and copy the “Change History” worksheet to another Excel workbook.

After deactivating track changes, joint editing of the workbook is no longer possible. A warning message will pop up. If another user saves their version of the workbook after deactivating track changes, previously saved versions will be overwritten.

When tracking changes is enabled, the restrictions for shared workbooks in Excel apply. This means that certain editing options are not possible, e.g., conditional formatting, joining cells, including hyperlinks, using Excel macros and a handful of additional features (see list below). If you want to use an Excel function but it is grayed out, you should check to see whether the title bar of your Excel window says “[Shared]” beside the file name – this may explain why certain functions are not available.

Examples of unsupported Excel functions when track changes in Excel is active include:

• Creating and pasting tables
• Pasting or deleting cell blocks
• Adding or changing conditional formatting
• Deleting worksheets
• Adding or changing of data validation
• Connecting cells or breaking up connected cells
• Creating or changing diagrams or PivotChart reports
• Sorting or filtering by format
• Inserting or changing images or other objects
• The use of drawing tools

Also note that tracking changes doesn’t work the same way as a typical undo function. You cannot reject changes in the reverse order in which they were made. Each change is rejected individually.

Not all changes are logged. Content changes to cells are tracked, but changes such as formatting, showing, or hiding rows/columns, or simply recalculating are not tracked.

Excel’s Track Changes only works in shared workbooks (enabling change tracking automatically shares the workbook, see “Settings” above). In newer versions of Excel like Excel 365, the concept of “shared workbooks” has been replaced by “shared document creation”, where several users can edit the workbook at the same time and immediately see the edits made by others – but this also means that tracking mode cannot be used anymore. This is true, for example, if you have saved the Excel workbook in a shared space such as OneDrive or SharePoint. You can then only track the changes made by other editors directly, but not afterwards.

If the buttons shown to enable tracking changes are not present in your Excel menu (e.g., as is the case with Excel 365), you can add them back to the menu in the following way:

1. Go to File > Options > Customize Ribbon.
2. Select “Review” from the main tabs on the right.
3. Click on “New Group” so that a new group is created under “Review”.
4. Click on the “Rename” option and give the group the name “Changes”.
5. Select the “All Commands” entry in the left window under “Select Commands”.
6. Scroll down the list to “Shared Workbook (Legacy)” (you may have an entry called “Share Workbook ...”). Select this entry and click “Add”.
7. Scroll down the list to “Track changes (Legacy)”. Select this item and click “Add” as well.
8. Confirm the process with “OK”.