How to Create Password Protect Excel & Unprotect it? It is a big challenge for every Excel user and every step should be aware of and have good knowledge of it. Because “Create Password Protect Excel” is a big term and there are 05 broad subcategories under it. We can password protect Excel formula, cells, worksheet, workbook and VBA Project by preventing users from accidentally or intentionally changing, adding, or deleting our data. 

Thus, we can say Excel offers a number of different protection options at different levels, e.g., protect our data at the workbook level, the worksheet level, the cell level, or the VBA code level.

I. HOW TO PROTECT FORMULAS IN EXCEL?

In many cases, we may want to protect our formulas from being overwritten or modified. To do so, we must unlock the cells and unhide the formulas that we will allow to be overwritten and then protect the sheet.

By default, all cells are locked. Locking and unlocking cells have no effect, however, unless we have a protected worksheet. 

Steps to Start:

(01) Unlock Cells using ‘Format Cells’ Dialog Box

Select all cells by pressing Ctrl+A  ➪ then press Ctrl+1 or Ctrl+Shift+F which will open the ‘Format Cells‘ dialog box ➪ Click on the ‘Protection‘ tab in the Format Cells dialog. As shown in the below Figure, we will see that the Locked option is chosen by default ➪ Uncheck the ‘Locked‘ box ➪ Click OK to close the Format Cells dialog.

(02) Using ‘Go to Special’ Dialog Box

With all the cells still highlighted, press Ctrl+G or F5 ➪ ‘Go To‘ dialog box opens, then press Alt+S or click on the ‘Special‘ button  ➪ ‘Go To Special‘ ➪ On the Go To Special dialog box, choose the ‘Formulas‘ option button ➪ Click OK to close the Go To Special dialog.

(03) Lock & Hide the Formulated Cells using ‘Format Cells’ Dialog Box

Excel will reduce the selection to only cells with formulas. Then press Ctrl+1 or Ctrl+Shift+F which will open the ‘Format Cells‘ dialog box ➪ Click on the ‘Protection‘ tab in the Format Cells dialog box ➪ Check both the ‘Locked‘ box and the ‘Hidden‘ box  ➪ Click OK to close the Format Cells dialog box.

As a result, the formulas work perfectly but hide from the formula bar to edit.

(04) Password Protect formulated Cells using the Ribbon

Enable protection for the sheet. (Note that if we skip this final step, we can still accidentally overwrite the formulas.)

• Method 1:

Click the ‘Review‘ tab ➪ Select ‘Protect Sheet‘ Select ‘Protect Sheet‘ in the Changes group to display the Protect Sheet dialog box. Leave Select locked cells and Select unlocked cells checked ➪ Type a password in the ‘Password to unprotect sheet‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed‘ box ➪ Read the caution statement and click OK.

• Method 2:

Home ➪ Cells ➪ ‘Format’ dropdown ➪ Protect Sheet

Note: Password protection is a serious activity. Passwords can be up to 255 characters, including letters, numbers, and symbols. Passwords are case sensitive, so ‘PASSword’ or ‘PassWORD’ is not the same as ‘Password’. Make sure we record the password in a secure location or select a password that we will always remember. If we forget the password, we will not be able to unprotect the worksheet. Also, remember that Excel passwords can be cracked, so it’s not a perfect security measure. 

Now, if we accidentally try to enter something in a formula cell, Excel will prevent us from entering the data.

II. HOW TO PROTECT CELLS IN EXCEL?

All cells in a worksheet are locked by default. Locked Cells don’t provide any protection until unlocked them. This is indicated by the blue border around the padlock icon for the Lock Cell option on the ‘Format‘ menu in the Cells group on the Home tab.

Steps to Start:

• Method 1: Using the Excel Shortcut (Ctrl+1 / Ctrl+Shift+F)

