How to Unlock Cells without Password in Excel (4 Methods) 

Get FREE Advanced Excel Exercises with Solutions!

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.

Unlock Cells in Excel Without 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 passwordprotected cells. Make sure the “File name extension” option is checked from the View tab in the Windows File Manager.

Unlock Cells in Excel Without Password

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.

Unlock Cells in Excel Without Password

  • Remove the .xlsx extension.

  • 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.

Unlock Cells in Excel Without Password

  • From the xl folder, now open the worksheets folder that holds the worksheets.

Unlock Cells in Excel Without Password

  • As the first sheet (sheet1.xml) is the passwordprotected worksheet, copy it using right-click of the mouse or press Ctrl + C on the keyboard.

Unlock Cells in Excel Without PasswordStep 3:

  • Now paste it using Ctrl + V anywhere outside the zipped folder. We pasted it in the Desktop folder.

Unlock Cells in Excel Without Password

  • 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.

Unlock Cells in Excel Without Password

  • Select the “<sheetProtection “ tag.

Unlock Cells in Excel Without Password

  • Now drag the mouse to the right until it reaches the end of the tag i.e., “/>”.

Unlock Cells in Excel Without Password

  • 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.

Unlock Cells in Excel Without Password

  • After that, rename the salesdata.zip folder.

Unlock Cells in Excel Without Password

  • 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.

Unlock Cells in Excel Without Password

  • Finally, open the file and click to edit the unlocked cells without a password.

Unlock Cells in Excel Without 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.

Unlock Cells in Excel Without Password

  • The sales-data.xlsx file is now imported into the Google Sheets.

Unlock Cells in Excel Without Password

  • Now go to the File menu and choose Microsoft Excel (.xlsx) option from the Download options.

Unlock Cells in Excel Without Password

  • 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


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 Sub

Now 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.

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 Sub

  • 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.

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 Sub

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.


Related Articles

Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo