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.
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.
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:
To begin, we’ll work with Protect Sheet. (See Figure 35.3)
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:
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.
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.
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.
To create a button to quickly protect certain cells on a sheet, follow these steps:
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.
This chapter primarily focuses on worksheets, but briefly, we’ll cover protecting an entire workbook.
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.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/spreadsheet_protecti.