Select the cells in which we want users to be able to enter or edit data ➪ then press Ctrl+1 or Ctrl+Shift+F which will open Format Cells dialog box ➪ Click on the ‘Protection‘ tab in the Format Cells dialog box. Note that Lock Cell is active by default. ➪ Uncheck the ‘Locked‘ box ➪ Click OK to close the Format Cells dialog box.

With all the cells still highlighted, click the ‘Review‘ tab ➪ Select ‘Protect Sheet‘ in the Changes group to display the Protect Sheet dialog box. Leave Select locked cells and Select unlocked cells checked ➪ Type a password in the ‘Password to unprotect sheet‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed‘ box ➪ Read the caution statement and click OK.

• Method 2: Using the Ribbon (Home ➪ Format ➪ Format Cells)

Select the cells in which we want users to be able to enter or edit data ➪ then go to the ‘Home‘ tab ➪ Click ‘Format‘ in the Cells group which will open the ‘Format Cells‘ dialog box ➪ Click on the ‘Protection‘ tab in the Format Cells dialog box. Note that Lock Cell is active by default. ➪ Uncheck the ‘Locked‘ box ➪ Click OK to close the Format Cells dialog.

With all the cells still highlighted, go to the ‘Home‘ tab ➪ Further click ‘Format‘ in the Cells group ➪ Select ‘Protect Sheet‘ in the Protection section to open the Protect Sheet dialog box. Leave Select locked cells and Select unlocked cells checked ➪ Type a password in the ‘Password to unprotect sheet‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed‘ box ➪ Read the caution statement and click OK.

Note: After protecting a worksheet, most commands on the Ribbon are dimmed, indicating that they are not available. If someone tries to enter or change data in a locked cell in a protected worksheet, Excel displays the warning message and instructs the user how to remove the protection. 

III. HOW TO APPLY EXCEL PROTECT SHEET?

The commands needed for Worksheet-level and cell-level protection can be found on the ‘Review‘ tab in the Changes group. When we protect a worksheet, we prevent users from altering the locked cells.

Steps to Start:

• Method 1: Review ➪ Protect Sheet

Open the worksheet we want to protect ➪ Click the ‘Review‘ tab ➪ Select ‘Protect Sheet‘ in the Changes group to display the Protect Sheet dialog box. Leave Select locked cells and Select unlocked cells checked. Additionally, Select the checkboxes for the actions we want users to be able to do in the Allow all users of this worksheet to list ➪ Type a password in the ‘Password to unprotect sheet‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed‘ box ➪ Read the caution statement and click OK.

• Method 2: Home ➪ Format ➪ Protect Sheet

Open the worksheet we want to protect ➪ Click the ‘Home‘ tab ➪ Click ‘Format‘ in the Cells group ➪ Select ‘Protect Sheet‘ in the Protection section to open the Protect Sheet dialog box. Leave Select locked cells and Select unlocked cells checked. Additionally, Select the checkboxes for the actions we want users to be able to do in the Allow all users of this worksheet to list ➪ Type a password in the ‘Password to unprotect sheet‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed‘ box ➪ Read the caution statement and click OK.

IV. HOW TO UNPROTECT EXCEL SHEET?

(1) Click Unprotect Sheet in the Changes group on the ‘Review‘ tab, or click ‘Format‘ in the Cells group on the Home tab and select Unprotect Sheet. The Unprotect Sheet dialog box opens.

(2) Type the password in the Password box and click OK. The worksheet is then unprotected so that we can make changes.

V. HOW TO APPLY EXCEL PROTECT WORKBOOK?

Although locking cells and protecting a worksheet prevent unauthorized modifications, users might make unwanted changes to other parts of the workbook. We can prevent users from inserting, deleting, renaming, moving, copying, and hiding worksheets within the workbook by protecting the workbook with a password. Protecting an entire workbook does not disable the unlocked cells within a workbook; it merely prevents worksheet manipulation from occurring. That is, individual cells must still be unlocked even if a workbook is unprotected.

When a user attempts to open a password-protected workbook, a password must be entered before the file is opened.

