Do you have an Excel template for others to fill out and you want to make sure that they only fill out certain cells and leave the other cells alone? How do you lock only some cells in an Excel worksheet?

Below is a screenshot of a very simple Excel sheet. The users will enter quantities and discounts in the green cells. The formula will automatically calculate the totals. You don’t want users to start changing the sheet in any way, except for entering their numbers in the green cells. How do you do this?

We can protect the entire sheet with Protect Sheet, but we don’t want to do that until we first unlock the cells that we want the users to enter data into.

Find Format

First we need to select all of the cells that we want the users to be able to edit. In this simple example we could just use Ctrl and manually select the cells, but let’s use Find instead. Press Ctrl+F to open the Find and Replace dialog box. Click on the down arrow of Format.

Here is the Find Format Dialog box, in the Fill tab.

Select your color. Click Ok. Click Find All. Click Ctrl+A to select all of the cells found.

Close the Find and Replace dialog box. We are done with it because it has selected all of the cells that are green. That’s all we needed it to do.

Unlock the Selected Cells

Press Ctrl+1 to open the Format Cells dialog box. Go to the Protection tab. Ensure that the Locked check box is not checked.

Press Ok. Now we’ve unlocked our editable cells. Now we can Protect the sheet. Review. Protect Sheet. Give it a password if you wish. That’s it.

Data Validation

Perhaps you want to further protect the integrity of the sheet by ensuring that users only enter in whole positive numbers in the Quantity column. Check out our post on Excel Data Validation. To make the change, first unprotect the sheet, add your data validation and protect the sheet.

Have you forgotton your password for your protected sheet? Do you want to unprotect it? Have a look at this YouTube video called EASILY UNLOCK Protected Excel Sheets WITHOUT Password.