Search Brown University

Protect Files, Sheets and Cells in Excel 2007

Please note that none of this security is foolproof.    It's possible, and not very difficult, to get past Excel's password protection.  However, some of these options will make it easier to collaborate with others on Excel documents, and avoid accidental data tampering.
Do not email passwords with your Excel documents.  A phone call is a safer way to communicate the password.

Protect a File

When to use: You are working with confidential data

If you're working with confidential data, you might want to password protect your entire Excel file.  As mentioned above, Excel security is not foolproof, but a password may deter someone who has accidentally stumbled upon your file.

To prevent your file from being opened without a password:

  1. From the Office button, choose Save As. 
  2. Click the Tools menu at the bottom of the Save As window and choose General Options.  
  3. Here, you can specify two passwords -  one to open the file, and another to make changes to the file.  If you check the "Read Only Recommended" checkbox, anyone who opens your file will be asked to save a copy instead of replacing your original file.

Protect a Worksheet

When to use:  You are sharing the file and want others to view, but not edit, a worksheet

When you share Excel files with others, it's possible for someone to accidentally delete a formula or replace a cell's content.  Protecting a worksheet or cell range can save you a lot of headaches.  Note that this is different from password protecting your entire file.  People will still be able to open and view your file, they just will be asked for a password if they try to make changes in a protected area.
To protect a worksheet:

  1. Click on the Review tab and choose Protect Sheet. 
  2. Choose a password.  Remember the password, because you are going to need it if you want to edit the worksheet yourself.
  3. Next you will be shown some options.  When protecting an entire sheet, it  makes sense to leave these options as is. 

Locking / Unlocking Specific Cells

When to use:  You are sending out the file for others to complete

If you're sending out an Excel file to people who are supposed to fill in information, your best option might be to selectively unlock the cells that they're supposed to edit, leaving the rest locked.  This will prevent them from accidentally erasing a formula or replacing important content.
An overview of the process: You will select and unlock the cells that you want to be editable, and then protect the entire worksheet. As a result, others will be able to easily enter data into the cells you selected, but will not be able to alter the rest of the cells without a password.

  1. Start from an unprotected sheet.
  2. Select the cells that you want people to be able to change.  To select adjacent cells, just click and drag. If you need to select a cell that is not adjacent to the other cells, hold down the control key and click on it.
  3. Right click on any one of the selected cells and choose Format Cells.  Go to the Protection tab and uncheck Locked. 
  4. Now we are going to password protect our entire worksheet.  The cells we just unlocked will remain editable, but the rest of the sheet will be locked.  From the Review tab, choose Protect Sheet.   I recommend unchecking the "select locked cells" option.  The result will be that people won't even be able to CLICK in your locked areas, making it easier for them to edit without stumbling into the locked cells.
  5. Last, try it out.  Try clicking on a locked area and then on an unlocked area. 

If you need to change a locked area in the future, click on the Unprotect Sheet option in the Review tab and enter your password. Remember to protect the sheet again before sending it out.  You don't need to select and unlock cells again, you just need to protect the sheet.

Ranges with Passwords

When to use: You're sending out a file to many people, each responsible for specific cell ranges

If you are sharing a file with several people and they each have their own area to edit, you may want to assign different passwords to different areas in order to prevent them from accidentally editing an area that isn't theirs.

  1. Start from an unprotected file.  In the Review tab, choose Allow Users to Edit Ranges.  Now, you will create a different password for each editable area.  Click the New button to designate a new area.  For a Title, I recommend typing something meaningful so it will be easy to keep track of the cell ranges. For example, if you are designating this area for a specific person, type their name.
  2. Where it says "Refers to cells", choose the cell range.  The easiest way to do this is to click the icon at the end of this field to go to the worksheet and choose your cells.  Like in the previous technique, you can either click and drag to select adjacent cells, or hold down control and click on cells that are not adjacent.  Click on the icon to return to the dialog.
  3. Last, choose a password for this range only.
  4. Repeat this process for each area you would like to make editable.  It's essential that you choose different passwords so that collaborators will only be able to edit their own area.
  5. Now the important part - we're going to protect the sheet so that the passwords take effect. Go to the Review tab and choose Protect Sheet.  This time, make sure you have "Select Locked Cells" CHECKED.  Otherwise, your collaborators will not be able to get to the cells they are supposed to edit.

Comments (0)

Add a comment