Excel Protection: Protecting cells, ranges and worksheets


Excel Protection:YOUR OBJECTIVE

You have created a template in Excel and want to specify that only particular cells may be filled in and that the selected calculations are executed automatically. You can set cells, worksheets and the workbook design in Excel so they cannot be edited by other people. This learning module “Excel protection” will show you how to do this.

 

Excel Protection:HOW DOES IT WORK?

You have created a document and want to protect this worksheet so that entries may only be made in particular cells specified by you. Additionally, you can also prevent unauthorized people from changing the workbook design. Unprotecting the worksheet is only possible when your specified password is entered.

 

Excel Protection:Protecting cells and worksheets

To edit particular cells after enabling the worksheet protection, you first have to select all the cells you want other users to be able to edit later on. In forms these would be all the cells that have to be filled in by the user.

Excel Protection: HINT  (1)

You can select several non-adjacent cells by selecting the first cell or cell range and then continue to select other cells while pressing the [Ctrl] key.

After you have selected all the required cells, go to the Home tab and click the Format drop-down menu in the Cells group. You will notice that the Lock Cell button is now active (button is highlighted in yellow). 

 

Then click the Lock Cell option to unlock the selected cells.

Excel Protection: HINT (2)

You can also enable the locking via the Protection tab in the Format Cells dialog box, which you can access by clicking the dialog box launcher in the Font group, Alignment group or Number group on the Home tab. 

Excel Protection: CAUTION

If have not protected the worksheet yet, you will not notice any differences when editing the worksheet. The cell locking only becomes active after enabling the worksheet protection.

To now protect the worksheet, go to the Home tab and click the Protect Sheet… option in the Cells group.

 

This opens the Protect Sheet dialog box, where you can apply the following settings: 

 

Leave this option enabled to protect the sheet from all editing.

You should specify a password so another user cannot unprotect the worksheet and change the cells that you want to protect.

In this section you can specify in detail which users will have which rights. The default settings are usually appropriate, as the users may click in protected cells, but may not enter data into them. You cannot extensively format or even edit rows or cells in the default settings.

After you have entered the password and confirmed your entry by clicking the OK button, the worksheet will be protected so that it is only possible to enter data in non-locked cells.

You can very easily verify this by trying to enter any content into a locked cell. The following message will appear: 

 

Excel Protection: HINT (3)

If you need to edit the file further, you can unprotect the worksheet by clicking the Format button again and clicking theUnprotect Sheet… option.

You will be prompted to enter the previously specified password.

Excel Protection: HINT (4)

You can also enable or unprotect the worksheet in the Changes group on the Review tab. The Protect Sheet button changes its labeling to Unprotect Sheet when the worksheet protection has been enabled. 

Excel Protection:Protecting the workbook

If your workbook contains several worksheets, where each worksheet is protected, you can also protect the workbook design. Unauthorized users will then not be able to delete or move worksheets.

To protect the workbook design, go to the Review tab and click the  command.

 

This opens the Protect Structure and Windows dialog box: 

 

Protecting the structure means, for instance, that the order of the worksheets may not be changed, and no sheets may be added or deleted.

Protecting the windows means that the zoom may be used or other views may be applied, but it will not be possible to change the size or position of the window or to freeze the windows (to fix the position of rows or columns, for instance).

As when protecting the workbook, it makes sense to specify a password so other users may not disable the protection by simply clicking the command.

Excel Protection: HINT (5)

You can disable the workbook protection by clicking the  button again and entering the specified password, if prompted.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen