Excel’s Cell Protection – Better Safe than Sorry

When people find out I work for PDF2XL they often send me questions about Microsoft Excel. I don’t mind, in fact it is one of the main ways I discover features and tricks that I might not have considered before. Here is a cool Excel tip I discovered exactly this way…

Recently my friend Tom called and asked …

“My colleagues at work don’t understand that some cells should NOT be edited. Is there an easy way to protect a cell to stop people from over writing it?”

I am sure this is a common complaint, and the less technically savvy your colleagues or assistants are, the more likely they can damage your precious data.

Luckily Microsoft included Cell Protection in their spreadsheet product for this very purpose!

Cell protection is a powerful tool for when you create spreadsheets as templates for others. It can save them breaking the spreadsheet by preventing them from overwriting existing formulas, and it can save you a lot of effort trying to fix the transcription errors anyone might introduce while inputting.

How to Confine Input to One Area

To confine data entry only to certain area, you need to first select the area you want to allow data entry in:

Next, right-click the selected area and select the ‘format cells’ option from the right-click menu.

In the ‘format cells’ dialog select the ‘Protection’ tab.

Remove the check-mark from the ‘Locked’ option and press ‘OK’.

Now to lock the sheet, select the Review Ribbon and press the ‘Protect Sheet’ option.

At this point you can choose if you want to only lock the data entry option or to disable selection altogether.

I think it is a good idea to disable selection of locked cells since it keeps confusion to a minimum. Simply remove the check-mark from the ‘Select locked cells’ option then click ‘OK’.

That’s it! You now have a safely protected Excel worksheet where your users can only enter data in the area you designated.

Password Protecting Worksheets and Cells

Wait, there’s more! You can go even further with Excel data collection and create a situation where each user can only update his/her area of the worksheet.

To do this you start with the same workbook.

You then select the ‘Allow Users to Edit Ranges’ option on the review Ribbon.

And press the ‘New..’ button

In the new range dialog, enter a meaningful title for that range to make it easier to refer to that range later when you want to change or delete it.

Next define the range in the ‘refers to cells’ field. You can press the ‘collapse dialog’ button and mark the range selection on the sheet itself.

Finally give a password to the range. Make it a specific password for each of your users. You’ll be sending this password along with the sheet to your user.

Now click ‘OK’.

Finally, click the ‘Protect sheet’ button and you are done.

When you share the file, don’t forget to let each user have their range password, otherwise they will get stuck at this message:

Advanced Excel User Permissions Control

If you and your coworkers all work within the same windows domain, you can make the update process even smoother.

In the range definition process, after you’ve defined the range, you can grant permissions to specific users to edit the range without entering a password.

To do this, once the range is defined, click the ‘Permissions…’ button:

In the ‘Permissions for…’ dialog press the ‘Add…’ button:

Enter the user name of the person you want to associate with the range and press ‘OK’

You’ll see that the user is added to the ‘Permissions for…’ window:

Now press OK

And finally press the ‘Protect Sheet…’ button.

Repeat this process for every user range and you’re done.

At this point you can put this file on a shared network folder and let all the users know that it is there. With this solution, you won’t need to give them passwords as they will be automatically able to access their own assigned range while the rest of the document will be blocked for updates.

P.S.

If you’d like to get an immediate boost in productivity try our PDF to Excel Converter. It’ll free up most of the time you spend on re-keying PDF data.