05 Best Ways: Create Password Protect Excel & Unprotect it

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.

[su_note note_color=”#fefff2″]

HOW MANY TYPES OF EXCEL PROTECTION?

Excel’s protection-related features fall into five broad categories:

➢ Cells protection: Protecting a range of cells from being modified or restricting the modifications to certain users.

➢ Formula protection: Protecting formulas from being overwritten or modified.

➢ Worksheet protection: Protecting all or part of a worksheet from being modified or restricting the modifications to certain users.

➢ Workbook protection: Protecting a workbook from having sheets inserted or deleted and requiring the use of a password to open the workbook.

➢ Visual Basic (VB) protection: Using a password to prevent others from viewing or modifying your VBA code.

[/su_note]

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.

[su_note note_color=”#fbf2ff”]

SHEET PROTECTION OPTIONS

Method 1: Click the ‘Review’ tab ➪ Select ‘Protect Sheet’ in the Changes group to display the Protect Sheet dialog box.

Method 2: Click the ‘Home’ tab ➪ Click ‘Format’ in the Cells group ➪ Select ‘Protect Sheet’ in the Protection section to open the Protect Sheet dialog box.

The Protect Sheet dialog box has several options, which determine what the user can do when the worksheet is protected:

➢ Select Locked Cells: If this is checked, the user can select locked cells using the mouse or the keyboard. This setting is enabled by default.

➢ Select Unlocked Cells: If this is checked, the user can select unlocked cells using the mouse or the keyboard. This setting is enabled by default.

➢ Format Cells: If this is checked, the user can apply formatting to locked cells.

➢ Format Columns: If this is checked, the user can hide or change the width of columns.

➢ Format Rows: If this is checked, the user can hide or change the height of rows.

➢ Insert Columns: If this is checked, the user can insert new columns.

➢ Insert Rows: If this is checked, the user can insert new rows.

➢ Insert Hyperlinks: If this is checked, the user can insert hyperlinks (even in locked cells).

➢ Delete Columns: If this is checked, the user can delete columns.

➢ Delete Rows: If this is checked, the user can delete rows.

➢ Sort: If this is checked, the user can sort data in a range as long as the range doesn’t contain locked cells.

➢ Use AutoFilter: If this is checked, the user can use existing auto filtering.

➢ Use PivotTable & PivotChart: If this is checked, the user can change the layout of pivot tables or create new pivot tables. This setting also applies to pivot charts.

➢ Edit Objects: If this is checked, the user can make changes to objects (such as Shapes) and charts, as well as insert or delete comments.

➢ Edit Scenarios: If this is checked, the user can use scenario management features.

[/su_note]

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.

[su_note note_color=”#fff7f2″]

PROTECT WORKBOOK OPTIONS

We should know more about the protect workbook options. 

The File ➪ Info ➪ Protect Workbook drop-down list contains the following options:

➢ Mark as Final: Use this option to designate the workbook as “final.” The document is saved as a read-only file to prevent changes. This isn’t a security feature. Rather, the Mark as Final command is useful to let others know that you’re sharing a completed version of a workbook.

➢ Encrypt with Password: Use this command to specify a password that is required to open the workbook.

➢ Protect Current Sheet: This command lets you protect various elements of a worksheet. It displays the same dialog box as the Review ➪ Changes ➪ Protect Sheet command.

➢ Protect Workbook Structure: This command lets you protect the structure of a workbook. It displays the same dialog box as Review ➪ Changes ➪ Protect Workbook.

➢ Add a Digital Signature: This command allows you to “sign” a workbook digitally.

[/su_note]

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.