facebook google plus twitter
Webucator's Free Advanced Microsoft Excel 2013 Tutorial

Lesson: Working with Others

Welcome to our free Advanced Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2013 Training course.

In this lesson, you will learn about features that are useful when sharing and working with others.

Lesson Goals

  • Password protect a workbook.
  • Password protect a worksheet.
  • Password protect ranges in a worksheet.
  • Track changes.
  • Accept and reject changes.
  • List all changes on a new sheet.

Protecting Worksheets and Workbooks

By default, any person who can access a workbook can open it, and if you can open a workbook, you can edit it. Fortunately, Excel offers numerous levels of protection for workbooks and worksheets. The three most common ways to protect data in Excel are:

  1. Password protect a workbook. You can protect your workbook with a password, thereby preventing people who do not know the password from opening or modifying (your choice) the workbook.
  2. Password protect a worksheet. You can protect individual worksheets with a password. Others can view the sheet, but you determine what, if any, changes they can make without a password.
  3. Password protect ranges in a worksheet. If you want to allow people to edit some cells in a worksheet, but not others, you can protect the worksheet but leave those cells you want people to edit "unlocked" and, therefore, editable.

Password Protecting a Workbook

To protect your workbook with a password to prevent people who do not know the password from opening or modifying it:

  1. Click FILE > Save As and navigate to where you want to save the file (this step is not necessary in Excel 2007 and 2010):Save File Location
  2. In the Save As dialog box, click Tools > General Options:Save As Dialog Box
  3. Depending on whether you want to prevent others from opening the workbook or just from modifying it, enter a password in either the Password to open box or the Password to modify box and then click OK:Password to Modify Box
  4. In the Confirm Password dialog box, reenter the password and then click OK:OK
  5. Back in the Save As dialog box, click Save:Save As Dialog Box
  6. If you previously saved the workbook, the Confirm Save As dialog box pops up asking if you want to replace the existing workbook. Click Yes:Confirm Save As Dialog Box

Password Protecting a Worksheet

To protect a worksheet within a workbook and to determine exactly which changes others can make without a password:

  1. Select any cell in the worksheet you want to protect.
  2. On the REVIEW tab, in the Changes group, click the Protect Sheet command:Protect Sheet Command
  3. In the Allow all users of this worksheet to list, check the boxes for all actions you want users to be able to perform without a password. Note that by default users can select locked and unlocked cells, meaning users can select cells to see the formulas within, but cannot change them. If you uncheck these boxes, users will see the worksheet in the same way they would see a picture. After making your selections, enter a password and click OK:Enter Password
  4. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password Dialog Box
  5. To unprotect the worksheet:
    1. On the REVIEW tab, in the Changes group, click the Unprotect Sheet command (note that this has replaced the Protect Sheet command on the toolbar):Unprotect Sheet Command
    2. In the Unprotect Sheet dialog box, enter the password and click OK:Enter Password

Watch and follow along as your instructor demonstrates how to protect a worksheet while still allowing users to make various specified changes to the protected worksheet.

Password Protecting Ranges in a Worksheet

By default, when you protect a worksheet, all cells in the worksheet are "locked," meaning they cannot be edited. However, you can "unlock" some cells in a worksheet before protecting it, thus allowing users to edit those, and only those, cells. This is a two step process:

  1. Unlock cells you want people to be able to edit without a password. To do so:
    1. Select the cells you want to "unlock."
    2. On the HOME tab, in the Cells group, click the Format command:Format Command
    3. Note that the image of a lock is highlighted. Click Lock Cell to unlock the cells:Unlock Cells
      1. Click the Format command again and note that the image of a lock is no longer highlighted, indicating that the selected cells are now unlocked:Cells Unlocked
      2. Press Escape.
  2. Protect the worksheet (see instructions provided earlier in this lesson).

Removing Workbook Metadata

Before you share a workbook with others, you may want to remove its metadata, which can include sensitive information.

To remove document metadata:

  1. Select the FILE menu tab, and from the Info section, in the Inspect Workbook section, select Check for Issues. Check for Issues
  2. Select Inspect Document from the drop-down list. Inspect Document Command
  3. Set options in the Document Inspector dialog box, and then click Inspect. Inspect Command
  4. Use the information that is returned to clean up your workbook.

Password Protecting a Workbook

Duration: 5 to 10 minutes.

In this exercise, you will password protect a workbook to prevent people who do not know the password from opening it.

  1. Open Password Protect.xlsx from your Excel2013.3/Exercises folder.
  2. Save the workbook so that others cannot open it without entering the password "OpenMe!".

