How to lock and protect selected cells in Excel?

It's easy to lock and protect the whole worksheet or workbook with clicking the Protect Sheet button or Protect Workbook button under Review tab. However, sometimes you may need to lock and protect only specific cells or selections in a sheet. How would you like to do? This article will guide you to lock and protest selected cells or ranges in Excel with following steps:

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Step 1: Select all cells in current worksheet with pressing the Ctrl key and A key together.

Step 2: Right click, and select the Format Cell item from the context menu.

Step 3: In the Format Cells dialog box, uncheck the Locked option under Protection tab, and click OK button. See the following screen shot:

shot-lock-cell-1

Step 4: Select cells and ranges that you want to lock.

Step 5: Right click selected ranges, and select the Format Cell item from the context menu.

Step 6: In the Format Cells dialog box, check the Lock option under Protection tab, and click OK.

Step 7: Click the Protect Sheet button in the Changes group under Review tab.

shot-lock-cell-2

Step 8: In the Protect Sheet dialog box, enter a password in the blank box under Password to unprotect sheet:. See the following screen shot:

shot-lock-cell-3

Step 9: Confirm Password dialog box pops up, please reenter the password again.

shot-lock-cell-4

Step 10: Click OK.

Then it locks and protects only selected cells and ranges in current worksheet, while unselect ranges are editable.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+7#Hidayat2013-11-01 17:15
Thank you very much for the great tutorial.
Reply | Reply with quote | Quote
+10#Dinesh2014-03-07 03:56
Please provide me details on how to lock certain cells and columns only not the whole sheet nor workbook but allow admin user and specific person to edit ranges using password provided.
Reply | Reply with quote | Quote
-1#Azhagu2014-04-19 08:45
Thank You very much for the excellent tutorial
Reply | Reply with quote | Quote
+1#herlin2013-11-20 01:06
thanks super usefull....


gracias super útil (utilísimo)......
Reply | Reply with quote | Quote
-1#Gueggeli2013-11-21 17:20
Thank you! Great tutorial :-)
Reply | Reply with quote | Quote
+1#shreeja2014-01-18 04:34
Thanx great help...... :-)
Reply | Reply with quote | Quote
+5#sulfikar2014-01-27 15:21
Hai,
I did above showing screen shot, but its protecting all area in the sheet,but ineed to lock only selected area.

Thanks
Reply | Reply with quote | Quote
-1#Excel Seeker2014-02-20 08:43
Please provide me details on how to lock certain cells and columns only not the whole sheet nor workbook but allow admin user and specific person to edit ranges using password provided.
Reply | Reply with quote | Quote
+3#suresh.p2014-02-25 15:22
Please provide me details on how to lock certain cells and columns only not the whole sheet nor workbook but allow admin user and specific person to edit ranges using password provided.
Reply | Reply with quote | Quote
0#suresh.p2014-02-25 15:35
please send step by step procedure protect the certain cell in the excelsheet
Reply | Reply with quote | Quote
-1#Admin-jay2014-02-26 09:04
Quoting suresh.p:
please send step by step procedure protect the certain cell in the excelsheet

There is no feature for that. We may consider to develop a feature in the future.
Reply | Reply with quote | Quote
-1#Milo2014-01-28 06:03
I needed to protect only certain areas of worksheets. Thank you.Very useful
Reply | Reply with quote | Quote
0#ANOJAN2014-02-04 05:39
THANKS FOR YOUR WONDER-FULL HELP....I AM VERY HAPPY NOW.....
Reply | Reply with quote | Quote
-1#chakravarthi S Prava2014-02-05 11:24
not working this option why ???
Reply | Reply with quote | Quote
-1#Amit Sharma2014-02-10 11:08
Thank You so much for so much easy understanding
Reply | Reply with quote | Quote
-1#Amit Sharma2014-02-10 11:09
Thank You so much for so much easy understanding.
Reply | Reply with quote | Quote
+5#archana name2014-02-15 02:28
please require me particular row & colum protec sheet , please give me step wise details ( note- not all sheet protec particular space protec in excell sheet)
Reply | Reply with quote | Quote
-1#Chetan2014-02-26 16:10
Hi, If i follow above mentioned Step then i have unable put filter in the top row so please suggest on this query ?
Reply | Reply with quote | Quote
-1#Raunak Saraogi2014-03-07 08:50
Thanks

Very Good INFo & tutorial.
Reply | Reply with quote | Quote
-1#sandeep2014-03-10 08:51
thanks ...Its very useful tutorial
Reply | Reply with quote | Quote
-1#mohammad farhan2014-03-16 13:08
Thanks,,,

