Al Arafat Siddique
This article illustrates how to unlock cells in Excel without a password with 4 different methods. Excel allows the feature to lock cells with passwords to protect them from editing, deleting, and even copying accidentally or deliberately. But unfortunately, it may happen to anyone to forget the password. Let’s dive into the methods to choose the suitable one for you to unlock the cells without the forgotten password.
How to Unlock Cells without Password in Excel: 4 Methods
Let’s say we have a password-protected worksheet without the password. To demonstrate the methods, we’ll use a dataset that represents the sale data of three months Jan, Feb, and March. The cells of the worksheet named Jan are protected with a password.
Try out the following methods to unlock cells from the protected sheet without the password.
1. Remove Password to Unlock Cells in Excel
With easy steps, we can remove the password that protects the Excel worksheet from editing. Here we have the Excel file that contains the password–protected cells. Make sure the “File name extension” option is checked from the View tab in the Windows File Manager.
Now, let’s follow the steps below to unlock cells without a password.
Step 1:
- Right-click on the file and select the Rename option from the context menu.
- Now add the .zip extension and hit Enter.
Step 2:
- The Excel file has been converted to a compressed zipped folder.
- After that, double click the zipped folder to open it and then open the xl folder.
- From the xl folder, now open the worksheets folder that holds the worksheets.
- As the first sheet (sheet1.xml) is the password–protected worksheet, copy it using right-click of the mouse or press Ctrl + C on the keyboard.
Step 3:
- Now paste it using Ctrl + V anywhere outside the zipped folder. We pasted it in the Desktop folder.
- Open the sheet1.xml file with notepad or any other code editor.
- By pressing Ctrl + F open the Find search box.
- Type Protection in the input box and hit Enter to find the word.
- Select the “<sheetProtection “ tag.
- Now drag the mouse to the right until it reaches the end of the tag i.e., “/>”.
- Delete the selected line of code and save it using Ctrl + S.
Step 4:
- Finally copy and paste this modified file to its original destination with the Copy and Replace option.
- After that, rename the sales–data.zip folder.
- Remove the .zip extension and add the .xlsx extension to make it an Excel file again. We deleted the sheet 1.xml file as it is not necessary anymore.
- Finally, open the file and click to edit the unlocked cells without a password.
Read More: How to Lock Certain Cells in Excel
2. Use of the Google Sheets to Unlock Cells without Password in Excel
To unlock cells in Excel that are password protected, we can use the help of Google Sheets. Follow the steps below to accomplish this.
Step 1:
- Open a new Google Sheet in your browser.
- From the File menu click on the Import option.
- Choose the Upload option and then click on the “Select a file from your device”.
Step 2:
- Select the file to unlock from computer storage and click the Open button.
- After that, click on the “Import data” button in the Import file window.
- The sales-data.xlsx file is now imported into the Google Sheets.
- Now go to the File menu and choose Microsoft Excel (.xlsx) option from the Download options.
- Save the file in your desired location and name it accordingly.
- As the final output, open the Excel file and edit the unlocked cells.
Similar Readings
- How to Protect Excel Cells from Deletion
- Protect Excel Cells But Allow Data Entry
- How to Protect Excel Cells from Being Edited
- How to Protect Cells Without Protecting Sheet in Excel
3. Run a VBA Code to Unlock Cells without Password in Excel
For Excel 2010 or lower version, we can run a password breaker VBA code to unlock cells of a protected spreadsheet. Follow the steps to open the visual basic editor and write the necessary code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module.
- Now copy and paste the following code.
Sub BreakPassword()
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "Password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End SubNow press F5 to run the code. The code will generate a password that is not the same as the original one. But put the password to unprotect the worksheet that’ll unlock the cells for editing.
Read More: Excel VBA to Lock Cells without Protecting Sheet
4. Unlock Cells by Copying the Contents to a New Worksheet in Excel
When we protect a sheet with a password, Excel gives us a number of options to select. We can allow the users to perform any of these actions on the locked cells of the protected sheet. By default, the “Select locked cells” option remains checked while protecting the sheet with a password.
If the option remained enabled, we could select the locked cells and copy them to a new sheet. Follow the steps below.
- Select the locked cells.
- Right-click the mouse and choose the Copy option or press Ctrl + C.
- Click the plus (+) button to create a new worksheet in the same workbook.
- Paste the copied cells in the new worksheet by pressing Ctrl + V.
In the new worksheet “Sheet1”, we can edit the unlocked cells without a password.
Read More: How to Lock Multiple Cells in Excel
Notes
- In method 4, we could also create a new workbook by pressing Ctrl + N and pasting the copied cells to unlock them without a password.
- The VBA code in method 3 could take a few minutes to run in the Visual Basic Editor depending on your computer’s processing speed.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to unlock cells in Excel without a password with 4 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
