Overview of security and protection in Excel

Microsoft Excel provides several layers of security and protection to control who can access and change your Excel data:

Securing a workbook file

You can help secure an entire workbook file by restricting who can open and use its data and by requiring a password to view or save changes to the file.

ShowPassword security

Password security at the workbook file level uses advanced encryption (a standard method of securing the content of a file) to help protect your workbook from unauthorized access. A password can be set on the Security tab of the Options dialog box (Tools menu, Options command). You can specify two separate passwords that users must type to:

  • Open and view the file     This password is encrypted to help protect your data from unauthorized access.
  • Modify the file     This password is not encrypted and is only meant to give specific users permission to edit workbook data and save changes to the file.

These passwords apply to the entire workbook file. For optimal password security, it's best to always assign a password to open and view the file, and have users with permission to modify data enter both passwords.

 Note   Password protection of a workbook file is separate from the workbook structure and window protection that you can set in the Protect Workbook dialog box (Tools menu, Protection submenu, Protect Workbook command).

 Important   Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

ShowEncryption types

For more secure password protection of the workbook file and its properties, you can choose from several encryption types that are available for use with Excel workbook files. Encryption makes text unreadable to all but authorized users who have a public key that matches the encryption type and that allows them to decrypt the text.

To access encryption options, click the Advanced button on the Security tab of the Options dialog box (Tools menu, Options command).

ShowRead-only recommendation

If you do not want to prevent users from opening a workbook file as read/write, but you would like to remind them that the data is important and should not be changed, you can have Excel recommend that the workbook should be opened as read-only. You can do this with or without requiring a password to open the file.

When you select the Read-only recommended check box on the Security tab of the Options dialog box (Tools menu, Options command), users get a read-only recommendation when they open the file. However, this does not prevent users from opening the file as read/write, so that they can change the file and save their changes.

Protecting specific worksheet or workbook elements

When you share an Excel file so that others can collaborate on the data, you can prevent any user from making changes to specific worksheet or workbook elements by protecting (or locking down) certain parts of the file. You can also specify a password to allow individual users to modify specific elements.

 Important   The following types of protection should not be confused with file security. They are not meant to make your workbook more secure and cannot protect it from users who have malicious intent.

ShowWorksheet element protection

When you protect a worksheet, all cells on the worksheet are locked by default, and users cannot make any changes to a locked cell. For example, they cannot insert, modify, delete, or format data in a locked cell. You can, however, specify which elements users will be allowed to change when you protect the worksheet.

To protect a worksheet, point to Protection on the Tools menu, and then click Protect Sheet. You can control access to individual worksheet or chart sheet elements by selecting or clearing the following check boxes.

Worksheet options

Check box When cleared, prevents users from
Select locked cells Moving the pointer to cells for which the Locked check box is selected on the Protection tab of the Format Cells dialog box. By default, users are allowed to select locked cells.
Select unlocked cells Moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. By default, users are allowed to select unlocked cells, and they can press the TAB key to move between the unlocked cells on a protected worksheet.
Format cells Changing any of the options in the Format Cells or Conditional Formatting dialog boxes. If you applied conditional formats before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition.
Format columns Using any of the commands on the Column submenu of the Format menu, including changing column width or hiding columns.
Format rows Using any of the commands on the Row submenu of the Format menu, including changing row height or hiding rows.
Insert columns Inserting columns.
Insert rows Inserting rows.
Insert hyperlinks Inserting new hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.), even in unlocked cells.
Delete columns Deleting columns. Note that if Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete.
Delete rows Deleting rows. Note that if Delete rows is protected and Insert rows is not also protected, a user can insert rows that he or she cannot delete.
Sort Using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
Use AutoFilter Using the drop-down arrows to change the filter on an AutoFiltered range. Users cannot create or remove AutoFiltered ranges on a protected worksheet, regardless of this setting.
Use PivotTable reports Formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), or creating new reports.
Edit objects
  • Making changes to graphic objects—including maps, embedded charts, shapes, text boxes, and controls—that you did not unlock before you protected the worksheet. For example, if a worksheet has a button that runs a macro, you can click the button to run the macro, but you cannot delete the button.
  • Making any changes, such as formatting, to an embedded chart. The chart continues to be updated when you change its source data.
  • Adding or editing comments.
