How to protect and unprotect Excel sheet with or without password

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.

What you need to know about Excel worksheet protection

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:

  1. Use the modern versions of Excel with enhanced data encryption mechanisms.
  2. Protect your sheets with strong passwords that include different types of alpha numeric characters and special symbols. At that, try to make passwords as random as possible, for example by using our password generating tool.
  3. Protect the workbook structure to prevent other people from adding, moving, renaming, deleting or unhiding your sheets.
  4. For workbook-level security, encrypt the workbook with different passwords from opening and modifying.
  5. If possible, store your Excel files with sensitive information in a secure location, e.g. on an encrypted hard drive.

How to protect Excel sheet

To protect a sheet in Excel, perform the following steps.

  1. Right click a worksheet tab at the bottom of your screen and select Protect Sheet… from the context menu.

    Or, click the Protect Sheet button on the Review tab, in the Changes group. Protecting a sheet in Excel

  2. In the Protect Sheet dialog window, do the following:
    • To password protect your Excel sheet, type a password in the corresponding field. Be sure to remember the password or store it in a safe location because you will need it later to unprotect the sheet.
    • If the worksheet protection is nothing more than a precaution against accidental modification of the sheet contents by yourself or by the members of your local team, you may not want to bother about memorizing the password and leave the password field empty. In this case, unprotecting the sheet takes just a single click on the Unprotect Sheet button on the ribbon or in the sheet tab's right-click menu.
    • Select the actions you allow the users to perform.
      • By default, the following 2 options are selected: Select locked cells and Select unlocked cells. If you leave only these two options selected, the users of your sheet, including yourself, will be able only to select cells (both locked and unlocked).
      • To allow some other actions such as sorting, filtering, formatting cells, deleting or inserting rows and columns, check the corresponding boxes.
      • If you don't check any action, users will only be able to view the contents of your Excel sheet.
    • Click the OK button.
    Type the password (optional) and choose the actions you want to allow your users to perform.
  3. The Confirm Password dialog box will pop-up prompting you to retype the password so that an accidental misprint won't lock your Excel sheet forever. Retype the password and click OK. Done!

How to unprotect Excel sheet

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 :)

How to unprotect Excel sheet with password

If you know the password for the protected sheet, removing protection is a matter of seconds. Just do any of the following:

  • Right-click the sheet tab, and select Unprotect Sheet… from the context menu. Unprotecting an Excel sheet with password
  • On the Review tab, in the Changes group, click Unprotect Sheet. Another way to unprotect an Excel sheet with password
  • On the Home tab, in the Cells group, click Format, and select Unprotect Sheet from the drop-down menu. One more way to unlock an Excel spreadsheet with password

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.

How to unprotect Excel sheet without password

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.

Unlock Excel spreadsheet with VBA code (for Excel 2010 and lower)

If you are using Excel 2010 or older, you can unlock a password-protected sheet with the following macro.

  1. Open your Excel document and switch to the password-protected sheet (it should be active when you run the macro).
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. Right-click the workbook name on the left pane (Project-VBAProject pane) and select Insert > Module from the context menu.
  4. In the window that appears, paste in the following code:

    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
  5. Press F5 or click the Run button on the toolbar and wait a couple of minutes.
  6. The macro will report a cracked password, which is not the original one (always some combination of A's and B's), but nevertheless it works. You do not need to remember this fake password or enter it anywhere. Simply click OK and the sheet is unprotected!

Tips and notes:

  • If your Excel file contains more than one protected sheet, run the macro for each sheet individually to unprotect it.
  • To unprotect a sheet without password in Excel 365 - 2013, you can save the document as Excel 97-2003 workbook (*.xls) first. Then, close Excel and reopen the workbook - it will be opened in compatibility mode. Run the macro to unprotect the worksheet, and then save the workbook back as an .xlsx file. Alternatively, you can use one of free tools, for example Password Protection Remover add-in for Excel. Or you can upload the protected Excel worksheet to your Google Drive, and then open it with Google Sheets.
  • In case you are not very comfortable with VBA, you can find the detailed guidance in the following tutorial: How to insert and run VBA code in Excel.

Copy the protected sheet contents to another worksheet

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:

  1. Open the password-protected sheet.
  2. With the cursor positioned in cell A1, press Shift + Ctrl + End to select all used cells in the worksheet.
  3. Press Ctrl + C to copy the selected cells.
  4. Create a new sheet in the current workbook or in a different workbook (to create a new Excel file, simply press Ctrl + N).
  5. In the new blank worksheet, place the cursor in A1 and press Ctrl + V to paste the contents of the password-protected sheet.

Notes:

  • This method works only if the Select locked cells and Select unlocked cells actions are allowed in the protected spreadsheet.
  • If the password-protected worksheet has external links to other sheets or workbooks, you may need to re-create those links manually.
  • You can use Paste Special to copy / paste the column widths so you won't need to adjust them in the new sheet manually.

