Which feature do you use to restrict the entry of a cell to a certain data range?
The tutorial explains how to lock a cell or certain cells in Excel to protect them from deleting, overwriting or editing. It also shows how to unlock individual cells on a protected sheet by a password, or allow specific users to edit those cells without password. And finally, you will learn how to detect and highlight locked and unlocked cells in Excel. Show
In last week's tutorial, you learned how to protect Excel sheets to prevent accidental or deliberate changes in the sheet contents. However, in some cases you may not want to go that far and lock the entire sheet. Instead, you can lock only specific cells, columns or rows, and leave all other cells unlocked. For example, you can allow your users to input and edit the source data, but protect cells with formulas that calculate that data. In other words, you may want to only lock a cell or range that shouldn't be changed. How to lock cells in ExcelLocking all cells on an Excel sheet is easy - you just need to protect the sheet. Because the Locked attributed is selected for all cells by default, protecting the sheet automatically locks cells. If you don't want to lock all cells on the sheet, but rather want to protect certain cells from overwriting, deleting or editing, you will need to unlock all cells first, then lock those specific cells, and then protect the sheet. The detailed steps to lock cells in Excel 365 - 2010 follow below. 1. Unlock all cells on the sheetBy default, the Locked option is enabled for all cells on the sheet. That is why, in order to lock certain cells in Excel, you need to unlock all cells first.
2. Select cells, ranges, columns or rows you want to protectTo lock cells or ranges, select them in a usual way by using the mouse or arrow keys in combination with Shift. To select non-adjacent cells, select the first cell or a range of cells, press and hold the Ctrl key, and select other cells or ranges. To protect columns in Excel, do one of the following:
To protect rows in Excel, select them in a similar manner. To lock all cells with formulas, go to the Home tab > Editing group > Find & Select > Go To Special. In the Go To Special dialog box, check the Formulas radio button, and click OK. For the detailed guidance with screenshots, please see How to lock and hide formulas in Excel. 3. Lock selected cellsWith the required cells selected, press Ctrl + 1 to open the Format Cells dialog (or right-click the selected cells and click Format Cells), switch to the Protection tab, and check the Locked checkbox. 4. Protect the sheetLocking cells in Excel has no effect until you protect the worksheet. This can be confusing, but Microsoft designed it this way, and we have to play by their rules :) On the Review tab, in the Changes group, click the
Protect Sheet button. Or, right click the sheet tab and select Protect Sheet… in the context menu. You will be prompted to enter the password (optional) and select the actions you want to allow users to perform. Do this, and click OK. You can find the detailed instructions with screenshots in this tutorial: How to protect a sheet in Excel. Done! The selected cells are locked and protected from any changes, while all other cells in the worksheet are editable. If you are working in Excel web app, then see how to lock cells for editing in Excel Online. How to unlock cells in Excel (unprotect a sheet)To unlock all cells on a sheet, it is sufficient to remove the worksheet protection. To do this, right-click the sheet tab, and select Unprotect Sheet… from the context menu. Alternatively, click the Unprotect Sheet button on the Review tab, in the Changes group: For more information, please see How to unprotect an Excel sheet. As soon as the worksheet is unprotected, you can edit any cells, and then protect the sheet again. If you want to allow users to edit specific cells or ranges on a password-protected sheet, check out the following section. How to unlock certain cells on a protected Excel sheetIn the first section of this tutorial, we discussed how to lock cells in Excel so that no one even yourself can edit those cells without unprotecting the sheet. However, sometimes you may want to be able to edit specific cells on your own sheet, or let other trusted users to edit those cells. In other words, you can allow certain cells on a protected sheet to be unlocked with password. Here's how:
Now, your worksheet is password protected, but specific cells can be unlocked by the password you supplied for that range. And any user who knows that range password can edit or delete the cells contents. Allow certain users to edit selected cells without passwordUnlocking cells with password is great, but if you need to frequently edit those cells, typing a password every time can be a waste of your time and patience. In this case, you can set up permissions for specific users to edit some ranges or individual cells without password. Note. This features works on Windows XP or higher, and your computer must be on a domain. Assuming that you have already added one or more ranges unlockable by a password, proceed with the following steps.
Note. If a given cell belongs to more than one range unlocked by a password, all users who are authorized to edit any of those ranges can edit the cell. How to lock cells in Excel other than input cellsWhen you've put a lot of effort in creating a sophisticated form or calculation sheet in Excel, you will definitely want to protect your work and prevent users from tampering with your formulas or changing data that shouldn't be changed. In this case, you can lock all cells on your Excel sheet except for input cells where your users are supposed to enter their data. One of possible solutions is to use the Allow Users to Edit Ranges feature to unlock selected cells, as demonstrated above. Another solution could be modifying the built-in Input style so that it not only formats the input cells but also unlocks them. For this example, we are going to use an advanced compound interest
calculator that we created for one of the previous tutorials. This is how it looks like: Users are expected to enter their data in cells B2:B9, and the formula in B11 calculates the balance based on the user's input. So, our aim is to lock all cells on this Excel sheet, including the formula cell and fields' descriptions, and leave only the input cells (B3:B9) unlocked. To do this, perform the following steps.
If Excel's Input style does not suit you for some reason, you can create your own style that unlocks selected cells, the key point is to select the Protection box and set it to No Protection, as demonstrated above. How to find and highlight locked / unlocked cells on a sheetIf you have been locking and unlocking cells on a given spreadsheet multiple times, you may have forgotten which cells are locked and which are unlocked. To quickly find locked and unlocked cells, you can use the CELL function, which returns information about the formatting, location and other properties if a specified cell. To determine the protection status of a cell, enter the word "protect" in the first argument of your CELL formula, and a cell address in the second argument. For example:
If A1 is
locked, the above formula returns 1 (TRUE), and if it's unlocked the formula returns 0 (FALSE) as demonstrated in the screenshot below (the formulas are in cells B1 and B2): It cannot be easier, right? However, if you have more than one column of data, the above approach is not the best way to go. It would be far more convenient to see all locked or unlocked cells at a glance rather than sorting out numerous 1's and 0's. The solution is to highlight locked and/or unlocked cells by creating a conditional formatting rule based on the following formulas:
Where A1 is the leftmost cell of the range covered by your conditional formatting rule. As an example, I've created a small table and locked cells B2:D2 that contain SUM formulas. The following screenshot demonstrates a rule that highlights those locked cells: Note. The conditional formatting feature is disabled on a protected sheet. So, be sure to turn off the worksheet protection before creating a rule (Review tab > Changes group > Unprotect Sheet). If you don't have much experience with Excel conditional formatting, you may find the following step-by-step instructions helpful: Excel conditional formatting based on another cell value. This is how you can lock one or more cells in your Excel sheets. If someone knows any other way to protect cells in Excel, your comments will be truly appreciated. I thank you for reading and hope to see you on our blog next week. You may also be interested inWhat feature restricts the entry of a cell to a certain data range?Excel Data Validation is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can be entered into a certain cell. Here are just a few examples of what Excel's data validation can do: Allow only numeric or text values in a cell.
What tool is used to restrict what is entered in a cell?By setting up Data validation, you can ensure users only enter text of a certain length to cells, or ensure they only enter a number between a specific range. A common use of data validation is to create a drop down containing a list that users can select between.
What tool would you use to prevent the input in a cell outside a specific range?You can use data validation to restrict the type of data or values that users enter into cells. For example, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook.
Which feature is used to control data entry Excel?Data validation is a feature in Excel which is used to control what users can enter into a cell.
|