How to Track Changes in Excel – Track Change History

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!

Track Changes Feature In Excel

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:

How Does The Track Changes Feature Work

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:

How Does The Track Changes Feature Work

Hover the cursor over this cell to see the infotip related to the change made to the cell:

How Does The Track Changes Feature Work

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:

How Does The Track Changes Feature Work

  • 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.

How Does The Track Changes Feature Work

  • Only manual changes will be tracked. Automatic changes in cell values as a result of formulas will not be marked.

How Does The Track Changes Feature Work

  • 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 Does The Track Changes Feature Work

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

How to Turn Track Changes On In Excel

The Excel Options window will open:

How to Turn Track Changes On In Excel

  • Click on the drop arrow under Choose commands from: and select All Commands.

How to Turn Track Changes On In Excel

  • Select the New Group button from the bottom-right of the window.

How to Turn Track Changes On In Excel

  • Then select the Rename button to open a small Rename

How to Turn Track Changes On In Excel

  • In the Display name: field, enter a name for the group such as Track Changes.
  • Click on the OK button to close the Rename

How to Turn Track Changes On In Excel

  • 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)

How to Turn Track Changes On In Excel

  • 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

How to Turn Track Changes On In Excel

The new group will appear on the ribbon with the added commands:

How to Turn Track Changes On In Excel

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

How to Turn Track Changes On In Excel

  • Select the Highlight Changes

How to Turn Track Changes On In Excel

  • 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.

How to Turn Track Changes On In Excel

  • Click on the OK button when done. Confirm the prompt window by clicking on OK.

How to Turn Track Changes On In Excel

Now any changes made will be tracked:

How to Turn Track Changes On In Excel

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.

How to Turn Track Changes On In Excel

  • Check the first checkbox in the Highlight Changes window to turn on Track Changes.
  • Click on OK.

How to Turn Track Changes On In Excel

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

Getting a List of All the Changes

  • Select the last checkbox reading List changes on a new sheet.

Getting a List of All the Changes

  • 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:

Getting a List of All the Changes

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

Accepting/Rejecting 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.

Accepting/Rejecting Changes

  • Accept or reject changes individually or all together using the command buttons in the Accept or Reject Changes dialog box.

Accepting/Rejecting Changes

If a cell has been changed multiple times, you will also have a choice of which change you want to keep:

Accepting/Rejecting Changes

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:

Accepting/Rejecting Changes

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

How to Turn Track Changes Off

  • Uncheck the Track changes while editing checkbox to disable Track Changes.

How to Turn Track Changes Off

  • Then click on OK.

How to Turn Track Changes Off

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:

Comments Vs 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:

Comments Vs Track Changes

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:

Comments Vs Track Changes

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!

About Ankit Kaul

Ankit is a die-hard fan of Microsoft Excel and has been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'.