3 Ways to Unprotect an Excel Worksheet

Table of contents:

3 Ways to Unprotect an Excel Worksheet
3 Ways to Unprotect an Excel Worksheet

Video: 3 Ways to Unprotect an Excel Worksheet

Video: 3 Ways to Unprotect an Excel Worksheet
Video: How to Create a CSV file in Excel 2024, November
Anonim

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

6297644 1
6297644 1

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.

6297644 2
6297644 2

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

6297644 3
6297644 3

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.

6297644 4
6297644 4

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

6297644 5
6297644 5

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.
6297644 6
6297644 6

Step 2. Click + New

It's in the top-left corner of the screen.

6297644 7
6297644 7

Step 3. Click File Upload

The “Open” panel on the computer will be displayed.

6297644 8
6297644 8

Step 4. Select the Excel file you want to edit and click Open

The file will be uploaded to your Google Drive account.

6297644 9
6297644 9

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.

6297644 10
6297644 10

Step 6. Click the Open with menu

This menu is at the top of the preview window. After that, the menu will be expanded.

6297644 11
6297644 11

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.

6297644 12
6297644 12

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

6297644 13
6297644 13

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.
6297644 14
6297644 14

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.

6297644 15
6297644 15

Step 3. Press Alt+F11 key to open the Visual Basic Editor window

6297644 16
6297644 16

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.

6297644 17
6297644 17

Step 5. Click Insert on the menu

After that, another menu will be expanded.

6297644 18
6297644 18

Step 6. Click Modules

A new module will be inserted and after that, you can paste the code in the module.

6297644 19
6297644 19

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

6297644 20
6297644 20

Step 8. Right-click the new module and select Paste

The copied code will be displayed in the module window.

6297644 21
6297644 21

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

6297644 22
6297644 22

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.

Recommended: