Today’s topic is password protecting your worksheet.
In my professional career I’ve had to make many spreadsheets for other people to use. This is always a tricky situation because regardless of Excel skills, there will always be an information asymmetry between the developer and user of the spreadsheet. Even if the spreadsheet developer uses the most clear formatting and annotation there is still a risk for the user to delete something, insert something, or paste something where it isn’t supposed to go.
To help combat this, it is sometimes wise to password protect a spreadsheet. Excel actually gives you quite a bit of flexibility when it comes to password protection. You can protect just a single cell of a spreadsheet all the way to the entire sheet and you also have a wide array of protection options. For example, you can set the parameters such that users can change the cell color, but can’t change the cell value. Or you could allow users to sort the data, but not use the Auto-Filter. Let’s look at some basic examples:
Download the example spreadsheet here.
Look at the first tab labeled “Example 1.” Every single cell is locked except for C4. Next, go to “Example 2” which demonstrates the opposite effect. The only cells that are locked are the ones with text.
To recreate what I did in the above examples follow these steps.
Example 1 – Only one cell can be manipulated
- Open a new workbook, select cell C4 and press Ctrl+1 (Mac: CMD + 1) which calls up the “Format Cells” box then go to the “Protection” tab.
- This is super important: by default every cell in Excel is locked. Thus, since we want users to be able to edit C4 we want to unlock the cell. Make sure the check box is empty then click OK.
- From the “Review” tab, in the “Changes” section, click on “Protect Sheet”. Keyboard shortcut Alt → R → PS. For a Mac go to the “Review” tab, in the “Protection” section select “Sheet.”
- You should now see the “Protect Sheet” box which allows you to set a password and fully customize your protection options. See image below.
- Come up with a password and type it in. Leave the other options as the default settings. Click OK and then type the password again after Excel prompts you for the password confirmation.
Example 2 – Selective Protection
In the first example everything was locked except for a single cell. This was easy to do because by default every cell in Excel is locked. But what if you wanted to set it up in reverse? It’s actually quite simple:
- Press Ctrl+A (Mac: CMD + A) to select all the cells in Excel
- Press Ctrl+1 to open the “Format Cells” box then go to the “Protection” tab
- Uncheck the “Locked” box and then click OK.
Now every single cell in Excel is unlocked. After doing this, you can follow the steps from the first example, but instead of unlocking specific cells you’ll be locking them.
As I previously mentioned, there are lots of different options you can turn on when protecting a worksheet. Be sure to take a look at a few of them and get an understanding of what you’re able to do with this feature in Excel.
Loading ...