Tips for Excel, Word, PowerPoint and Other Applications
Tracking Changes in Excel
Why It Matters To You
Most of our work these days is done collaboratively with co-workers and clients. This presents certain challenges. We want to solicit feedback yet at the same time we want to be able to easily incorporate everyone’s changes and edits. As a reviewer, I want the ability to see what other people have said so that everyone on the team isn’t wasting time making the same comments. Both Excel and Word have pretty powerful collaboration tools that allow a team to share documents, track changes, and incorporate feedback.
Excel is a slightly different beast from Word. There are some nice capabilities to see what a person has changed in a spreadsheet, but sometimes it's harder to interpret how it's actually changed your work, especially if changes in formula or values was made. In any case, play around with it a bit and see if it's useful to you..
How to ...
Most people are already probably somewhat familiar with the track changes tool in Word. However, today we are also going to introduce you to a couple of the reviewing tools in Excel, focusing on inserting Comments and Tracking Changes.
Inserting Comments
Most users are familiar with at least one the three methods for inserting comments in Excel:
- Insert: Comment from the main toolbar
- Right-click on the cell and select Insert Comment from the pop-up box
- Insert Comment icon from the Review Toolbar
You can activate the above toolbar by going to View: Comments. From left to right the first six icons do the following:
- Insert/Edit Comment
- Jump to Previous Comment
- Jump to Next Comment
- Hide/Show Comment
- Hide/Show All Comments
- Delete Comment
Note that b) and c) are excellent for quickly identifying and navigating to a reviewer’s comments. This way, one avoids hunting through the sheet for small flags or cluttering the screen by displaying comments for all cells.
Viewing and Printing Options for Comments
Comments for a given cell can be hidden or shown on the screen. A small red tag in the upper right corner of the cell indicates a hidden comment. Such comments can be viewed by leaving the mouse cursor placed over the relevant cell.
Another way to toggle the Hide/Show setting of an individual cell is to right-click on the target cell and select either “Hide Comment” or “Show Comment” from the menu.
Printing options for comments are adjusted by going to File: Setup: Sheet. Your options include:
- Exclude comments on the printed sheet.
- Display all comments at the end of the sheet.
- Print comments as they appear on the screen.
Track Changes
As in Word, going to Tools: Track Changes: Highlight Changes will provide you with several reviewing options
- When - Specify time frame: All, Since I Last Saved, Not Yet Reviewed, or since a certain date.
- Who - Specify which reviewer(s) changes you want to highlight.
- Where (optional) - Indicate a specific cell range to which you want to limit tracking.
You can also specify the above parameters when using the Accept or Reject Changes function from Tools: Track Changes. Since the process is the same as in Word, this will not be discussed here.
A small black tag in the upper left corner of the cell denotes individual cell changes. You can make change detail appear by placing the mouse cursor over the relevant cell:
Excel allows you to generate a historical record of all saved changes. This can be very useful in keeping an automatic history of changes made by a single user or multiple users. The software’s filtered-table format facilitates analysis of the change history. Excel will generate the following “History” sheet:
To generate this History, simply, select the “List changes on a new sheet option” from the Highlight Changes menu (Tools: Track Changes: Highlight Changes) and click on “OK”:
This option is only available once the changes have been saved and the document is shared (the document becomes shared by checking the first option on the above menu).
Notes
| Last updated | 9/2/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | None |