Locking a certain number of Cells in an Excel Sheet

abhivics

New member
Joined
Feb 27, 2012
Messages
8
Reaction score
0
Points
0
Hello Guys,

Is there a way to lock a certain number of fields in Excel sheet. ie. no one is able to access amend them except the administrator who has the password for it.
Although I have just done a work around by putting a data validation on the fields .
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi
By default all cells in a sheet are Locked, so as soon as you Protect a sheet, you are unable to change them.
So, select all of the cells other than those you want to be locked, and then choose Format Cells>Protection and remove the check mark.
Now, protect your sheet with a Password and those cells you have chosen will be locked from user amendment.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hiya Roger,

The question was a little ambiguous, but I was thinking something bigger that dealt with specific users:

Starting with Excel 2002, if you have a domain at work, you can lock cells and give permissions to certain individuals to be able to edit them. To do this:
  • Excel 2002/2003 Tools-->Protection-->Allow Users To Edit Ranges
  • Excel 2007+ Review-->ChangesàAllow Users To Edit Ranges
The dialog box that results is shown below. Its concept is that you need to specify the ranges that users CAN edit. To do so you’d click New.

protect1.jpg

Once done, you’ll see the dialog box shown below:

protect2.png

Give the range a title and a range then also provide a password. This is a very important step, as if you do not provide a password then this setup will have no effect.

Finally, click Permissions and add the users you wish to include. And don’t forget to include yourself or you may also be locked out (which can, of course, be undone by unprotecting the workbook).

While we have tested this interesting feature in our offices, I admit it is not one we use, as it seems like it would be difficult to maintain. Regardless, it could offer value in some situations.
 
Top