Section 21.3. Using Change Tracking | Excel 2010: The Missing Manual

21.3. Using Change Tracking

Comments are a great way for people to leave messages for each other in a spreadsheet. However, they become awkward if the workbook requires substantial changes, and they're downright aggravating if more than one person revises a workbook. For example, imagine a worksheet that tracks a team's weekly progress or one that represents a communal effort to create a business plan. In such situations, where more than one person needs to make substantial additions or changes to the worksheet data, comments just can't handle it all.

Excel provides another tool that makes it easier for groups of people to work together, and it's called change tracking . Change tracking makes sure that the changes made by different people are carefully logged, giving you the power to inspect each person's changes individually and reverse them if you choose. It's a little like keeping several versions of the same worksheet in a single spreadsheet file.

There are several reasons that you might consider using change tracking. These include:

You want to send a workbook to another person for review or editing. However, you want to be able to quickly spot the changes they make.
You want to have the last word on other people's changes to your workbook. In other words, you not only want to review changes, but also want to discard them if they aren't correct.
You want to distribute copies of your spreadsheet to several people at once. Once everyone has made their changes, you want to merge all these changes back into the original copy.

21.3.1. Switching On Change Tracking

To turn on change tracking, follow these steps:

Select Tools Track Changes Highlight Changes.

The Highlight Changes dialog box appears.
Turn on the "Track changes while editing" checkbox.

When you turn on change tracking, you also automatically switch on workbook sharing (another feature that's described later in this chapter). There's no way to turn on change tracking without also turning on workbook sharing.
Ignore the Who, When, and Where checkboxes for now, and click OK to return to your worksheet.

The Who, When, and Where checkboxes don't affect how change tracking works. They just configure what changes Excel highlights in your document. You'll learn about these settings a little later in this chapter.

To see how change tracking works, just enter text in a new cell or edit an existing cell. Once you complete the edit (by pressing Enter or moving to another cell), you'll see a small blue triangle appear in the top-right corner of the cell. This triangle indicates that the cell has been changed. You can get the specifics by hovering over the cell with your cursor. When you do, a yellow box appears that looks just like a comment box. However, you can't edit the text in this boxinstead, it contains a message that indicates who last changed the cell , the date and time the change was made, and what the change was. This message disappears as soon as you move the cursor somewhere else. Figure 21-5 shows a closer look.

Figure 21-5. Cells A4 and B10 have the telltale blue triangles indicating they've been changed. To examine a change, simply position your cursor over the correct cell. Using this technique, you can see the name in cell A4 was modified by removing an extra "t" in the word "Vittoria."

Note: Change tracking applies to the entire workbook. When you switch on change tracking, Excel tracks changes for each cell in every worksheet.

21.3.2. Understanding the Change Log

Excel's change tracking is a little quirky, and it often confuses reasonably intelligent people. Even though it's superficially similar to the change tracking features used in other applications (like Microsoft Word), it's subtly different in many ways. In this section, you'll learn how it really works.

Once you turn on change tracking, Excel keeps a change history log , which it stores along with your workbook file. The change history log lists every modification you've made since you first turned on change tracking. It also records who made the change and when.

Excel records all changes on a per-cell basis. So if you change the text in cell A2 from John Smith to Adam Bergman , Excel adds one change item to the log, indicating the old and new values. If you change the cell text twice, Excel adds two entries. Excel records the change as soon as you commit it (either by pressing Enter or moving to a new cell).

Excel doesn't track every possible change. In fact, it ignores all of the following changes:

Formatting changes (like when you change the font or background color for a cell).
Hiding or unhiding rows or columns .
Adding, changing, or deleting comments.
Inserting or deleting worksheets. (However, if you add a new worksheet, Excel does track all changes you make to that worksheet.)

Excel also doesn't worry about cells that change indirectly. For example, if you change the value in cell A1, and that causes a formula in cell A2 to display a new value, Excel only records a change for cell A1. If you think about this approach, it makes perfect sense, because the real content in cell A2the formulahasn't changed at all. The only difference is the displayed result.

Changes don't remain in the change history log forever. In fact, once thirty days have passed since a change was made, Excel discards it from the log. Excel checks the date and discards old entries every time you open a file, so make sure you aren't sharing workbooks between two people with drastically different dates set on their computers, or some changes will start to go missing!

Note: If you decide you need more time, you can adjust how long Excel hangs onto the change log. To do so, select Tools

Share Workbook. Select the Advanced tab, and change the number next to the "Keep change history for" option. This is the number of days that changes are kept in the log.

There's one other way that you can clear changes from the change history logby turning off change tracking altogether. Just select Tools Track Changes Highlight Changes to show the Highlight Changes dialog box, and turn off the checkmark in the "Track changes while editing" checkbox. You can turn change tracking back on later, but you won't be able to recover the information about the changes you made earlier.

21.3.3. Highlighting Changes

One of the most confusing aspects of change tracking is the difference between how a change is tracked and how it's highlighted . As you've learned so far, Excel tracks every change you make in a cell. However, it doesn't necessarily highlight these changes so that you can see them.

To witness an example of this behavior in action, try saving and closing a workbook that uses change tracking. Then, reopen the workbook. You'll immediately see that all the blue triangles that flag the changed cells have disappeared. Even though Excel is still tracking these changes, it automatically switches off change highlighting when you open the workbook, because it doesn't know what changes you really want to see.

Confusingly enough, you use the exact same dialog box to configure change highlighting as you do to turn on change trackingthe Highlight Changes dialog box (Figure 21-6). To show this dialog box, select Tools Track Changes Highlight Changes.

Figure 21-6. The Highlight Changes dialog box lets you switch change tracking on or off with the "Track changes while editing" checkbox. You can also configure what changes Excel highlights (with blue triangles and pop-up boxes) by using the When, Who, and Where lists.

What you do next depends on the types of changes you want to see. Here are some of your choices:

Show all changes . In the When list, choose All, which is the standard choice that Excel selects when you first switch on change tracking.
Show recent changes . In the When list, choose "Since I last saved" to show all the changes you've made since the last time you saved the workbook (or, if you haven't saved it yet, since you first created or opened it). Excel automatically selects this choice when you open a workbook that uses change tracking.
Show changes since a specific date . In the When list, choose "Since date . . . "; Excel automatically fills in the current date, but you can edit this date by hand. When you use this option, Excel highlights the changes that were made any time on or after the specified date.
Show changes that haven't been reviewed . In the When list, choose "Not yet reviewed." This option works best when you use Excel's change revision feature to examine each individual change. When you use this approach, Excel highlights only the changes that you haven't yet reviewed and confirmed. For information about this technique, see the Section 21.3.5 a little later in this chapter.
Show changes made by a specific person . In the Who list, choose the name of the person whose changes you want to see. Excel automatically fills the Who list with the name of each person who has made a change in the workbook. You can also choose to show changes by other people and hide your own changes by choosing "Everyone but me" from the Who list. If your worksheet has changes from several different people, you might not see the familiar blue triangles. Instead, Excel tries to color-code the comment flags, so that you can tell at a glance which person left which set of comments.
Show changes made in specific cells . Click in the Where box, and then click and drag on the worksheet to select the appropriate range of cells. Alternatively, you can type the cell references in range notation (A1:B3) or as individual cell references separated by commas (A1, A2, A3, B1, B2, B3).

