05 Best Ways: Create Password Protect Excel & Unprotect it

05 Best Ways: Create Password Protect Excel & Unprotect it


Person climbing a staircase. Learn Data Science from Scratch: online program with 21 courses

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.

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.

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:

How to Protect Formula in Excel_1

(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.

How to Protect Formula in Excel_2

(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.

How to Protect Formula in Excel_3

(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.

How to Protect Formula in Excel_4

(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.

How to Protect Formula in Excel_05

• Method 2:

Home Cells ➪ ‘Format’ dropdown Protect Sheet

How to Protect Formula in Excel_7

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.

How to Protect Formula in Excel_6

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.

How to Protect Cells in Excel_1

Steps to Start:

How to Protect Cells in Excel_2

• 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.

How to Protect Cells in Excel_3

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.

How to Protect Cells in Excel_4

• 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. 

How to Protect Excel Sheet_2

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.

How to Protect Excel Sheet_1

• 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.

How to Unprotect Excel Sheet_1

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.

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.

How to Protect Excel Workbook_1

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 FileInfo ➪ 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.

How to Protect Excel Workbook_2

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

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

(1) Choose FileSave 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

How to Protect Excel Workbook_3

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.

How to Unprotect Workbook_1

• 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 FileInfo ➪ 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.

How to Unprotect Workbook_2

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.

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:

How to Protect VBA Project_1

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.

How to Protect VBA Project_2

Suggested-Courses_Green_Advance Excel Forum
If you would like to improve your academic and professional career as well, then the below premium courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
AEF_192by192
Advance Excel Forum

Premium Courses on ed2go

excel 2019 cert training4&bids=739114

Microsoft Excel 2019 Certification Training

Bestseller
4.9/5
Accounts Payable Specialist Excel 2019 GES20914&bids=739114

Accounts Payable Specialist Certification with Microsoft Excel 2019 (Voucher Included)

Bestseller
4.9/5
accounting with excel 2019 suite4&bids=739114

Accounting with MS Excel 2019 Suite

Bestseller
4.9/5

Premium Courses on Coursera

04 Fundamentals4&bids=759505

Excel Fundamentals for Data Analysis

Bestseller
4.9/5
4&bids=759505

Excel to MySQL: Analytic Techniques for Business

Bestseller
4.9/5
ICON 44&bids=759505

Excel Skills for Business: Advanced

Bestseller
4.9/5

Premium Courses on Udemy

2557448 8a4d 24&bids=507388

HR Analytics using MS Excel (Excel Analytics)

Bestseller
4.6/5
773214 f3b8 94&bids=507388

Advanced Excel: Top Excel Tips & Formulas

Top Rated
4.7/5
1885612 b008 64&bids=507388

Excel PRO TIPS: 75+ Tips to go from Excel Beginner to Pro

Bestseller
4.7/5
AI on Microsoft Azure: Machine Learning and Python Basics
Read-More_Orange

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.


Like it? Share with your friends!

Share via

Join Our Community List

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Thanks for your interest joining to Advance Excel Forum community.

Fill the Correct Information.

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

 

Cookie settings

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.

Social mediaOur website places social media cookies to show you 3rd party content like YouTube and FaceBook. These cookies may track your personal data.

AdvertisingOur website places advertising cookies to show you 3rd party advertisements based on your interests. These cookies may track your personal data.

OtherOur website places 3rd party cookies from other 3rd party services which aren't Analytical, Social media or Advertising.