its very helpful
Reply | Reply with quote | Quote
-1#jane2014-03-17 17:35
Hi... is there any way to lock the contents of the cells so that someone may copy their contents...but not be able to change or erase the contents of the cells? I tried checking off the first two boxes and protected the sheet... still able to alter contents of the cells. Thanks
Reply | Reply with quote | Quote
-1#Kishore2014-03-24 08:04
Very useful link. I solved my query
Reply | Reply with quote | Quote
0#Ivorymes2014-03-26 15:58
It won't work if you select the columns - it will only work if you select the cells you wish to lock.
Reply | Reply with quote | Quote
+1#Joseph2014-04-09 09:20
It didn't work ... It locks every cell in my worksheet though i selectively locked the cell as u said Excel 2010
Reply | Reply with quote | Quote
0#Alagbo2014-04-10 00:49
It will work for selected column.
Reply | Reply with quote | Quote
0#Trupti Deshmukh2014-04-11 06:45
Thanks.. Great help...
Reply | Reply with quote | Quote
0#Maqsood Ahmed2014-04-16 10:19
I have done my selected cell looked requriment
Reply | Reply with quote | Quote
0#Krista2014-04-17 15:15
Very helpful, thank you!
Reply | Reply with quote | Quote
0#Arushi Agrawal2014-04-18 05:54
Thank you very much. It works
Reply | Reply with quote | Quote
+1#pishgaman2014-04-18 13:05
I did all above steps 100 times but when i want to edit unprotected cells this message shows off:"to make changes click unprotect Sheet in the Review.....".
it seems it does not work.Please help :cry:
Reply | Reply with quote | Quote
0#Jay Chivo - Admin2014-04-19 06:09
Quoting pishgaman:
I did all above steps 100 times but when i want to edit unprotected cells this message shows off:"to make changes click unprotect Sheet in the Review.....".
it seems it does not work.Please help :cry:

Please contact me at :-)

please be more specific about your issue in your email. :-)
Reply | Reply with quote | Quote
+1#pishgaman2014-04-21 17:45
Thanks everybody.i did it.
Reply | Reply with quote | Quote
-1#pishgaman2014-04-21 17:47
I'm so happy.it worked
Reply | Reply with quote | Quote
+1#pishgaman2014-04-18 13:08
it does not work...please help...needed urgently
Reply | Reply with quote | Quote
0#priyanka2014-04-19 10:02
thank you so much.


:)
Reply | Reply with quote | Quote
0#Mika2014-04-25 13:07
Hi,.. when I lock the concrete column, it works that I am not able to change anything in it. But after locking the one concrete column, I can not change to add,insert and to cut, to change the width of the other columns. Do you know to help me,.how to lock one column, and do not avoid the work with other columns ?

Thank you.
Reply | Reply with quote | Quote
+1#Sanjay Kumar2014-04-26 09:28
Please go through this link, I am sure you will get your answer for locking any range of cells.
http://office.microsoft.com/en-in/excel-help/lock-or-unlock-specific-areas-of-a-protected-worksheet-HA010096837.aspx
Reply | Reply with quote | Quote
-1#Ashish Sharma2014-05-12 04:29
Nice, very helpful and my query resolved
Reply | Reply with quote | Quote
0#Matt Eastwood2014-05-30 07:49
I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance.
Reply | Reply with quote | Quote
0#Jahangir2014-06-12 06:25
Very nice .... Awesome ... Thanks a lot ... :P
Reply | Reply with quote | Quote
0#Rebecca2014-06-12 09:23
Thanks it worked!
Now I'm wondering if there's a way so that when I copy this table into Word those selected cells remain locked?
Reply | Reply with quote | Quote
0#Amar Nath2014-06-15 18:27
I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance.
Reply | Reply with quote | Quote
0#Jawaid2014-06-25 05:20
It's great help in very easy steps without confusing the user. :-)
Reply | Reply with quote | Quote
0#Sethu2014-07-04 06:29
It really works..I was searching for the same for so many days.
Thank u
Reply | Reply with quote | Quote
0#Armaghan Oonwala2014-07-16 20:36
thanxxxxx alottt.....
now im doing work easily
too easy and great tutorial
God bless u.....
Reply | Reply with quote | Quote
0#Ompong2014-07-31 19:37
Thousand thanks for this easy to follow guide in locking the specific cells in Microsoft Excel.
Reply | Reply with quote | Quote
0#Ankur2014-08-05 07:30
After going through this process of locking specific cells and protecting the sheet with a password, height and length of the rows and columns respectively of the sheet can't be changed. Any way out? Need help urgently.
Reply | Reply with quote | Quote
0#Parameshwar2014-08-08 11:29
It is a nice and crispy description. I like it. Thank you very much
Reply | Reply with quote | Quote
0#Marto2014-08-11 21:45
once i have locked cells to my likeing, the cells i can acess and edit, i dont seem to be able to change colour, font , size etc, even though they are not in the selcted lock range, the tool bar on my home tab is all greyed out, any ideas???
Reply | Reply with quote | Quote
0#Bubba Louie2014-08-12 18:00
After looking at several tutorials on this subject, all of which were very confusing, I found this one. I followed the tutorial step by step and it works great. Thank you for making it so easy for this old timer that is high tech deficient. Bubba
Reply | Reply with quote | Quote
0#Sol2014-08-15 14:28
thank you very much.. easy to follow
Reply | Reply with quote | Quote

Add comment


Security code
Refresh