Edit scenarios Viewing scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) that you have hidden, making changes to scenarios that you have prevented changes to, and deleting these scenarios. Users can change the values in the changing cells, if the cells are not protected, and add new scenarios.

 Note   If you run a macro that affects an element that is protected on the worksheet, a message appears and the macro stops running.

Chart sheet options

Check box When selected, prevents users from
Contents Making changes to items that are part of the chart, such as data series, axes, and legends. The chart continues to reflect changes made to its source data.
Objects Making changes to graphic objects—including shapes, text boxes, and controls—unless you unlock the objects before you protect the chart sheet.

ShowPermission to access specific areas of a protected worksheet

Before you protect a worksheet, you can unlock the ranges that you want users to be able to change or enter data in. You can:

  • Unlock cells for all users on the Protection tab of the Format Cells dialog box (Format menu, Cells command).
  • Unlock cells for specific users in the Allow Users to Edit Ranges dialog box (Tools menu, Protection submenu, Allow Users to Edit Ranges command).

Users whom you specify in the Permissions for range dialog box (Permissions button) can automatically edit the range without entering the password. All other users are prompted for the password when they want to edit the range.

Keep the following in mind:

  • If a cell belongs to more than one range, users who are authorized to edit any of those ranges can edit the cell.
  • If a user attempts to edit multiple cells at once and is authorized to edit some but not all of those cells, the user will be prompted to select and edit the cells one by one.
  • If you specify ranges in the Allow Users to Edit Ranges dialog box without assigning a password, those ranges will be unlocked for all users.

ShowPassword protection of worksheet and workbook elements

When you protect a worksheet or workbook to lock its elements, adding a password is optional. In this context, the password is merely intended to allow access to certain users while helping to prevent changes by other users. This level of password protection does not ensure that all sensitive data in your workbook is secure. For optimal security, you should use a password to help safeguard the workbook file itself from unauthorized access.

 Note   It is very important that you remember the password that you set. Without the password, there is no way to unprotect the workbook or worksheet.

 Important   Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Use a strong password that you can remember so that you don't have to write it down.

ShowWorkbook structure and window protection

You can lock the structure of a workbook, which prevents users from adding or deleting worksheets or from displaying hidden worksheets. You can also prevent users from changing the size or position of worksheet windows. Workbook structure and window protection applies to the entire workbook.

To protect a workbook, point to Protection on the Tools menu, and then click Protect Workbook.

You can choose which elements you want to protect (workbook structure, windows, or both) by selecting or clearing the following check boxes.

Workbook options

Check box When selected, prevents users from
Structure

 Note   If you run a macro that includes an operation that can't be performed in a protected workbook, a message appears and the macro stops running.

Windows
  • Changing the size and position of the windows for the workbook when the workbook is opened.
  • Moving, resizing, or closing the windows. (However, users can hide and unhide windows.)

 Note   In the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), programmers can protect macros so that the macros cannot be viewed or changed by users. Programmers can protect macros by using the Protection tab of the Project Properties dialog box in the Visual Basic Editor (Tools menu, Project Properties command). For more information, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).

To hide an entire workbook so that users cannot see it but can gain access to contents such as macros (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), use the Hide command on the Window menu, and then save the hidden workbook.

ShowProtection of confidential data

Excel features related to hiding data or locking data with passwords are not intended to secure or protect confidential information in Excel. These features are merely meant to obscure data or formulas that might confuse some users or to prevent others from viewing or making changes to that data.

Excel does not encrypt data that is hidden or locked in a workbook. To help prevent modification of confidential data and to help protect it from being viewed, you may want to limit access to any workbook files that contain such information by storing them in locations that are available only to authorized users.

 
 
Applies to:
Excel 2003