How to Protect Excel Formula, Cells, Worksheet & Workbook

How to Protect Excel Formula, Cells, Worksheet & Workbook

By

How to Protect Excel Formula, Cells, Worksheet & Workbook? It is a big question in every Excel user’s mind. We can protect Excel formula, cells, worksheet, and workbook data by preventing users from accidentally or intentionally changing, adding, or deleting our data. 

Excel offers a number of different protection options. For example, we can protect our data at the workbook level, the worksheet level, or the cell level, and we can protect the VBA code as well.


This Tutorial Contents:

TYPES OF PROTECTION

Excel’s protection-related features fall into five 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 Formula 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 ‘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 ‘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.)

• Method1: 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

• Method2: 

Home Cells ➪ ‘Format’ dropdown Protect Sheet

How to Protect Formula in Excel_7

NotePassword 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

• Method1: 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

• Method2: 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 the ‘Home‘ tab ➪ Click Format‘ in the Cells group 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.

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 Protect Excel 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:

• Method1: 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

• Method2: 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

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

Method2: 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 Protect Excel 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.

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

• Method2: 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

• Method3: 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?

• Method1: Review ➪ Unprotect Workbook

(01) If we protect a workbook via Review tab, then unprotect the workbook via 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

• Method2: 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 – <Project Name> Properties (where <Project Name> 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


Read-More_Orange

80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts

90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key

Paste Special in Excel Vs Break Link – Which one is better?

 

 

Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF