Track Changes in Excel (Table of Contents)
- Track Changes in Excel
- How to Track Changes in Excel
- Viewing Changes in Separate Sheet
- How to Maintain Track History
Track Changes in Excel
Excel being a vast database system, we can store entries and help us to make any data changes. This also means that there will be changes made to the data on any day or on a regular period of time, which makes it mandatory to track the number of changes made to the sensitive data. Imagine that you have a list of capital intensive work that you are responsible to keep updated on a regular basis, wherein the files go to multiple departments for review or make changes if any in the amount of total Capex spent, the person responsible, etc. in the ongoing projects.
This case makes a classic example that if there are thousands of records to be tracked and the changes made must be identified and any movement in cells has to be recorded for reporting to the senior management. These tracking would be cumbersome and time taking if the changes are tracked manually and would also eventually lead to errors. Hence MS Excel provides us a perfect tool to capture this requirement electronically by using the “Track Changes” feature.
Let us see an example to understand how to track changes work in excel.
How to Track Changes in Excel?
Below is a sample table that shows how to track changes in excel.
Step 1: Now to enable “Track Changes” go to “Review” and click on “Track Changes” and select “Highlight Changes”.
Step 2: After selecting the highlight changes, a window will pop up.
Highlight changes detect any change in the workbook shared with other users.
Step 3: Select the checkbox for “Track changes while editing”, this is essential for excel to keep a track of records for any change made in the shared workbook.
Select “When” for the desired time and “Who” checkbox for whose ever changes are needed to be tracked and highlighted for changes.
The “Highlight changes on screen box” are selected to indicate the cell where the records are changed and provide details in the comments at the corner of the cells. And click OK to save the actions in the workbook.
Step 4: Any changes in the workbook would be highlighted in the cell borders along with the details of users in the comments. The comments will be available in the upper left corner of the cell with the name of the user who has made the changes to the cell and the content change.
As we can see, the changes were made in two cells D4. The comment box will appear by hovering the cursor over the highlighted cells.
Viewing Changes in Separate Sheet
Step 1: Highlight changes also provide the record for the list of changes made to the cells available in a separate sheet for review. This allows different users to make the changes to the workbook, for this to happen to go to Review -> Share Workbook.
Step 2: After selecting Share workbook, a window will pop up. Now select the box for “Use the old shared workbooks features instead of the new co-authoring experience.” Then click OK.
Step 3: Next go to Review – > Track Changes -> Highlight Changes as shown in Step 1.
Step 4: Once the window pops-up select “Who” checkbox for enabling other users to make changes to the workbook. Then select the checkbox for “List changes on a new sheet” to view all the changes made in a new sheet. Then click OK. Again click OK for the prompt to save the actions.
Step 5: This will trigger a new sheet to generate named “History” tab as shown below.
As we can see, excel provides all the information related to any change in the cell. The Action type and losing action indicates if there are any conflicting changes or changes done to the same cell by different users.
The History tab can be saved as a separate workbook or can be regenerated again to see for any new changes done by different users, the new changes would be merged to the existing records when selecting “List changes on a new sheet” checkbox again.
Accept or Reject Changes in Workbook
Step 1: To accept or reject the changes made by a different user in the workbook, go to Review -> Track Changes -> Accept/Reject Changes.
Step 2: Accept/Reject changes would pop up a window, which needs to be configured for the below selections.
When selection box, would ask for the changes which are not reviewed yet or since date.
Who checkbox selection, choose either everyone, everyone but me or a specific user. Then click OK.
Step 3: Once the above selections are done, a pop up will ask whether the changes made be accepted, rejected or to keep the original content.
If there are any conflicts, that is if different users have made changes to the same cell, then the pop up will list down all the changes made to the specific cell and the selections can be made to accept anyone record.
How to Change Number of Days for Maintaining Track History?
Generally, the track changes in excel for the records are usually maintained for 30 days by default, this can be changed as per convenience by going to Review -> Share Workbook.
After the selections, a pop-up window will appear as can be seen in the below image. Then in the “Advanced” tab, the history can be changed to the desired number of days, else excel would take the default settings. Then click on OK, once the necessary settings are updated.
How to Disable Track Changes?
Now go to Review -> Track changes, then select Highlight changes.
Once the pop-up window appears, uncheck the box for “Track changes while editing”. Click OK. Then turn off tracking and stop sharing the workbook to other users.
Things to Remember
- Once the track changes are enabled, the workbook becomes a shared file, which means that other users could also access the excel file.
- Only content changes in cells are tracked, other changes such as formatting or grouping won’t be considered.
- The track record changes will be maintained for only 30 days by default, this could be changed by following how to maintain track history.
- Option for undo is not available, once the changes are made to the cell, they can be brought to its original content by accepting or rejecting in “Track Changes”.
This has been a guide to track changes in excel. Here we discuss how track changes work in excel which includes Accept or Reject Changes, Maintaining Track History and Disable Track Changes along with example and downloadable excel template. You may learn more about excel from the following articles –