Adding protection to a sheet or workbook is a fantastic way of protecting data from any unwanted changes. However, there are times when a sheet or workbook needs to be unprotected so that modifications can be made to the file.
In this blog I will cover ...
- how to unprotect an Excel sheet
- how to unprotect an Excel workbook
- how to remove a password from an Excel file AND...
- how to unprotect an Excel sheet and workbook without a password (if you have forgotten the password).
How to unprotect Excel sheet
A good indicator of the protection status of your worksheet is when you try to enter data into the sheet only to see the Microsoft Excel Error message;
"The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password."
This means the worksheet has been protected and the cell you were trying to modify is locked and unable to be edited.
You can also go to the Review tab and check if the sheet is protected. Look for the Unprotect Sheet button. If it’s there, your sheet is currently protected.
To unprotect the sheet, follow the steps below.
Step 1: From the Review tab, in the Changes group select Unprotect Sheet.
Step 2: If required, type in your password to unlock the worksheet.
Note: You will need to type in the password in the same case you used when you applied the password.
Tip: if you have lost the password please see How to unprotect an Excel sheet or workbook without the password steps below.
Step 3: Click OK.
The protection will now be removed.
Step 4: Save your file. You will now be able to edit the cells that had been locked while the sheet was protected.
How to unprotect an Excel workbook
When a workbook is protected, many of the options that allow us to change the structure of the workbook are disabled.
For example, options like Insert, Delete, Rename, Move or Copy, Hide and Unhide are greyed out and unable to be selected.
To enable these options, you will need to unprotect the workbook.
Step 1: From the Review tab, in the Changes group click Protect Workbook.
Step 2: If a password was applied to the protection you will need to type the password in the Unprotect Workbook dialog box.
Tip: if you have lost the password please see How to unprotect an Excel sheet or workbook without the password steps below.
Step 3: Click OK.
Step 4: Save your file.
Your workbook will now be unprotected, and you will be able to now make structural changes to it.
How to unprotect an Excel sheet or workbook without the password
There are times when you will need to unprotect an Excel sheet or Excel workbook when you no longer have the password.
A good example of this is when a member of your team leaves and you take on the responsibility of their Excel files, only to find you can’t make updates without a password.
Thankfully there is a workaround using Google Sheets.
Google Sheets is an online spreadsheet software, similar to Microsoft Excel.
You can access it for free when you have a Google account . If you don’t have a Google account yet, you can easily sign up for a free account, or maybe ask a colleague who does have an account to offer you some assistance with the next steps.
To get past the password protection applied to your Excel file, we will need to upload it to Google Sheets.
Step 1: In Google Sheets, click the folder on the screen (Open File picker). An Open a file box should appear.
Step 2: Select Upload.
Step 3: Select the Excel file from your device.
The file should now be displayed in your Google Sheets list of files.
For this example, we will use the file ‘Monthly Expenses Protected workbook with pwd’.
This file has a ‘Jun’ sheet which has been protected in Excel with a password. Additionally, the entire workbook has been protected, so that users can’t make structural changes unless they have the password.
Step 4: Open your file in Google Sheets by selecting it.
The file will open in Google sheets.
Note: If your browser displays the message “You can open this in view only mode or edit in Google Sheets", click Edit in Google Sheets.
You should now be able to edit the workbook in Google Sheets without needing a password even though it was password protected in Excel.
This shows that the protected worksheet doesn’t have the same layer of protection in Google sheets as it did in Excel which is something to be mindful of when sharing Excel files.
Step 5: Now just click File, Download, Microsoft Excel and save your updated file.
Step 6: Open the updated file into Excel and the sheet and workbook will now be unprotected.
How to Remove Password from an Excel file
For Excel files that require a password to open or modify them, you will need to know the password in order to remove the password from the file.
Note: There are many third-party apps available for purchase that claim they can remove the password from Excel files. I haven’t personally tested any of these so I can’t give any advice as to which of these actually work.
Step 1: First open the Excel file. You will need to use the password to do this.
Step 2: Press F12 to open the Save As dialog box.
Step 3: Select the Tools option then click General options.
Step 4: Delete the password or passwords. You might also like to remove the check from the Read-only recommended option box.
Step 5: Click OK.
Step 6: You now need to save these changes to your file. Click Save.
Step 7: A warning should pop up asking if you want to replace the file that already exists. Select Yes if you are happy to save over the original file. If not, click No and give the file a new name.
Note: Excel files that require a password to open or modify them are still password protected when accessed in Google Sheets.
When you open your file in Google Sheets a message will be displayed “Please enter this documents password to view it”.
Was this blog helpful? Let us know in the Comments below.
