How to keep track of changes in Excel

Are you looking for a way to keep track of changes made to an Excel spreadsheet? There are many cases where you have to distribute a file to several people and keep track of the changes that were made. You may want to keep track of when the change was made, who made the change, in which cell the change occurred, and what data was changed.

Excel has built-in tracking functions that can handle all of the above cases. You can easily review all changes directly in the spreadsheet, and you can also accept or reject each change. Here are some points to keep in mind about Excel’s tracking features:

1. Enabling tracking does not mean that you can return the spreadsheet to a previous state by undoing the changes. It’s basically a log file that records everything and that’s it. You can manually go to see what data has been deleted or added, but you will need to make the changes to the spreadsheet yourself.

2. Enabling tracking does not mean that all the changes you make are recorded. All data stored in a cell is tracked, but there are no other changes like the format. Other changes that are not tracked include hiding / hiding hidden rows and columns, comments, and cell values ​​that change due to a formula recalculation.

3. The change history is only maintained for 30 days by default. If you make changes to an Excel spreadsheet and reopen the book 45 days later, you can see the 45-day change history until you close the book. When you close it, there will be no more than 30 days of change history. This means that the next time you open it, you won’t be able to see the change you made 45 days earlier.

4. Whenever you enable tracking, the book becomes a shared book. This means that multiple users will make changes to the document.

Now that you know some basics about how tracking works in Excel, let’s talk about how to enable it, change settings, and keep track of changes.

Activation of monitoring

We go ahead and activate tracking first. Open Excel and click Review ribbon tab. On the far right, you should see an option called Change control under the Changes section.

Click the button and choose Highlight changes. You will receive a dialog box where you will now have to check the option “Keep track of changes while editing. This also shares your workbook. ”.

Here are several options, including When, WHO, i On. To When, Everyone means that each change will be highlighted. You also have the option to highlight changes from the last time you saved the document, from a specific date, or changes that haven’t been reviewed yet.

If you check it out WHO, you can choose to keep track of changes made by anyone or changes made by everyone except you. He On The option allows you to track changes only for a specific portion of the spreadsheet. Just right-click and select the range of cells you want to track.

Finally, you can uncheck the file Highlight changes on the screen option if you do not want other users to know that you are tracking changes. By default, once you start tracking and select this option, any changed cells will display a small arrow at the top left to indicate that they have been changed.

Also, if you click on a changed cell (with Highlight changes on the screen activated), you will get a small pop-up window that will show what value has been changed and changed and at what time. If you deselect the Highlight changes on screen option, this small black triangle will not appear.

So what you can do, for example, is hide the changes on the screen if you don’t check the box, send the file to everyone who needs to make changes, and when you recover it, go to Change control and check the box again.

Set up tracking

The next step is to take a look at the tracking settings and adjust it to your needs. To do so, click Share the workbook which is located directly to the left of the file Change control button. Click the button Advanced when the file Share the workbook the dialog box appears.

Here you can change the number of days to keep the change history to something other than 30 days. By default, changes are updated when the file is saved, but you can do so so that it is done automatically every few minutes. Finally, you can choose how you want to deal with conflicts: whether you are prompted or simply let win the last change when saving the file.

Display of changes

Once you’ve turned on tracking and made some changes, you can click the Change Tracking button again, click Highlight Changes, and you’ll notice that Lists the changes on a new sheet box is no longer dimmed.

Check it out and click OK. A new worksheet has been named History it will add that it will allow you to see all the changes that have been made to the workbook.

At some point you will need to approve or reject the changes. You can review all changes and choose the ones you want to keep or discard.

Just click Change control choose again Accept / reject changes. Select options to choose the changes you want to accept or reject. If you want to review all changes, just leave When check and make sure it is set to Not reviewed.

Click OK and Excel will start showing you every change that was made and give you the option to do so. Accept or Reject. You can too Accept it all or Reject everything change if you want.

If you reject a change, it will immediately return to what was originally in the cell. That is, if another cell refers to the contents of the rejected cell, that value will also change when the value of the referenced cell is reversed. This can cause formulas to break, etc., so be careful.

This is! You can now easily track changes made to your Excel spreadsheet using this built-in feature. If you have any questions, please post a comment. Enjoy it!

Leave a Reply