This tutorial will give you the ins and outs of Track Changes Feature in Excel. Everything from what Track Changes is and what it does, how to turn it on and off and everything that you can do with the tracked changes is right here. We also clear up the confusion between comments and Track Changes.
If your spreadsheet has multiple users, changes in the file may be inevitable. You can protect the sheet and lock the cells but your sheet needs constant updates. You are fine with those updates but it won’t hurt if the other users had your approval, right? Well, Track Changes is the feature with proof of change and electronic approval. All changes made to the data will be highlighted and left up to you to take up or turn down. Ready to learn how it’s all done?
Let’s get tracking!
What is Track Changes Feature in Excel
Track Changes in Excel allows users to review changes made to the worksheet. These changes can be viewed as highlighted on the worksheet and also enlisted in a separate sheet. The changes can be accepted or rejected, switching the rejected changes back to the original values.
Learn how Track Changes works and what it does right ahead.
How Does The Track Changes Feature Work?
Once Track Changes has been enabled, any changes made to the cells are tracked. If you have turned on Track Changes with the settings mentioned above, this is how a change made to a cell value will appear:
Note the current value of cell G13:
After changing the date from the 7th to 8th, the change is marked by surrounding the cell with a dark blue border with a small blue arrow on the top-left corner:
Hover the cursor over this cell to see the infotip related to the change made to the cell:
The infotip displays the author, date and time of the change, the location of the changed cell, and the value of the cell before and after the change.
The color of the column header of the changed cell goes from black to purple:
- All changes are denoted by the same blue border and the change in color of the column header. Changes made by other users are denoted by different color borders.
- Only manual changes will be tracked. Automatic changes in cell values as a result of formulas will not be marked.
- Track Changes only applies to shared workbooks. When Track Changes are enabled, note that the previously grayed-out Unshare Workbook button is now also enabled. This is because Tracked Changes is only applicable to shared workbooks with multiple simultaneous users.
The disadvantage of shared workbooks and henceforth of Tracked Changes is that many other features would be grayed out and unavailable. If you attempt to use Unshare Workbook, Track Changes will also be disabled.
How to Turn Track Changes On In Excel
This depends on the version of Excel you are using. For the older versions, you should find the Track Changes button in the Review tab. And in the later versions, you will have to add Track Changes to your Quick Access Toolbar or the Review tab. We will make the addition to the Review tab. The details on turning Track Changes on for both older and later versions are below.
Note that the Track Changes button will be grayed out if the worksheet contains Excel Tables. If you want to use Track Changes, the tables will need to be converted to ranges.
Latest Versions
In the later versions of Excel after Excel 2016, the Track Changes feature is not directly accessible. That is because the features under Shared Workbook have been replaced with co-authoring due to several limitations. Co-authoring, however, does not include the Track Changes feature.
So if you’re looking to recreate the features under Shared Workbook from the older Excel versions, we will show you how to add them to the Review tab by customizing the ribbon. Track Changes can then be turned on from the Review tab. Let’s start with the steps for customizing the Review tab:
Step 1 – Adding Track Changes to the Review tab by customizing the ribbon:
- In the Review tab, right-click anywhere on the ribbon and select the Customize the Ribbon
The Excel Options window will open:
- Click on the drop arrow under Choose commands from: and select All Commands.
- Select the New Group button from the bottom-right of the window.
- Then select the Rename button to open a small Rename
- In the Display name: field, enter a name for the group such as Track Changes.
- Click on the OK button to close the Rename
- Now from the list of commands on the left, select the following commands and click on the Add > > button after selecting each command to add them to the new group one by one. Read note below.
- Track Changes (Legacy)
- Share Workbook (Legacy)
- Protect Sharing (Legacy)
- If needed, adjust the commands in the new group by clicking and dragging the commands up or down.
- When done, click on the OK command to close the Excel Options
The new group will appear on the ribbon with the added commands:
Note: The other two commands are added only for recreating the Changes group from the older versions. Only add the Track Changes command if that is all you need to use.
Step 2 – Turning Track Changes On:
Click on the Track Changes button in the Track Changes
- Select the Highlight Changes
- In the Highlight Changes window, check the Track changes while editing checkbox. The rest are the default settings which you can change as per preference.
- Click on the OK button when done. Confirm the prompt window by clicking on OK.
Now any changes made will be tracked:
Older Versions
The older versions, up to Excel 2016, have the Track Changes feature accessible in the Review tab. Enabling Track Changes is the same for the older versions as it is for the newer versions. Here’s how to turn on Track Changes for Excel versions up to Excel 2016:
The Track Changes button is already available in the Review tab:
- Select the Review tab > Changes group > Track Changes button > Highlight Changes.
- Check the first checkbox in the Highlight Changes window to turn on Track Changes.
- Click on OK.
Track Changes will have been enabled.
Getting a List of All the Changes
If all the changes are marked on the sheet and there are a lot of them, your worksheet might look like badly done homework checked by a teacher. If it’s becoming difficult separating what are changes and what aren’t, you can get a list of all the changes in a new temporary sheet with all the details of the change infotip also mentioned.
These are the steps to get all the changes enlisted in a new sheet:
- Make sure Track Changes is on before making any changes to the data.
- Save the workbook for any changes made to the data.
- From the Review tab, select the Track Changes button and select Highlight Changes to go to the Highlight Changes
- Select the last checkbox reading List changes on a new sheet.
- Click on OK.
Only saved changes can be enlisted so make sure you have saved the file before attempting to enlist the changes otherwise at this point, you will receive a prompt encouraging to save the file and you will have to do the steps again. The changes will not enlist until the file is saved.
If the file was saved, the changes will be enlisted and you will be redirected to a new tab named History:
Notes:
Bear in mind that the History tab is not dynamic and will not be updated with changes made to the sheet. For enlisting any new changes, the steps will have to be repeated.
The History list is a temporary tab that disappears when the workbook is saved or closed or when Track Changes are disabled. If you wish to keep the history, you can copy-paste the contents of the History sheet to another worksheet and save the workbook.
The History list will only enlist changes made after turning on Track Changes.
Accepting/Rejecting Changes
Tracked changes can be accepted or rejected. The rejected changes are restored and the accepted changes are kept. Here we have the steps to accept or reject changes made under Track Changes:
- Go to the Review tab, open the Track Changes menu and select the Accept/Reject Changes
- Below are the default settings in the Select Changes to Accept or Reject dialog box. You can change these as required.
- Click on the OK command button.
- Accept or reject changes individually or all together using the command buttons in the Accept or Reject Changes dialog box.
If a cell has been changed multiple times, you will also have a choice of which change you want to keep:
Accepted changes keep their changed cell values and values of rejected changes revert to the original value.
We have highlighted a rejected and accepted change each below:
How to Turn Track Changes Off
Turning off Track Changes is as simple as turning it on. The changes borders and History sheet will disappear and changes made to the data won’t be marked anymore. Follow these steps to turn off Track Changes:
- Go to the Review tab and select the Track Changes button and then the Highlight Changes
- Uncheck the Track changes while editing checkbox to disable Track Changes.
- Then click on OK.
As you can see, the highlighted borders have gone. Any changes made to the data won’t be highlighted anymore.
Comments Vs Track Changes
A comment is a note added by the user to a cell. A Track Changes infotip is added automatically to a cell changed while Track Changes is enabled.
Right off, let’s see the obvious visual differences between comments and Track Changes:
When a comment is inserted, the username is added automatically but the note of the comment itself is manually added by the user:
With Track Changes on, when a cell is changed, an infotip is automatically added, displaying the user, the date, time, and location of the altered cell, and the altered and former value of the cell:
A Track Changes infotip comes from the result of changing a cell with the feature on. A comment has no prerequisites for adding.
To enlist all comments in a separate sheet, you need a VBA Macro code. To enlist a complete tracked history of changes, you can use the preset option provided with the Track Changes feature.
Enabling Track Changes disables some features while inserting comments has no indirect impact.
Comments can be added, edited, or deleted easily. An accepted tracked change cannot be reverted.
That was everything on Track Changes. Next, when you're having trouble with anything on Track Changes, come back here for some guidance. We are certain that with our Excel tricks, you’ll become quite the Excel wizards. We’ll be back with more sprinklings of Excel stardust. Remember your magic words!
