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 a warning 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.
Please post back if you have any more problems or questions.