Many of us protect the sheets using passwords, but sooner or later forget it. While this may seem like a big issue. But owing to the fact that Excel has a very low level of security, we can unprotect the files without much of a hassle. In this article, we will explain how to unprotect excel sheet without password, step by step.
Unlock Excel spreadsheet with VBA code (for Excel 2010 and lower)
If you are using Excel 2010 or older, you can unlock a sheet with a password with the following steps:
- Open your Excel document and switch to the password-protected sheet. You can do it by clicking on the file icon, it will open a list of options, select the info tab and click on the protect workbook.(it should be active when you run the code).
- Press Alt + F11 to open the Visual Basic Editor.
- Right-click the workbook name on the left pane (Project-VBAProject pane) and select Insert > Module from the context menu.
- In the window that appears, paste in the following code:
‘Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim i 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)
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
- Press F5 or click the Run button on the toolbar and wait a couple of minutes.
- The macro will report a cracked password, which is not the original one (always some combination of A’s and B’s), but nevertheless, it works. You do not need to remember this fake password or enter it anywhere. Simply click OK and the sheet is unprotected!
Tips and notes:
- If your Excel file contains more than one protected sheet, run the macro for each sheet individually to unprotect it.
- To unprotect Excel sheet without password in the modern versions of Excel 2019, Excel 2016 and Excel 2013, you can save the document as Excel 97-2003 workbook (*.xls) first. Then, close Excel and reopen the workbook – it will be opened in compatibility mode. Run the macro to unprotect the worksheet, and then save the workbook back as a .xlsx file. Alternatively, you can use one of the free tools, for example, Password Protection Remover add-in for Excel. Or you can upload the protected Excel worksheet to your Google Drive, and then open it with Google Sheets
Copy the protected sheet contents to another worksheet
Another way (or rather a workaround) to unlock an Excel sheet that was protected with a password is to copy its entire contents to a new sheet. Here’s how:
- Open the password-protected sheet, this is the sheet which has the password you have forgotten or misplaced.
- With the cursor positioned in cell A1, press Shift + Ctrl + End to select all used cells in the worksheet.
- Press Ctrl + C to copy the selected cells.
- Create a new sheet in the current workbook or in a different workbook (to create a new Excel file, simply press Ctrl + N).
- In the new blank worksheet, place the cursor in A1 and press Ctrl + V to paste the contents of the password-protected sheet.
It is important to understand that:
- This method works only if the Select locked cells and Select unlocked cells actions are allowed in the protected spreadsheet.
- If the password-protected worksheet has external links to other sheets or workbooks, you may need to re-create those links manually.
- You can use Paste Special to copy/paste the column widths so you won’t need to adjust them in the new sheet manually.