Excel 2010

Reviewing and Sharing Workbooks

Introduction

Lesson 15

Let's say someone asked you to proofread a worksheet. If you had a hard copy, you might use a red pen to cross out cell data, mark misspellings, or add comments in the margins. However, you could also do all of these things in Excel using the Track Changes and Comments features.

When you've finished reviewing the worksheet, the other person can choose to automatically accept all of your changes, or decide whether to accept or reject each change one by one. In this lesson, you'll learn how to track changes , add comments , and compare two versions of a worksheet. You'll also learn how to prepare a final version for sharing, including how to check spelling .

Tracking changes

When you need to collaborate on the content of a spreadsheet—or if you need someone to proofread your worksheet—Excel's Track Changes and Comments features make it easier for you to collaborate with others.

Optional: You can download this example for extra practice.

About Track Changes

When you turn on the Track Changes option, every change you make to the worksheet will be highlighted with a unique border and indicator. Hovering your mouse over a highlighted cell will display the details of the change. This allows the other person to see what changes have been made before making them permanent.

Worksheet with tracked changes Worksheet with tracked changes

The color of the highlights will vary depending on who's reviewing the document, so if there are multiple reviewers you'll be able to tell at a glance who made each change.

There are some changes that Excel cannot track. Familiarize yourself with changes that Excel does not track or highlight .

To turn on Track Changes:

  1. Go to the Review tab.
  2. Click Track Changes , then select Highlight Changes from the drop-down menu.
    Highlight tracked changes Highlight tracked changes
  3. The Highlight Changes dialog box will appear.
  4. Check the box next to Track changes while editing .
  5. Verify that the box is checked for Highlight changes on screen . This will highlight your changes while you work.
  6. Click OK .
    Turning Track Changes on Turning Track Changes on
  7. If prompted, click OK to allow Excel to save your workbook.
  8. Change tracking is now active. Any changes you make to the worksheet will be highlighted with a unique border and indicator.
    Worksheet with tracked changes Worksheet with tracked changes

Your workbook will be shared automatically when you turn on Track Changes. Shared workbooks are designed to be stored where other users—such as users on the same network—can access and edit the workbook at the same time. However, you can also track changes in a local or personal copy, as seen throughout this lesson.

To turn off Track Changes:

  1. From the Review tab, click Track Changes . Then select Highlight Changes from the drop-down menu.
  2. Uncheck the box next to Track changes while editing .
    Turning Track Changes off Turning Track Changes off
  3. Click Yes to confirm that you want to turn off Track Changes and stop sharing your workbook.
    Removing the workbook from shared mode Removing the workbook from shared mode

Turning off Track Changes will delete any tracking that has taken place in your workbook. You will not be able to view, accept, or reject changes; instead, they will be applied to your workbook automatically. Always review the changes in your worksheet before turning off Track Changes.

Adding and deleting comments

Sometimes you may want to add a comment to a worksheet to provide feedback for the author instead of changing the contents. Comments are highlighted by a unique indicator and can be read by the original author or by any other reviewers.

To add a comment:

  1. Select the cell where you want the comment to appear.
    Selecting a cell Selecting a cell
  2. From the Review tab, click the New Comment command.
    New Comment command New Comment command
  3. Type your comment.
    Typing a comment Typing a comment
  4. The red indicator in the upper-right corner shows that there is a comment in that cell.
    Comment indicator Comment indicator
  5. Hover your mouse over the cell to view the comment.
    Viewing a comment Viewing a comment

To edit a comment:

  1. Select the cell containing the comment you want to edit.
  2. From the Review tab, click the Edit Comment command (where the New Comment command used to be).
    Edit Comment command Edit Comment command
  3. Edit your comment.
    Editing a comment Editing a comment

To delete a comment:

  1. Select the cell containing the comment you want to delete.
  2. From the Review tab, click the Delete command.
    Delete command Delete command
  3. The comment will be deleted.

Accepting and rejecting changes

Tracked changes are really just suggested changes. To become permanent, they have to be accepted . On the other hand, the original author may disagree with some of the tracked changes and choose to reject them.

To accept or reject changes:

  1. From the Review tab, click Track Changes . Then select Accept/Reject Changes from the drop-down menu.
    Accept/Reject Changes command Accept/Reject Changes command
  2. If prompted, allow Excel to save your workbook.
  3. Identify which changes you want to accept or reject. In this example, we'll address all the changes we have not yet reviewed .
    Identifying which changes to accept or reject Identifying which changes to accept or reject
  4. Click OK .
  5. The Accept or Reject Changes dialog box will appear.
  6. Click Accept or Reject for each change in the document. Excel will cycle through each change automatically until you have addressed them all.
    Accepting or rejecting each change Accepting or rejecting each change

To accept or reject all of the changes at once, click Accept All or Reject All in the Accept or Reject Changes dialog box.

Adjusting how changes are viewed

