Tracking In Excel 2003

racking is one Excel feature you could use to keep an eye on who has done what when. And you can also use tracking to let you decide which changes to approve or reject. This article explores how.

Perhaps the situation is familiar. You've changed your spreadsheet quite a bit and suddenly your calculations don't add up. Or several colleagues have contributed to your spreadsheet and now there's a problem with it. You want to know what editing you or someone else has done so you can sort out the problems. Tracking is one Excel feature you could use to keep an eye on who has done what when. And you can also use tracking to let you decide which changes to approve or reject.

To enable tracking in Excel 2003 you need to set your file to Sharing mode. You can do this and turn on Tracking at the same time by carrying out the following steps. Have you file open in Excel first.

Now choose Tools, Track Changes, Highlight Changes. In the Highlight Changes panel tick "Track changes while editing. This also shares your workbook". Ensure "When" shows "All", "Who" shows "Everyone" and "Highlight changes on screen" is ticked, and click OK to finish. A prompt then appears advising Excel will save your file, click OK to continue. Notice that your filename in the top blue bar now includes [shared].

Now try adding some data to your spreadsheet, for example several numbers in separate cells which you then autosum. You'll see that Excel adds comments to each edited cell after tracking was started. Hover over one of the edited cells to see the tracking comments. Then save your file.

You can also have Excel keep a log of all the tracked changes in a separate History worksheet within the same workbook. To see this, again choose Tools, Track Changes, Highlight Changes. This time the option "List changes on a new sheet" is available - tick it, and click OK to finish. Excel creates a brand new History worksheet. Have a look at the History sheet and you'll see a list of all the changes since the file was created. Then save your file. Once the file is saved the History sheet vanishes.

Now make some further edits to your file, and save the file again. Then choose Tools, Track Changes, Highlight Changes, and again tick "List changes on new sheet" and click OK. The History sheet reappears with the extra edited details added.

Sometimes you may not want to show all the changes as they occur, for example if there are lots of changes or lots of users. You can turn off the cell comments in Tools, Track Changes, Highlight Changes panel by unticking "Highlight changes on screen". Now although Excel is still tracking, the comments do not appear in the edited cells, but you can still create the History sheet to list all the changes. Don't forget, to create a new History sheet, save the file first and then set the History sheet to show again.

Once you've recorded Tracking in your Excel file you may want to review it and accept or reject the changes made. To do this choose Tools, Track Changes, Accept or Reject Changes. In the "Select Changes to Accept or Reject" panel accept the default settings ("When" is ticked and set to "Not yet reviewed") and click OK.

Excel now selects the first edited cell and in the "Accept or Reject Changes" panel the edit details are shown. You can Accept or Reject this change as you wish and Excel then moves to the next edited cell and so on. You can also click a global Accept All or Reject All choice.

So Tracking in Excel allows you to log edits made by whom and when, and then lets you review all the changes so you can accept or reject changes as you wish. You will find that you cannot track absolutely everything in Excel. Once a file is in shared mode some features are disabled, for example chart creation.

To turn off Tracking (and Sharing), choose Tools, Track Changes, Highlight Changes and untick "Track Changes while editing", and click OK to finish. Tracking and Sharing are now turned off.

Bear in mind that whenever you set an Excel file to share, tracking is also turned on, even though you may not see the tracking comments. You can check this for a shared file by choosing Tools, Track Changes, Highlight Changes and look to see what's ticked on the lower left of the panel.

Hopefully this article has given you a glimpse into how you can use Tracking in Excel. To really find out more about this feature and many more, consider attending an Excel course and really build up your Excel skills.