Avoiding spreadsheet cells from any types of unwanted modifications become really important as no one want inadvertently change in cells, That should not be modified actually. Luckily, Microsoft Excel 2016 and many other of its earlier versions permit you in order to lock cells and avoid them from being modified. In case, you guys want to lock all the cells in a worksheet or secure a specific cell. Then allow some cells of the Excel file to be changeable then you can also do this. Let’s now read the article to know about How to Lock and Unlock Cells in Excel 2016. Let’s begin!
One can easily lock some particular cells and ranges before you avoid the worksheet and, optionally. Then permit users in order to modify only exact ranges of a locked sheet. Now, coming to the point, “how to Lock and Unlock Cells in Excel Worksheet?”
- 1 How to Lock and Unlock Cells in Excel 2016
How to Lock and Unlock Cells in Excel 2016
In this section you guys will catch information on how can you lock cells in Excel just through making few small changes in the settings.
- First of all, you need to open the Excel file and then tap on the Review tab.
- Then, tap on the Protect Sheet. A Protect Sheet window will appear. There you guys have to enter a password to protect the Excel sheet and any of the other actions as well.
- Tap on the OK to turn on the protection.
Now, your whole Excel sheet is protected from any types of modifications. Whenever anyone tries to edit any of the locked cells then the following message will appear:
The cells can only unlock whenever the sheet is unprotected. (through going to the Review tab again, selecting “Unprotect Sheet,” and entering the password, if required).
How To Lock Individual Cells in Excel Worksheet
With the help of above-discussed methods, all cells will get locked via default as it protects the whole sheet. However, if you want to lock or unlock a particular cell then you guys have to go through the below-given steps. These steps are very much important for those who actually wants to lock individual cells in Excel.
Such as: If you are handling an inventory list and you guys might want that unit rate and stock quantities to be editable. However, avoid the item IDs, names, or descriptions from any type of modification. Then you should follow this method.
- Here, first you have to select all the cells that you guys don’t want to be locked. All the cells selected via you can be edited even after the spreadsheet is protected as well.
- Now, right-click on your selection, you have to select Format Cells, and then head to the Protection tab. Or you can also navigate through, Home tab -> tap on the expansion icon -> Format Cells window -> Protection tab as well.
- Now unmark the “Locked” and then click OK.
- Then, head to the Review –> Protect Sheet and tap on the OK
All the cells that you guys have didn’t unlock through the above steps will be locked. However, the unlocked cells will be changeable as well.
How To Lock Cells along With Excel VBA
Another really catchy way to lock cells in Excel is via using the Excel VBA code. Although it’s a little bit long procedure, however really helpful in situation where other methods fail to work actually. So, it’s necessary to have knowledge of locking cells with Excel VBA.
- Just select entire cells in your Excel spreadsheet.
- Now head to the Home tab first and then to cells From this cell section click on the Format option and then select the format cells option.
- This will then open a Format Cells dialogue box. On this opened dialog box, you guys have to hit the Protection tab. There you have un-select the option Locked.
- Now in your Excel file where name of your sheet appears actually. Make a right tap from your mouse and select the View Code.
- This will now open Microsoft Visual Basic for Applications dialogue box. In this opened white sheet along with the dialogue box, you guys just have to type the below code.
- In the written code, we now have to select the data range from C7 to E11. And have also applied a password like, 1234.
- After you enter the complete code go to the Debug. There you will find a Run Sub/UserForm (F5) option click on it.
- Now you guys will see that your Excel worksheet is opening along with a password window.
- So, just write same password that you as allotted in the VBA code eg: 1234.
- After you do this whenever you approach for making changes in the desired cells of the Excel. This will then throw a warning message stating that “you can’t edit the cells as it is protected”.
Note: In order to unlock the cells locked along with Excel VBA code you have to follow the same unlocking procedures as explained earlier.
How to Unlock Specific Cells in a Worksheet?
- First you have to choose the locked cells that you want to unlock or modify.
- Tap on the “Home” tab and in the “Cells” area, and select “Format” > “Format Cells“.
- Head to the “Protection” tab.
- At this step, you guys have to unmark the box present along with the “Locked” in order to unlock the cells.
- Then tap on the “OK“.
- A dialog box will appear saying that “locking cells or hiding formulas that has no effect until you protect the worksheet.”
- In order to do that, just head to the “Review” tab and select “Protect Sheet“.
How To Lock Formulas In Microsoft Excel?
You have to follow the below given steps on how to lock a cell in Excel formula:
- First of all, you have to unlock all the cells.
- Now, choose all the cells that contain formulas. In order to do this you can use Go To Special option.
- Then you guys have to Lock these selected cells.
- Then secure the worksheet.
When you have locked formulas in Excel then the user can’t actually make any modifications to the cells having formulas.
How can you Hide Formulas In Excel?
In order to do this, follow the below given steps:
- Tap on the Control + A from the keyboard to choose all the cells present in the worksheet.
- Then, head to the Home and tap on the Find & Select.
- Now, from the drop-down menu you have to select Go to Special.
- Choose Formulas from the head to Special option.
- Then, tap on the OK. All the cells that contains formulas get selected.
- Now, you have to tap on the Control + 1 in order to open the format cells dialog box.
- At last, Format the Cells dialog box, and then just head to the Protection tab. Mark the Hidden option and then tap on the OK.
Well, I hope you like this article and understand all the steps. However, if you still have any problems and queries related to this article. Then feel free to ask me in the comments section below. I’ll get back to you shortly.