In this article, you will learn how to unprotect a workbook in Excel. Throughout this article, we will discuss all the effective ways to unprotect a workbook in Excel. You can unprotect the whole workbook or specific cells using a password or without a password in Excel.
Our Excel workbook is sometimes protected with a password to prevent further structural modifications. When we make any updates to the file structure, we need to make the workbook an unprotected one. There are several options available in Excel for unprotecting a workbook. Depending on our needs, we can select any of these to unprotect an Excel workbook.
I hope you find it helpful and informative. Let’s get to the main point.
Download Practice Workbook
You can download the practice workbook from here:
Unprotect Workbook in Excel: All Possible Ways
To demonstrate the approaches of unprotecting a workbook in Excel, we have taken a dataset that has two worksheets named Sales Record of Year 2021 and Sales Record of Year 2022. Both worksheets have 7 rows and 3 columns.
1. Unprotect Excel Workbook from Review Tab
In this following method, we will unprotect the Excel workbook from the Review tab. Follow the below steps:
- Go to the Review tab>> Protect>> Protect Workbook.
- A small dialog box called Unprotect Workbook will appear.
- Enter the password you set while protecting the workbook. In our case, it is 12345.
- Then click OK.
- As a result, we will get our protected workbook as an unprotected workbook since there is the availability of the New sheet
- Now, we can change the structure of the file by adding a new sheet or deleting any sheet.
Thus we can unprotect an Excel workbook from the Review tab.
2. Unprotect Excel Workbook Using Excel’s Info Feature
We can unprotect a protected workbook by using Excel’s Info feature. Here are the necessary steps:
- First, go to File>> Info>> Protect Workbook>> Encrypt with Password.
- A small dialog box titled Encrypt Document will appear where you will see a 5-digit password that was set before.
- Delete the password and click OK.
- Now, select the Save option to save the workbook and close the Excel file.
- Now, open the Excel file again and this time you will not require any password to access the dataset.
3. Unprotect Excel Workbook with VBA Code
We can also use VBA code to unprotect an Excel workbook. To know the whole procedure follow the below steps:
- First, press ALT + F11 on your keyboard to open Visual Basic.
- Then click Insert and select the Module option.
- Insert the following code in the code editor and press F5 to run the entire code.
Sub Unprotect_Workbook_with_Password()
Dim p As Integer, q As Integer, r As Integer
Dim s As Integer, t As Integer, u As Integer
Dim X_1 As Integer, X_2 As Integer, X_3 As Integer
Dim X_4 As Integer, X_5 As Integer, X_6 As Integer
On Error Resume Next
For p = 65 To 66: For q = 65 To 66: For r = 65 To 66
For s = 65 To 66: For t = 65 To 66: For X_1 = 65 To 66
For X_2 = 65 To 66: For X_3 = 65 To 66: For X_4 = 65 To 66
For X_5 = 65 To 66: For X6 = 65 To 66: For u = 32 To 126
ActiveSheet.Unprotect Chr(p) & Chr(q) & Chr(r) & _
Chr(s) & Chr(t) & Chr(X_1) & Chr(X_2) & Chr(X3) & _
Chr(X_4) & Chr(X_5) & Chr(X6) & Chr(u)
If ActiveSheet.ProtectContents = False Then
MsgBox "Possible password: " & Chr(p) & Chr(q) & _
Chr(r) & Chr(s) & Chr(t) & Chr(X_1) & Chr(X2) & _
Chr(X_3) & Chr(X_4) & Chr(X_5) & Chr(X_6) & Chr(u)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
- It may take quite a long time to complete and you will see a possible password in the message box. Just click OK to proceed.
Thus, we can say that our visual code worked successfully and we were able to unprotect the Excel workbook with a password.
4. Unprotect Excel Workbook Using Google Sheets
We can also use another method to unprotect the Excel workbook by using Google Sheets. To know more about this method follow the below steps:
- First, open a new Google Sheet by clicking on Blank.
- Then, go to the File menu>> select Import option.
- Next, select the Upload option>> click Browse.
- Now, select the file and click on Open.
- After that, click on Import data.
- As a result, you will be able to import your Excel file into Google Sheets.
- Next, go to the File menu >> select Download>> then choose Microsoft Excel (.xlsx).
- Now save the file to your desired location and rename it.
- Finally, open the file and you will not need any password to access the data.
5. Unprotect Excel Workbook by Copying Contents to a New Workbook
5.1 Use of Copy-Paste Options
In this part, we will discuss the fastest and quickest way to unprotect the Excel workbook without passwords. Go through the below steps:
- First, press Ctrl + A to select the whole worksheet of the protected workbook.
- Then, press Ctrl + C or right-click the mouse to copy the worksheet.
- After that, open a new workbook and paste it by pressing ‘Ctrl + V’.
- Finally, open the file and click to allow it to be viewed without a password.
Thus you can easily unprotect an Excel workbook by copying contents to a new workbook.
5.2 Use of VBA Code
You can also use VBA code to unprotect an Excel workbook without a password. Here, we will copy the entire workbook. Let’s follow the below steps to know the whole procedure:
- First, press ALT + F11 on your keyboard to open Visual Basic.
- Then click Insert and select the Module option.
- Insert the following code in the code editor and press F5 to run the entire code.
Sub Unprotect_Workbook_without_Password()
ActiveWorkbook.Sheets.Copy
For Each sh In ActiveWorkbook.Sheets
sh.Visible = True
Next
End Sub
- After running the VBA code, it will open up a new workbook with a different name.
- The new workbook is the same as the original workbook but without protection.
- Thus we can unprotect an Excel workbook without a password using VBA macro.
How to Unprotect Worksheet in Excel
1. Unprotect Excel Worksheet with Password
It’s very easy to unprotect an Excel worksheet with some quick steps. Here they are:
- First, go to the Review tab>> select Unprotect Sheet under the Protect section.
- Enter the password that was used before to protect the worksheet.
- Finally, click OK.
Thus, you can unprotect an Excel worksheet easily.
2. Remove Password to Unprotect Specific Sheets in Excel Workbook
Let’s go through the steps to unprotect a specific sheet in an Excel workbook by removing the password.
- First, right-click on the Excel file that you want to unprotect and select the Rename option.
- Then, remove the .xlsm (since we have a VBA macro in our Excel file) extension and add the .zip
- Press Enter.
- Afterward, open the zipped folder by double-clicking it, and then open the xl folder.
- Then open the worksheets folder in the xl folder.
- Now, press ‘Ctrl+C’ on the keyboard to copy the sheet1.xml file.
- Then, use ‘Ctrl+V’ to paste it into your required folder.
- Now, open XML file in Notepad.
- Open the Find search box by pressing ‘Ctrl+F’.
- Now, in the Find what box, type Protection and click Find Next.
- After that, select the “sheetProtection” tag and drag the mouse to the right until you come to the end of the “/>” tag.
- Remove the selected line from the code, then save it with ‘Ctrl+S’.
- Then, press ‘Ctrl+C’ to copy the modified file, and with the Copy and Replace option, paste this file to its original destination.
- Now, rename the zip folder by removing the .zip extension and adding the .xlsm extension.
- Finally, open the file and click to allow it to be viewed without a password.
Thus we can remove the password to unprotect specific sheets in an Excel workbook with these easy steps.
Unprotecting a Workbook vs. Unprotecting a Worksheet in Excel
In Excel, we can protect both workbooks and individual worksheets to prevent unauthorized access or modification. However, unprotecting a workbook and unprotecting a worksheet are two distinct actions with different outcomes. Unprotecting a workbook allows us to make structural modifications to the entire workbook, such as adding, deleting, concealing, or renaming worksheets. We are also able to modify the workbook properties and settings of your Excel spreadsheet by removing its protection.
Unprotecting a worksheet, on the other hand, allows us to modify the worksheet’s data and formatting, such as altering the content of a cell, inserting or deleting rows and columns, applying or removing filters, etc. Other worksheets remain protected unless we unprotect all the worksheets of a workbook.
Frequently Asked Questions
1. What is the shortcut to unprotect Workbook?
Shortcuts to unprotect Excel workbooks depend on the version you are using. Here are the common shortcuts for different Excel versions:
- Excel 2010 and later versions: Press Alt + R, followed by G, and then U.
- Excel 2007: Press Alt + R, followed by U.
- Excel 2003 and earlier versions: Press Alt + T, followed by P. Then, press U.
2. What are the workbook protection types in Excel?
A workbook can be protected at the file or worksheet level in Excel. Excel provides three levels of password protection: password protection for opening files, password protection for changing data, and password protection for adding, deleting, or hiding worksheets.
3. What are workbook properties?
Excel’s properties are an important part of working with it. A workbook’s properties include the author, title, and subject, as well as the date it was created and modified. Workbook properties can be accessed and modified in Excel through the document properties settings.
Key Takeaways from the Article
- In this article, we have discussed all the effective ways to unprotect a workbook.
- Here, we have discussed how to unprotect a workbook with or without a password.
- We have also explained how to unprotect Excel workbooks using VBA macro.
- Showed a step-by-step procedure of all methods.
- Also showed how to unprotect an Excel worksheet.
- Discussed the differences between unprotecting a workbook and a worksheet.
- Provide solutions to frequently asked questions by readers.
Conclusion
This article covers every possible way to unprotect a workbook in Excel with some easy ways. Don’t forget to download the Practice file. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.
Unprotect Workbook in Excel: Knowledge Hub
- How to Unprotect Excel Workbook without Password
- How to Remove Password from Excel File
- How to Recover Excel File Password
- How to Unprotect Excel Workbook with Password
<< Go Back to How to Unprotect in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!