To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) or workbook elements, with or without a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.). You can remove the protection from a worksheet as needed.
Important Worksheet and workbook element protection should not be confused with workbook-level password security. Element protection cannot protect a workbook from users who have malicious intent. For optimal security, you should help protect your whole workbook file by using a password. This allows only authorized users to view or modify data in the workbook.
For more information on how to set passwords and for read-only recommendation for a workbook, see Set a password to open or modify a document, workbook, or presentation.
When you share a workbook with other users, you may want to protect data in specific worksheet or workbook elements to help prevent it from being changed. You can also specify a password that users must enter to modify specific, protected worksheet and workbook elements. In addition, you can prevent users from changing the structure of a worksheet.
By default, when you protect a worksheet, all the cells on the worksheet are locked, and users cannot make any changes to a locked cell. For example, they cannot insert, modify, delete, or format data in a locked cell. However, you can specify which elements users will be able to change when you protect the worksheet.
Hiding, locking, and protecting workbook and worksheet elements is not intended to help secure or protect any confidential information that you keep in a workbook. It only helps obscure data or formulas that might confuse other users and prevents them from viewing or making changes to that data.
Excel does not encrypt data that is hidden or locked in a workbook. To help keep confidential data confidential, you may want to limit access to workbooks that contain such information by storing them in a location that is available only to authorized users.
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 or for specific users.
For information on how to unlock cells and ranges in a protected worksheet, see Unlock specific areas of a protected worksheet.
When you protect a worksheet or workbook by locking its elements, adding a password to edit the unlocked elements is optional. In this context, the password is only intended to allow access to certain users while helping to prevent changes by other users. This level of password protection does not guarantee that all sensitive data in your workbook is secure. For optimal security, you should secure a workbook itself with a password to help safeguard it from unauthorized access.
When you protect worksheet or workbook elements by using a password, it is very important that you remember that password. Without it, you cannot 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. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
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 whole workbook.
This displays the Picture Tools or Drawing Tools, adding the Format tab.
Tip You can also use the Go To command to quickly select all the graphic objects in a worksheet. On the Home tab, in the Editing group, click Find & Select, and then click Go To. Click Special, and then click Objects.
Note You do not need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify.
|Clear this check box||To prevent 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 can 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 column formatting commands, including changing column width or hiding columns (Home tab, Cells group, Format button).|
|Format rows||Using any of the row formatting commands, including changing row height or hiding rows (Home tab, Cells group, Format button).|
|Insert columns||Inserting columns.|
|Insert rows||Inserting rows.|
|Insert hyperlinks||Inserting new hyperlinks, even in unlocked cells.|
Note If Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete.
Note If Delete rows is protected and Insert rows is not also protected, a user can insert rows that he or she cannot delete.
Using any commands to sort data (Data tab, Sort & Filter group).
Note Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
Using the drop-down arrows to change the filter on ranges when AutoFilters are applied.
Note Users cannot apply or remove AutoFilters on a protected worksheet, regardless of this setting.
|Use PivotTable reports||Formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports, or creating new reports.|
Doing the any of the following:
|Edit scenarios||Viewing scenarios 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.|
|Select this check box||To prevent 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.|
Note The password is optional. If you do not supply a password, then any user can unprotect the sheet and change the protected elements. Make sure that you choose a password that is easy to remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.
|Select this check box||To prevent users from|
Note Users will be able to insert an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.) in an existing worksheet.
Note Users will be able to hide and unhide windows.
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.
Note The password is optional. If you do not supply a password, then any user can unprotect the workbook and change the protected elements. Make sure that you choose a password that you can remember, because if you lose the password, you cannot gain access to the protected elements in the workbook.
Note You may also want to save or print the current version of the workbook, because this history data might not apply to later versions of the workbook. For example, cell locations, including row numbers, in the copied history may no longer be current.
Note If this check box is not available, then you must unprotect the workbook before clearing the check box. Do the following:
Note The Protect Sheet option changes to Unprotect Sheet when a worksheet is protected.