Tip: You can also use a combination of settings in the When, Who, and Where checkboxes. In this case, a change must meet all of the criteria in order for Excel to highlight it. For example, you might want to find changes made to a certain range of cells by a certain person after a certain date.

Once you make your selections, ensure that the "Highlight changes on screen" checkbox is turned on, and click OK. If Excel can't find any changes that match the criteria you've chosen , it displays a warning message. However, Excel still flags any new changes you make, provided they meet the highlight criteria.

Note: The When, Who, and Where checkboxes determine what types of changes Excel highlights in your worksheet, but they don't affect what types of changes Excel tracks . Once you switch on change tracking, Excel tracks every change you makeno matter who you are or what cell you modifyuntil these changes expire and get removed from the log.

21.3.4. Examining the Change Log

Change highlighting can get a little crowded. If you have a heavily edited document, you'll quickly run into two problems:

There's no way to distinguish the most recent changes from ones that were made earlier. You can change the highlighting settings so you don't see all the changes at once, but it's still difficult to figure out the order in which changes were made.
When you hover your cursor over a changed cell, Excel shows a box with information about the most recent change for that cell. If a cell was modified more than once, you can't see the information about any of the earlier changesin fact, you have no way of knowing whether they were made.

FREQUENTLY ASKED QUESTION
Features Change Tracking Affects

I turned on change tracking, and now a bunch of other features don't workwhat gives?

Unfortunately, when change tracking is on, Excel automatically disallows certain actions. This side effect is unavoidable because change tracking requires workbook sharing (Section 21.5). Workbook sharing always applies a few basic safety restrictions.