How to protect a workbook in Excel

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:

  1. On the Review tab, in the Changes group, click the Protect Workbook button. Protecting a workbook in Excel
  2. Under Protect workbook for, select one of the two options, or both:
    • Check the Structure box to protect the structure of a workbook. This will prevent the following actions:
      • Viewing hidden worksheets.
      • Renaming, moving, hiding or deleting sheets.
      • Inserting new sheets and chart sheets. However, users will be able to insert a chart in an existing worksheet.
      • Moving or copying sheets to another workbook.
    • Check the Windows box to retain the size and position of workbook windows. This will prevent users from moving, resizing, minimizing, or closing the workbook windows. Protecting the workbook structute

      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.

  3. In the Password box, type a password, and click the OK button.
    As indicated on the dialog window, a password is optional. However, if you do not enter a password, then anybody will be able to unlock your workbook with a button click.
  4. Retype the password in the Confirm Password window and click OK. That's it!

How to unlock a protected workbook in Excel

To remove the workbook protection, do the following:

  1. Click the Protect Workbook button again (in a locked workbook, it is highlighted in green like in the screenshot below): Removing the workbook protection in Excel
  2. Type the password if prompted.
  3. Save the workbook.

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.

Password protect Excel workbook from opening

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.

  1. In the workbook you want to encrypt, click File > Info, click the Protect Workbook button, and select Encrypt with Password in the drop-down menu. Select the 'Encrypt with Password' option to protect your Excel workbook from opening.
  2. The Encrypt Document window will appear, you enter a password in the Password box, and click OK. Enter a password to open the workbook.

    By default, Microsoft Excel uses AES 128-bit advanced encryption, and you can supply a password up to 255 characters.

  3. Retype the password in the Reenter password box, and click OK.
  4. Save your workbook.

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.

Password protect Excel workbook from modifying

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.

  1. In your Excel workbook, click the Save As button, and then click the Tools button at the bottom of the window.
  2. In the Tools drop-down menu, select General Options. Password protect Excel workbook from modifying.
  3. In the General Options dialog window, type a password in the Password to modify box. Additionally, you can select the Read-only option, which is recommended. Type a password in the 'Password to modify' box.

    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.

  4. Retype the password in the Confirm Password dialog, and click OK. If you've entered 2 passwords in the previous step, you will be asked to confirm both.
  5. Finally, click the Save button in the Save As dialog.

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

