Here is a small tutorial that explains how can you lock cells in Microsoft Excel so that no one can edit those.
When people find out I work with Cogniview they often send me questions about Microsoft Excel. I do not 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 in Microsoft Excel
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 is 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 automagically able to access their own assigned range while the rest of the document will be blocked for updates.
[Want to see something interesting that can be done with Excel? Check out this article that explains How to Post Tweets from Microsoft Excel].
Over to You
How to do you share your Excel worksheets? Do you use cell protection or have you used other methods to share data input using Excel? Please share your tips, questions and experiences in the comments …
About the author
Yoav is the CEO a company called Cogniview that creates software to convert files from PDF to XLS.
Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.
For more Excel tips from Yoav, join him on Facebook or Twitter
