Comments on: How to lock and unlock cells in Excel

Learn a few useful techniques to lock a cell or a few cells in Excel to protect them from deleting, overwriting or editing. The tutorial also shows how to unlock individual cells on a protected sheet by a password, or allow specific users to edit those cells without password. And finally, you will learn how to detect and highlight locked and unlocked cells in Excel. Continue reading →

Comments page 2. Total comments: 64

  1. hi is anyone can help me out.i want make drop down list with name (for example john,david,craig,)and as soon as name(david)selected from that list ask for password to allow access to next cell for select that name(david)person signature

    thanks a lots it will be lots help if some one provide some solution

  2. Hi Dear

    I want to protect my cells through VBA code can you tell me simple Code for same.

  3. great article, thanks!!

  4. Could you protect a spreadsheet but still able to use a filter? if so, how?

  5. hi,
    Nice article.. :)
    i have 3 ranges for certain cells wiz no password..
    Each range has different user name permission set.

    If i select range 1, 'the allow users to edit ranges option is disabled' automatically. Im not able to do apply same for other 2 range...

    anyone can help??

  6. Hi,
    I want protect a cell in excel, so that user cannot delete or modify that cell. Only able to select the value from dropdown

    Pls help me on this issue.

    Thank you.

  7. Great instructions. In a few minutes I was able to protect a worksheet, yet be able to have selected cells provide user input into a computation, with results appearing in protected cells.

  8. Can any one help me figure out this?
    I am trying to protect my work book without using password but unfortunately I am unable.

    1. Hello, Stephen,

      Please see how to protect Excel files with or without passwords in this article on our blog.

      If you don't find the solution to your problem, then please describe in more detail what you do to protect the workbook.

  9. Hi?
    Thank you for the article, it is really very helpful, but i would like to have another additional guidance;
    My excel sheet is used as an inventory, i want to make it the way no one can modify Lows and cells, but also they can add tools and materials information then be able to save.
    Is that possible? If yes can you please provide steps to do it?

    Thank you very much.

  10. How I protect the cell from adding or deleting at a certain time. for example:
    I do not add or delete on this cell from 1:00 PM to 2:00 PM at 04/20/2018.
    Is there a formula in Excel? Please

  11. I have an worksheet where I have protect exact cells and leave other cell for entry. But my problem is I can entry the unprotected cell but I can not format that unprotected cell (such as font colour,size etc) unless un-protect the sheet.

    Is any way to protect the exact cell and leave other cell as normal cell work.

  12. Hi,

    Awesome tutorials! May I know on how to lock the filter in the Pivot?
    I tried to locked the specific cell but it's not possible to refresh data.

  13. good morning!

    madam Svetlana Cheusheva,

    kindly let me know how to lock one colum or row to protect from editing.

  14. I have a spreadsheet with locked and unlocked cells. I want to use conditional formatting to permanently lock particular cells at specified future times.

    Stated simply:

    if now() > cell X then
    lock cell Y.
    endif

    Can anyone help me?

  15. nice one

  16. Hi Natalia,

    I´ve used the info here to create the ranges with different passwords, as Ive got a sheet which will be modified by different people each one of them with their own password. Once a range is unlocked how does the user lock again that particular range?
    What we want to avoid is that once a range is unlocked, one of the other users can modify a range which is not his/her responsibility

    many thanks

  17. Awesome.Today I learned how to lock some columns on my Payroll template. Thanks a million.

  18. I went to 5 different sites to figure out how to protect some cells on a worksheet with no luck, then I found this one and I had success. Directions are so easy and clear. it's my "Dummies for protecting some cells" Thank you once again.

  19. I don't have the option 'Allow users to Edit Ranges'. Any ideas? Using Office 365 on Mac.

    1. Hello, Phil,

      I'm very sorry you're having a problem finding that option, but I'm afraid, we won't be able to help you here. If you still hasn't managed to find the option, please, contact Microsoft support team.

  20. Thank you so much, this is super helpful!! Very clear and easy to follow.

  21. I am grateful. You made my day.

  22. Solving lookup salutation.

  23. Hi Svetalana,
    Is it possible to freeze a colour while still editing celss. As my files move through various stages, I change the colour of the row.
    However, I would like to be able to freeze certain cells within a row ie. keep the same colour for some cells while still being able to edit inside the cells and change the colour of the other cells around it.
    For example, I would like the purple, brown and blue parts to remain the same irrespective of how many times I change the colour of a row.

  24. Thank you for helping me out on this. I was little confused when I was protecting some particular cells.

    You have a kind heart.

    :)

  25. Hi. Great instructions. Very detailed too. I have a strange problem. I collect data in an input range and I have set them to be unlocked as instructed. I use this data in some formulae and give output. The formula cells and out put cells are locked and hidden. But once I input data the input cells also automatically become locked and I am not able to paste again without using ctrl z. The other option is I have to allow users to select locked cells in protect sheet to enable adding new input. But I don't want the users to be able to copy other locked cells. Please help.

    1. Good question. the problem is when pasting by default, you are also pasting the formatting (AKA the cells being copied from are locked, so it pastes the "Locked" format also)
      A few workarounds:
      1. To paste, you have to always right click and paste as "Values"
      2. create a macro (a little more advanced) that does this for you. You can use the code below as a starting point. The code below just pastes whatever you have copied with values only in whatever cell you have selected.
      You could have this code behind a button, so you just click the button and it automatically pastes in the place required. If you do this, you could also have the required cells always selected as well if you utilize the lock 'options' while locking the cells.

      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  26. Does anyone have a macro that unlocks a column in a protected worksheet and each week locks that column and unlocks the next column (I am trying to prevent users retrospectively changing data they have already submitted).

    Many thanks

    1. I would love to know that as well.

  27. you have made excel easy to understand. thank you!

  28. hello, do you know how to make the cells immediately uneditable once you pressed "enter" in one of the unlocked cells?

    1. Love to know this answer, so lock cells by a formula of some sort

  29. It never got near it... not even close

    Do you know of a program other than excel 2016 that I can work with that will allow me to do this simple function?
    I would be most grateful...
    David

    1. Hi David,

      Did you try the steps from "How to lock cells in Excel other than input cells'?

      To preserve the current cell formatting, on step 2, in the Style dialog window, be sure to uncheck all boxes other than Protection.

  30. I have an excel spreadsheet that I want locked except for 4 cells that are in-filled with yellow, and have RED font in them. Nice and noticeable.
    Unfortunately it seems utterly impossible to lock the rest of the sheet and leave these 4 cells open for data input. The above instructions won't do it.

    Do you know of a program other than excel 2016 that I can work with that will allow me to do this simple function?
    I would be most grateful...
    David

    1. All cells are "locked" by default, all you have to do is "protect sheet" and now the entire sheet is read only.

      To unlock the yellow/red cells like you are saying:
      highlight the cells you want unlocked > press ctrl+1 (command+1 if using mac) to open the "Format Cells" menu > navigate to the "Protection" tab > Uncheck "Locked"

  31. Thank you fantastic article

  32. Wow! Nice article

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)