Solution:

  1. Click FILE > Save As:Save As Command
  2. In the Save As dialog box, click Tools > General Options:General Options
  3. Since the purpose of the password is to prevent others from opening the workbook, enter the password in the Password to open box and click OK:Enter Password
  4. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password Dialog Box
  5. Back in the Save As dialog box, click Save:Save As Dialog Box
  6. In the Confirm Save As dialog box, click Yes:Confirm Save As Dialog Box

Password Protecting a Worksheet

Duration: 5 to 10 minutes.

In this exercise, you will password protect a worksheet to prevent people who do not know the password from editing it.

  1. Open Protecting Worksheets.xlsx from your Excel2013.3/Exercises folder.
  2. Protect the worksheet named "Loans" with the password "NoEdits". Allow users to:
    1. Select locked and unlocked cells.
    2. Format cells, rows, and columns.
    3. Use AutoFilter.

Solution:

  1. Select any cell in the worksheet named "Loans".
  2. On the REVIEW tab, in the Changes group, click the Protect Sheet command:Protect Sheet Command
  3. In the Allow all users of this worksheet to list, verify that Select locked cells and Select unlocked cells are both checked. Check the Format cells, Format columns, Format rows, and Use AutoFilter boxes. After making your selections, enter the password "NoEdits" and click OK:Enter Password
  4. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password Dialog Box
  5. Note that while you can't edit any of the data without unprotecting the sheet, you can change the formatting and use AutoFilter. Also note that the sheet named "Grades" is not protected, and thus is fully editable.

Password Protecting Ranges in a Worksheet

Duration: 10 to 15 minutes.

In this exercise, you will unlock cells in a worksheet before protecting it, thus allowing users to edit much of the worksheet, but protecting the formulas and structure of the worksheet.

  1. Open Protecting Ranges in a Worksheet.xlsx from your Excel2013.3/Exercises folder.
  2. Protect the worksheet so that users can edit cells D2:D5 (Periods), but cannot edit anything else.

Solution:

  1. Select cells D2:D5.
  2. On the HOME tab, in the Cells group, click the Format command:Format Command
  3. Click Lock Cell to unlock the cells:Lock Cell Command
  4. On the REVIEW tab, in the Changes group, click the Protect Sheet command:Protect Sheet Command
  5. In the Allow all users of this worksheet to list, verify that Select locked cells and Select unlocked cells are both checked and click OK:Verify Locked Cells
  6. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password Dialog Box

Tracking Changes

Change tracking allows you to easily see changes that have been made in a workbook, including who made them and when they were made. Change tracking is most commonly used in workbooks that are edited by multiple people. Another common use is to turn change tracking on before giving a workbook to others for their input so you can easily see exactly what each person changed.

Things you should know about tracking changes include:

  1. When you turn change tracking on, your workbook becomes shared, meaning multiple people can edit the workbook at the same time.
  2. Some features are not available in shared workbooks and so are unavailable when change tracking has been turned on. These features include:
    1. Charts
    2. Conditional formats
    3. Data validation
    4. Hyperlinks
    5. Macros
    6. Merged cells
    7. Outlines
    8. Pictures
    9. PivotTable reports
    10. Subtotals
    11. Workbook and worksheet protection
  3. By default, change history is only kept for 30 days, though this can be changed.
  4. When you turn off change tracking, you delete the change tracking history. If you want to keep it, save it to another sheet or file before turning off change tracking.

To track changes in Microsoft Excel:

  1. On the REVIEW tab, in the Changes group, click the Track Changes command:Track Changes Command
  2. Click Highlight Changes:Highlight Changes Command
  3. In the Highlight Changes dialog box:
    1. Check the box to the left of Track changes while editing. This also shares your workbook.
    2. Choose which changes to highlight. The default is to highlight all changes, but you can choose to highlight only those you haven't reviewed, changes made since you last saved, or changes made since a specified date.
    3. Choose whose changes to highlight. You can select everyone, everyone but yourself, or specific people with whom the workbook is shared.
    4. To highlight changes on the screen, check the Highlight changes on screen box.
    5. To see a list of changes on a separate worksheet, check the List changes on a new sheet box. Note that this option is only available after changes have been made.
    6. Click OK:List of Changes

Accepting or Rejecting Changes

To accept or reject changes in Excel:

  1. On the REVIEW tab, in the Changes group, click the Track Changes command:Track Changes Command
  2. Click Accept/Reject Changes:Accept/Reject Changes Command
  3. In the Select Changes to Accept or Reject dialog box, select which changes (based on when they were created, who created them, or specified cells) you want to accept or reject and click OK:Select Changes
  4. In the Accept or Reject Changes dialog box, you can accept or reject individual changes or all changes at once:Accept or Reject Changes Dialog Box
    1. After accepting/rejecting changes, the History sheet goes away. You can show it again by clicking Track Changes > Highlight Changes. Make sure the List changes on a new sheet box is checked and click OK.

