How to Lock and Unlock cells in Excel is a question that almost every Excel user has inquired.
A lot of times it happens in the professional course that you share your spreadsheet and do not want the users to inadvertently change cells creating a confusion for others. This is where the whole concept of locking cells in Excel comes into the picture.
Locking a worksheet means that you are restricting the editing of the sheet to certain users only. In simpler words, you are giving access to only a few users, to make any changes in the sheet. When cells are locked, users won’t be able to reformat or edit any content in the sheet.
All cells in Excel worksheets or workbooks by default, are locked. However, the locked attribute is activated only when the Excel sheet is protected. Let’s dig deeper into the whole concept of How to lock cells in excel:
How to Lock Cells in Excel
There goes a simple process before you can exercise any locking or unlocking of cells in Excel, i.e. protecting the worksheet. This is one of the best features to secure and maintain the integrity of your data in Excel.
However, you have to protect the worksheet, as locking cells would be viable only when the worksheet is protected. So, in order to lock all cells on an Excel sheet, all you have to do is protect the sheet, as by default, all cells are locked.
Take a look at how to enable worksheet protection settings:
- Hover on the Review tab, and in the Changes group, select the Protect Sheet button.
- The Protect Sheet dialog box will appear, asking you to enter the password. In the Allow all users of this worksheet to list, just checkmark all the boxes to select the tasks that you want the users to exercise while unprotecting the worksheet. Click OK to finish.
And you are done! The worksheet will be protected. Though entering a password is optional, but it is recommended to keep a password as it adds to the security of the worksheet, as only the users with the password will be able to unprotect the sheet.
Note- Make sure that the password you choose is easy to memorize, because if by chance you forgot the password, you will not be able to access the protected worksheet.
Locking Specific Cells in Worksheet
Now, it may happen numerous times that you want to lock certain cells only and give the users access to modify or adjust the remaining cells. Fortunately, Excel comes with a way to exercise this technique. Take a look at How to lock and protect selected cells in Excel:
- Select the specific cells that you don’t want to be locked.
- Right-click on the cell that you have selected, scroll down to Format Cells and click on the Protection tab.
- You will see a ‘Locked’ option, which is selected by default. Uncheck this option and click OK.
- Now, simply follow the above- listed steps on protecting the worksheet.
There is one other way on How to Lock Individual Cells in a Worksheet:
- Simply select the cells that you want to keep unlocked.
- Go to the Home tab, hover over the Alignment tab.
- The Format Cell tab will open up, select the protection tab. Uncheck the Locked option that is selected by default. Click OK to finish.
Choose the method, which seems easier. More or less, both the methods are almost similar to each other.
Note- You van even use the keyboard shortcut, i.e. press Ctrl+1 to open the Format Cells dialog box.
Unlocking all Cells in Excel
In order to unlock the cells, all you have to do is remove the worksheet protection, i.e. just unprotect the worksheet.
- Right-click the sheet tab, and select Unprotect Sheet.
- Or you can go to the Review tab and in the Changes group, click the Unprotect Sheet button.
- In case the sheet is a password-protected worksheet, you have to enter the password to unlock the sheet. Just type the password and click OK.
And you are done. The worksheet is unprotected and not you can edit or make changes in any cell of the sheet. This is all it takes to unlock the sheet.
Unlocking Specific Cells on a Protected Excel Sheet
Let’s say the whole sheet is locked, but there are certain specific cells that you want to keep open for users to edit. This is possible, i.e. certain cells on a sheet can be unlocked with a password.
This feature works only in an unprotected sheet, i.e. before you can exercise this feature make sure that the sheet is unprotected. Take a look at the steps:
- Choose the cells or ranges that you want to unlock.
- Hover on the Review tab, and select Changes group, then click Allow Users to Edit Ranges.
- A dialog box will pop up on the screen. Tap on the ‘New’ button to add a new range.
- The New Range dialog window opens up. You will see the following:
a) Title: In this box, type in the name of the range that you want to select.
b) Refer to cells: Type in the cell or range reference. Type the number with which you want to begin the range to the number with which you want to end the range.
c) Password- Punch in the password and click the OK button.
- A window will appear on the screen, asking you to confirm your password. Just re-type the password and click OK.
- Now, you can see under the Allow Users to Edit Ranges dialog box, that the new range is listed. Click on the Protect sheet button to enable sheet protection. Click OK to finish.
- The protect sheet window sheet opens up. Type the password and checkmark all the boxes to select the tasks that you want the users to exercise. Click OK.
Note- Make sure you keep separate passwords for locking the sheet and unlocking the cells.
Locking cells in Excel or protecting your worksheet is a great way to preserve and maintain the integrity of your original data. Once you protect a sheet, users can only look at the sheet and cannot change anything in it.
This was all about How to lock cells in excel. So, next time when you forward your confidential or important spreadsheet that you want to keep intact, just simply exercise these locking and unlocking tricks to make your task easier.