Locking Specific Cells
Microsoft Excel allows you to restrict users’ ability to modify the contents of an entire workbook, a sheet, or particular cells.
Microsoft Excel allows you to restrict users’ ability to modify the contents of an entire workbook, a sheet, or particular cells. The ability to lock only particular cells can be very useful when you want to allow someone to modify parts of the sheet but have values or formulas that you want to remain unchanged. See the example of a spreadsheet that shows projected sales, revenue, and profits for seven different products based on a combination of set values and user inputs. In this example, you may want to allow users to modify the Price Per Unit and Units Sold values (cells in green), but other values like Cost Per Unit and the formulas that calculate Total Revenue, Total Profit, and the Grand Totals should remain fixed.
Cells typically start with a default format of locked, however, this doesn’t have any impact until you protect the sheet or workbook. If you only want to lock specific cells, you’ll need to unlock all of the other cells before you protect the sheet. The easiest way to do that is to select all of the cells in the sheet and then right-click and select Format Cells. In the Format Cells options, you’ll need to select the Protection tab. On the Protection tab, uncheck the Locked checkbox and click OK. Now all of the cells in your sheet are unlocked. That means that when the sheet becomes protected they will still be able to be edited by users.
Now that all of the cells are unlocked, select the cell(s) you want to restrict users from accessing. Go back into Format Cells and the Protection tab, and check Locked for these cells, and click OK. Now the specific cells you selected are the only locked cells in the sheet.
Now that you have the cells formatted the way that you want, all you need to do is protect the sheet to make sure that the cells you locked will be remain unchanged by users. To protect the sheet go to the Review ribbon, and click Protect Sheet. Make sure that Protect worksheet and contents of locked cells is checked, and then enter a password that will be used if you want to unprotect the sheet in the future. There are a number of additional options, allowing you to limit how much users are able to do within the worksheet. In this case, you want to allow the users to select both locked and unlocked cells. This means users will be able to click in any cell in the sheet but will only be able to change the values of the cells that were left unlocked. Once you click OK, you will be asked to confirm your password. Then you are all set to start sharing your password-protected worksheet.
