In certain cases, we want to be able to lock some cells on excel to protect the formula or content in the cell itself.
The goal can be to lock the excel formula in certain cells or even vice versa you want some excel cells can not be edited and some other cell locations can be changed its contents.
In Microsoft Excel, there are 3 levels or levels of protection. protection of files level, workbooks protection level and the worksheets or sheets protection level. Another additional level of protection is for Macro VBA code access.
In certain cases you may want only a part of the cell to be locked from change. And in other cases you want the opposite, on certain sheets all cells are protected except for a few cells or ranges.
First, you have to determine whether you want to protect some cells or do you want to not lock certain cells or ranges?
Your choice will affect the steps you must take to lock certain cells in Excel.
Locking certain cells
For this first choice you want all excel cells to be unlocked or protected except for a few excel cells or ranges. This means you want the file user to be able to change anything on a sheet except for some cells that you specify to not be able to edit its contents.
In this example you want every cell in Range A1: G2 locked so that it cannot be changed by the user.
The main purpose of this section is to limit certain cells or ranges to be protected, so that users cannot change their contents.
The steps to protect some of these cells are as follows:
Deactivates Cell protection
By default all cells of a sheet are basically locked when you enable sheet protection.
For that the first step you need is to deactivate the protection settings. The trick is as follows.
1. Block or select all cells in a sheet associated with the shortcut Ctrl + A or click the triangle icon in the top left corner of the sheet.
2. In one of the selected cells, right-click then select Format Cells menu.
3. Next will appear Format Cells Dialog. In this dialog box select or activate the Protection Tab.
4. On the Protection Tab, uncheck the Locked option.
5. Click OK to close the Format Cells dialog box
You can also access the “Format Cells” dialog box via the Home – Group Cell – Format – Format Cells Tab menu
Alternatively, for a shorter and easier way you can deactivate the protection of all cells in the following way:
1. Block or select all cells in a sheet associated with the shortcut Ctrl + A or click the triangle icon in the top left corner of the sheet.
2. Then disable the protection by clicking on the Lock Cell menu in the Home tab – Cell Group – Format.
At this stage the main objective is to disable lock cell settings for all excel cells in the assembled sheet.
Lock a small portion of a cell
The second step, after adjusting all unprotected cells (unlocked cells) is to activate the lock cell settings on certain cells that you need. In this example the cell or range in question is Range A1: G2.
The trick is as follows:
1. Block / range selection A1: G2. Please adjust the range to be locked as needed.
2. In one of the selected cells, right click then select Format Cells menu.
3. After the Format Cells Dialog Box appears, activate the Protection Tab.
4. In the Protection Tab, make sure the Locked option is checked.
5. Click OK to exit the Format Cells Dialog.
For an easier way you can use the Lock cell menu as before in the Home tab – Group Cell – Format.
After setting the lock cell is correct and in accordance we proceed to the next step.
Enable Sheet Protection
In the previous steps you have disabled protection for all cells except for certain cells.
Then all you need to do is enable sheet protection as explained on this page: How to Protect Excel Sheet
Here are the steps:
1. Open the Review Tab – Group Changes then select the protect sheet menu.
2. Check the Protect worksheet and contents of locked cells option
3. Determine what options you allow the user file to do in the Allow all users of this worksheet to: section. Details about these options will be explained in the next section.
4. Write down the password that you will use to open the sheet protection.
5. Click OK and finish.
Done, now you have managed to lock or protect certain cells in Excel which in this case is the range A1: G2.
How to Open the Protection of Certain Cells in Excel
If in the previous section we locked certain cells, in this section the opposite will be explained, namely not locking or protecting certain cells or ranges on an excel sheet when you activate a password or sheet protection.
That is, you will exclude some cells or ranges from protection so that they are not locked and can be changed or filled when sheet protection is active.
For example, suppose you create the following form on an excel sheet. Then you want when sheet protection is activated, every cell in range D5: D10 is not protected so that it can be filled by the user of the excel file.
The steps you need are as follows:
Disable Lock Cell
The first step you need to do is disable the Lock Cells setting on the cell or range you need.
In this example the range is D5: D10. The method is almost the same as before namely:
1. Blocks or range selection that will not be locked (Range D5: D10)
2. Open the Format cells dialog box by clicking the Format Cells menu in the Home tab – Group Cell – Format or by right clicking on the selected cell and then selecting the Format Cells menu.
3. After the Format Cells Dialog Box appears, activate the Protection Tab and make sure the Locked option is not checked.
4. Click OK to exit the Format Cells Dialog.
After making sure that the Locked setting on certain cells is not active (not checked), the next step is to activate the sheet protection.
Enable Sheet Protection
Once you are sure the cell or range that you want is not locked, the step you need is to activate the worksheet protection as explained earlier. Namely by accessing the Protect Sheet menu on the Review Tab – Changes Group then proceed to set some parameters that you need.
For an explanation of the parameters or settings for this Protection Sheet, it will be explained in the next section.
Excel Sheet Protection Settings
When activating Sheet Protection, a dialog box like this will appear:
What do the parameters mean? Following is the explanation.
Protect worksheets and contents of locked cells: If you check this section then when you click OK you will activate the relevant Sheet protection. so that every cell that is set as locked on the sheet will be locked.
Password to unprotect sheet: This section sets the password or password to be used to open protection. The password here is optional. If you want the sheet to be locked without a password, leave this section blank. Selecting this option aims to prevent accidental changes to the contents of a sheet.
Allow all users of this worksheet to: This section sets several parameters about what users can do when a sheet is locked. Explanation of each part you can learn in the table below.
If the following settings are checked / active when the sheet is protected the user will be allowed to do things as described in the following list:
Excel Worksheet Protection Options
Options – Allow You To
Select locked cells Select, select or activate locked cells. By default, you are allowed to select locked cells.
Select unlocked cells Select, select or activate cells that are not locked. By default, you can select cells that are unlocked, and can use the TAB key to move between cells that are not locked on a protected worksheet.
Format cells Change the Format of a Cell or Conditional Formatting to a locked cell.
Format columns Use each column formatting command, such as changing the width of a column or hiding a column.
Format rows Use each row formatting command, such as changing row height or hiding rows.
Insert columns Allows you to insert columns
Insert rows Allows you to insert rows
Insert hyperlinks Insert hyperlinks, including locked cells
Delete columns Delete a column.
Delete rows Delete rows.
Sort Uses each command to sort data
Use AutoFilter Use the filter feature in the range cell When AutoFilter is active. Including enabling or disabling Autofilter
Use PivotTable reports Format, change layouts, refresh or change PivotTable reports including creating new PivotTables.
Edit objects Make changes to graphic objects including maps, charts, shapes, text boxes, and any controls that you did not unlock before you protected the worksheet. Make chart changes, such as formatting. Add or edit comments.
Edit scenarios Displays the scenario that you are hiding, makes changes to the scenario that you have prevented from changing, and deletes this scenario.
I hope that this posting will help you understand what to do when you want to lock cells on Excel.
