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:
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.
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.
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:
- Click File > Save As and navigate to where you want to save the file (this step is not necessary in Excel 2010):
- Select where you want to save the file, and in the Save As dialog box, click Tools > General Options:
- 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:
- In the Confirm Password dialog box, reenter the password and then click OK:
- Back in the Save As dialog box, click 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:
- Select the File menu tab, and from the Info section, in the Inspect Workbook section, select Check for Issues.
- Select Inspect Document from the drop-down list.
- Set options in the Document Inspector dialog box, and then click Inspect.
- 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.
Password Protecting a Worksheet
To protect a worksheet within a workbook and to determine exactly which changes others can make without a password:
- Select any cell in the worksheet you want to protect.
- On the Review tab, in the Changes group, click the Protect Sheet command:
- 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:
- In the Confirm Password dialog box, reenter the password and click OK:
- To unprotect the worksheet:
- 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):
- In the Unprotect Sheet dialog box, enter the password and click OK:
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:
- Unlock cells you want people to be able to edit without a password. To do so:
- Select the cells you want to "unlock."
- On the Home tab, in the Cells group, click the Format command:
- Note that the image of a lock is highlighted. Click Lock Cell to unlock the cells:
- 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:
- Press Escape.
- Protect the worksheet (see instructions provided earlier in this lesson).