Protecting worksheets and workbooks: How to keep others from changing your spreadsheet.

Another common problem many users have in Excel is figuring out how to keep other people from messing up their spreadsheets by changing something that shouldn't be changed. Below, I will show you how to keep this from happening using Excel's built-in protection. (To learn how to protect your VBA code, click here.)

I've shown the procedure in 2013, but the process is the same for 2010 and 2007 as well.

 

Protect a Specific Worksheet

1. Go to the sheet you want to protect, then click on "Protect Sheet" in the Review tab.

Protect Sheet

 

2. If you want this sheet to be password protected, enter a password (you will have to confirm it), otherwise just leave it blank. You can also select what options you want the user to be able to perform.

Enter password

 

Now, notice that you are unable to change anything in the sheet.

Can't change it

 

Make Certain Cells Editable

1. If you want certain cells to be editable, highlight those cells, right click on them, and choose "Format Cells."

Format Cells

 

2. Go to the Protection Tab.

 

3. Uncheck the box that says "Locked" and hit OK.

Uncheck Locked

 

The cells you had selected will now be editable.

 

Protect the Workbook

1. To keep changes from being made to the structure of the workbook (adding and deleting sheets, etc), first click on the "Protect Workbook" in the Review tab.

Protect Workbook

 

2. If you want the workbook to be password protected, enter a password (you will have to confirm it), otherwise just leave it blank. You can also select what options you want the user to be able to perform.

Password

 

Now if you try to add or delete a sheet, you'll notice that those options are unavailable.

Can't delete

 

Unprotect a Sheet or Workbook

1. Click "Unprotect Sheet" (or workbook, depending on what your trying to do) in the Review tab.

Unprotect

 

2. Type in the password if prompted.

Type password

 

Now you can again edit all cells.

 

A word of advice about passwords: If you lose your password, you're pretty much out of luck. However, you don't always have to put in a password to keep your spreadsheet safe. Just the fact that it is protected can frequently act as a sufficient indicator that the user should be careful. Also, generally speaking, the people who are most likely to mess up your spreadsheet are the same ones who don't know how to unlock it, password or not.

 

 

Back to Resources page.