Setting Tracking Options

To set tracking options in Excel 2013:

  1. Select the REVIEW tab, and from the Changes group, select Share Workbook. Share Workbook Command
  2. On the Editing tab of the dialog box, check the Allow changes by more than one user at the same time check box. Editing Tab
  3. Select the Advanced tab, and set tracking options and click OK. Advanced Tab

Merging Multiple Workbooks

To merge two workbooks, first you need to add the Compare and Merge Workbooks button to the Quick Access Toolbar, if it is not already there.

To do so, select FILE > Options. Select Quick Access Toolbar. Select All Commands from the Choose Commands from list, and then select Compare and Merge Workbooks and click Add. Click OK.

Merge Workbooks

You can now use the Compare and Merge Workbooks option on the Quick Access Toolbar.

Compare and Merge Workbooks Option

Tracking Changes

Duration: 10 to 20 minutes.

In this exercise, you will practice tracking changes and then accepting and rejecting changes.

For the purpose of this exercise, pretend you are a teacher. You have a worksheet showing your students' grades on four exams. You are letting your students grade themselves on the final project. You have given them access to your workbook on which they are to enter the grade they are giving themselves. You have told them they are not to make any changes to the workbook other than adding their grade for the final project. To make it easy to figure out if everyone did as asked without actually preventing them from making changes, you plan to turn on change tracking before sharing the workbook with them.

  1. Open Tracking Changes.xlsx from your Excel2013.3/Exercises folder. This is the workbook you will share with your students.
  2. Turn on Track Changes. Do not highlight the changes on the screen.
  3. Open Tracking Changes2.xlsx from your Excel2013.3/Exercises folder. This is the workbook after your students have entered their grades.
  4. Accept changes in column F (Final Project grades) and reject inappropriate changes. Nolan Ryan reminded you that you incorrectly gave him credit for a problem he missed on the fourth exam and you told him it would be okay to lower his grade to an 88, so accept this change.
  5. List all the changes on a new sheet.

Solution:

  1. To track changes in Tracking Changes.xlsx:
    1. On the REVIEW tab, in the Changes group, click the Track Changes command:Track Changes Command
    2. Click Highlight Changes:Highlight Changes Command
    3. In the Highlight Changes dialog box:
      1. Check the Track changes while editing. This also shares your workbook. box.
      2. Under Highlight which changes, accept the defaults.
      3. Uncheck the box next to Highlight changes on screen.
      4. Click OK:Highlight Changes
      5. In the dialog box that appears, confirm that you want to save the workbook by clicking OK:Confirm Dialog Box
  2. To accept appropriate changes and reject inappropriate changes in Tracking Changes2.xlsx:
    1. On the REVIEW tab, in the Changes group, click the Track Changes command:Track Changes Command
    2. Click Accept/Reject Changes:Accept/Reject Changes
    3. In the Select Changes to Accept or Reject dialog box, accept the defaults and click OK:Select Changes to Accept or Reject Dialog Box
    4. In the Accept or Reject Changes dialog box, review each change individually by clicking Accept or Reject. Accept all changes to column F (Final Project grades) and also the change to cell E11 (Nolan Ryan's 4th exam) and reject all other changes as students weren't supposed to change any other grades.
      1. Reject the first change, which is to cell B8:Reject Change
      2. Reject the second change, which is to cell C8:Reject Change
      3. Reject the third and fourth changes, which are to cells D8 and E8.
      4. Accept all remaining changes:Accept Changes
  3. To list all the changes on a new sheet:
    1. On the REVIEW tab, in the Changes group, click the Track Changes command:Track Changes Command
    2. Click Highlight Changes:Highlight Changes Command
    3. In the Highlight Changes dialog box, uncheck the When box, check List changes on a new sheet, and click OK:Highlight Changes Dialog Box

Marking a Workbook as Final

You can mark a workbook as final to discourage others from editing it.

To mark a workbook as final:

  1. Select the FILE menu tab, and from the Info section, in the Protect Workbook section, click Protect Workbook. Protect Workbook Command
  2. Select Mark as Final from the drop-down list. Mark As Final
  3. Click OK in the dialog box. OK in Dialog Box
  4. Click OK in the next box that appears. Click OK
  5. The workbook is now read-only and the following appears: Final Workbook