How to Lock Cells in Excel

puneet-gogia-excel-champs

- Written by Puneet

Locking cells is a feature in Excel to protect the cell’s data from getting edited, modified, or deleted by any user.

In Excel, users can lock all the cells with the specific worksheet to protect that entire worksheet cells or only lock the specific cells within the worksheet to get protected only those specific cells from getting edited.

In this tutorial, we have explained how to lock all the cells, specific cells, and cells with formulas and unlock them.

Steps to lock All Cells in Excel

In Excel, all the cells are by default locked, but anyone can edit the cells until we protect the worksheet. To activate the lock cells feature, one has to protect the sheet, and once the sheet gets protected, all the cells will become locked and non-editable.

To make all the locked cells feature effective, follow the below steps:

  1. First, select all the cells using the shortcut “Ctrl +A” or by clicking on the top left triangle on the column header bar.
    select-all-cells
  2. After that, right-click and select the “Format cells” option or click on the “Alignment” group arrow to open the “Format Cells” dialog box.
    open-format-cells-option
  3. Here, click on the “Protection” tab and see if the “Locked” option is selected or not, if not select the “Locked” option and click OK.
    select-locked-option
  4. Now, go to the “Review” tab and click on “Protect Sheet” and then provide the password and other permissions you might want to grant to users, and then click OK.
    protect-sheet-icon
  5. In the end, re-enter the password, and your all cells locked feature will become effective, and now no one can edit any cell unless the sheet gets unprotected.
    re-enter-password

Lock Specific Cells in Excel

  1. First, select all the cells using “Ctrl +A” or by clicking on the top left triangle on the column header bar.
  2. After that, right click and select the “Format cells” option or click on the “Alignment” group” arrow to open the “Format Cells” dialog box.
    lock-specific-cells-only
  3. Here, click on the “Protection” tab and make sure to un-select the “Locked” option to un-lock the default locked cells and click OK.
    un-select-locked-option
  4. Now, select only those cells or the range of cells, which you want to lock and open the “Format Cells” dialog box.
    range-to-be-locked
  5. In the “Format Cells,” dialog box go to the “Protection” tab and select the “Locked” option so that only selected cells will get locked this time.
    tickmark-locked-option
  6. At this point, go to the “Review” tab and click on the “Protect Sheet” icon and then provide the password and other permissions you might want to grant to users, and then click OK.
    enter-password-to-protect-sheet
  7. In the end, re-enter the password and your only selected cells will get locked this time and now the users can only edit the cells other than those selected lock cells.
    enter-password-again

Shortcut to lock Specific Cells in Excel

There is no direct keyboard shortcut to lock cells, but you can use a combination of two shortcuts to lock cells, as mentioned below:

  1. First, select the entire sheet using the keyboard shortcut “Ctrl +A”.
  2. After that press the “Ctrl + 1” keys to open the “Format Cells” dialog box.
  3. Enter the “P” key to go to the “Protection” tab and then press the “Tab + Spacebar” keys together to un-select the “Locked” option and press “Enter”.
    untick-locked-option
  4. Now, select the cells or range of cells that you only want to lock.
  5. Again, press the “Ctrl+1” keys and your “Format Cells” dialog box will get opened.
  6. Enter the “P” key to go to the “Protection” tab and then press the” Tab” key and then the “Spacebar” key to select the “Locked” option and press “Enter”.
    locked-option-selected
  7. In the end, press “Alt → T → P → P” to open the “Protect Sheet” dialog box and enter the password, and press “Enter”.
    shortcut-to-enter-password

Lock Only the Formula Cells in Excel

  1. First, select all the cells and open the “Format Cells” dialog box to un-select the “Locked” option.
    lock-formula-cells
  2. After that, go to the “Home” tab and then click on the “Find & Replace” icon and then click on the “Go To Special” option.
    go-to-special-option
  3. Now, select the “Formulas” option and then click OK.
    select-only-formulas-option
  4. The moment you click OK, all the cells that have formulas within them will get selected.
    selected-cells-with-formulas
  5. At this point, again open the “Format Cells” dialog box and select the “Locked” option to lock only the above-selected cells.
    format-cells-dialog-box-opened
  6. At this end, go to the “Review” tab and click on the “Protect Sheet” icon and then provide the password and other permissions you might want to grant to users, and then click OK.
    password-to-protect-sheet

Now, only the selected cells with the formulas got locked for any editing.

Unlock the Locked Cells in Excel

To unlock the cells, un-protect the sheet, and the cells will get unlocked.

Last Updated: October 24, 2023