Excel 2010: Protecting Excel Workbooks

In Excel, you can protect the entire workbook, specific worksheet, or just specific cells within a worksheet. The workbook is the entire file. The Worksheets are the tabs within the workbooks and the cells are the data is entered. With worksheet protection you can have total control over the spreadsheet by protecting each element of the worksheet such as data, cells, formulas and ranges. Protecting the workbook controls the structure and worksheet window.

Password Protection to Open Workbooks

Password protecting the entire workbook in Excel is similar to setting password protection in Word. Remember the password is not retrievable by IT, so make sure that it is a password that you will remember.

1. To save a password for opening a document, click on the File tab to go to the Excel backstage.
2. Click on Info, Protect Document, and Encrypt with Password.

      3.    The Encrypt Document dialog will appear, type in your password.
      4.    You will be prompted to enter your password in a second time.

***Remember this password is not recoverable by IT or by Microsoft, so make it a password that you will remember***

5.     You can tell the file is password protected because Excel will update the status to show that a password is required to open the document. Permissions message will now reflect that a password is required to open this document.

To test the password, close the file and reopen it. Excel will prompt you to enter your password before it will open the document.

Password Protection to Specific Worksheets

Excel offers you the option of just protect one or more worksheet within the entire work book. This feature adds flexibility to allow editing or data entry on specific worksheets, but ensure others are left intact.

1. Navigate to the Review tab, in the changes group click on Protect Workbook, click Protect Sheet. (See below)
2. A Protect Sheet dialog box pops up. This dialog box allows you to specify if
    you want the entire worksheet and the entire contents or set rules to allow
    users to only modify the areas you choose for them to modify.
3. Check the Protect worksheet and contents of locked cells.
4. In Password to unprotect sheet text box, type your password.
5. Click OK.

Password Protection to Modify Specific Areas

Another option for protecting your data in Excel is to set the protection to only allow a user to add data to specific cells. This feature is very useful for ensuring that your formulas and formatting are not changed. There are two phases to protecting specific cells. First locking/unlocking specific cells, and then protecting the worksheet. Both phases must be complete in order for the contents of the cells to be protected. This method is especially important in protection the integrity of formulas and preventing someone form tampering with them.

Unlocking Cells
By default the cells in a worksheet are locked. This makes it easier to lock the entire worksheet/workbook. If you want users to be able to entire data into specific cells then you must unlock the cells.  In the example below, the goal is to ONLY allow data to be entered in Last yr Column.

1. Select the cells that you want to allow other users to entire data into the cells. Right-Click in the highlighted cells, and select Format Cells.
2. Click on the Protection tab, and uncheck the Locked checkbox.
   **remember the default is locked**
3. Click OK.

Protecting Worksheet

     4.     Navigate to the Review tab, in the Changes group click on, click Protect Sheet.

      5.     A Protect Sheet dialog box pops up, check the Protect worksheet and contents of locked cells.
      6.    In Password to unprotect sheet text box, type your password.
      7.    Click OK.

Protecting the Workbook

Protecting a workbook protects the integrity of the structure of the workbook and window instead of protecting the individual cells.

1. Navigate to the Review tab, in the Changes group click on, click Protect Sheet.

      2.     A Protect Structure and Window dialog box opens, type your password and click Ok.