Knowing how to protect your worksheet will prevent any other users (or yourself) from inadvertently making changes erroneously. This is particularly important if you are using complex formulas in your spreadsheet that simply cannot be fiddled with, but it applies to any data that is critical to you. While individual cells can be locked, unlocked or hidden; these instructions will not become effective until the entire worksheet has been protected. This Excel tutorial is going to teach you exactly how to protect worksheets from unintentional sabotage.
The first thing to understand is that Excel automatically locks each cell in your spreadsheet; so that when you protect the worksheet, no changes can be made anywhere. If you only wish to protect certain data within your spreadsheet and not all of it, then you will first need to unlock the cells that do not require protection. This will allow users to continue working in the unlocked cells, but restrict them from changing the cells that are locked.
Now we can go ahead and protect our worksheet. The first thing that we need to do is click on the worksheet tab in need of protection and open our Protect Sheet dialog box. There are three ways to accomplish this and we are going to discuss each below:
-
Worksheet Menu
If you right-click on your worksheet tab, a menu will appear that has several options to choose from. Click on Protect Sheet…
-
Format Cells Drop-down Menu
On your Home tab in your Excel toolbar is your Cells box. There are icons to Insert, Delete or Format. Click on the arrow beneath Format and a drop-down menu will appear. You will find Protect Sheet… under the Protection heading and you should click on it.
-
Protect Sheet Command Icon
In the Review tab on your Excel toolbar is your Changes box. Click on the icon that says Protect Sheet.
Using any of the above methods will open your Protect Sheet dialog box, which should look like this:
- Once you have access to the Protect Sheet dialog box, you will need to make sure that the box is ticked at the top where it says Protect worksheet and contents of locked cells. Excel automatically checks this box for you. Do not uncheck it.
- The next step involves creating a password that must be entered if the worksheet needs to be unprotected at a later date. This is optional, but be sure to use a password that you will remember as they cannot be recovered.
- Now you need to give permission to users to perform certain functions. If there are people working on the spreadsheet on a continual basis, then you will need to authorize their ability to make changes in specific areas. An unchecked box means that no authorization is given. If you tick on a box, then users will be able to do that instruction within the spreadsheet.
Click OK at the bottom of the Protect Sheet dialog box once you have completed your instructions. If you have set a password, then Excel will prompt you to confirm it once more. Enter the password again, and click on OK.
You now have a worksheet that is protected as per your instructions. Users will not be able to fiddle with anything in locked cells and they will only be able to change what they have been authorized to. To unprotect your worksheet, remember to have your password ready if you have chosen to use one.
This article was written for you after several of our Excel training course students recommended it. Using the Protect Sheet function will prevent you from having to find and fix inadvertent changes in the future. To find out how Excel can help your spreadsheets perform optimally, watch our Excel video tutorials.
