Allowing changes to a protected worksheet

The Protect Sheet function is a handy tool which allows users to lock down a worksheet against editing.  However, what if you have a data entry sheet where you want to allow users to edit some cells but leave other ones? Excel doesn’t make the process for this very clear, but it is actually a very simple process:

The first step is to change the protection settings for the cells we wish to leave unprotected. Normally, when someone clicks on ‘Protect Sheet’, every cell within the current worksheet becomes locked. Since we want some of these cells to be edited, we need to change this:

1.       Select the range of cells that you wish to allow changes to. In this example, I’ll use A3:D8

2.       Right click on the selected range. A menu will pop up. Scroll down and choose ‘Format Cells’.

3.       Click on the Protection tab and uncheck the box next to ‘Locked’

4.       Click OK to return to the worksheet

The second step is to protect the rest of the worksheet:

1.      On the Excel Ribbon (if you’re using Excel 2007 or 2010), click to the Review tab. In Excel 2003, click on Tools and scroll down to Protection

2.      Click on the ‘Protect Sheet’ button

To see if these changes have worked, try and enter data within the unprotected range. You should be able to do so without any trouble. Now try to enter data outside of this range. You should see a prompt notifying you that users are unable to be make changes in that area.

And there you are. Happy computing!