How to Track and Review Changes in Excel 2016

Edited 2 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelTrack ChangesReviewCollaborationSpreadsheetDataEditingWindowsMacSecurityProductivity

There is a feature available in Microsoft Excel 2016 called “Track Changes” that is very useful when you are working with multiple users on the same document or when you need to monitor changes over time. This feature is part of the workbook collaboration tools and is especially helpful in the auditing stages. Let’s take a deeper look at how you can use this feature to its full potential.

Understanding 'track changes'

The 'Track Changes' feature keeps a record of changes made to the contents of a workbook. This can prove beneficial in many situations, such as when collaborating with team members or reviewing the progress of your work. When this mode is turned on, Excel marks any edits made by other people with the user name, time stamp, and description of the changes. This allows everyone to see what has changed, who changed it, and when it was changed.

To understand better, let's assume you are part of a finance team working on a quarterly report. Your team members may make multiple edits to budget estimates and forecasts before finalizing it. Using 'Track Changes', you will have a comprehensive log of all the revisions, and you can decide which changes to accept or reject.

Enable track changes

Here is a step-by-step guide on how to enable the 'Track Changes' feature in Microsoft Excel 2016:

1. Open the workbook you want to track changes on.
2. Click the Review tab in the ribbon.
3. In the 'Changes' group, look for the Track Changes command button. Click on it, then choose Highlight Changes from the dropdown menu.
4. This will open the 'Highlight Changes' dialog box. Select the checkbox that says "Track changes while editing." This also shares your workbook."
5. From here, you can set conditions for tracking changes:
   • When: You can choose to highlight changes since the workbook was last saved, or since a specific date.
   • Who: Choose to see changes made by everyone or only specific users.
   • Where: Specify certain cells or the entire sheet to be tracked.
6. Check the "Highlight changes on screen" box if you want to see the changes as they happen.
7. Click OK to activate tracking.

When you enable Track Changes, Excel will save your workbook as a shared workbook, which means it might be a bit slower if you're working with large data, but will still be functional for tracking and collaboration.

Review of changes

After tracking is enabled, all changes made to the worksheet will be recorded. To review these changes, follow the steps below:

1. Return to the Review tab on the ribbon.
2. In the 'Changes' group, select Track Changes, and then click Accept/Reject Changes.
3. The 'Choose changes to accept or reject' dialog box will appear with filtering options that are the same as in the Highlight changes dialog. Set your criteria and click OK.
4. Look at each change; you can accept or reject them individually, or apply your decision to all at once by choosing the 'Accept All' or 'Reject All' option.

By reviewing changes and accepting or rejecting them, you can maintain control and ensure that only appropriate modifications are applied to the workbook.

Tracking and reviewing in a shared workbook

Enabling 'Track Changes' will turn a normal workbook into a shared workbook. In shared workbooks, multiple users can work at the same time, which is great for collaborative efforts. Keep in mind that shared files have some limitations, such as not being able to use tables or certain formatting features.

When you share your workbook, always ensure that all users understand the need to properly save their changes, as this is important for maintaining the integrity of the data and ensuring that changes are effectively recorded.

Example scenario

Let's say you and five colleagues are working on a marketing strategy document. This document contains many complex formulas, charts, and text boxes that outline different strategies. With 'Track Changes' enabled, you would manage it like this:

• Find out who made changes to the sales projection model and evaluate whether the input is accurate before accepting the changes.
• Find out who modified the chart data series and make sure the graphs are consistent with your current data set.
• Review changes to text box descriptions to ensure consistency and correct grammar throughout the document.

Continuously monitor changes to avoid conflicts or errors in the document, protect data integrity and ensure all inputs are critically evaluated.

Protecting workbooks

Although it is not directly related to tracking changes, it is wise to apply security to sensitive data within your worksheet, which can be done as follows:

1. Click the Review tab.
2. Choose the option to Protect Sheet or Protect Workbook, and set any password if desired.

This adds another layer of security, especially in shared environments, and prevents unauthorized changes to your data.

Handling saved settings and backups

To protect tracked changes, always save your workbook with tracking enabled. If the workbook is being closed, Excel will prompt you to save the changes. Save your work regularly and consider keeping backup copies to avoid data loss, as constantly creating versions with a large amount of changes has its disadvantages.

Practical considerations

Implementing 'Track Changes' may not be inherently suitable for all Excel projects, due to the constraints introduced by the shared workbook functionality. Especially when dealing with large data sets or complex tasks, it is important to limit the number of simultaneous users and ensure clear communication about workbook usage.

Always balance the need for collaboration with data reliability. If you find limitations in meeting your productivity expectations, consider other collaboration tools.

Conclusion

Tracking and reviewing changes in Excel 2016 is an indispensable tool for managing collaborative projects and ensuring that all edits to important data are well documented and properly evaluated. By following the steps outlined, you will be able to effectively monitor revisions, accept or reject them as needed, and maintain a high level of accuracy and integrity in your Excel workbooks.

  Share This Article

If you find anything wrong with the article content, you can request an update