How to Lock & Unlock an Excel Spreadsheet

Locking a worksheet in Excel 2013 gives you the freedom to share the file with someone, without worrying about that person making unwanted changes to the data or its formatting. Because you can customize the protection for any worksheet, it's important to review what the user can and cannot do once its locked. The options you leave unlocked for the user range from the ability to edit PivotCharts to the ability to click and select a cell.

...
Use a password to protect a locked worksheet from editing.
credit: Screenshot courtesy of Microsoft.

If the Excel file contains more than one worksheet, you may want to consider locking the entire Excel workbook.

Step

Select the Excel worksheet you want to lock. Select the "Review" tab and click the "Protect Sheet" icon in the Ribbon's Changes section.

...
Click "Protect Sheet."
credit: Screenshot courtesy of Microsoft.
...
By default, users can select locked and unlocked cells in a protected worksheet.
credit: Screenshot courtesy of Microsoft.

Step

Scroll through the list of options and choose those that you don't want locked. Any item without a check mark beside it will be locked. Options with check marks are unlocked.

Step

The first two items are checked by default, allowing anyone opening the worksheet to select both locked and locked cells. Clear either of these check boxes if you don't want anyone be able to select these cells.

...
"Format Cells," "Format Columns" and "Format Rows" are selected.
credit: Screenshot courtesy of Microsoft.

Step

Click the "Format Cells," "Format Columns" and "Format Rows" check boxes if you want users to be able to change the formatting, like the font or background colors. Note that if you applied conditional formatting to the worksheet, the formatting will still change if someone changes a value in a cell, even if the formatting options are locked.

...
Options for allowing users to insert or delete rows, columns and hyperlinks.
credit: Screenshot courtesy of Microsoft.

Step

Specify if you want the user to be able to insert and delete rows or columns in the worksheet. There is also an option to allow the user to insert hyperlinks into the worksheet, like creating a link to another worksheet or a Web page within one of the cells.

...
Check marks unlock the last five options in this example.
credit: Screenshot courtesy of Microsoft.

Step

Click the "Sort" check box if you want users to be able to sort data using the Sort & Filter group options under the Data tab. This won't allow them to sort ranges on any locked cells on a protected worksheet.

Step

Select "AutoFilter" if you want users to be able to change filter ranges on AutoFilters you have already applied to the worksheet. Note that users can't add or remove AutoFilters on any protected worksheet, regardless of the options that have been unlocked.

Step

Select the "Use PivotTable & PivotChart" option to allow users to create or change PivotTables and PivotCharts.

Step

Unlock the "Edit Objects" option to allow users to change graphics like charts, maps, shapes and text boxes. This option also needs to be unlocked for users to interact with PivotCharts.

Step

Click the "Edit Scenarios" option to allow users to look at hidden scenarios, or to edit and delete scenarios. Values in changing cells can only be edited if the cells aren't protected.

...
Enter a password and click "OK."
credit: Screenshot courtesy of Microsoft.

Step

Type a password in the "Password" field and click "OK." Enter the password again in the confirmation window that opens. You will need to enter this password in order to unlock the worksheet.

...
Click "Unprotect Sheet."
credit: Screenshot courtesy of Microsoft.

Step

Click the "Review" tab above the worksheet at any time and click "UnProtect Sheet." This prompts you to enter the password you selected. Once the worksheet is unprotected, you can edit the worksheet as usual.