Retrace your shared workbook’s steps with a History worksheet
by Kara Hiltz
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
When you share a workbook with your colleagues, it’s sometimes difficult to keep track of everyone’s changes. Even with the changes highlighted onscreen, the workbook often ends up looking chaotic (albeit colorful). Fortunately, Excel includes an automated History worksheet with its Tracking Changes feature. We’ll show you how to use the History worksheet to trace your shared workbook’s footsteps.
To use the History worksheet to track changes made to a shared workbook, we’ll:
• Demonstrate how to create a History worksheet and explain all of its parts.
• Look at three scenarios in which you can use the History worksheet to quickly find a workbook change.
• Preserve and edit a History worksheet that’s worth saving.
• Point out changes that the History worksheet doesn’t track.
Tracking changes in your workbook can help you get input from several colleagues. But sometimes you need more detail than the tracked changes markup provides. Or, you need to look into changes that were already accepted or rejected, meaning that they no longer appear on your markup. We’ll highlight a few ways you can use Excel’s History worksheet to audit your changes — even rejected ones.
Track an agenda
The History worksheet puts all of your workbook’s changes in a separate worksheet with AutoFilter dropdown lists, which makes it easy to filter and customize, as shown in Figure A.
A:
Excel separates the tracked changes by both date and user with a thick horizontal border.
In our example, the company’s CEO keeps his appointments in an Excel workbook. He shares the workbook with you and another colleague. When the CEO questions one of the dates in his May Appointments worksheet, you create a History worksheet to audit every change and straighten out the confusion.
To create your History worksheet:
1. Launch Excel and open our sample file or your own shared workbook.
2. Choose Tools | Track Changes | Highlight Changes from the menu bar. (In 2007, go to the Review tab. In the Changes group, click the Track Changes button and choose Highlight Changes from the dropdown list.)
3. In the Highlight Changes dialog box, select the When check box, if necessary, and choose All from its dropdown list so Excel lists every change, regardless of when you saved it.
4. Select the Who check box and choose Everyone from its dropdown list.
5. Select the List Changes On A New Sheet check box, as shown in Figure B, and click OK.
Excel generates a new worksheet in your workbook titled History. This worksheet lists the changes made in your workbook chronologically.
Note: If the List Changes On A New Sheet check box is grayed out, you probably haven’t shared your workbook. To do so, choose Tools | Share Workbook from the menu bar. (In 2007, go to the Review tab and click the Share Workbook button in the Changes group.)
B:
If you want to see your changes onscreen, you can also select the Highlight Changes On Screen check box.
Dissect the History worksheet
The History worksheet contains 11 columns and a row for each change Excel tracks, so it looks overwhelming on first glance. But we’ll break it down into four simple parts, as shown in Figure C: Identification, the Four W’s, Values, and the Action Log.
C:
We’ll break down the complicated History worksheet into these simple sections to make it easy to understand.
•
