Excel gives you the opportunity to protect your work and save your formulas from being overwritten. Here is a quick introduction.
The exercise and result are in the Protection file.
Below we have a simple sales forecast. We want to protect the formulas, but let users change the sales growth percentages.
First let's look at the properties of the cells for Sales Growth. Highlight the 4 regional sales growth numbers, then go to the format menu and select cells. When the Format Cells dialog box appears, click the Protection tab.
Notice two things:
The cells are automatically checked off as locked.
As the dialog below says, locking has no effect unless the worksheet is protected.
Since we want these sales growth cells unlocked, click the Locked box to unselect it. Then click OK to close the format dialog.
Then go to the Tools menu, select Protection and then Protect Sheet.
You then see a Protection dialog box.
There are several choices:
We are protecting the sheet, so we leave protection checked
Enter a password, otherwise anyone could go in and override the protection (remember what you set up)
Last, you can select what you want users to be able to do
Click OK. One final dialog box, to confirm your password. Notice the warning.
You have now protected the worksheet. Try to change a formula to test it out.
If your protection is working, if you try to change a formula, it will not let you.
To unprotect the sheet, follow the instructions in the above last dialog box.
Protecting sheets takes a few steps, but it can be worth it to preserve critical formulas for workbooks others will use.