You usually password-protect an MS Excel file if the data it contains is sensitive and you don’t want any untrusted person to view, edit, delete, print, or misuse the information in any manner. However, at times, you may wish to unprotect the Excel sheet if you want easy access to its contents, probably to share the workbook with your colleagues for further processing or to make some changes to it.
In the following sections, you will learn how to unprotect an Excel sheet without a password and the one with it.
NOTE: If your Excel sheet requires a password to open it, check these solutions.
- Unprotect Excel Sheet without Password
- Unprotect Excel Sheet with Password
- Unprotect Read-Only Excel Sheet
Unprotect Excel Sheet without Password
You can unprotect an Excel sheet without a password in many ways. While some methods are a bit challenging to follow, others may require a few extra steps but are convenient. If you don’t come from a technical background, some simple solutions explained below should help.
Solution 1: Use Google Sheets
If you have a Google account, you already have access to Google Sheets. Google Sheets is a free web tool and you can use it as a replacement for Microsoft Excel. The best thing about this solution is that you can access your sheet from anywhere across the globe as long as you have a laptop, desktop, or smartphone, and are connected to the Internet.
When unprotecting a worksheet with Google Sheets, unfortunately, it cannot access a password-protected file (ask for a password when you open an Excel file). As for modification protection, it allows you to upload a secured file and reveal its contents. You can then follow the steps below to unprotect Excel sheet.
To use Google Sheets:
- Use your preferred web browser to sign in to your Google account.
- Click the Google Apps icon from the top-right corner. Scroll down and click Sheets.
- On the Sheets page, click Blank to start working on a new sheet from scratch.
- Click File from the menu bar at the top.
- Click Open.
- Go to the UPLOAD tab from the Open a file page.
- Click BROWSE.
- Locate and upload the modification-protected workbook to Google Sheets.
- When the spreadsheet opens in Google Sheets, click the Download icon from the top-right area to download the unprotected file to your PC.
- Try opening the workbook you downloaded and see if the protection is removed.
If the solution doesn’t work, you can also copy the content in the protected Excel and paste it into a new spreadsheet. The process to do so is given below:
- Get back to the Google Sheets page.
- Click to open the worksheet you uploaded.
- Press Ctrl + A on your keyboard to select all the contents.
- Press Ctrl + C to copy everything that you selected.
- Click File and go to New this time.
- Click Spreadsheet.
- Click to select the first cell of the worksheet.
- Press Ctrl + V to paste the data you copied.
- Next, go to File > Download.
- Click Microsoft Excel (.xlsx) from the submenu.
- Open the file you downloaded to access and use the data.
NOTE: If you deselect the 2 options (they are selected in default) in the screenshot below when you were creating the password, then you won’t be able to copy the content in your Excel worksheet.
Solution 2: Use iToolab PassWiper
If you need to unprotect more than one Excel sheet, using Google Drive can result in a significant amount of additional work. In this case, you can use an efficient third-party app that does the job with merely a few mouse clicks. One such tool that has earned some reputation in recent years is PassWiper by iToolab.
iToolab PassWiper is available for Windows computers and can recover the password to open an encrypted Excel file and unprotect an Excel worksheet or workbook by removing the password. For your purpose, just select Remove Excel Password, and upload your Excel file. You Excel sheet will be unprotected within a few seconds or minutes.
At the time of this writing, iToolab PassWiper supports Microsoft Excel 2003 to 2021 including office365 and works on Windows 7 to Windows 11. The Excel file formats supported including xlsx, xls, xlsm, xlsb, csv, xltx, xltm, xlt, xml, xlam, xps, xla, and ods format.
If you are using a Mac computer, you can try PassFab for Excel which offers a Mac version.
Solution 3: Use VBA Code
If you have turned on structural or content modification protection with a password, you can unprotect the Excel sheet without a password using a Visual Basic for Applications (VBA) code. However, for this method, you must have a basic understanding of how macros in Excel work and you must be able to create and edit macros. For your convenience, a working VBA code and the process to use it is given below.
The VBA Code
Sub RemoveProtection()
Dim dialogBox As FileDialog
Dim sourceFullName As String
Dim sourceFilePath As String
Dim sourceFileName As String
Dim sourceFileType As String
Dim newFileName As Variant
Dim tempFileName As String
Dim zipFilePath As Variant
Dim oApp As Object
Dim FSO As Object
Dim xmlSheetFile As String
Dim xmlFile As Integer
Dim xmlFileContent As String
Dim xmlStartProtectionCode As Double
Dim xmlEndProtectionCode As Double
Dim xmlProtectionString As String
‘Open dialog box to select a file
Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)
dialogBox.AllowMultiSelect = False
dialogBox.Title = “Select file to remove protection from”
If dialogBox.Show = -1 Then
sourceFullName = dialogBox.SelectedItems(1)
Else
Exit Sub
End If
‘Get folder path, file type and file name from the sourceFullName
sourceFilePath = Left(sourceFullName, InStrRev(sourceFullName, “\”))
sourceFileType = Mid(sourceFullName, InStrRev(sourceFullName, “.”) + 1)
sourceFileName = Mid(sourceFullName, Len(sourceFilePath) + 1)
sourceFileName = Left(sourceFileName, InStrRev(sourceFileName, “.”) – 1)
‘Use the date and time to create a unique file name
tempFileName = “Temp” & Format(Now, ” dd-mmm-yy h-mm-ss”)
‘Copy and rename original file to a zip file with a unique name
newFileName = sourceFilePath & tempFileName & “.zip”
On Error Resume Next
FileCopy sourceFullName, newFileName
If Err.Number <> 0 Then
MsgBox “Unable to copy ” & sourceFullName & vbNewLine _
& “Check the file is closed and try again”
Exit Sub
End If
On Error GoTo 0
‘Create folder to unzip to
zipFilePath = sourceFilePath & tempFileName & “\”
MkDir zipFilePath
‘Extract the files into the newly created folder
Set oApp = CreateObject(“Shell.Application”)
oApp.Namespace(zipFilePath).CopyHere oApp.Namespace(newFileName).items
‘loop through each file in the \xl\worksheets folder of the unzipped file
xmlSheetFile = Dir(zipFilePath & “\xl\worksheets\*.xml*”)
Do While xmlSheetFile <> “”
‘Read text of the file to a variable
xmlFile = FreeFile
Open zipFilePath & “xl\worksheets\” & xmlSheetFile For Input As xmlFile
xmlFileContent = Input(LOF(xmlFile), xmlFile)
Close xmlFile
‘Manipulate the text in the file
xmlStartProtectionCode = 0
xmlStartProtectionCode = InStr(1, xmlFileContent, “<sheetProtection”)
If xmlStartProtectionCode > 0 Then
xmlEndProtectionCode = InStr(xmlStartProtectionCode, _
xmlFileContent, “/>”) + 2 ‘”/>” is 2 characters long
xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _
xmlEndProtectionCode – xmlStartProtectionCode)
xmlFileContent = Replace(xmlFileContent, xmlProtectionString, “”)
End If
‘Output the text of the variable to the file
xmlFile = FreeFile
Open zipFilePath & “xl\worksheets\” & xmlSheetFile For Output As xmlFile
Print #xmlFile, xmlFileContent
Close xmlFile
‘Loop to next xmlFile in directory
xmlSheetFile = Dir
Loop
‘Read text of the xl\workbook.xml file to a variable
xmlFile = FreeFile
Open zipFilePath & “xl\workbook.xml” For Input As xmlFile
xmlFileContent = Input(LOF(xmlFile), xmlFile)
Close xmlFile
‘Manipulate the text in the file to remove the workbook protection
xmlStartProtectionCode = 0
xmlStartProtectionCode = InStr(1, xmlFileContent, “<workbookProtection”)
If xmlStartProtectionCode > 0 Then
xmlEndProtectionCode = InStr(xmlStartProtectionCode, _
xmlFileContent, “/>”) + 2 ””/>” is 2 characters long
xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _
xmlEndProtectionCode – xmlStartProtectionCode)
xmlFileContent = Replace(xmlFileContent, xmlProtectionString, “”)
End If
‘Manipulate the text in the file to remove the modify password
xmlStartProtectionCode = 0
xmlStartProtectionCode = InStr(1, xmlFileContent, “<fileSharing”)
If xmlStartProtectionCode > 0 Then
xmlEndProtectionCode = InStr(xmlStartProtectionCode, xmlFileContent, _
“/>”) + 2 ””/>” is 2 characters long
xmlProtectionString = Mid(xmlFileContent, xmlStartProtectionCode, _
xmlEndProtectionCode – xmlStartProtectionCode)
xmlFileContent = Replace(xmlFileContent, xmlProtectionString, “”)
End If
‘Output the text of the variable to the file
xmlFile = FreeFile
Open zipFilePath & “xl\workbook.xml” & xmlSheetFile For Output As xmlFile
Print #xmlFile, xmlFileContent
Close xmlFile
‘Create empty Zip File
Open sourceFilePath & tempFileName & “.zip” For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
‘Move files into the zip file
oApp.Namespace(sourceFilePath & tempFileName & “.zip”).CopyHere _
oApp.Namespace(zipFilePath).items
‘Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(sourceFilePath & tempFileName & “.zip”).items.Count = _
oApp.Namespace(zipFilePath).items.Count
Application.Wait (Now + TimeValue(“0:00:01”))
Loop
On Error GoTo 0
‘Delete the files & folders created during the sub
Set FSO = CreateObject(“scripting.filesystemobject”)
FSO.deletefolder sourceFilePath & tempFileName
‘Rename the final file back to an xlsx file
Name sourceFilePath & tempFileName & “.zip” As sourceFilePath & sourceFileName _
& “_” & Format(Now, “dd-mmm-yy h-mm-ss”) & “.” & sourceFileType
‘Show message box
MsgBox “The workbook and worksheet protection passwords have been removed.”, _
vbInformation + vbOKOnly, Title:=”Password protection”
End Sub
The Process
- Create a new Excel file.
- Go to the View menu from the menu bar at the top.
- Click Macros from the Macros section in the Ribbon.
- Enter any name in the Macro name field of the Macro box.
- Click Create.
- When the new window opens, replace all the text in the right pane with the VBA code given above.
- Click the Save icon.
- In the File name field of the Save As box, enter any name.
- Select Excel Macro-Enabled Template from the Save as type drop-down list.
- Click Save from the bottom right to save the file to your preferred location.
- Close the VBA code window when done.
- Back on the Excel sheet, go back to View if you are not already there, and click Macros.
- Click to select the RemoveProtection macro from the Macro box.
- Click Run.
- Locate and select the password-protected file when Excel prompts.
- Click OK.
- Click OK on the information box that says that the workbook and worksheet protection passwords have been removed.
- Note that the VBA code has created a new file with the date and time added to its name.
- You can open this unprotected file and start working on it normally.
Note: This method doesn’t work if you have enabled an encryption password to open a workbook. This code only removes the structural and content modification passwords.
Solution 4: Copy the Content to Another Excel File
Sometimes, when you protect the Excel sheet from editing, deleting, printing or other uses with a password, it may allow users to “Select locked cells” and “Select unlocked cells” because these 2 options are checked by default. That means you can select the cells, copy and paste them into another Excel file. Just open your protected Excel sheet and have a try.
Note: This method requires you to resize the rows and columns after you paste the contents. You may have to invest some time if the original file has some complex formatting.
Unprotect Excel Sheet with Password
This is pretty straightforward. All you need to do is to unprotect the Excel sheet with the password. You can follow the methods given below to unprotect Excel sheet.
To Remove Workbook Protection
- Open the password-protected workbook.
- Go to Review from the menu bar.
- Click Protect Workbook from the Protect section of the Ribbon.
- Enter the correct password in the Unprotect Workbook field.
- Click OK.
- Save the workbook.
- Close and reopen the file and try to add a new sheet to the workbook to see if Excel allows you to do so.
To Remove Worksheet Protection
- Open the protected workbook.
- Go to Review and click Unprotect Sheet.
- Enter the password in the Unprotect Sheet box.
- Click OK.
- Save, close, and reopen the workbook to see if the file is protected any longer.
Unprotect Read-Only Excel Sheet
In some cases, you may want the users to only view the contents of an Excel file, but allow them to edit it if needed. To do this, you might have enabled the Read-Only protection on the workbook. If you want to get rid of this barrier, you can follow the steps given below:
- Open the workbook that has the Read-Only mode enabled on it.
- Go to File > Info > Protect Workbook.
- Click to deselect the Always Open Read-Only option.
- Save, close, and reopen the workbook.
- Check if you can edit the content.
Conclusion
Data protection is extremely important, especially when you are dealing with several highly-paying clients who trust you with their sensitive information. To prevent any unauthorized access, many times you protect workbooks with passwords. However, you may want to lift such barriers while making some significant changes to the details or if you no longer need to secure the data. In such cases, you can unprotect the Excel sheet with the help of the solutions given above.
