Excel Has Its Own Track Changes


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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.