Tuesday, February 9, 2021

Track changes in a shared workbook

Important: This article explains an older method of tracking changes using a "Shared Workbook." The Shared Workbook feature has many limitations and has been replaced by co-authoring. Co-authoring doesn't provide the ability to track changes. However, if you and others have the file open at the same time, you can see each other's selections and changes as they happen. Also, if the file is stored on the cloud, it's possible to view past versions so you can see each person's changes. Learn more about co-authoring.

Windows macOS

  1. Be certain that you want to use this method before continuing. Shared Workbooks have limitations, and one in particular is the inability to edit using Excel for the web. Therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.

  2. Click Review > Share Workbook.

    Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.

  3. In the Share Workbook dialog box, on the Editing tab, select the Allow changes by more than one user at the same time check box.

  4. Click the Advanced tab.

  5. Under Track changes, click Keep change history for and, in the days box, type the number of days of change history that you want to keep. By default, Excel keeps the change history for 30 days and permanently erases any change history that is older than this number of days. To keep change history for a longer than 30 days, enter a number that is larger than 30.

  6. Click OK and, if you are prompted to save the workbook, click OK to save the workbook.

  1. Click Review > Track Changes, and then click Accept or Reject Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. If prompted to save the workbook, click OK.

  3. In the Select Changes to Accept or Reject dialog box, do the following:

    • To accept or reject changes that are made after a particular date, select the When check box, click Since date in the When list, and then type the earliest date for which you want to review the changes.

    • To accept or reject changes that are made by another user, select the Who check box and then, in the Who list, click the user whose changes you want to review.

    • To accept or reject changes that are made by all users, clear the Who check box.

    • To accept or reject changes that are made to a specific area, select the Where check box, and then type the cell reference of the worksheet range.

    • To accept or reject changes to the entire workbook, clear the Where check box.

  4. Click OK, and then review the information about each change in the Accept or Reject Changes dialog box.

  5. To accept or reject each change, click Accept or Reject.

  6. If prompted to select a value for a cell, click the value that you want, and then click Accept.

Notes: 

  • You must accept or reject a change before you can advance to the next change.

  • You can accept or reject all remaining changes at once by clicking Accept All or Reject All.

  1. Click Review > Track Changes, and then click Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. To select the changes that you want to see, do the following:

    • To view all changes that have been tracked, select the When check box, click All in the When list, and then clear the Who and Where check boxes.

    • To view changes that were made after a particular date, select the When check box, click Since date in the When list, and then type the earliest date for which you want to view changes.

    • To view changes that were made by a specific user, select the Who check box and then, in the Who list, click the user whose changes you want to view.

    • To view changes to a specific range of cells, select the Where check box, and then type the cell reference of the worksheet range.

  3. To specify how you want to view the changes, do the following:

    • To highlight changes on the worksheet, select the Highlight changes on screen check box.

    • To create a list of changes on a separate worksheet, select the List changes on a new sheet check box to display the history worksheet.

      Note: This check box is available only after you turn on change tracking and save the file with at least one trackable change.

Turning off change tracking deletes the change history. To keep a copy of this information, do the following to print the history worksheet or copy it to another workbook:

  1. Click Review > Track Changes > Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. Under Highlight which changes, select the When check box, and then in the When list, click All.

  3. Clear the Who and Where check boxes.

  4. Select the List changes on a new sheet check box.

  5. Click OK.

  6. You can now print, or copy the history to another workbook.

When you highlight changes as you work, Excel marks any revisions (such as changes, insertions, and deletions) with a highlighting color.

  1. Click Review > Track Changes, and then click Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. In the Highlight Changes dialog box, select the Track changes while editing check box. Selecting this check box shares the workbook and highlights changes that you or other users make.

  3. Under Highlight which changes, select the When check box and then, in the When list, click the option that you want.

  4. To specify the users for whom you want to highlight changes, select the Who check box and then, in the Who list, click the option that you want.

  5. To specify the worksheet area where you want changes to be highlighted, select the Where check box and then, in the Where box, type the cell reference of the worksheet range.

  6. Make sure that the Highlight changes on screen check box is selected.

  7. Click OK. If prompted, save the workbook.

  8. On the worksheet, make the changes that you want. Please note that some changes, such as formatting, are not tracked and therefore are not marked with a highlighting color.

When you no longer want changes to be highlighted, you can turn off change highlighting.

  1. Click Review > Track Changes, and then click Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. In the Highlight Changes dialog box, clear the Track changes while editing check box.

  1. Click Review > Track Changes > Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  2. Under Highlight which changes, select the When check box and then, in the When list, click All.

  3. Clear the Who and Where check boxes.

  4. Select the List changes on a new sheet check box.

  5. Click OK.

  6. In the history worksheet, click the filter arrows next to the column labels to find the information that you want.

Note: Saving the workbook hides the history worksheet. To view the history worksheet after saving, you must display it again by selecting the List changes on a new sheet check box in the Highlight Changes dialog box.