As a result, once you've turned on change tracking, all the following changes are off-limits:

Creating a data list, picture, chart, or pivot table.
Deleting a block of cells.
Deleting a worksheet.
Merging cells or splitting merged cells.
Adding or changing conditional formats.
Adding or changing data validation.
Inserting or changing hyperlinks .
Protecting or unprotecting worksheets or the workbook.
Creating, changing, or viewing scenarios.
Grouping or outlining data.
Recording, editing, or assigning macros.

In all of these instances, the limitation is on what you can create or change. If these features already exist in your workbook, they'll continue to function. For example, once you turn on change tracking, you can't add a hyperlink. However, if you've already created a hyperlink, it still works as it did before.

If you need to find out a little more about the changes made to a document, you can create a change history report . This report presents an ordered list of changes. It's not very useful if you want to see the final product of all the revisions, but it's a great tool if you want to find out what's been modified along the way.

To create a change report, just follow these steps with any shared workbook:

Save your workbook.

The change report takes into account changes only in the current saved version of your workbook. If you've made changes but haven't saved the workbook, these changes won't appear in the change report.
Select Tools Track Changes Highlight Changes.

The Highlight Changes dialog box appears.
If you want to create a change report that only shows certain changes, you can adjust the Who, When, and Where checkboxes as described earlier.

If you want to create a change report that shows all the changes made by everyone ( anywhere in the entire workbook), select All from the Who list, and make sure the When and Where checkboxes are not turned on.
Turn on the "List changes on a new sheet" checkbox.

This setting tells Excel to create the change report worksheet. This is a new worksheet, into which Excel copies all the information from the change history.
Click OK.

Excel adds a new worksheet named History to your spreadsheet file. The History worksheet contains a list of the changes ordered from oldest to most recent (see Figure 21-7). By reviewing the change report, you can quickly see a list of all the changes made in the worksheet. Using Excel's change highlighting, by contrast, you would only be able to see the information for the most recent change. At the bottom of the list is a message indicating when Excel generated it.

Figure 21-7. This change report shows three changes. The change report indicates who made the change, when it was made, where it was made, and what the old and new values were.

Excel doesn't let you delete the History worksheet. However, as soon as you save the workbook, Excel automatically removes the History worksheet. Excel takes this rather drastic step to ensure that you never end up with an out-of-date change report. If you decide you want to keep a change report for a longer amount of time, you can select the cells in the History worksheet and copy them to another worksheet.

Tip: The change report uses a data list. Your payoff is that you can use all the data list features you learned about in Chapter 13, like sorting, filtering, and searching.

21.3.5. Accepting and Rejecting Changes

So far, you've learned how change tracking helps you to see the history of a spreadsheet, letting you examine who modified it and when the changes were made. Change tracking is also useful if you want to review changes and make final decisions about whether to keep or discard each change. This review process is called accepting and rejecting changes .

When a change goes into the change history log, Excel flags it as a new, unconfirmed edit. If you decide not to perform a review of the workbook, the latest changes remain in effect. However, if you review the workbook, Excel lets you independently examine each change and decide whether to make the change permanent or discard it. If you decide to discard, or reject a change, Excel restores the previous value for the cell and removes the change from the history log. If you decide to keep, or accept a change, it stays in the history log, but it's flagged as a confirmed edit. Excel will never ask you to accept or reject that change again.

To review the changes in a workbook, follow these steps:

Select Tools Track Changes Accept or Reject Changes.

If you haven't saved the document yet, Excel prompts you to do so now. In Excel, you can't review changes until you've saved them to the workbook file.

Once you've saved the document, the "Select Changes to Accept or Reject" dialog box appears (see Figure 21-8). This box lets you set filter conditions to indicate the types of changes you want to review. These filter conditions are the same as those that you find in the Highlight Changes dialog box.

Figure 21-8. When you start the review process, you can choose the type of edits you want to examine. Usually, you'll choose to take a look at everything that hasn't already been confirmed or rejected, as demonstrated in this example.

Adjust the When, Who, and Where lists to indicate the changes you want to review.

If you want to review every change that hasn't been reviewed yet, select "Not yet reviewed" from the When list, and don't make any other selections. (For more information about the When, Who, and Where lists, see the Section 21.3.3 on Section 21.3.3.)

No matter what selection you make, Excel ignores changes that have already been accepted.

Click OK to start the review process.

