This wikiHow teaches you how to remove protection from a worksheet in a Microsoft Excel document or workbook on a Windows or macOS computer. If the sheet is password protected and you don't know it, use Google Sheets or VBA commands (on earlier versions of Excel) to remove the protection.
Step
Method 1 of 3: Using Microsoft Excel
Step 1. Open the document or workbook with the sheet protected in Microsoft Excel
You can open it by double-clicking the file name on your computer.
Step 2. Right-click the protected worksheet tab
Each sheet tab is at the bottom of the Excel window. Protected sheets are usually marked by a lock icon in some versions of Excel. Right-click the tab (or lock icon) to open the context menu.
If multiple sheets are protected, you need to remove the protection for each sheet individually
Step 3. Click Unprotect Sheet
If it is not password protected, the sheet will open immediately. Otherwise, you will be asked to enter your password in a pop-up window.
Step 4. Enter the password and click OK
If the password is correct, the protected sheet will be unlocked.
- If you don't know the password to enter, read the methods for uploading to Google Sheets. This method allows you to upload files to Google Sheets so that all the protections applied to the document can be unlocked.
- If you're using Excel 2010 or an earlier version and don't want to upload the file to Google Sheets, read the methods for using VBA code in Excel 2010 and earlier versions.
Method 2 of 3: Uploading Files to Google Sheets
Step 1. Visit https://drive.google.com via a web browser
If you have a Google account, you can use the Google Sheets service (a free online application similar to Excel) to remove protection on all sheets in an Excel document or workbook, even when you don't know the password to enter.
- If you're not signed in to your Google account yet, follow the on-screen instructions to access your account.
- If you don't have a Google account, read the article on how to create a Google account.
Step 2. Click + New
It's in the top-left corner of the screen.
Step 3. Click File Upload
The “Open” panel on the computer will be displayed.
Step 4. Select the Excel file you want to edit and click Open
The file will be uploaded to your Google Drive account.
Step 5. Double-click the Excel file on Google Drive
You may need to scroll through the screen to find the file. A file preview window will open after that.
Step 6. Click the Open with menu
This menu is at the top of the preview window. After that, the menu will be expanded.
Step 7. Click Google Sheets
Once the file is opened and ready to be edited via Google Sheets, the protection applied to the file will be removed.
Step 8. Download the file back to the computer
If you want to continue working in Microsoft Excel, and not in Google Sheets, download the new, unprotected version with these steps:
- Click the menu " File ” in the top-left corner of the sheet.
- Click " Download As ”.
- Click " Microsoft Excel (.xlsx) ”.
- Select the file storage folder. If you want to keep the original version of the file (the protected version), type a new name for the file to be downloaded.
- Click " Save ” to download the file.
Method 3 of 3: Using VBA Code in Excel 2010 and Older Versions
Step 1. Open the document or workbook with the sheet protected in Excel
You can double-click the file name on your computer. Excel files usually end with an.xls or.xlsx extension.
- Use this method if you've tried lifting the sheet protection, but the existing file is protected by a password (and you don't know the password).
- This method cannot be followed in Microsoft Excel 2013 or later versions.
Step 2. Save the file again in xls format
If the existing file has an ".xlsx" extension (a common extension for files created or edited using later versions of Excel), you can only use this method if you first convert the file to Excel 97-2003 format (.xls). Here's how to convert a file to that format:
- Click the menu " File ” in the upper right corner of the screen.
- Click " Save As ”.
- Go to the folder where the file is saved.
- Choose " Excel 97-2003 (.xls) ” from the " Save as type " or " File Format " menu.
- Click " Save ”.
Follow the on-screen prompts to make the necessary changes.
Step 3. Press Alt+F11 key to open the Visual Basic Editor window
Step 4. Right-click the file name in the " Project – VBAProject " pane
The file name is at the top of the left pane. Make sure you right-click the option that contains the file name (ending with the ".xls" extension) which is usually at the top. After that, the menu will be expanded.
Step 5. Click Insert on the menu
After that, another menu will be expanded.
Step 6. Click Modules
A new module will be inserted and after that, you can paste the code in the module.
Step 7. Copy the code
Highlight the code provided below, then press Ctrl+C (PC) or Command+C (Mac) to copy it:
Sub PasswordBreaker() Breaks worksheet password protection. 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 "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
Step 8. Right-click the new module and select Paste
The copied code will be displayed in the module window.
Step 9. Press F5 key to run the code
Excel will run the code and this process will take a few minutes. After the code has finished executing, the new password will be displayed in a pop-up window.
The new password will contain a random number of “A” letters in place of the original password
Step 10. Click OK on the “Password” pop-up window
The new password will be displayed, but you don't need to write it down. Click OK ” to lift the protection on the worksheet automatically.