In Microsoft Excel, if you apply protection on a worksheet, all the cells on the sheet will be locked and could not be edited anymore. However, it is also possible to unprotect cells in Excel to enable cell editing while keeping other cells locked. It is user's choice to lock only specific cells or a range of cells before protecting Excel worksheet. MS Excel also gives the option to enable specific user to edit specific cells (or a range of cells) in a protected workbook.
Part 1. Unlock Cells in Excel with Known Password
You can always unlock cells in a worksheet if you have the password for this sheet. You just need to remove the protection of the worksheet. This can be done with the following two Methods.
Method 1: Right click the "sheet" tab and then from the "context menu", select the option of "Unprotect Sheet".
Method 2: Go to the "REVIEW" tab and click the "Unprotect Sheet" in the "Changes" group. You can edit any cells as soon as the Excel sheet is unprotected.
It is also possible if you want the user to edit certain cells or a range of cells. You can do that by allowing certain cells to be unlocked with a password. Follow these steps to unlock certain cells of Excel sheet.
Step 1. Open the unprotected Excel worksheet and select the range of the cells that you need to unprotect.
Step 2. Next, move to the "Review" tab, then go to the "Changes " group, select the option of "Allow Users to Edit Ranges".
Keep in mind that this feature will only be available if your Excel worksheet is unprotected. If you see that the button for "Allow Users to Edit Ranges" is greyed out, you need to unprotect the sheet first by clicking "Unprotect Sheet" button in the "Review" tab.
Step 3. Next, to add a new range, you need to click the "New…" button in "Allow Users to Edit Ranges" dialog box.
Step 4. When the dialog box of "New Range" is opened, go to the "Title" box, here you can set a range name for your cells. (Default name is "Range1").
Step 5. Next, you need to enter a cell or range of cell reference in the "Refers to cells" box. (There may be currently selected cells or ranges of cells included, by default).
Step 6. Type a password in the "Range password" box. Optionally, you can leave this empty, which means that you allow everyone to edit the selected range without having password. Next, press the "Ok" button.
Step 7. Next screen of "Confirm Password" will appear, here you need to insert the password again. After this, click "OK".
Step 8. This will open the dialog box of "Allow Users to Edit Ranges". If you want to add more cell ranges, you can add them at this step.
Step 9. At the bottom of the dialog box, click "Protect Sheet" button to enforce the sheet protection.
Step 10. To unprotect the sheet, insert the password in the "Protect Sheet" window and select the actions you allow using check boxes and click "OK". It is recommended to use different passwords for protecting the sheets and for unlocking the ranges.
Step 11. Re-type the password in the "password confirmation" window and click next. At this point, your Excel worksheet is protected and specific cells are also unlocked using the password that you inserted. Any user with that password can edit the cells.
If your computer is on a domain and the operating system is Windows XP or higher, you can set permissions for the specific domain users so that they may edit specific cells or ranges of cells even without having the password. This option is helpful in the case if you need to edit cells frequently and you do not want to enter password every time for editing those cells. Assuming you have already added certain ranges that are unlockable using a password (following the steps in previous section), follow these steps to allow certain users to edit cells.
Step 1. Go to the "Changes" group in the "Review" tab and click on "Allow Users to Edit Ranges". (if this option is grayed out, click "Unprotect Sheet" to remove the protection)
Step 2. In "Allow Users to Edit Ranges" window, select the range of cells for which you want to change the permissions and press the "Permissions…" button.
Step 3. On the "Permissions…" window,click on "Add…" button, this will open a new dialog box for "Enter the object names to select". Enter the name of users to allow them to edit certain cell range. Type the name of the domain user and click "Check Names" to verify the names. This will verify the name and apply the required format.
Step 4. After entering and validating the names of all users to give permissions, click the "OK" button.
Step 5. Specify the permission type in "Group or user names" for each user and press the "OK" button to save changes and close the dialogue box.
Part 2. Unlock Cells in Excel without Password
Passper for Excel is one of the most popular and powerful tools to recover the opening password and remove restrictions for MS Excel as it guarantees its users that no data will be deleted from the sheets or the workbooks. It has amazing features and capabilities to recover the password. Following are some of the features of Passper for Excel software:
100% useful to unprotect your Excel sheet and cells.
It is 100% secure to use Passper as it is the sub-brand of the highly recognized authorized iMyFone.
It supports all versions of MS Excel including: Excel 2019, 2016, 2013, 2010, 2007, 2003, 2000 and 97.
We offer 30-day money back guarantee if users have any tech issues with our software.
You can remove restrictions from Excel workbook/worksheet by following these steps.
Step 1. Open the program and select the option of "Remove Restrictions".
Step 2. Click on "Add" to import the Excel sheet which is password protected. When the file is imported in the program, click on the "Remove" option to remove the password protection.
Step 3. Password will be successfully removed from the file within two seconds.
So, if you do not have the password and still want to unlock the Excel sheet cells, you may follow the following two methods.
You can use "ZIP software" to unlock cells in a Excel worksheet even without having a password to unprotect the sheet. This method will work for you if you have Excel file with ".xlsx" format. In the case of ".xls" format, simply save the file as ".xlsx" format. Steps to unlock Excel cells without password are as follows:
Step 1. First, change the Excel file extension from ".xlsx" to ".zip" format.
Step 2. Now, you need to open this zip archive with "7-ZIP". For this, open the folder "xl > worksheet" and you can now see your excel sheets as xml files.
Step 3. For example, if you want to open sheet1 by unprotecting it. Select that xml file, right click it, then select edit and open it in Notepad.
Step 4. Search for the line/tag that begins with "sheetProtection" and delete that tag. Then save and close that xml file.
Step 5. If prompted, update the modified xml file in the ZIP archive and close the archive.
Step 6. In the last step, change the ZIP file extension back to ".xlsx" format. If all goes well, your Excel sheet is now unprotected. You can open and edit this file without using a password.
Excel worksheet protection is an amazing feature giving you the flexibility to lock or unlock certain cells using a password. But if you somehow lose or forgot the password, it can be really troublesome to retrieve your important data. You can use the methods described above to unprotect cells in Excel. But even if these methods do not work, you can still use "Passper for Excel" that is an amazing tool to unprotect the whole sheet without password.