Let’s say that you asked someone to proofread or collaborate on an Excel workbook with you. And you may want to keep track of the changes, insertions, and deletions that have been made to that shared workbook. You can do that with the help of Excel’s Track changes feature, you can identify who made the changes, and what changes did they make to your shared worksheet/workbook.
Excel Worksheets are not tracked by default, so you would not know what changes have been made, who made them, or when. But when Track changes feature is enabled, Excel will outline revisions done by any user in a workbook. This is a step-to-step guide to track changes in Microsoft Excel.
How to Turn on Track Changes in Excel
Once you are done entering all the data in the worksheet, enable the ‘Track Changes feature’ before sharing the Excel workbook for review. After they finish the review, you can compare your original data with the changed data and then, choose to accept or reject their changes. Then, you can turn off Track Changes.
Remember this is technically a legacy feature, so you won’t see the track changes option in Excel 2019 and 365. You can only find this feature in the Review tab of Excel 2016 and lower versions.
To turn on the track changes in Excel, go to the ‘Review’ tab and click ‘Track Changes’. Then, select ‘Highlight Changes’ from the drop-down.
A Highlight Changes dialog box will open up. In that, check the ‘Track changes while editing’ check box.
Now we have three options When, Who, and Where. ‘When’ box lets us choose since when do want to track the changes, choose between ‘Since I last saved, All, Not yet reviewed, or Since date (Specific date)’. Here we are selecting ‘All’ in the When box.
And we are choosing, ‘Everyone’ in the Who field. You can also choose to only track changes made by specific users or everyone with this option.
If you want the changes to be tracked in the entire sheet, simply leave this box unchecked. check the ‘Highlight changes on screen’ box and click the ‘OK’ button.
Or, If you only want to track changes in only some part of the sheet, then you can specify the cell range. Click on the ‘Where’ box and select the cell range in the worksheet. Then, click ‘OK’.
Click ‘OK’ to save the workbook.
Next, on the ‘Review’ tab, select ‘Share Workbook’ option.
In the Share Workbook dialog box, make sure that the ‘Use the old shared workbooks feature instead of the new co-authoring experience’ checkbox is checked. Then, click ‘OK’.
Finally, select ‘Protect Shared Workbook’ on the Review tab. It will open up Protect Shared Workbook dialog box. In the Protect Shared Workbook dialog box, check the ‘Sharing with track changes’ option to prevent anyone from removing the tracking history. Then, click ‘OK’.
After that save the file and share your spreadsheet to your collaborators for review.
How to View and Accept or Reject Changes
After all your collaborators have reviewed your workbook and made some changes, you can choose to accept or reject those changes.
Now if any change is made to the spreadsheet, this will be shown by a colored cell border and a small triangle in the upper-left corner of the cell.
To check the details, just move your cursor over the cell with the small triangle in the upper-left corner. A comment box is displayed with the what are the changes that were made, who made them, and when it was made. If multiple reviewers made changes to your worksheet, each reviewer will be assigned a different box color.
Go to the ‘Review’ tab and select ‘Track Changes’ from the Changes group. This time, select the ‘Accept or Reject Changes’ option from the drop-down.
In the ‘Select Changes to Accept or Reject’ dialog box, click ‘OK’ to continue.
Then a ‘Accept or Reject Changes’ dialog box will open up, here you can accept changes one by one, or accept/reject all changes at once. Here, the first change that was found on the worksheet will appear first. In our case, the first change that was found was that the value of cell B11 changed from 16.99 to 17.99. Once you accept or reject it, the next change will load-up next.
Upon clicking ‘Accept’, the change that was made will be applied to your worksheet. If you ‘Reject’ it, the change will be reversed.
Creating a Separate File To list Changes History
Instead of seeing the changes in the same worksheet, you can also view the list of changes on a separate worksheet named ‘History’. The history sheet will list every detail about the changes that have been made.
To view history, select ‘Track Changes’ from the Review tab and click ‘Highlight Changes’ from the drop-down.
In the Highlight Changes dialog box, make sure to check the ‘List changes on a new sheet’ checkbox at the bottom before clicking ‘OK’.
This will create a new sheet called ‘History’ which will list every detail about the changes that have been made to the workbook.
Just save the file again to remove the History sheet from the workbook.
How to Turn Off Track Changes in Excel
After you’re finished with the review, you can turn off the track changes feature in the workbook. This will remove all the tracked details (highlights on cells) and will stop tracking any further changes to your workbook.
You need to unprotect the workbook first before you can turn off track changes. To do that, just click the ‘Unprotect Shared Workbook’ option under the ‘Review’ tab.
To turn off track changes, go to the ‘Review’ tab and select Track Changes > Highlight Changes.
In the Highlight Changes dialog box, clear the box next to ‘Track changes while editing’, then click ‘OK’.
Track Changes feature in Excel is now turned off.