Protect cells in Excel Spreadsheets

Do you develop spreadsheets for others? Have you spent a lot of time developing complex calculations within those spreadsheets? If the answer is 'Yes', you will want to protect your formulas and formatting before sharing those Excel files with others.

The power of Excel lies in the ability to perform “what-if analysis” by using formulas rather then raw numbers in your spreadsheets. For example, in Figure 1 below, if the telephone expense for March changes to $200, both the monthly totals in cell E15 and the balance in E17 will automatically update to reflect that change.

When sharing a similar worksheet with others, you’ll want to be certain that the formulas cannot be deleted or modified. There are two common scenarios where you may wish to apply protection to an Excel spreadsheet. Your choice will depend on how much or how little you want to protect the worksheet. Here's a brief description of the two different methods you might want to try.

Method 1: "Full Protection"
In this scenario, the intent is to protect almost the entire worksheet. The user will be allowed to enter information in only designated (usually colored-coded) cells.

Method 2: "Partial Protection"
The interest here is in protecting the formulas only. The user has the ability to add data to other areas within the worksheet.

Let's step through an example of applying worksheet protection using Method 1. Your workbook must be saved before the protections will take affect.

Select the cells that you want to remain unprotected. To select nonadjacent cells, hold down the CTRL key (Apple key on a Mac) and click the cells that are to remain unprotected.



On the Format menu, choose Cells, and then click the Protection tab. Clear the Locked check box and click OK.



On the Tools menu, choose Protection, Protect Sheet. (At this point, you have the option of entering a password that must be provided before you can unprotect your worksheet.)




In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

Click OK to enable worksheet protection.





The formulas and other cells in the worksheet are now protected from tampering. If the user tries to enter data into a protected cell, they will receive the following message:

Now let's step through Method 2. Here the goal is to protect formulas only; changes and additions to the rest of the worksheet are allowed. Your workbook must be saved before the protections will take affect.

Select the entire worksheet by clicking the Select All button (the gray rectangle in the upper-left corner of the worksheet where row 1 and column A headings intersect.) You can also press CTRL+A (Apple+A on a Mac) to select the entire worksheet.

Choose Cells from the Format menu, or Press Ctrl-1 (Apple+1 on a Mac).



Click the Protection tab, clear the Locked check box, and click OK.
Note: This has just unprotected every cell in the worksheet. The next step will be to protect only the cells with formulas in them.



On the Edit menu, choose Go To, or press F5. Click the Special button.



Click the Formulas option, and then make sure the four check boxes below it are selected. Click OK. This forces Excel to select the cells that contain formulas.



Press Ctrl+1 (Apple+1 on a Mac) again and click the Protection tab. This time we'll check the Locked check box. Click OK to complete the operation. Now the cells containing formulas will be locked and the cells containing text and values will be unlocked.

Finally, open the Tools menu and select Protection, Protect Sheet. (At this point, you have the option of entering a password that must be provided before you can unprotect your worksheet.) Click OK to enable worksheet protection.

To remove the protection from your worksheet, choose the Tools, Protection, Unprotect Sheet command. If you have assigned a protection password, you will have to enter it at this point.

Make sure you remember your protection passwords, or write them down and keep them in a secure place. If you lose a password, you cannot gain access to the protected elements on the worksheet.

When several worksheets within a workbook need to be protected, follow the above scenarios on each worksheet and then select Tools, Protection, Protect Workbook.