By using the Track Changes feature in Excel for Mac, you can track, maintain, and display information about the changes that are made to a shared workbook.

Important: 

  • The Track Changes button is no longer available in the ribbon in the newer versions of Excel for Mac. In order to use this feature, you will need to add Track Changes back to the ribbon first. For more information, see Add the Track Changes button to the ribbon.

  • Change tracking is available only in shared workbooks. In fact, when you turn on change tracking, the workbook automatically becomes a shared workbook. Although a shared workbook is typically stored in a location where other users can access it, you can also track changes in a local copy of a shared workbook.

Add the Track Changes button to the ribbon

To add the Track Changes button in the ribbon, you will need to create a custom group and then add the button to the group.

  1. Click Excel > Preferences > Ribbon & Toolbar.

    Office2016 for Mac Ribbon Toolbar Preferences

  2. Under Customize the Ribbon, under Main tabs, select Review.

  3. Below the Main Tabs list, click Office2016 for Mac Customize Ribbon Add Button , and select New group.

  4. You can now rename this custom group. Just select New Group (Custom), click Office2016_for_Mac_Customize_Ribbon_Settings > Rename > and type a name in the Display name box, and click Save.

  5. Under Choose commands from, select Main Tabs, and then in the list select Review > Changes > Track Changes (Legacy), and then click > (Add arrow) to move the item under the custom group you created.

    Click Track Changes (Legacy) and then click > to move the option under the Review tab

  6. Click Save and close the Excel Preferences dialog box.

    You should see the Track Changes (Legacy) option in the Review tab on the ribbon.

    Track Changes (Legacy) option on the Ribbon

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

  2. Select or clear the Track changes while editing. This also shares your workbook check box.

    Selecting this check box shares the workbook and highlights changes that you or other users make. If you clear the check box, you will receive a message indicating that this action will remove the workbook from shared use.

When you highlight changes as you work, Excel outlines any revisions (such as changes, insertions, and deletions) with a highlighting color.

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

  2. Select the Track changes while editing. This also shares your workbook check box.

  3. Under Highlight which changes, select the When check box, and then in the dropdown list, click the option that you want.

  4. Do any of the following:

    To specify

    Do this

    The users for whom you want to highlight changes

    Select the Who check box, and then in the Who menu, click the option that you want.

    The sheet area where you want changes to be highlighted

    Select the Where check box, and then in the Where box, type the cell reference of the sheet range.

    Tip: You can also click the Collapse Dialog icon at the right end of the Where box and then select the range that you want to use on the sheet. After you finish, click Collapse Dialog again to display the entire dialog box.

  5. Make sure that the Highlight changes on-screen check box is selected.

  6. Click OK.

    If you are prompted to save the workbook, click OK.

  7. On the sheet, make the changes that you want.

    Note: Some changes, such as formatting, are not tracked and therefore are not marked with a highlighting color.

When you no longer want changes to be highlighted, you can stop highlighting them.

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

  2. Clear the Highlight changes on-screen check box.

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

    Note: If the Track changes while editing. This also shares your workbook is not selected, Excel has not recorded any change history for the workbook.

  2. To select the changes that you want to see, do any of the following:

    To view

    Do this

    All changes that have been tracked

    Select the When check box, select All from the When pop-up menu, and then clear the Who and Where check boxes.

    Changes that were made after a particular date

    Select the When check box, click Since date in the When list, and then type the earliest date for which you want to view changes.

    Changes that were made by a specific user

    Select the Who check box and then, in the Who list, click the user whose changes you want to view.

    Changes to a specific range of cells

    Select the Where check box, and then type the cell reference of the sheet range.

  3. You can also click Collapse Dialog at the right end of the Where box and then select the range that you want to use on the sheet. After you finish, click Collapse Dialog again to display the entire dialog box.

  4. To specify how you want to view the changes, do any of the following:

    To

    Do this

    Highlight changes on the sheet

    Select the Highlight changes on screen check box.

    This enables you to view the details about a change by resting the pointer over a highlighted cell.

    Create a list of changes on a separate sheet

    Select the List changes on a new sheet check box to display the history sheet.

    This check box is available only after you turn on change tracking and save the file with at least one trackable change.

  1. On the Review tab, click Track Changes, and then click Accept or Reject Changes. .

    If you are prompted to save the workbook, click OK.

  2. In the Select Changes to Accept or Reject dialog box, do any of the following:

    To accept or reject

    Do this

    Changes that are made after a particular date

    Select the When check box, click Since date in the When list, and then type the earliest date after which you want to review the changes.

    Changes that are made by another user

    Select the Who check box and then, in the Who list, click the user whose changes you want to review.

    Changes that are made by all users

    Clear the Who check box.

    Changes that are made to a specific area

    Select the Where check box, and then type the cell reference of the sheet range.

    You can also click Collapse Dialog at the right end of the Where box and then select the range that you want to use on the sheet. After you finish, click Collapse Dialog again to display the entire dialog box.

    Changes to the entire workbook

    Clear the Where check box.

  3. Click OK, and then review the information about each change in the Accept or Reject Changes dialog box.

    The information includes other changes that are affected by the changes you make. You can scroll to see all the information as needed.

  4. For each change, click Accept or Reject.

    Notes: 

    • If you are prompted to select a value for a cell, click the value that you want, and then click Accept.

    • You must accept or reject a change before you can advance to the next change.

    • You can accept or reject all remaining changes at once by clicking Accept All or Reject All.

    • All changes must be saved before they can be listed on the history sheet.

    • When you reject a change, the history sheet records the rejection with "Undo" or "Result of rejected action" in the Action Type column. To view the history sheet, see View the history sheet for more information.

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

    Note: If the Track changes while editing. This also shares your workbook check box is not selected, Excel has not recorded any change history for the workbook.

  2. Under Highlight which changes, select the When check box, and then in the When pop-up menu, click All.

  3. Clear the Who and Where check boxes.

  4. Select the List changes on a new sheet check box, and then click OK.

  5. Click OK.

    The history sheet appears.

  6. In the history sheet, click the filter arrows next to the column labels to find the information that you want.

    Note: Saving the workbook hides the history sheet. To view the history sheet after saving, you must display it again by selecting the List changes on a new sheet check box in the Highlight Changes dialog box.

