by Svetlana Cheusheva, updated on
The tutorial shows how to protect Excel sheet with or without passwords. It also explains how to unlock Excel spreadsheet without password if you forgot or misplaced it.
When sharing your worksheets with other people or sending your Excel files outside of your organization, you may want to prevent other users from changing cells that shouldn't be changed, and also from moving or deleting important data in your worksheet or workbook.
Luckily, Excel provides a quick and easy way to lock a sheet or password protect an entire workbook to prevent accidental or deliberate changes in their contents.
Worksheet protection in Excel is not a security feature. It's is not actually intended to prevent people from accessing data in a password-protected worksheet. Why? Because Microsoft Excel uses a very simple encryption algorithm for worksheet protection. Even when you protect your Excel worksheet with a password, that password is very easy to break.
In older versions (below Microsoft Excel 2010), it doesn't even matter how long and complex your password is. Anyone who has at least some basic knowledge of VBA can crack it in minutes (here's an example of the VBA code that easily cracks passwords in Excel 2010 and lower).
The recent versions of Excel use more sophisticated methods of sheet protection, but they are also vulnerable and can be unlocked without knowing a password. In other words, if someone really wants to get access to a protected sheet, they will.
So, does this mean that Excel worksheet protection is absolutely pointless? Not at all! It makes sense to lock Excel sheets to avoid accidental changes in the sheet contents. A common example is Locking formula cells to prevent inadvertent erasure or modification of formulas.
The bottom line is - lock an Excel spreadsheet to prevent other users from accidentally tampering with your data, but never store sensitive information in your Excel sheets.
If you strive to ensure the maximum security protection for your Excel files, be sure to do all of the following:
To protect a sheet in Excel, perform the following steps.
Or, click the Protect Sheet button on the Review tab, in the Changes group.
As you have just seen, it's very easy to password protect a sheet in Excel. Unprotecting a spreadsheet is even easier, provided that you know the password. Though, unlocking an Excel spreadsheet without password is no big deal either :)
If you know the password for the protected sheet, removing protection is a matter of seconds. Just do any of the following:
If you are dealing with a password-protected worksheet, Excel will ask you to enter the password. Type the password and click OK.
If the worksheet was not protected with password, clicking the Unprotect Sheet button is all it takes to unlock the sheet.
As mentioned in the beginning of this tutorial, Excel worksheet protection is quite weak even in the recent versions of Excel. If you need to edit a password protected sheet but you cannot remember the password, try to unlock that Excel spreadsheet by using one of the following methods.
If you are using Excel 2010 or older, you can unlock a password-protected sheet with the following macro.
Tips and notes:
Another way (or rather a workaround) to unlock an Excel sheet that was protected with password is to copy its entire contents to a new sheet. Here's how:
Notes:
Apart from protecting the sheet contents, you may also want lock the entire workbook to prevent users from adding, moving, renaming or deleting worksheets, and viewing hidden sheets.
To protect the workbook, perform the following steps:
Note. The Windows option is not available (greyed out) in Excel 2013 and higher, as shown in the screenshot above. It is because Microsoft introduced the Single Document Interface in Excel 2013, where each workbook has its own top-level window that can be minimized, restored, and closed.
To remove the workbook protection, do the following:
Important note! Protecting the workbook structure should not be confused with workbook-level password encryption that can prevent other users from opening or/and modifying your workbook. If you are interested in this level of workbook protection, check out the next sections of this tutorial.
If you store your workbooks in a shared location such as a company local network, and you want to prevent other users from viewing your Excel files, you can encrypt your workbook with password that won't even let other people open it.
By default, Microsoft Excel uses AES 128-bit advanced encryption, and you can supply a password up to 255 characters.
To remove the workbook encryption, repeat the above steps, delete the password from the Password box, click OK, and then save your workbook.
Tip. In addition to password-protection, you can secure your Excel files further by using the BitLocker Drive Encryption, which is available in the Ultimate and Enterprise editions of Windows Vista and Windows.
If you don't mind other users opening your Excel file but you want to prevent them from making any changes to your workbook, perform the following steps.
Tip. If you want to protect your Excel file both from modifying and opening, type the passwords in the Password to open and Password to modify boxes. It can be the same password of two different ones.
To remove the password protection, repeat the above steps, and delete the password(s) from the Password to open and Password to modify boxes. Then click the Save button to save your workbook.
This is how you can protect an Excel sheet and lock the entire workbook from changing or opening. You can also protect a worksheet from changing by making it very hidden. Sometimes you may not want to go that far and rather lock only certain cells in your sheet. And this is exactly what we are going to discuss in the next article.
I thank you for reading and hope to see you on our blog next week.
Table of contents