How to Track Changes in Excel (with Easy Steps)

This tutorial will demonstrate steps to calculate track changes in Excel. If you work with an Excel file that needs to be updated or reviewed by other people, the option to track changes can come in handy. You can also use it when you’re updating a file and need to keep a track of all the changes you do yourself. This can be helpful when you revisit it sometime later. After going through this article, you will be able to track changes in Excel on your own.

Step-by-Step Procedures to Track Changes in Excel

We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have a dataset of people with their Work Hours in  Column B  and Daily Pay in Column C. At this point, you want to change one data and keep track of this change. Follow the steps below to do so.

Step 1: Turning On Excel Track Changes Feature

By default, this feature is disabled in Excel. So at first, to track change in Excel we have to turn it on. Here are the steps to turn on this feature:

• First, go to the File tab.

• Then, click on the More option to select the Options. In short File > Options.

• Afterward, go to ‘Customize Ribbon’, in the right panel with ‘Main tabs’ find Review, right-click on the Review option, and ’Add new group’.
• Then in the left panel ‘Choose commands from’ select ‘Commands Not in the ribbon’, find ‘Track changes (Legacy)’ and Add>> to your New Group(Custom).

• By that, the ‘Track Changes (Legacy)’ option is added in the main tabs section.

Step 2: Highlighting Changes on Display

When you highlight changes in your work in Excel outlines it becomes very easy to make any revisions (such as changes, insertions, and deletions).

• Initially, go to the Review tab.
• Then click on the ‘Track Changes (Legacy)’ and Select the Highlight Changes option.

• Afterward, check the option – ‘List changes on a new sheet’ from the’ Highlight Changes dialog box. From the When field, we can specify if we want all changes or changes that are not reviewed or ‘changes since last saved’ and click OK.

• In the end, if we make any change, the following result will come on to the screen.

Step 3: Viewing Tracked Changes in History Sheet

It is very helpful to show the tracked changes in a distinct sheet if multiple changes are made.

• Firstly, turn on the Excel Track Changes feature following step 01.
• Secondly, select the ‘Share Workbook(Legacy)’ option from the Review tab button.

• Thirdly, a new dialogue box named Highlight Changes will open.
• Moreover, go to the Highlight which changes the box. Type the values of the following image in the When and Who field.
• Thirdly, check the option ‘List changes on a new sheet‘ box, and click OK.

• Lastly, the following result will show up on the ‘History Sheet’.

Step 4: Accepting or Rejecting Changes

After making changes, there is a possibility that all changes will not be needed. In that case, using the accepting or rejecting option is very helpful.

• At the start of this step, go to the Review tab.
• Then select the option Accept/Reject Changes. from the Track Changes(Legacy) dropdown.

• Now open the Select Changes to Accept or Reject dialog box, and edit the following options.
• After that, go to the text field When. Select ‘Not yet reviewed’. We can also select Since.
• Go to the text field Who. Select the user type, that we want to review.
• Unselect(No tick) the box named Where and then click OK.

• At the last of this step, you will get the changes in order, and you have to select either Reject or Accept to cancel or keep the changes individually.

Step 5: Setting Duration to Keep Change History

You don’t need to track changes for a long period of time. So, it is very necessary to set the duration of the track change history.

• Initially, select the Share Workbook option from the Review tab.
• Next, go to the Advanced tab.
• Then, in Keep change history for box insert our intended number.
• Now, click OK.

Turning Off Track Changes

When all the sufficient changes have been made, then tracking changes of the same Excel file won’t be that much necessary. In that case, you should turn off the track changes.

Steps:

• Go to the Review tab.
• Then, similar to Step 1 go to Track Changes > Highlight Changes
• Uncheck the option Track changes while editing from the Highlight Changes dialog box,

Track Cell Changes in Excel

1. Using Cell Function

You might need to track the last changed cell in a very large worksheet on many occasions. Moreover, some other person might need to know the cell which was changed most recently. So, to track the last cell worked on, you can use the CELL function.

Steps:

• In cell G4, Apply the following formula to track the Cell Address:

• In cell G5, Insert the following formula to track the Cell Content:

• In cell G6, Create the following Formula to Track the File Address:

• After that, if any change is made, the last change will be shown on the screen in the following way.

2. Applying VBA Code

You can track changes in an Excel file using VBA Code.  You can use a sheet and cell address to track where the changes are made from the old or original value.

Steps:

• Putting two shapes for a Reset button and a Track Change.

• Pressing ALT + F11 to open the VBA Then, click Right-Click on the sheet you are working on and select Insert > Module.

• Then copy the following code and insert the code into the blank space.

Sub DeleteFormat()
Cells.FormatConditions.Delete
End Sub
Sub TrackCellChange()
For Each sheetw In ActiveWorkbook.Worksheets
sheetw.Cells.FormatConditions.Delete
Err.Clear
On Error Resume Next
x = sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23).Count
If Err.Number = 0 Then
For Each cell In sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23)
cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=" & cell.Value
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
With cell.FormatConditions(1).Interior
.Color = 2552550
.TintAndShade = 0
End With
cell.FormatConditions(1).StopIfTrue = False
Next cell
End If
Next sheetw
End Sub

• Right-click on the Reset shape and select the ‘Assign Macro’.

• Then press the Delete Format option and click OK.

• After that, similar to before Assign Trackcellchange macro to the Track Change.
• If you make any change in a cell then, in the following way the result will come up on the screen:

Things to Remember

• The work of the history sheet is to show the saved changes, so try to save the latest work by pressing Ctrl + S.
• If the history sheet is unable to show the list of all the changes, then go to All using the When box and make the Who and Where boxes empty.
• If you want your track change of Excel to look similar to Word’s track changes, then you can get help by reading this macro post by the Microsoft Excel Support Team.

Download Practice Workbook

You can download the practice workbook from here.

Track Changes in Excel: Knowledge Hub

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to calculate how to track changes in Excel and try to keep using them. Let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Share Workbook | Workbook in Excel | Learn Excel