Unprotect Excel sheets: This is how it works

It makes your hair stand on end: There you are, having painstakingly created a perfect spreadsheet in Excel at last. You send it to the team members or the boss – and they change a value here, move a cell there or even delete the important table completely. All the work was for nothing … To prevent this, individual or even all cells of such a worksheet can be locked and the entire sheet can be secured with a password. But what if you still have to work on it later? Then it makes sense to be able to unlock the Excel sheet. Here we show step by step how this works.

Unprotect Excel sheets with a few clicks

Actually, you can only unprotect Excel sheets if you know the corresponding password (exceptions to this below). If the sheet is protected with a password, it cannot be edited for the time being. In order to make this possible, five steps are sufficient to unprotect Excel sheets: 1. First open the protected file. 2. Then jump to the cash register „Check“ at the top. 3. Find the category „Changes“ and press the button „Unprotect sheets“. 4. Now the password must be entered in the corresponding prompt field. 5. Confirm with „OK“. To protect an Excel sheet with Microsoft Excel again, a new password is created via the button „Protect sheet“.

Microsoft Excel keeps a loophole open

Too bad if you have secured an Excel spreadsheet with a password as a precaution – and then forget exactly that! Do you then have to do all the work all over again and create a new Excel spreadsheet? No! Fortunately, with Microsoft Excel and an additional tool, you can remove the Excel sheet protection without using the password.

How to remove the Excel password

If you want to remove the Excel sheet protection without knowing the password, you first have to download the programme 7-Zip for free and install it on your PC. Once all this has been done, proceed as follows:

  • Open the folder with the corresponding Excel file.
  • Right-click on the file and open it with the command "7-Zip > Open".
  • A folder named "xl" is shown, which must be opened.
  • Select the term "worksheets" in this folder.
  • Copy the file "sheet1.xml" that appears to the desktop.
  • open "sheet1.xml" in a word processing programme, leaving the 7-Zip programme open.
  • In Excel 2010, search for the term "password" using the "Search" command (key combination "Ctrl + F") (in Excel 2013 and Excel 2016, enter "sheetProtection" as the search term).
  • In the Excel 2010 file, the entry with the password is now displayed in inverted commas.
  • Delete the password (not the inverted commas!).
  • The display must now show password="". (As of Excel 2013, the display must look like this "<sheetProtection>").
  • Save the file "sheet1.xml".
  • Next, copy the file m 7-Zip archive to the same place where it was before.
  • Mark the pop-up option field with "Yes" and close the 7-Zip programme.
  • Now open the file with Microsoft Excel.
  • Now the Excel sheet protection can be removed with a few clicks as described above.

Does this work with the whole Excel file?

That would be too good – especially for people who would have unauthorised access to protected files. That’s why the steps only work if individual Excel sheets are protected with a password.

Help - the button "Unprotect sheets" cannot be activated!

If the button „Unprotect sheets“ is greyed out and therefore cannot be clicked, the document probably has share protection. In order to be able to edit the document now, this must first be removed.

  • To do this, first click on Review > Unprotect.
  • Then click on Review > Release Workbook to release the Excel spreadsheet.
  • In the window that now appears, check "Allow multiple users to edit at the same time". This also enables you to merge workbooks.
  • In the next window, simply confirm with "Yes".