How to protect Excel sheet

To protect a sheet in Excel 2016, 2013 and 2010, 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.



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.



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 with password

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

1. Right-click the sheet tab, and select Unprotect Sheet… from the context menu.



2. On the Review tab, in the Changes group, click Unprotect Sheet.



3. On the Home tab, in the Cells group, click Format, and select Unprotect Sheet from the drop-down menu.



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 2013 and Excel 2016. 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 version, you can unlock a password-protected sheet with the following macro.

1. Press Alt + F11 to open the Visual Basic Editor.



2. Right-click the workbook name on the left pane (Project-VBAProject pane) and select Insert > Module from the context menu.



3. Copy and paste the code below into the (Code) window. Do not change any of the code, just copy and paste 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 "One usable 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

4. Press F5 or click the Run button on the toolbar and wait a couple of minutes. 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. If 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. If you need to unprotect Excel sheet without password in the modern versions of Excel 2013 and Excel 2016, try one of free tools, for example Password Protection Remover add-in for Excel.

4. Save your workbook as "Excel macro-enabled workbook". Press Crl+S, then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog.

The "Save as" dialog will open. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.

5. Press Alt+Q to close the Editor window and switch back to your workbook.

How to run VBA macros in Excel

When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the "Macro" dialog.

Then select the wanted macro from the "Macro Name" list and click the "Run" button.

https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/