Because the change history is deleted when you turn off change tracking, you may want to save a copy of the history. You can print the history sheet or copy it to another workbook.

  1. On the Review tab, click Track Changes, and then click Highlight Changes.

  2. Under Highlight which changes, select the When check box, and then, in the When list, click All.

  3. Clear the Who and Where check boxes.

  4. Select the List changes on a new sheet check box.

  5. Click OK.

    Tips: 

    • To print the history sheet, click Print on the File menu, select the print options you want, and then click Print.

    • To copy the history sheet to another workbook, follow these steps:

    • Hold down CONTROL and click the History tab at the bottom of the history sheet, and then click Move or Copy.

    • In the To book area, click the name of the workbook you want to move the sheet to, or click (new book).

    • Select the Create a copy check box, and then click OK.

    • Save the workbook that contains the copied history sheet.

You can use change tracking to log details about workbook changes every time that you save a workbook. This change history can help you identify any changes that were made to the data in the workbook, and you can then accept or reject those changes.

Change tracking is especially useful when several users edit a workbook. It is also useful when you submit a workbook to reviewers for comments and then want to merge the input that you receive into one copy of that workbook, incorporating the changes and comments that you want to keep.

When changes are made in the shared workbook, you can view the change history directly on the sheet or on a separate history sheet. Either way, you can instantly review the details of each change. For example, you can see who made the change, what type of change was made, when it was made, what cells were affected, and what data was added or deleted.

When you use change tracking, consider the following:

  • Change tracking differs from the undo operation and does not create a backup copy    

    You might expect that change tracking creates a backup copy of the workbook before changes are made, or that you can undo any of those changes. Unfortunately, you cannot use the change history to revert changes by undoing them or by restoring an earlier version of the workbook. However, the history sheet includes a record of all deleted data so that you can copy that data back to the original cells in the shared workbook.

    Because change tracking is not designed to help you return to earlier versions of a workbook, you should continue to back up workbooks that have change tracking in effect.

  • Some types of changes are not tracked    

    Changes that you make to cell contents are tracked, but other changes, such as formatting changes, are not tracked. Some Excel features are unavailable in shared workbooks and therefore cannot be tracked.

  • The change history is kept only for a specific interval    

    By default, when you turn on change tracking, the change history is kept for 30 days to keep the size of the workbook manageable. However, you can increase or decrease the number of days of the change history that you want to keep. To keep the change history indefinitely, you can specify a large number of days. You can also make periodic copies of the history information.

  • The oldest part of the change history is periodically deleted    

    Excel determines the change history that is kept by counting back from the current date. Each time that you close the workbook, Excel deletes any part of the change history that is older than the number of days that were in effect the last time the workbook was saved.

    For example, if you are keeping 30 days of change history, and you open a workbook for the first time in two months, you will be able to view the history from two months ago. However, when you close this workbook, the history from the previous 30 days (31 to 60 days ago) is deleted.

Important: When you turn off change tracking or stop sharing the workbook, all change history is permanently deleted.

Excel provides the following ways to access and use the stored change history.

  • Onscreen highlighting    

    Excel can outline changed areas in a different color for each user and display the basic details as a comment when you rest the pointer over each changed cell. Onscreen highlighting is useful when a workbook does not have many changes or when you want to see at a glance what has changed.

  • History tracking     

    Excel can display a separate history sheet that provides a printable list of change details on which you can filter to find the changes that you are interested in. The history sheet is useful when a workbook has many changes or when you want to investigate what occurred in a series of changes.

  • Change reviewing    

    Excel can step you through the changes in sequence so that you can decide whether to accept or reject each change. This method is useful when you are evaluating comments from other users.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)