Data protection is the right of every user that’s why Excel offers a feature to protect your spreadsheets with a password. No other person can peek into your private data files without your permission and if someone tries to do so, you will get to know.
However, when you need to send a worksheet to other people, how would you stop them from making changes to the sheet without your consent? Yes, it happens. Sometimes you don’t want anyone else to edit the files you have finalized after so much effort. But when you need to share those files with others how would you not let them ruin it?
For this, Excel let you lock the worksheet so that no other person can use it. In an opposite scenario, when you have a locked spreadsheet to make changes in it, how would you unlock it?
Of course, by using a password you will open that sheet. What if you forgot the password or you don’t have access to the password?
Seems like a tough situation, isn’t it?
If you know the password, you can simply type it and open the file. Or else you will have to follow the below-given methods to understand how to unlock an Excel spreadsheet.
Copy the Data to Another Sheet to Edit it
In case, when you don’t have a password for a locked spreadsheet, and you need to unlock it for editing purposes, make a new sheet. Copy and paste the data to the new sheet.
Wait
This method works only when the locked sheet allows you to these options: “Select locked cells” and “Select unlocked cells”
- Select the entire data on the sheet to be unlocked.
- Press “CTRL + C” and copy the content.
- Click on the New sheet button and a new sheet will be added to the workbook. Or else you can press “CTRL + N” to add a new sheet.
- Press “CTRL + V” to paste the data on the new worksheet. All set! Now, you can make changes to the data available on the new sheet.
Bonus Tip: To automatically adjust the data in the new sheet, select the option “Keep Source Formatting” given under the Paste Options. If the worksheet has some external links, you will not be able to add them by default. You have to add those links manually.
Use VBA Code to Unlock the Locked Excel Sheet
Usually, this method is helpful with Excel spreadsheet 2007 and previous versions.
- Go to the Excel file and press “ALT + F11.”
- Choose the Insert option and click on Module.
- Now, paste the following VBA code to the module box:
Sub UnprotectSheet()
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 “One usable 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)
ActiveWorkbook.Sheets(1).Select
Range(“a1”).FormulaR1C1 = 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
- Press F5 or else click on the Run button to start the Macro.
- If the process works, you will see the password in a new box.
Note:
Try these codes on Excel 2007 and previous versions because it will not work on the latest editions.