If Excel can't find any unconfirmed changes, it informs you that no changes are available and ends the review process. Otherwise, Excel begins scanning the document from your current position. It scans from left to right and then top to bottom, examining one cell at a time. When it reaches the last cell, it starts over at the top of your worksheet to scan the remaining cells, and then continues its search with the other worksheets in your workbook.

Whenever Excel finds a changed cell, it shows a message that offers you the opportunity to review the change and choose to either accept it or reject it, as shown in Figure 21-9.

Figure 21-9. Excel has found a change made to cell A4. The Accept or Reject Changes dialog box lets you choose to accept this correctionin this case, a minor correction of a misspelled nameor reject it and move on.

Click Accept to confirm the edit, or Reject to discard the edit and remove the change from the log.

If you know that you want to reverse all the changes in a workbook, you can click Reject All to cancel everything in one fell swoop. Similarly, Accept All confirms all the outstanding changes. Be careful, thoughif you use these buttons , you can't undo your action afterward.

After you make your choice, Excel continues its search. When it can't find any more unconfirmed changes, the review process ends, and you return to your worksheet.

Tip: Even after you've accepted a change, it remains in the change log until the time limit expires . This system differs dramatically from the change tracking you'll find in an application like Microsoft Word, which tracks changes only until you review them.

21.3.6. Merging Multiple Revisions into One Workbook

Change tracking works great when you need to send a workbook from one person to another. Each person can add her insights, and the original author can review all the changes before preparing a final version. Of course, life rarely works so smoothly. A far more common situation is when several people need to work on the same workbook at the same time to get the document revised as quickly as possible. In this case, each reviewer ends up with a separate copy of the original workbook. When the revisions are finished, the original author needs to consolidate all the changes from each copy into one workbook file.

This situation probably seems like a guaranteed recipe for hours of manual Excel labor. However, Excel provides a special merge feature that neatly solves the problem of multiple reviewers. It lets you combine all the changes into one workbook. Just follow these steps:

Make sure each file you want to merge has a different file name.

Excel can't open files with the same name at the same time. Usually, you'll give each file a new name based on the reviewer. For example, if your workbook is called expenses.xls, you might want to create copies of it with names like expenses_rr.xls (for someone whose initials are R.R.), expenses_mj.xls (for someone whose initials are M.J.) and so on.

Tip: You can't merge a document if some of the changes have expired from the change history log. To avoid this problem, it's a good idea to make sure the change history tracks changes for longer than 30 days before you send the workbook out for review. Just select Tools Share Workbook, and select the Advanced tab. In the "Keep change history for" setting, enter a really large number like 500 days. The only disadvantage to keeping change entries for a longer amount of time is that your workbook file will become larger as it retains additional data.
Put all the workbook copies you want to merge into the same folder on your computer.

By having all the workbook files in one location, you'll be able to merge all the files in one step.
Open your master copy of the workbook.

Before continuing, you should also make sure none of the workbook copies are currently open. If they are, you can't merge them into your master workbook. You may also want to make a backup copy of your original workbook. That's because Excel automatically saves the workbook after the merge process is finished, and there's no way to undo the changes.
Select Tools Compare and Merge Workbooks.

A standard file dialog box appears.
Browse to the folder where the files are located, and select the copies you want to merge.

To select more than one file at a time, press the Ctrl key while you click the file name.
When you have selected all the copies, click OK.

Excel compares each copy and applies all the changes to the current workbook.

During the merge process, Excel might need to make a decision between two conflicting changes. For example, a conflict occurs if two people have modified the same cell, or if you modified a cell after sending the workbook out for review. In this situation, Excel uses the most recent change. However, Excel carefully enters every change in the change history log. After you've merged all the workbooks, you can use the Accept and Reject Changes command to review all the changes, including cells where there were conflicts.

When you find a cell where multiple changes were made while reviewing a document, you'll see a message like the one shown in Figure 21-10. You can then choose which values should be used.

Figure 21-10. Occasionally, you may find that more than one unconfirmed change has been made to the same cell. In this situation, Excel adds them all to the change log, and uses the most recent value in your worksheet. When you Accept and Reject changes, Excel shows a dialog box that shows all the changes, ordered from oldest to most recent. You must choose which change in the list "wins."

Tip: You can also use Excel's workbook merging tool to combine comments from several different versions of the same file. Excel automatically adds the comments into your master workbook when you perform the merge. Remember, in order for this plan to work, you must have change tracking turned on in each file.