I recently noticed that Excel’s toolbar has a Track Changes function. Intrigued, I opened it, figuring it was like Track Changes in Word, but when I noticed that it involved a Shared workbook, I decided I may be creating a security problem, so I backed off. Was my worry justified? Can I use it without a Shared workbook?
You need not worry. While it is true that when you engage it, Excel automatically goes into the Shared workbook mode, you can sidestep the problem by not storing the file in a place where anyone else can access it. That way there is no security danger.
Excel’s Track Changes is a very handy tool. It works somewhat like the Word version in that it records a history of any changes made to a workbook—with the exception of format changes. And like Word’s Track Changes, much of its operation is quite intuitive, but I will mention some of the steps that might temporarily bog you down.
To launch it, open a worksheet, click on Tools, Track Changes and then on Highlight Changes. Notice in the screenshot below that the command directly under it, Accept or Reject Changes, is dimmed—and that’s because it’s not yet engaged.
To start it up, place a check next to Track changes while editing. This also shares your workbook (see screenshot below).
Next you have to set up your defaults by clicking on the down arrows next to When, Who and Where. Before you do anything, explore all the options they offer.
If you want the whole worksheet monitored by Track Changes , do not place a check next to Where. But if you want Track Changes to record only a section of the worksheet, click on Where and highlight that section. As the screenshot below shows, that portion will be set up automatically for monitoring.
After you’ve customized the options and clicked on OK , Excel will warn that doing so will Save the Workbook. It will allow you to Continue or Cancel. Click on Continue, and the workbook will become Shared . All changes will be tracked.
After you make a change in a cell, Track Changes records it and posts a black triangle in the upper left corner of the cell. When you pass your cursor over the cell, this screen will pop up.
At any point you can review and accept or reject changes. To do that, click on Tools, Track Changes, Accept and Reject Changes. Then make your When, Who and Where choices.
Then click on OK and decide which changes to accept or reject.
Now we get to the best part of Track Changes. At the bottom of the Highlight Changes screen you probably noticed a box labeled List changes on a new sheet. That line remains grayed out until after you save changes.
If you subsequently place a check in the box, a history worksheet is created that contains loads of information. Because there is so much data, Excel defaults it with AutoFilters formatting for easy viewing (see screenshot below).
Be aware that once you save the workbook, the history sheet will be deleted. But you can display it again by going to Tools , Track Changes, Highlight Changes. Check the List changes on a new sheet option and click OK.