Are you looking for a way to track changes made to an Excel spreadsheet? There are many cases where you need to share a file with multiple people and keep track of what changes have been made. You might want to keep track of when the change was made, who made the change, which cell the change took place, and what data was changed.
Excel has built-in tracking functionality that can handle all of the above cases. You can easily view all changes right on the sheet, and accept or reject each change. There are a few things to keep in mind about tracking functionality in Excel:
1. Turning tracking on does not mean that you can revert the spreadsheet back in time to a previous state by undoing the changes. Basically, it is a log file that everything is written to and that’s it. You can manually see what data has been removed or added, but you will have to make changes to the table yourself.
2. Turning tracking on does not mean that every change you make will be recorded. Any data stored in the cell is tracked, but other changes, such as formatting, are not tracked. Other changes that are not tracked include hiding / showing rows and columns, comments, and cell values ??that change due to formula recalculation.
3. By default, the change history is kept for only 30 days. If you make changes to an Excel sheet and then reopen the workbook after 45 days, you will be able to see the history of changes for all 45 days until you close the workbook. When you close it, the history of changes older than 30 days will disappear. This means that the next time you open it, you will not see the changes made 45 days earlier.
4. When you turn on tracking, the book is shared. This means that multiple users will be making changes to the document.
Now that you know the basics of tracking in Excel, let’s talk about how to turn it on, change settings, and track changes!
Tracking enabled
Let’s enable tracking first. Open Excel and click the Browse tab on the ribbon. On the right, you should see a Track Changes option under the Changes section.
Click the button and select Highlight Changes. You will see a dialog box in which you will need to check the box “Track changes while editing. It also gives you access to your book. ”
You have several options here, including When, Who, and Where. For When, everything means that every change will be highlighted. You also have the option to highlight changes since the document was last saved from a specific date, or changes that have not yet been checked out.
If you check “Who”, you can keep track of changes made by anyone, or changes made by everyone except you. The Where option tracks changes only for a specific portion of the spreadsheet. Just click the button on the right and select the range of cells you want to track.
Finally, you can uncheck the Highlight Changes On Screen option if you don’t want others to know that you are tracking changes. By default, when you start tracking and select this option, any modified cell displays a small arrow in the upper left corner, indicating that it has been modified.
Also, if you click on the modified cell (with on-screen change selection enabled), you will see a small popup showing from what the value was changed, and at what time. If you deselect the Highlight Changes On Screen option, this little black triangle will not appear.
So what you can do, like hide the changes on the screen without checking the box, send your file to everyone who needs to make changes to it, and when you check it back, just go to Track Changes and check the box again.
Trace configuration
The next step is to take a look at the tracking settings and customize them according to your needs. To do this, click the Share Book button, which is directly to the left of the Track Changes button. Click the Advanced tab when the Share Book dialog box appears.
Here you can change the number of days to store the history of changes to any other than 30 days. By default, changes are updated when you save the file, but you can do this automatically every few minutes. Finally, you can choose how you want to deal with conflicts: either ask or just let the last change win when you save the file.
View changes
After you’ve turned tracking on and made a few changes, you can click the Track Changes button again, click Highlight Changes, and you will notice that the list of changes in the new sheet is no longer grayed out.
Check it out and click OK. A new worksheet called “History” will be added that will allow you to review all the changes made to this workbook.
At some point, you will need to either approve or reject the changes. You can preview all the changes and then choose which ones you want to keep or discard.
Just click on Track Changes again and select Accept / Reject Changes. Select options to choose which changes you want to accept or reject. If you want to see all the changes, just leave the checkbox “When” and make sure it is set to “Not checked”.
Click OK and Excel will start showing you each change you make and offering you an “Accept†or “Reject†option. You can also accept all or reject all changes if you like.
If you reject the change, it will immediately revert to what was originally in that cell. It is worth noting that if another cell refers to the content of the rejected cell, then this value will also change when the value of the specified cell is returned back. It can break the formula, etc., so be careful.
This is it! Now you can easily track any changes made to your Excel spreadsheet with this built-in function. If you have any questions, please leave a comment. Enjoy!
–