Sometimes it's difficult to see exactly what has been changed in a shared workbook. Taking in the meaning of a large amount of feedback can also be problematic if your worksheet contains several comments. There are a few settings you can use to adjust the way changes and comments are viewed.

An alternative to viewing changes as highlights on your worksheet, you can view them on a new worksheet of their own, sometimes called the change history . The change history lists everything in your worksheet that has been changed, including the old value—what the cell used to contain—and the new value—what the cell contains now.

To list changes on a separate worksheet:

  1. Save your workbook.
  2. From the Review tab, click Track Changes . Then select Highlight Changes from the drop-down menu.
  3. Check the box next to List changes on a new sheet .
    Listing changes on a separate worksheet Listing changes on a separate worksheet
  4. Click OK .
  5. The changes will be listed in a new sheet of their own, called History .
    The change history worksheet The change history worksheet

To remove the History worksheet from your workbook, you can either save your workbook again or uncheck the box next to List changes on a new sheet .

To show or hide comments:

  1. Select a cell that contains a comment.
  2. From the Review tab, click the Show/Hide Comment command to show the comment.
    Show/Hide Comment command Show/Hide Comment command
  3. The comment will remain on display without you having to hover your mouse over the cell.
    Comment on display Comment on display
  4. Click the Show/Hide Comment command again to hide the comment.

Click the Show All Comments command to view every comment in your worksheet at once.

Finalizing and protecting your workbook

Before you send your workbook, take a minute to prepare a final copy. Excel has tools that might save you from sharing a workbook that contains spelling errors or information about the workbook itself that you do not want recipients to see. Use the Spelling command to find and edit any misspelled words ; use the Document Inspector to find and delete any hidden data or personal information that is stored in the workbook, such as hidden comments, invisible objects, and file paths; and consider your Protect Workbook options, which are designed to keep other users from making unwanted changes to your workbook.

To check spelling:

  1. Select the cells you want to check.
  2. Click the Spelling command on the Review tab.
    Selecting the Spelling command Selecting the Spelling command
  3. The Spelling dialog box will open. From the Spelling dialog box, you can review and edit any misspelled words.

You can also check the spelling of every cell in an active worksheet by selecting any empty cell in the worksheet and clicking the Spelling command.

Ignoring spelling "errors"

There are times when Excel will say something is an error when it is not. This often happens with people's names, which may not be in the dictionary. Even if Excel says something is an error, you can choose not to change it. There are three options you can choose from:

  • Ignore Once: This will skip the word without changing it.
  • Ignore All: This will skip the word without changing it, and it will also skip all other instances of this word in the worksheet.
  • Add to Dictionary: This adds the word to the dictionary so it will never come up again as an error. Make sure the word is spelled correctly before choosing this option.

To use the Document Inspector:

  1. In Backstage view , click Info .
  2. Click the Check for Issues button. A drop-down menu will appear.
  3. Select Inspect Document . A dialog box will appear.
    Selecting the Inspect Document command Selecting the Inspect Document command
  4. Check the boxes next to the content you want to inspect. To unselect a box, click it again and the check mark will disappear.
  5. Click Inspect .
    Document Inspector dialog box Document Inspector dialog box
  6. After the Document Inspector finishes, a new dialog box will appear where you can review the inspection results . Click the Remove All command next to any information you want to remove.

It is a good idea to save an additional copy of your workbook before you use the Document Inspector to remove information because some changes cannot be undone.

Protecting your workbook

By default, anyone with access to your workbook will be able to open, copy, and change any part of it unless you protect it. There are many ways you can protect your workbook, depending on your needs. For example, you can mark your workbook as final, set it up with a password, put restrictions on who can make changes, or make it so only certain cells or features of the workbook are changeable.

To protect your workbook:

  1. Click the File tab to access Backstage view .
  2. From the Info pane, click the Protect Workbook command.
  3. Choose the option that best suits your needs. In this example, we'll select Mark as Final. Marking your workbook as final is a way to discourage others from making changes to the workbook. It will alert whoever opens it that typing, editing, and proofreading commands are unavailable.
    Selecting a Protect Workbook option Selecting a Protect Workbook option
  4. Click OK .
    Marking the workbook as final Marking the workbook as final
  5. Another dialog box will appear. Click OK to confirm.
    Selecting the Inspect Document command Selecting the Inspect Document command

Challenge!

  1. Open an existing Excel 2010 workbook . If you want, you can use this example .
  2. Turn on Track Changes .
  3. Delete, add, or edit the text in several cells.
  4. Add several comments .
  5. Show all of the comments, then hide them.
  6. Save your workbook , then list the tracked changes on a separate worksheet (also known as the change history ).
  7. Accept all of the changes, and turn off Track Changes.
  8. Perform a spelling check to prepare the final copy.
  9. Use the Document Inspector to check the workbook.
  10. Mark the workbook as final . If you are unable to select this option, make sure Track Changes is turned off.