By locking certain cells in your workbook, you can guide your users into other sections of your workbook and essentially convey the idea that the locked cells are to be treated as outputs, and all others inputs. This is especially useful in applications where you want to distribute the workbook to larger audiences. In this article, we’re going to show you how to lock cells in Excel to protect them, and prevent other users from editing.
Locking cells in Excel is a 2-step process. First, you need to set the cells you want to lock. Next, you need to protect the worksheet itself. Let's take a closer look at these two steps.
You can set a cell's Locked status from the Format Cells dialog. The Format Cells dialog is where you can change the number formatting, alignment, or color of a cell, as well as its protection properties. Follow the steps below to set a cell or cells as locked.
- Select the cells you want to lock
- Press Ctrl+1 to open the Format Cells dialog
- Go to the Protection tab
- Enable the Locked property
- Click OK to save your settings
- Upon selecting cells, right-click and choose the Format Cells… option
- Click one of the small arrows in either one of the Font, Alignment, or Number categories under Home
Protecting a Sheet
Setting a cell as locked makes it ready to be locked when its parent worksheet becomes protected. After setting which cells are to be locked, let's now protect the entire worksheet.
Excel's protection commands reside under the Changes section of the Review tab in the Ribbon. Use Protect Sheet icon to lock cells in the active worksheet.
Upon clicking Protect Sheet icon, you will see the Protect Sheet dialog. In the dialog, you can set a password to unlock and and also define what users can do once protection is enabled. By default, Select locked cells and Select unlocked cells actions are selected. If you leave only these two options selected, the users will only be able to select cells, regardless of the cell's locked or unlocked state.
Clicking the OK button completes the locking and protection process. You need to unprotect the sheet for any further modifications in the future.
Unprotecting a Sheet
The unprotecting process is fairly simple. You need to use the Protect sheet button again. However, this button will be labeled as Unprotect Sheet when you're on a protected sheet.