Excel Tips & Tricks - How to unprotect only a portion of an Excel spreadsheet

Posted on March 15, 2007 at 3:39 pm

Make sure to read other posts from the Excel Tutorials series!

Microsoft Excel Basics Tutorial - Learning how to use Excel
Excel Tips & Tricks - How to unprotect only a portion of an Excel spreadsheet
Excel Basics - How to use the Conditional Sum wizard
How to use Data Validation in Microsoft Excel to create dropdown lists
Excel Tutorial - How to make a simple graph or chart in Excel
Excel Tutorial - Example of how to use the VLOOKUP function

If you’ve protected Excel documents before, you may have run into the issue where you wanted to protect most of the spreadsheet, but allow users to modify particular sections. Here’s how you can allow certain parts of the spreadsheet to be editable:

Let’s say I have a simple table of numbers going down from 1 to 5 in one column and then I have 3 columns where I multiply each number by 10, 20 or 30. Now let’s say I want to allow the users of my little Excel sheet to be able to change the number (1-5), but not modify anything else (such as the formulas). In order to do this, select the range that you want to allow to be editable, right click on the cells and go to Format Cells.

Click on the Protection tab and uncheck the Locked check box.

Now that those cells are “unlocked”, we’ll go ahead and protect the sheet. Protecting the sheet does not affect unlocked cells and therefore allows you to create partially modifiable spreadsheets.

Click on Tools and Protection and Protect Sheet.

In the dialog box, type a password if you like and you can leave the defaults as they are. If you would like to give your users more control, you can select any of the other self-explanatory options from below.