How to remove protections from Excel files

As everyone knows, Microsoft Excel is extensively used to store information. It is necessary to keep your Excel documents safe from thieves that steal or modify your valuable data. Imposing restrictions on your Excel files stops spies from altering and deleting your worksheets. Encrypting Excel files with open passwords prevents intruders from checking the content. But sometimes issues arise if you forget the password. You can’t edit cells in worksheets or delete unwanted sheets. Or you need to access the file urgently but can not open it. It is indeed a headache if the password is lost. But don’t be worried. The problem can be solved. In this article, I’ll teach you how to remove protections from Excel files so that you can regain the control of your data or the access to your files.

#1. How to unlock a password protected Excel workbook or worksheet with 7-ZIP

We have an easy method to get rid of restrictions from a workbook or worksheet. All you need to do is to change the Excel file to an archive, manually delete the code and convert the file back to the one in Excel format. I would like to mind you that it doesn’t work if your file is encrypted with an open password. So please make sure the file is not protected by an open password.

Step 1.

  • Install 7-ZIP because we will use it to edit parts of an archive.

Step 2.

  • Rename the file extension “.xlsx” to “.zip”. Click on “Yes” in the warning prompt dialog.

Step 3.

  • Open the zip file with the 7-Zip program and you will see some files and folders. Double click on the “xl” folder to open it.
Open the “xl” folder in the archive

Option 1. Remove the protections from the workbook.

  • Right-click on “workbook.xml” and select “Edit” in the menu. This .xml file will be opened in the notepad.
Right-click on “workbook.xml” and select “Edit”
  • Find and delete the tag highlighted in the image below.
  • Close the notepad and click on “Save”. Confirm to update the modified “workbook.xml” in the archive.
Find and delete the tag <workbookProtection…/>

Option 2. Remove the protections from the worksheet.

  • Open the “worksheets” folder in the archive.
Choose “worksheets” in the archive
  • Right-click on the encrypted worksheet and select “Edit”.
Right-click on the password protected sheet and select “Edit”
  • Select the tag highlighted in the image below and delete it.
  • Save the changes, close the notepad and confirm the updates.
Select the tag <sheetProtection … /> and delete it

Step 4.

  • Finally, close the archive and rename the extension name from ZIP to XLSX. Open the Excel file and there are no restrictions on the workbook or worksheet any more.

The method is a little technical but really works. Surely you can also take advantage of WinRAR. But I prefer 7-Zip because WinRAR can’t let you edit the file wrapped in the archive. Using WinRAR, you have to copy and paste the .xml file somewhere, edit the text in the copy and replace the original file in the archive with the copy. In a word, 7-ZIP is more convenient than WinRAR.

#2. How to unlock a password protected Excel worksheet using VBA code.

VBA code can be used to unlock your worksheet. The condition is that you have the permission to open the Excel file.

Step 1.

  • Open the Excel file and navigate to the sheet you want to decrypt. It is important that you choose the right one.
Navigate to the sheet you want to decrypt

Step 2.

  • Press “Alt+F11” to open the VBA window.
  • Click on the “Insert” button and in the menu bar and select “Module”.
Select “Module” in the “Insert” drop-down menu

Step 3.

  • Once the “Module” window opens, enter the VBA code .
Copy and paste the VBA code into the “Module” window
  • Press “F5” to run it. A dialog will pop up when the process is done. Click on “OK”.
  • Close the VBA window and check the sheet. You will find the protections have been removed. The last thing is to save the file.

VBA Code:

  • Sub PasswordRecovery()

    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)

    Exit Sub

    End If

    Next: Next: Next: Next: Next: Next

    Next: Next: Next: Next: Next: Next

    End Sub

I put a XLSX file created with MS Excel 2010 to a test. The protections on a worksheet were removed within seconds.

Whether you use 7-ZIP or VBA code, if your excel file contains multiple encrypted worksheets, you need to decrypt each worksheet individually.

#3. How to unlock an open password protected Excel file.

MS Excel Workbook (.xlsx) Password Recover

MS Excel Workbook (.xlsx) Password Recover comes to aid when you lose the open password for the Excel file. This is a piece of free Excel password recovery software. It comes packed with three modes to retrieve your lost open password. Each mode is developed based on a user’s different situations. “Dictionary Attack” attempts all words in the built-in dictionary. The program also gives the facility to build your own dictionary. “Mask Attack” speeds up the recovery process by using some clues you provide. You can specify length, letters (capital/small), numbers and symbols if you recall any segment of the password. The last mode is “Brute Force Attack”. Try this mode if you do not remember anything about the password. Although it takes longer to crack the password, a multi-core and multi-threading tech will dramatically shorten the time.

No professional knowledge is required to operate the program since it is suitable for both newbies and advanced users. Tests indicate the app recovers the password at a very high success rate. Honestly speaking, the only drawback of the tool is that only the.xlsx file is supported. A six characters password is found within a few seconds to several minutes. Follow 3 simple steps below to see how to use MS Excel Workbook (.xlsx) Password Recover.

  • Add your encrypted file to the program using the “Add file” button.
  • Choose one mode that best suits you and do configurations as per your needs.
  • Click on “Start” and the program will start scanning the password combinations. The password will display on the screen once the recovery process is completed.

Summary

Microsoft Excel is usually used for data statistics. With password protections on the Excel file, you can prevent illegal access as well as unwanted modification. But you might lose the password for many reasons. Using the solutions mentioned above, you can get back the open password or remove restrictions from the workbook and worksheet.