Protection of documents and cells can help prevent inadvertent changes to your worksheet. This can be especially helpful if you have someone who is unfamiliar with worksheets or doing your data entry, or if you spent many hours struggling to get the worksheet right.
Excel offers the option of protecting the entire document, individual objects, the structure of a window, and/or specific cells. With Excel, you can even add a password to the file. If you want to prevent changes to sheets or cells, you need to lock the cells and then protect the sheet.
Protection Options
When you are protecting your workbook, you have two primary options:
Prevent data entry for select cellsUsers can access the worksheet and view the information; however, access for making changes is restricted. For more information, refer to
Locking & Unlocking Cells.
Restrict or prevent access to the fileUsers can be prevented from viewing the worksheet, or users can view the workbook but not make changes to it. For more information, refer to
File Level Protection.
Locking & Unlocking Cells
Excel can
protect cells, graphics, charts, and other worksheet objects. This protection will take effect
only after you turn on the
Worksheet Protection option in the
Protect Sheet dialog box. If you enable protection, no changes can be made to a cell until you unlock that cell.
Locking Cells
You can easily lock any cell in a worksheet.
NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.
Select the cell(s) to be locked
From the Ribbon, select the Home command tab
In the Cells group, click FORMAT
In the Protection section, select LockNOTE: If the icon is highlighted, the cells are locked.
-
Unlocking Cells
-
Select the cells you want to unlock
From the Ribbon, select the Home command tab
In the Cells group, click FORMAT
In the Protection section, deselect LockThe cells are unlocked.
NOTE: If the icon is not highlighted, the cells are unlocked.
Workbook Level Protection
You can prevent a workbook from having its structure and windows modified or resized by another user.
Protecting the Workbook
From the Ribbon, select the Review command tab
In the
Changes group, click
PROTECT WORKBOOK
The
Protect Workbook dialog box appears.
- Select the appropriate option(s):
Structure
Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.
Windows
Prevents the user from being able to resize or move the window.
Password
Allows only those who know the password entered in this text box to turn the workbook protection off.
Click OKThe workbook is protected.
Unprotecting the Workbook
From the Ribbon, select the Review command tab
In the Changes group, click PROTECT WORKBOOK
The workbook is unprotected. Users can now modify the structure and windows of the workbook.
NOTE: If you included a password when you turned the protection on, you must enter the password to turn the protection off.
Worksheet Level Protection
You can protect a single worksheet from being modified by another user.
Protecting the Worksheet
From the Ribbon, select the Home command tab
In the Cells group, click FORMAT
In the
Protection section, select
Protect Sheet...
The
Protect Sheet dialog box appears.
- In the Protect Sheet dialog box, select the appropriate options:
Protect worksheet and contents of locked cells
Prevents changes to locked cells.
Password to unprotect sheet
Allows only those who know the assigned password to unprotect the worksheet.
Allow all users of this worksheet to
Checked boxes are aspects that any user can access.
Click OKThe worksheet is protected.
Unprotecting the Worksheet
From the Ribbon, select the Home command tab
In the Cells group, click FORMAT
In the Protection section, select Unprotect Sheet...
The worksheet is unprotected. Users can now modify the worksheet.
NOTE: If you included a password when you turned the protection on, you must type the password in thePassword text box to turn the protection off.
File Level Protection
You have two options when saving your workbook file with a password. You can save the file so only those who know the password can open the file, or so only those who know the password can modify the file.
Protecting the File
From the File menu, select Save As...The Save As dialog box appears.
From the
Tools pull-down list,
select
General Options...The
General Options dialog box appears.
To prevent unauthorized access to the file, in the Password to open text box, type a password
To prevent unauthorized changes to the file, in the Password to modify text box, type a password
OPTIONAL: If you would like Excel to recommend that this file be opened as a read-only file each time it is opened, select Read-only recommended HINT: Read-only files can be modified, but the changes cannot be saved without creating a new file.
Click OKThe Confirm Password dialog box appears.
In the Reenter password to modify text box, type the password(s) in the order of the password(s) that you typed in the Save Options dialog box
EXAMPLE: If you typed apple in the Password to open text box and orange in the Password to modify text box, you must type apple in the first confirmation dialog box and orange in the second confirmation dialog box.
Click OK
Click SAVE
Your file is saved with password protection.
Unprotecting the File
If you no longer need to password-protect the file, you can remove the password.
Open the file
NOTE: In order to open the file, you will need to know the password.
From the File menu, select Save As…The Save As dialog box appears.
In the Save As dialog box, from the Tools pull-down list, select General Options...
In the Password to open text box, select the text » press [Delete]
In the Password to modify text box, select the text » press [Delete]
Click OKThe file is no longer password protected.
To save the file, click SAVE A confirmation dialog box appears.
NOTE: This will overwrite the existing saved file with the new file, which is currently not password-protected. Any other changes you have made to this file since opening it will also be saved and will overwrite the old document.
Click YES
Your file is re-saved without password protection.
