You are in: how-to > Protecting your Work in Excel

Protecting your Work in Excel


05/10/18 |Excel 2013/2016 | All levels | by Jorge Vilar

Excel has several protection-related features, covered below.

Types of Protection

Protected related features fall into three categories:

• Worksheet protection: Protecting a worksheet from being modified or restricting the modifications to certain users. Worksheet is one of the pages from the workbook.
• Workbook protection: Protecting a workbook from having sheets inserted or deleted, and also requiring the use of password to open the workbook. Workbook is your Excel file.
• Visual Basic (VB) protection: Using a password to prevent others from viewing or modifying your VBA code

Protecting a Worksheet

You may want to protect a worksheet for a variety of reasons. One reason is to prevent yourself or others from accidentally deleting formulas or other critical data. A common scenario is to protect a worksheet so that the data can be changed, but the formulas can’t be changed..

To protect a worksheet, select the worksheet and choose Review -> Protect Sheet.

Excel displays the Protect Sheet dialog box shown below.

Note that providing a password is optional. If you enter a password, that password will be required to unprotect the worksheet. If you accept the default options in the Protect Sheet dialog box (and if you haven’t unlocked any cells), none of the cells on the worksheet can be modified.
Press OK.
If you try to write in any cell of the worksheet, the following message will appear.

To unprotect a protected sheet, choose Review -> Unprotect Sheet. If the sheet was protected with a password, you’re prompted to enter that password.

Unlocking cells

If you need to allow some cells to be changed when the worksheet is protected, you will need to unlock them. This should be done before protecting the sheet.

By default, all cells in a worksheet can be edited but are ready to be locked. As soon as you protect a worksheet, all cells will be locked.

To change the lock attribute, select the cell or range of cells, right click and choose Format Cells. Go to tab Protection and untick Locked.

Choose Review -> Changes -> Protect Sheet to protect the sheet. After doing so, you can change the unlocked cells, but if you attempt to change a locked cell, Excel displays the warning alert.

Sheet protection options

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

• Select locked cells: If checked, the user can select locked cells using the mouse or the keyboard. This setting is enabled by default.
• Select unlocked Cells: If checked, the user can select unlocked cells using the mouse or the keyboard. This setting is enabled by default.
• Format cells: If checked, the user can apply formatting to locked cells.
• Format columns: If checked, the user can hide or change the width of columns.
• Format rows: If checked, the user can hide or change the height of rows.
• Insert columns: If checked, the user can insert new columns.
• Insert rows: If checked, the user can insert new rows.
• Insert hyperlinks: If checked, the user can insert hyperlinks (even in locked cells).
• Delete columns: If checked, the user can delete columns.
• Delete rows: If checked, the user can delete rows.
• Sort: If checked, the user can sort data in a range as long as the range doesn’t contain any locked cells.
• Use AutoFilter: If checked, the user can use existing autofiltering.
• Use PivotTable & PivotChart: If checked, the user can change the layout or create new pivot tables or charts.
• Edit objects: If checked, the user can make changes to objects (such as Shapes) and charts, as well as insert or delete comments.
• Edit scenarios: If checked, the user can use scenarios (see Chapter 36).

TIP
When the worksheet is protected, and the Select Unlocked Cells option is set, pressing Tab moves to the next unlocked cell, making data entry much easier.

Protecting a Workbook

Excel provides three ways to protect a workbook:

• Require a password to open the workbook.
• Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets.
• Prevent users from changing the size or position of windows.

Requiring a password to open a workbook
Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.

To add a password to a workbook, follow these steps:
1. Choose File -> Info -> Protect Workbook -> Encrypt with Password. Excel displays the Encrypt Document dialog box shown.
2. Type a password and click OK.
3. Confirm the password again and click OK.
4. Save the workbook.

To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols from the Encrypt Document dialog box, click OK, and save your workbook.

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

1. Choose Office -> Save As.
2. In the Save As dialog box (if you have Excel 2016, press "More options…), click the Tools button and choose General Options. Excel displays the General Options dialog box.
3. In the General Options dialog box, enter a password in the Password to Open field.
4. Click OK. You’re asked to re-enter the password before you return to the Save As dialog box.
5. In the Save As dialog box, make sure that the filename, location, and type are correct; then click Save.

The General Options dialog box has another password field: Password to Modify. If you specify a password for this field, the file opens in read-only mode (it can’t be saved under the same name) unless the user knows the password. If you use the Read-Only Recommended check box without a password, Excel suggests that the file be opened in read-only mode, but the user can override this suggestion.

Protecting a workbook’s structure
To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook’s structure. 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.

To protect a worksheet’s structure:

1. Choose Review -> Protect Workbook to display the Protect Workbook dialog box.
2. In the Protect Workbook dialog box, select the Structure check box.
3. (Optional) Enter a password.
4. Click OK.

To unprotect the workbook’s structure, choose Review -> Unprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password.

NEW!
You can download pdf from this how-to here!

- end -

Comments, suggestions and questions are always welcome - jorge@jorgevilar.com.

 •   top...»