Unprotect an Excel Sheet or Workbook With or Without Password

Maaz Perwez

Microsoft Excel is the key tool that is being used by almost everyone who is into corporate jobs. Often the organizations use password protected excel files so that the sensitive information about the company remains safe. Microsoft Excel is a very underrated yet powerful tool that is capable of doing a lot of things. Protecting the sheets is one such feature that Microsoft has given to its users, through which users won’t be able to make changes to the original data. In this post we will learn how to unprotect the Excel sheet or the workbook that you got from the organization either if it came with the password or without a password, we have you covered, just go through the article and tutorial below.

Microsoft Excel allows two ways through which a spreadsheet can be secured. The first one is by not using any password, and this way it can be unprotected by anyone. The second method is by using a password to protect the workbook or the worksheet, so to unprotect the workbook, you will have to enter the password.

When it comes to security or protection, Microsoft allows protection of its files in Excel at the cell, spreadsheet or workbook level. Once you have locked down and protected the worksheets or the workbooks, other people won’t be able to edit the file unless you unprotect them again.

You can easily unprotect the excel worksheets and files if you know the password. However, unprotecting a spreadsheet without a password is not easy, but it isn’t impossible as well, you can follow through with some of the tricks below, using which you might be able to unprotect your Excel spreadsheet.

How To Unprotect Excel Sheet with Password/ No Password

To unprotect an Excel sheet with the password is quite simple, but to unprotect the excel sheet without knowing the password is a more complex method, and you can remove the protection of the sheet by following the steps below.

  1. On your computer open the protected Excel spreadsheet and then switch to the Review tab and then click the Unprotect sheet icon in the changes group.
  2. Alternatively, you can also access it by right-clicking the protected spreadsheet tab, and then selecting the Unprotected Sheet option from the contextual menu.
  3. If your Excel sheet is password-protected then Excel will ask for the password to enter in the dialogue box. You will have to enter the password to unprotect the Excel sheet and then click on OK.
  4. If your worksheet is not protected with a password, then you can click the Unprotect sheet option and it should unlock your Excel sheet.

How To Unprotect Excel Workbook with Password/ No Password

If you have got an Excel workbook that is password protected then you won’t be able to change the structure of the workbook like add, move, rename or delete the worksheets inside that particular workbook or if you want to view any hidden sheets all of this won’t be accessible to you. But you can still edit the data on the worksheets even if your workbook is password protected. To edit or modify the structure of the workbook like adding or removing the worksheets then you must unprotect the Excel workbook structure first.

  1. To remove the protection from your Excel workbook, you need to open the protected workbook and then click the Protect Workbook button which should be located under the review tab.
  2. Now, type the password in the Unprotect Workbook prompt box and then save the workbook.
SEE ALSO  Here's How to Make X Bar Symbol in Word

That’s it your workbook is now unlocked and you can proceed with further modifying or editing your workbook like adding or removing the spreadsheets.

How To Unprotect Excel Worksheet Without Password?

If your worksheet is password protected and you don’t know the password or if you have forgotten the password o your worksheet, then don’t worry we got you covered we have some ways through which you will be able to unprotect that excel sheet. You can follow through with the methods mentioned below to unprotect the Excel worksheet without a password.

Unprotect Excel Worksheet with VBA Code

Microsoft uses a very simple encryption algorithm to lock the Excel worksheets. Even if you have password-protected your Excel worksheet, anyone who has access to the below VBA code can unlock your protected Excel sheet within minutes.

You can unlock a password-protected sheet simply by using the below written VBA code as a macro to identify the password that worksheet. Below are the steps to use the VBA code and crack the password of the protected Excel worksheet.

  1. Open your password-protected Excel sheet on your computer and then head to the developer tab and then click the View Code button on the ribbon. It should open the code editor window inside Microsoft Visual Basic for applications, make sure it is installed on your computer.
  2. Otherwise, you can also go to the Developer tab and then click the Visual Basic button. Now, once you are inside the visual basic editor, you can expand the Microsoft Excel Objects option on the left window pane, you need to right-click on the worksheet that is password protected and then select insert Module from the context menu.
  3. Next inside the code window of the protected sheet, you need to copy and paste the code written below.
Sub PasswordBreaker()
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
  • Click on the Run button from the toolbar and then select the Run Sub/UserForm option or directly press the F5 button from your keyboard to execute the code that you just copied pasted.
  • It will take a couple of minutes to execute and crack the password. Once the password is cracked, or the execution is completed, you will receive the pop-up with a cracked password, although it isn’t the original one but it will still work. Simply click on OK and the sheet will be unprotected and will be ready for your edits.
  • Do note that you will have to save the Excel file before closing after pasting the code to the module.

Unprotect Excel Sheet Without Password Using Zip

There is also an alternative trick that can be used to unprotect your Excel sheet, below we have the guide you can follow the steps.

  1. On your computer head to the control panel and then open the File Explorer option.
  2. Inside the File explorer options window, you need to uncheck the Hide Extensions for known file types to enable your file extensions. Click on Apply to apply changes and then click on OK to confirm and close the window. Once done, you will be able to see the file extension next to the file name.
  3. Navigate to the Excel file which is protected and you need to unprotect it and rename the extension of the Excel file from .xls to .zip.
  4. You can change the extension by right-clicking on the file and then selecting rename option from the contextual menu and then replacing .xls with .zip. Hit Enter and then click yes when prompted to change the file extension.
  5. You should see your Excel file as a zip file.
  6. You need to extract the zip file and you should see three or four files being extracted including a few folders. You need to open each folder and search for the files that have the data of the excel sheet, we found it inside the /xl folder. Now you will be able to see it as a separate .xml file.
  7. Next, you have to navigate to the xl folder then worksheets>> sheet1.xml (the name of the protected excel sheet). Open the /xl/worksheets/ directory and you will be able to see the list of all sheets (in XML Format) available in your workbook. Simply open the sheet1.xml file with Notepad or Wordpad (right-click on the file and then select a text editor from the Open with context menu).
  8. In the editor you need to find the tag that says <sheetProtection passwords+…/> and delete that tag.
  9. If your worksheet is very large and you have a lot of information inside that, then it can be pretty difficult to find and locate the sheet protection tag. In that case, you can press CTRL+F to open the find feature of windows and then you can start typing protection in the Find What column and then keep clicking on find next until you find the sheet protection tag. Once you find it you need to select it and then delete it.
  10. Once you have finished deleting it, you need to save that XML file and then re-zip all of the extracted files into a zip file again. Then, you need to change the extension of that zip file from .zip to .xls.
SEE ALSO  How to Calculate Range in Excel

Once you have changed the file extension you will be able to open that file in Excel and you can check your spreadsheet it should be unprotected.

Do note that this method only works in password-protected workbooks. For files that have protection using Encrypt with password feature, it won’t work.

Unprotect Excel Sheet with a Google Sheets

We have another workaround through which you can unprotect an Excel worksheet without a password. This time we will be using Google Sheets and Google drive, so we will be needing a Google drive account for this method. Follow through with the tutorial below.

  1. Head to Google Drive and open your Google Drive account and then click the New button at the top left corner.
  2. Now, from the New menu, you need to select the Google Sheets and then click on Blank spreadsheet.
  3. Inside the blank spreadsheet, you have to click on the File in the toolbar and then click on the Import option.
  4. You will see an import file dialogue box, select the Upload tab from the menu and then click on the Select a file from your device button.
  5. From the popup menu browse through your computer and find the Excel workbook on your local drive, then select and click Open to upload that file to Google sheets. Alternatively, you can simply drag and drop your Excel file into the Google sheets import file box section.
  6. On the import file dialogue box, you have to select the replace spreadsheet option and then click on the Import Data button.
  7. It should import your protected Excel worksheet into your google sheets including all of your data. You can also see that it has imported everything and the worksheet is not protected anymore and you can edit the data easily.
  8. Once you have edited it, you can export the worksheet back to Excel format again.
  9. On your Google sheets where you have the unprotected spreadsheet click the File menu from the top toolbar and then select the Download option. You need to choose Microsoft Excel .xlsx from the context menu.
  10. You can give your exporting file a new name and browse the location where you want to export it in the XLS format.
  11. Click on Save and you will have the same Excel spreadsheet but without any password protection.
SEE ALSO  How to Calculate Range in Excel

How to Unprotect an Excel Workbook Without Password

For a password-protected Excel workbook, you need to find the password and if you can’t find the password then you can use the VBA code and zip methods to unprotect the workbook, a detailed tutorial is below.

Unprotect Excel Workbook Without Password Using VBA Code

There are several different methods through which you can unprotect your Excel workbook, one such method is by using the Microsoft Visual Basic for Application (VBA). Below are the steps to do it using VBA.

  1. On your desktop open the Excel file with the protected workbook structure and then you need to go to the Developer tab and click the Visual basic button located on the top left corner.
  2. Once inside the Visual Basic editor, click on the Insert tab and then select the Module option.
  3. On the popup module window, you have to copy and paste the code written below to unlock the workbook structure.
Sub Shareus()
        ActiveWorkbook.Sheets.Copy
          For Each sh In ActiveWorkbook.Sheets
            sh.Visible = True
          Next
        End Sub
  • You need to hit the F5 button from your keyboard or click on the Run button from the toolbar and then select the Run Sub/User Form option to run the Macro.
  • A new workbook should open up having a different name. Although it is the same workbook as the original workbook but without the protection. You can edit and modify the workbook structure in Excel without even knowing the password.

Unprotect Excel Workbook Without Password Using Zip

You can also unprotect the Excel workbook without a password or without doing any coding on the VBA. All you have to do is change the extension of the file and manipulate its constituents. To unprotect an Excel workbook using the Zip file method, follow through the tutorial below.

  1. On your computer open the location where the protected Excel file with the protected workbook is located. Rename and change the extension of the file from .xls to .zip as we did for the protected worksheet method. Also, before you start changing the extension make sure to have a backup copy of the file somewhere else.
  2. Extract the zip file using any file archiver software like WinRAR or Winzip and you will be able to see some folders and files.
  3. Find and open the /xl folder and then open the workbook.xml file using Notepad.
  4. In the Notepad you will see some coding lines written, you need to find the protection tag and then delete it. <workbookProtection workbook Password=…/>
  5. If you are not able to find the tag, then you can also use the find feature of windows by pressing the ctrl+F button together on your keyboard which will open the find feature, now inside the Find what column you need to write protection and then start clicking on Find Next until you find the tag highlighted. Once you find it, you need to delete the workbook protection tag.
  6. Once you have removed the protection tag, you need to save the workbook.xml file and then zip or compress all of the files and folders that you just extracted back to a zip file.
  7. Once you have compressed the files, you need to rename them and change the extension from .zip to .xlsx.
  8. After pressing Enter you will see the zip file is changed to Excel sheets, and you can try opening it, the file should no longer have any password-protected workbooks.

Conclusion

These were the best extensive guides on how you can edit Excel sheets or workbooks which are password-protected, we have also covered how you can remove the password protection of Excel spreadsheets and workbooks.

Leave a Comment