390 comments

  1. Works perfectly! The tip on unlocking sheets in 2013 and 2016 excel works as well, just make sure you save to an .xls file, and ALSO close out of excel and reopen the file before trying it. This opens the file in compatibility mode. Took me 20 minutes to figure out why it wasn't working.

  2. This is the coolest VBA code. Works perfectly. For those of you having problems, make sure you are right-clicking on the sheet to add the module, not the workbook. Thank you so much for posting!! What a time saver.

  3. Hi Thanks a lot
    you really made my day smartie.....

  4. Thank you so much!!! This was an incredible help.

  5. Hello,
    I would like ot thank you for the script to break up the PSW. Great job. really great work!

    Thank you!

  6. it worked!

  7. I have to agree with Kurt. I've been trying for hours to "Remove" a password from a WORKBOOK

  8. Me thinks Pepe Mano is rude and Stupid

  9. I've been using the cracker for years, working perfect. now my problem is I have new computer with office 265 and when I try to use it excel goes to "not responding" I have to go to task manager and end task to restart excel. Can anybody help me?

  10. Amazing...!
    I like excel and I appreciate...

  11. guys please help i have locked the workbook and then forgot the password how can i crack that

  12. There is a more simple way . You can simply upload the protected excel sheet to your google drive. Then open it using google spread sheet. Then u will be able to edit any cells from your google spread sheet. If you want to save it in your computer,just copy and paste it to a new sheet in MS excel.

    • OMG this saved the day!! The VBA method worked last year and earlier this year but does not anymore, at least for the spreadsheets we have. THANK YOU KANAN

      • Yes, Google Sheets works!!!!
        Thanks!

    • Brilliant! my prob solved in 2 mins - ive been trying to crack the pwd for months but never thought of this! I adore you!

  13. Hi,
    awesome tip, worked perfectly, thanks for sharing!!
    But it won't let me save it, have to enter the password to save.
    thanks heaps

  14. ALERT!!! DO NOT WASTE YOUR TIME ON THIS CRAP!!!!
    THIS MACRO AS POSTED ABOVE DOES NOT NOT NOT NOT NOT NOT NOT NOT WORK
    THIS ARTICLE SHOULD BE REMOVED FROM THE INTERNET
    CRAP WASTE OF TIME!!!!!!

    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

    • I just used this method for the 1st time today and it worked like a charm.
      Your just not doing it right!!

  15. wait a couple of minutes (password crecker) mind you, a couple actually mean two. Been waiting for 15 minutes, still counting... Anybody experienced this??

  16. Hi all,
    I have the below macro code, that is intended to place the date and hour on the next columns after the column "X" is filled. The issue I need the support is that, the cells to place the date and time are protected, and when running the macro it crashes because it cannot edit the addressed Cells. So i need a way in which eider the cells are unlocked to allow the data to be updated and then locked or that the macro should be able to place the data even if the cells are locked.

    Any hints?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 And Target.Row > 2 Then
    Cells(Target.Row, Target.Column + 2).Value = Date
    Cells(Target.Row, Target.Column + 3).Value = Now
    End If
    If Target.Column = 9 And Target.Row > 2 Then
    Cells(Target.Row, Target.Column + 2).Value = Date
    Cells(Target.Row, Target.Column + 3).Value = Now
    End If
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    • Delete the columns and add them back.

  17. Works amazingly

  18. I keep trying the module procedure on VBA Project but I keep coming up with Compile Error: Invalid Outside Procedure. What am I doing wrong? Any tips?

  19. Awesome, Thank you its worked very well

  20. This is awesome! Thanks it worked like a charm!

  21. 100% Workable.
    Many Many Thanks for your nice & Simple presentation
    to solve the problem.

  22. Works perfectly fine!!! Thanks man!

  23. Thanks,Excellent details,very help full.

  24. how to prtect excel file to unprotect.plese send me process?

  25. Works fine, thanks.

  26. it works than you

  27. Excellent script! Thanks so much for publishing this!

  28. Hi,

    I need a macro to open a separate sheet from protected excel and disable all the formulas

  29. if it's not working on XlSX files - try to save the file first as macro-enabled file - XLSM - then try this hack it works perfectly - have a good day

  30. When I run the code, I get no response from Excel or the VBA editor and have to force close it. I waited as long as an hour to try and see if it would work.

  31. LIFESAVER! THANK YOU! Fixed my problem in 3 minutes.

  32. Thank You

    You are really Great

  33. Extremely useful info, managed to unlock very important docs so that our team could introduce needed changes

  34. Super, its works 100% :-)

  35. Hi,
    I protected a Sheet. I used a Password. We need to unlock and relock to add information. When I went back in to 'unprotect' its greyed out and I cant select unprotect. Now it seems its locked up. There is no option in the 'Info or Right click the sheet, nor on the review tool bar' any ideas to ungrey so we can lock and unlock everyday?

    FYI
    This is a shared workbook. Others will be updating the other tabs.

    thanks

  36. i have a XLS file but SHARED and protected
    vba is unusable, most of options are greyed out including insert-module
    can;t save it as xlsx and unzip it

    any solutions? can't find nothing on the internet

    • Save the sheet as an xls file and then follow the steps. once completed save again as an xlxs sheet

  37. I'm using this on xlxs file but its not working only loading, no output since half hour.
    pls help....

  38. Hi,
    I am having an xlsx password protected sheet. e forgot the password the sheet and now we need the data from sheet on urgent basis.

    I tried ot follow your blog steps but unfortunately VBA editor for this sheet is coming as all options disabled.

    Can You please suggest what could be the issue.
    Thanks,
    Nida

  39. Excellent! The password cracker saved me days of work. My IT guys moved all my files to a new laptop, but some glitch in the MS Excel program on the new computer locked/protected all versions (online and my desktop) of a particular file. Thanks for being the smartest person around!

  40. Your password cracker worked perfectly. Thank you for this.

  41. WOW ITS WORKING

  42. Hi how to open the excel file with out password,

  43. Thank you very much for the code, it works perfectly. You saved me one day of work because after 4 years I had forgotten my password... I also unedrstand that security is relative :)

  44. Hello.
    Please could you help me solve small problem.
    I would like to know if is possible to modify Protect sheet /which is set up like preset. To add in the list one more option- allow /merge and center/

    thx

  45. Unprotect excel sheet formula doesn't works

    • It does work. If you are working with a spreadsheet that is later than 2010, you must save as .xls format first then run the macro.

  46. You are genius!!! Thank you so very much!!!!!

  47. Thanks for your help

  48. It works all from the first strike of the keyboard. You're good.

  49. all works!
    awesome

  50. Wasted 1.5 hours..nothing works as described

    • I think you can't understand. This tips is actually Perfect.
      Try again.
      I tried it Excell 2010.

    • you must try again... its working perfectly..

    • If you are trying to unprotect a workbook, it doesn't work as described. You have to unprotect from the 'file' tab. Once there just erase the password and save. Attempting to do it the way described in this article will appear to work, but won't actually remove the password protection (which you will clearly still see when you go to the 'file' tab).

      • did you save as xls first? I tried it wouldn't work if the file is xlsx

    • Working Prefectly

    • It works perfect! I tried

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)