Sometimes you donโt want some cells in your excel sheets to be altered. You can do so by learningย how to lock or unlock cells in excel.ย
Microsoft Excel provides us with an excellent way to store our data in a tabulated and organized form. But this data can be altered when shared among other people. If you want to protect your data from deliberate changes, then you can protect your Excel sheets by locking them. But, this is an extreme step that may not be preferable. Instead, you can lock particular cells, rows, and columns too. For instance, you can allow users to enter specific data but lock the cells with important information. In this article, weโll see different ways to lock or unlock cells in Excel.
How To Lock Or Unlock Cells In Excel?
You can either lock the entire sheet or just choose individual cells depending upon your choices.
How to Lock All Cells in Excel?
To protect all the cells in Microsoft Excel, you simply have to protect the entire sheet. All the cells in the sheet will be protected from any over-writing or editing by default.
1. Select โProtect Sheetโ from the bottom of the screen in โWorksheet Tabโ or directly from the โReview Tabโ in the Changes group.
2. The โProtect Sheetโ dialogue box appears. You can either choose to protect your excel sheet with a password or leave the โpassword protect your excel sheetโ field empty.ย
3. Choose the actions from the list that you want to allow in your protected sheet and click on โOK.โ
4. If you choose to enter the password, a โconfirm passwordโ dialogue box will appear. Type your password again to finish the process.
Also read: How to Remove Password from Excel File
How to Lock and Protect Individual Cells in Excel?
You can lock single cells or a range of cells by following the steps below:
1. Select the cells or ranges that you want to protect. You can do it with a mouse or by using the shift and arrow keys on your keywords. Use the Ctrl key and mouse to select non-adjacent cells and ranges.
2. If you want to lock whole column(s) and rows(s), you can select them by clicking their column or row letter. You can also choose multiple adjacent columns by right-clicking on the mouse or using the shift key and mouse.
3. You can also select only the cells with formulas. In the Home tab, click on Editing group and then โFind and Selectโ. Click on Go to Special.
4. In the dialogue box, select the Formulas option and click OK.
5. Once you have selected the desired cells to be locked, press Ctrl + 1 together. โFormat Cellsโ dialogue box will appear. You can also right-click on the selected cells and choose the Format cells option to open the dialogue box.
6. Go to the โProtectionโ tab and check the โlockedโ option. Click on OK, and your work is done.
Note:ย If you are trying to lock cells on a previously protected Excel sheet, youโll need to unlock the sheet first and then do the above process. Youย can lock or unlock cells in Excelย in 2007, 2010, 2013, and 2016 versions.
How to Unlock and Unprotect Cells in Excel Sheet?
You can directly unlock the entire sheet to unlock all cells in Excel.
1. Click on โUnprotect Sheetโ on the โReview tabโ in the changes group or click on the option by right-clicking on the Sheet tab.
2. You can now make any changes to the data in cells.
3. You can also unlock the sheet using the โFormat Cellsโ dialogue box.
4. Select all cells in the sheet by Ctrl + A. Then press Ctrl + 1 or right-click and choose Format Cells. In the โProtectionโ tab of the Format Cells dialogue box, uncheck the โLockedโ option and click OK.
Also Read: Fix Excel is waiting for another application to complete an OLE action
How to Unlock Particular Cells in a Protected Sheet?
Sometimes you may want to edit specific cells in your Protected Excel sheet. By using this method, you can unlock individual cells on your sheet using a password:
1. Select the cells or ranges that you need to unlock in a protected sheet by a password.
2. In the โReviewโ tab, click on the โAllow users to edit Rangesโ option. You need to unlock your sheet first to access the option.
3. The โAllow users to Edit Rangesโ dialogue box appears. Click on the โNewโ option.ย
4. A โNew Rangeโ dialogue box appears with Title, Refers to cells, and Range password field.
5. In the Title field, give a name to your range. In the โRefers to cellโ field, type the range of cells. It already has the selected cells range by default.
6. Type the password in the Password field and click on OK.
7. Type the password again in the โconfirm passwordโ dialogue box and click OK.ย
8. A new range will be added. You can follow the steps again to create more ranges.
9. Click on the โProtect Sheetโ button.
10. Type a password in the โProtect Sheetโ window for the entire sheet and choose the actions you want to allow. Click OK.
11. Type the password again in the confirmation window, and your work is done.ย ย
Now, even though your sheet is protected, some of the protected cells will have an extra protection level and would be unlocked only with a password. You can also give access to the ranges without having to enter a password every time:
1. When you made the range, click on the โPermissionsโ option first.
2. Click on Add button in the window. Enter the name of the users in the โEnter the object names to selectโ box. You can type the user name of the person as stored in your domain. Click on OK.
3. Now specify the permission for each user under โGroup or user namesโ and check the Allow option. Click on OK, and your work is done.
Recommended:
- How to Swap Columns or Rows in Excel
- How to convert Excel (.xls) file to vCard (.vcf) file?
- Quickly Switch Between Worksheets in Excel
- How to Fix Whatsapp Images Not Showing In Gallery
These were all the different ways in which you can lock or unlock cells in Excel. Knowing how to protect your sheet is very necessary to protect it from accidental changes. You can either protect or unprotect cells in an Excel sheet all at once or select a particular range. You can also give certain users access with or without a password. Follow the steps above carefully, and you shouldnโt have a problem.