• Method 1: Review ➪ Protect Workbook

Click the ‘Review‘ tab ➪ Select ‘Protect Workbook‘ in the Changes group to display the Protect Structure and Windows dialog box  ➪ Make sure that we enable the ‘Structure‘ checkbox in the Protect workbook for section ➪ Type a password in the ‘Password (optional):‘ box and click OK. ➪ The Confirm Password dialog box opens. Type the same password in the ‘Reenter password to proceed’ box ➪ click OK, and save the workbook. 

When we reopen the workbook, we’ll be prompted for a password.

Note: 

• When a workbook’s structure is protected, the user may not

➢ Add a sheet

➢ Delete a sheet

➢ Hide a sheet

➢ Unhide a sheet

➢ Rename a sheet

➢ Move a sheet

• With the workbook protected, all the commands that involve changing the structure of the workbook, for example, add, delete, move, or rename worksheets will be disabled. To re-enable these commands, we’ll need to remove the password protection.

• Method 2: File ➪ Info ➪ Encrypt with Password

Click File ➪ Info ➪ click the ‘Protect Workbook‘ drop-down, which displays some additional options in the drop-down list ➪ Click the second option ‘Encrypt with Password’ ➪ Excel displays the Encrypt Document dialog box. Enter the password and click OK ➪ The ‘Confirm Password’ dialog box opens. Type the same password ➪ Click OK and save the workbook. 

When we reopen the workbook, it will prompt us for the password.

• Method 3: File ➪ Save As ➪ Tools ➪ General Options

Excel provides another way to add a password to a document:

(1) Choose File ➪ Save As (sequentially press Alt, F, A) and click Browse. The Save As dialog box appears.

(2) Click the ‘Tools‘ drop-down and choose General Options. The General Options dialog appears.

(3) Enter a password in the ‘Password to Open‘ field.

(4) Click OK. You’re asked to reenter the password before you return to the Save As dialog box.

(5) In the Save As dialog box, make sure that the file name, location, and type are correct; then click Save

Note:

• The General Options dialog box has another password field: ‘Password to Modify‘. If we specify a password in this field, the file is opened in read-only mode (it can’t be saved under the same name) unless the user knows the password.

• If we use the ‘Read-Only Recommended‘ checkbox without a password, Excel suggests that the file be opened in read-only mode, but the user can override this suggestion.

VI. HOW TO UNPROTECT EXCEL WORKBOOK?

• Method 1: Review ➪ Unprotect Workbook

(01) If we protect a workbook via the Review tab, then unprotect the workbook via the Review tab.

Click the ‘Review‘ tab ➪ Click ‘Protect Workbook‘ and type the password in the Password box in the Unprotect Workbook dialog box, and then click OK.

• Method 2: File ➪ Info ➪ Remove Encryption

On some occasions, we may want to remove a password from an Excel file. The process of setting a password encrypts the file, so we’ll need to remove the encryption to remove the password.

To remove the password of an Excel file, follow the steps below:

First, open the workbook and enter the password in the Password box  ➪ Click File ➪ Info ➪ click the ‘Protect Workbook‘ ➪ Encrypt with Password ➪ In the Encrypt Document dialog box, delete the contents of the Password text box ➪ Click OK.

Close and reopen the workbook. It will no longer challenge you for a password.

VII. HOW TO PROTECT VBA PROJECT?

If our workbook contains VBA macros, then we may want to protect the VBA Project to prevent others from viewing or modifying the macros. To protect a VBA Project, follow these steps:

1. Press Alt+F11 to activate the VB Editor.

2. Select our project in the Projects window.

3. Choose Tools – Properties (where corresponds to our Project name). The Project Properties dialog box appears.

4. Select the Protection tab.

5. Select the ‘Lock Project for Viewing’ checkbox.

6. Enter a password and confirm it.

7. Click OK and then save our file. When the file is closed and then reopened, a password will be required to view or modify the VBA code.