Tracking changes in Excel is essential for effective collaboration and auditing. With this feature, you can monitor edits made by multiple users, ensuring data integrity and accountability. It allows you to maintain a detailed history of modifications, making it easier to review, accept, or reject changes.
Things to Remember about Supervising changes in Excel
Before you start tracking changes, keep these points in mind:
-
Not all Excel edits are tracked. Cell edits are recorded, but formatting changes, formula recalculations, and column/row adjustments aren’t.
-
You can’t undo changes directly through the change history. You can only manually revert them.
-
Tracking changes requires a shared workbook, which disables some Excel features like sorting, filtering, and certain formatting options.
-
By default, the change history is kept for 30 days. You can adjust this retention period.
-
Workbooks containing tables or XML maps can’t be tracked. Remove these elements before enabling track changes.
How to keep track of changes in Excel
Here’s how to enable and use the track changes feature in Excel.
Method 1: Using a new sheet to track change history in Excel
If you’d prefer to view changes in a separate sheet rather than highlighting them directly in the dataset, Excel offers a feature to generate a new sheet containing a detailed log of all modifications, including timestamps and user names.
Step 1: Go to the Review tab and click the Track Changes button in the Changes group.
Step 2: From the dropdown menu, select Highlight Changes.
Step 3: In the Highlight Changes dialog box, if track changes is already enabled, check only the List changes on a new sheet option.
Step 4: Click Ok.
This action creates a new worksheet named History Sheet with a comprehensive log of all changes, including when and by whom they were made, and the nature of the data modification.
The generated table includes an Action column, which indicates whether a change was Won or Lost. A Won status means the change was kept, while a Lost status indicates the change was overridden by another user.
Ensure your workbook is shared before opening the change history in a new sheet. If not, go to the
Reviewtab, select theShare workbookbutton from theChangessection, and select Allow changes by more than one user at the same time option.
Method 2: Enabling the Track Changes Feature
The Track Changes feature might be hidden in newer Excel versions (Excel 2016, 2019, and Microsoft 365). If the Review tab only shows the Protect group with Comment buttons, follow these steps to enable it:
Step 1: Open your workbook.
Step 2: Go to the File menu and select Options at the bottom left.
Step 3: In the Excel Options dialog box, select Customize Ribbon.
Step 4: Under Choose commands from, select All Commands.
Step 5: Under the Main Tab list, find and select Review Tab.
Step 6: Select New Group at the bottom.
Step 7: A new group will appear under the Review tab; right-click it to rename it.
Step 8: In the Rename dialog box, enter the name Track Changes and click Ok.
Step 9: From the Commands list, select the following four commands and drag them into the Track Changes group:
Track Changes (Legacy)Compare and Merge Workbooks (Legacy)Share Workbook (Legacy)Protect Sharing (Legacy)
Step 10: Click Ok to close the Excel options window.
Now, a new group with these four buttons will be visible in the Excel Review tab.
Step 11: Click on Share Workbook to enable the Accept/Reject Changes options.
Step 12: Select the Use the old shared workbooks feature instead of the new co-authoring experience option.
Step 13: Check who has access to the workbook now and add or remove a user.
Step 14: Press Ok.
Method 3: Turning On Track Changes
By default, the tracking changes feature is off. To activate it:
Step 1: Go to the Review tab and click the Track Changes button from the Changes group.
Step 2: From the dropdown menu, select Highlight Changes.
Step 3: The Highlight Changes dialogue box will appear.
Step 4: Check the Track changes while editing. This also shares your workbook. option.
Step 5: Under the Highlight which changes box, select the time and people whose changes you want to see in the Excel sheet. Choose all times and everyone, if required.
Step 6: Check the Highlight changes on the screen option.
Step 7: Click Ok.
Excel will now highlight any changes made to the workbook from this point forward.
If your file isn’t already saved, Excel will prompt you to save it before enabling track changes.
Method 4: Accepting or Rejecting Changes
As the workbook owner, you can accept or reject changes made by others.
Step 1: Go to the Review tab and select the Track Changes button from the Changes group.
Step 2: From the drop-down menu, select Accept/Reject Changes.
Step 3: The Select Changes to Accept or Reject dialog box will appear.
Step 4: Under Which changes, select the criteria for accepting or rejecting changes (e.g., Not yet reviewed).
Step 5: In the Who box, select the users whose changes you want to review (e.g., Everyone).
Step 6: Leave the Where box empty to review changes across the entire workbook.
Step 7: Click Ok.
Excel will then present each change one by one, allowing you to accept or reject each individually, or all at once.
Whether you accept or reject a change, the highlighting will remain until you disable the track changes feature entirely.
Method 5: Highlighting Changes on Screen
When track changes is enabled, Excel highlights changed cells directly in the worksheet.
Excel highlights the row numbers and column letters in a maroon color. The cells are highlighted in different colors depending on who edited them.
Cell borders are colored, and a triangle appears in the top-left corner of changed cells. Hovering over this triangle reveals information about the user and the time of the change.
Method 6: Setting the Duration for Keeping Change History
The default setting is to keep the change history for 30 days. To modify this:
Step 1: Go to the Review tab and select the Share Workbook button from the Changes section.
Step 2: The Share Workbook dialog box will appear.
Step 3: Select the Advanced tab.
Step 4: Under Track Changes, specify the number of days you want to retain the change history.
Step 5: Click Ok.
Method 7: Turning Off Track Changes
To disable the track changes feature:
Step 1: Go to the Review tab and select the Track Changes button from the Changes group.
Step 2: From the dropdown menu, select Highlight Changes.
Step 3: In the Highlight Changes dialog box, uncheck the Track changes while editing. This also shares your workbook. option.
This guide covered how to track changes in Excel, including enabling and disabling the feature, highlighting changes, setting history duration, and accepting/rejecting modifications. You can choose to view changes directly or in a separate sheet.