In this chapter, we’re going to learn about spreadsheet protection. We will learn how to lock cells and protect worksheets in order to restrict the kinds of things that a user can do on a given worksheet or within a given workbook. In a dashboard type of worksheet, using locked or unlocked cells helps users focus on what they should interact with instead of having to guess. These practices ensure a clear and consistent pattern, which means users can easily navigate the worksheet.
Use this workbook for the chapter.
Create a Dashboard
First, you’ll need to create a basic input and output workbook. (See Figure 35.1)
Next, we’ll make a make-believe dashboard to see how these functions might work.
- In Output 1, column H, multiply the first input by 20. The formula should read =D4*20.
- Copy the formula into all of the other H column Output cells.
- Use the Paste tool in the Home tab in the top right of the page. In the drop-down box under Paste, select Use Format so you’re only pasting the function.
- In Output 1, column J, take Output 1 from column H and multiply it by Input 1. The formula should read =H4*D4.
- Copy the formula into all of the other J column Output cells.
- Use the Paste tool in the Home tab in the top right of the page. In the drop-down box under Paste, select Use Format so you’re only pasting the function. In the end, the sheet should contain all of the information represented in Figure 35.2.
Locking the Worksheet
Now that we have a functional dashboard, it’s time to think about how we want the user to interact with it. In this example, we really only want them to change content in the Input cells, not the calculations in the Output cells. Let’s look at protection options for this sheet and workbook:
- Go to the Review Tab.
- Go to the Protect section (see Figure 35.3). This section allows you to protect certain parts of this Excel sheet: the sheet, the workbook, and certain ranges.
To begin, we’ll work with Protect Sheet. (See Figure 35.3)
- Select Protect Sheet. A popup box should appear titled Protect Sheet. (See Figure 35.4)
The sheet protection is automatically set to allow users to Select Locked Cells and Select Unlocked Cells. Locked and unlocked cells determine what a user can actually do. There are other features that will allow you to allow or disallow, so if you don’t want users to be able to do anything except click on cells that are unlocked, you can uncheck the Select Locked Cells box. (See Figure 35.5)
At the top of the Protect Sheet popup, there is a text-input cell titled Password to Unprotect Sheet. This allows you to set a password, but remember that it’s important that you keep the password safe because there isn’t any way to recover it if you forget it. Try adding a password to this sheet:
- Type the password 1234 into the textbox titled Password to Unprotect Sheet. (See Figure 35.5)
- Select OK.
- Reenter the password 1234 in the textbox titled Reenter Password to Proceed. (See Figure 35.6)
- Select OK.
The worksheet is now password protected and all of the cells are locked; you won’t be able to change or even select any cells in the sheet until you reenter the password.
Unlocking the Worksheet
To unlock the sheet, select Unprotect Sheet and enter the password 1234. (See Figures 35.7 and 35.8)
Note: In the future, we do not recommend that you use a password as it will make it difficult for others to check your work. The password feature is available, but for the BUS 115 class, you should not use it.
You can select which cells you’d like the user to be able to interact with. Use the following steps to lock specific cells.
- Ensure the sheet is Unprotected.
- Select all of the input cells. Use the CTRL key to select multiple cells.
- Right-click on the selected cells and click Format Cells (see Figure 35.10). You can also use the keyboard shortcut CTRL+1 as a shortcut.
- A popup titled Format Cells will appear. Select the Protection tab.
- There are two checkboxes; the first box, Locked, will automatically be checked. Uncheck that option and select OK. (See Figure 35.11)
- Go back to Protect Sheet and check the box Select Unlocked Cells. Select OK. (See Figure 35.12)
If you try to click any of the cells, you’ll notice that you’ll only be able to select the four Input cells you formatted earlier. You won’t be able to select any of the other cells in the worksheet.
Unprotect the sheet and protect it again. This time, check the Select Locked Cells box. Select OK. (See Figure 35.13)
This time when you click cells, you’ll notice you can select any of them, but if you try to change or delete the information in the Outputs box, you’ll receive a popup box telling you it can’t be changed.
Note: All cells are automatically locked by default in a new Excel sheet. So, you need to make sure that you have the appropriate cells unlocked while using the Protect Sheet. One way to do this a little more quickly is to use the Quick Access Toolbar.
Quick Access Toolbar
To create a button to quickly protect certain cells on a sheet, follow these steps:
- At the top green bar of your Excel, select the drop-down arrow to the left of the title of the workbook.
- Select more commands. (See Figure 35.14)
- A popup box titled Excel Options will appear.
- In the drop-down box under Choose Commands From, select the down arrow. Change it from Popular Commands to All Commands. (See Figure 35.15)
- You will be directed to a list of commands. Press L on your keyboard to quickly find Lock Cell. (See Figure 35.16)
- Select Lock Cell and then select Add.
- Select OK.
On the quick access bar, next to the drop-down button to the left of the title of the workbook, you’ll see a button that looks like a lock.
Now, when you select a cell that is locked, the icon is highlighted. If you select a cell that’s unlocked, the icon is not highlighted.
You can select single cells or an entire range and you can select the lock icon button to lock and unlock the cells quickly. When you go to the protect sheet menu (especially if you have a PivotTable or PivotChart on your sheet, the user will not be able to interact with these things unless the specific checkbox is checked.
Protect a Workbook
This chapter primarily focuses on worksheets, but briefly, we’ll cover protecting an entire workbook.
- In the Review tab in the Protection section, you will see an option called Protect Workbook. Selecting this option allows you to protect the workbook for structure with a password.
When we choose to protect the structure of the workbook, it means that users are not going to be able to make any typical changes. Locking the workbook is helpful in certain instances, but in the practices discussed in this text, you’ll mostly focus on protecting the sheets.