facebook twitter
Webucator's Free Advanced Microsoft Excel Tutorial

Lesson: Working with Others

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

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

Lesson Goals

  • Learn to password protect a workbook.
  • Learn to password protect a worksheet.
  • Learn to password protect ranges in a worksheet.
  • Learn to track changes.
  • Learn to accept and reject changes.
  • Learn to 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 2010):Save As Command
  2. Select where you want to save the file, and in the Save As dialog box, click Tools > General Options:General Options
  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:Add Password
  4. In the Confirm Password dialog box, reenter the password and then click OK:Confirm Password
  5. Back in the Save As dialog box, click Save:Save

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
  3. Set options in the Document Inspector dialog box, and then click Inspect. Document Inspector Dialog Box
  4. Use the information that is returned to clean up your workbook.

Restoring Previous Versions

When working with others, you may occasionally need to restore an Excel file to a previous version. To do so, use the History tab in Backstage view. History Tab

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:Password
  4. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password
  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
    2. In the Unprotect Sheet dialog box, enter the password and click OK:Password Entry

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:Lock Cell
      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).

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 Excel2019.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:Password to Open
  4. In the Confirm Password dialog box, reenter the password and click OK:Password
  5. Back in the Save As dialog box, click Save:Re-Enter Password
  6. In the Confirm Save As dialog box, click Yes:Click Yes

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 Excel2019.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
  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:Select Options
  4. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password
  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 Excel2019.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:Confirm Options
  6. In the Confirm Password dialog box, reenter the password and click OK:Confirm Password

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
  2. Select Mark as Final from the drop-down list. Mark as Final
  3. Click OK in the dialog box. Click OK
  4. Click OK in the next box that appears. Click OK
  5. The workbook is now read-only and the following appears: Read-Only Workbook