# How to Lock Cells in Excel (Step by Step)

To lock cells in Excel, you set the Locked formatting on the cells you want to protect, then turn on sheet protection for the worksheet. The lock only takes effect after you protect the sheet, so the two steps always go together. This guide shows how to lock cells in Excel while leaving the cells you still need to edit open for typing.

## Quick Answer

- Every cell in a new worksheet already has the Locked check box selected, so the real work is unlocking the cells you want people to edit [1].
- Select the cells to protect, press Ctrl+1, open the Protection tab, and check Locked [1].
- Select the input cells, press Ctrl+1, and clear Locked so they stay editable [2].
- Go to Review > Protect Sheet and click OK to turn protection on [1].
- If you set a password, write it down. A lost password means you cannot access the protected parts of the sheet [3].

## Before You Start

Worksheet protection is a two-step process. The first step is to unlock the cells that others can edit, and the second is to protect the worksheet with or without a password [2]. Excel applies the Locked formatting to all cells by default, so if you protect a sheet without changing anything, nothing on it can be edited [1].

That default is the reason so many people think locking failed. They protect the sheet, try to type in a cell, and get blocked everywhere. The fix is to unlock the input cells first, then protect.

Decide which cells fall into each group before you touch anything. Cells that hold formulas, labels, and totals should stay locked. Cells where you or a colleague type new data should be unlocked. If you want to lock a whole sheet except a few cells, it is faster to select the exceptions and unlock them than to lock every other cell one by one.

You can also unlock cells after you apply protection, but Microsoft describes unlocking first as a best practice [1]. Doing it first means you only enter the password once.

## Step by Step

1. Select the cells you want to protect. For a block of formula cells, drag across the range or click the first cell and Shift+click the last.
2. Press Ctrl+1 to open the Format Cells dialog box. On the Home tab, you can also select the Alignment Settings arrow in the Alignment group to open the same window [1].
3. Go to the Protection tab and select the Locked check box. Select OK to close the window [1].
4. Select the cells you want to leave editable, press Ctrl+1, and clear the Locked check box [2].
5. On the Review tab, in the Protect group, select Protect Sheet [1].
6. In the Protect Sheet dialog box, leave the defaults under "Allow all users of this worksheet to" if you want people to select both locked and unlocked cells. By default, users are allowed to select locked cells, and they can press the TAB key to move between unlocked cells [4].
7. Type a password if you want one, then select OK. The password is optional. Without one, any user can unprotect the sheet and change what was protected [3].
8. Test it. Type in an unlocked cell to confirm it accepts input, then try a locked cell and confirm Excel blocks the edit.

If you need to change the sheet later, go to Review > Unprotect Sheet and enter the password. The steps for that are covered in [How to Unlock an Excel Spreadsheet](/blog/data-analysis/how-to-unlock-excel-spreadsheet).

## Worked Example

This gradebook has nine students, a score column, and a result column that calculates Pass or Fail from each score.

|   | A | B | C |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 78 | `=IF(B2>=70,"Pass","Fail")` -> displays Pass |
| 3 | Ben | 65 | `=IF(B3>=70,"Pass","Fail")` -> displays Fail |
| 4 | Cara | 91 | `=IF(B4>=70,"Pass","Fail")` -> displays Pass |
| 5 | Dan | 54 | `=IF(B5>=70,"Pass","Fail")` -> displays Fail |
| 6 | Eve | 82 | `=IF(B6>=70,"Pass","Fail")` -> displays Pass |
| 7 | Finn | 69 | `=IF(B7>=70,"Pass","Fail")` -> displays Fail |
| 8 | Gus | 73 | `=IF(B8>=70,"Pass","Fail")` -> displays Pass |
| 9 | Hana | 88 | `=IF(B9>=70,"Pass","Fail")` -> displays Pass |
| 10 | Ivan | 60 | `=IF(B10>=70,"Pass","Fail")` -> displays Fail |

Column C holds the formulas, and each one checks whether the score in column B meets the pass mark of 70. The formula in C2 checks Ana's score, C3 checks Ben's, and so on down to C10 for Ivan. The results are Pass for Ana, Cara, Eve, Gus, and Hana, and Fail for Ben, Dan, Finn, and Ivan.

The goal is to let someone type new scores in B2:B10 without letting them overwrite the formulas in C2:C10. Here is the sequence.

1. Select C2:C10, then press Ctrl+1 (Format Cells), go to the Protection tab, and check Locked.
2. Select B2:B10, press Ctrl+1 (Format Cells), go to the Protection tab, and uncheck Locked.
3. Go to Review > Protect Sheet, leave the "Allow all users of this worksheet to" defaults, and click OK.
4. Try typing in B2 to confirm it is editable, then try typing in C2 to confirm it is locked.

After protection is on, changing B3 from 65 to 72 makes C3 recalculate and display Pass. The formula still works because the locked cell is protected from editing, not from calculating.

## Other Ways to Do It

The Format Cells route is the most direct, but Excel offers a few other paths to the same Locked check box.

On the Home tab, in the Cells group, select Format, then Format Cells. This opens the same dialog box, and the Protection tab works the same way [5]. This route is handy when you are already working in the Cells group for row and column changes.

For a single cell, you can right-click it and choose Format Cells from the context menu. The Protection tab is identical.

If you are protecting a control such as a check box or a button, the lock lives in a different place. Right-click the control, select Format Control, and use the Protection tab there. If the control has a linked cell, unlock that cell so the control can write to it, then hide the cell so a user cannot cause unexpected problems by modifying the current value [5].

Excel for the web does not expose the full protection workflow. If you want to lock cells or protect specific areas there, select Open in Excel and do the work in the desktop app [1].

## Troubleshooting

**Nothing is locked after I check the Locked box.** The Locked formatting does nothing on its own. You have to protect the sheet on the Review tab before it takes effect [1].

**Everything is locked, including the cells I need to type in.** You protected the sheet without unlocking the input cells first. Unprotect the sheet, clear Locked on the input range, and protect it again [2].

**I can select a locked cell but not type in it.** That is the default behavior. Users are allowed to select locked cells unless you clear the Select locked cells check box in the Protect Sheet dialog box [4].

**I forgot the password.** If you lose the password, you cannot access the protected parts on the sheet [3]. There is no built-in recovery, so keep a record of any password you set.

**The TAB key jumps past my input cells.** By default, users can press the TAB key to move between the unlocked cells on a protected worksheet [4]. If TAB is not landing where you expect, check which cells actually have Locked cleared.

**A formula result changed even though the cell is locked.** Locking prevents edits, not recalculation. A locked formula still updates when its inputs change, which is usually what you want.

## Common Mistakes

- **Protecting the sheet before unlocking the input cells.** Everything gets blocked. Unlock the editable range first, then protect [2].
- **Assuming the Locked check box alone protects a cell.** It only sets the formatting. Sheet protection is what enforces it [1].
- **Locking the linked cell of a form control.** The control cannot write to a locked cell. Unlock the linked cell, then hide it so nobody edits the value directly [5].
- **Setting a password you cannot remember.** If you lose the password, you cannot access the protected parts on the sheet [3]. Write it down and store it somewhere safe.
- **Leaving the Select locked cells option on when you want a clean form.** If you do not want people to select locked cells, clear that check box in the Protect Sheet dialog box [3].
- **Protecting only the worksheet when you also need to protect the structure.** To prevent users from changing the protections on the cells and controls you have set, protect both the worksheet and the workbook [5].

## Limitations

Sheet protection is a guardrail against accidental and casual edits, not a security boundary. Anyone who can open the file can unprotect the sheet if there is no password, and the password itself is not strong encryption. Treat it as a way to keep a shared workbook tidy, not as a way to keep determined users out of your data.

Protection also interacts with other features in ways that surprise people. Conditional formatting you applied before protection continues to change when a user enters a value that satisfies a different condition [2]. Paste behavior has changed across versions, and in current versions paste honors the Format cells option [2]. If you rely on users pasting data into a protected sheet, test that specific action before you share the file.

## Frequently Asked Questions

### How do I lock cells in Excel without protecting the whole sheet?

You cannot. Locking is a two-part mechanism. The Locked check box sets the formatting, and sheet protection is what makes it active [1]. If you want only some cells locked, unlock the rest of the sheet first, then protect it. The unlocked cells stay editable while everything else is blocked.

### How do you lock a cell in Excel so it cannot be edited at all?

Check Locked on the Protection tab of Format Cells, then protect the sheet. If you also want to stop people from even clicking the cell, clear the Select locked cells check box in the Protect Sheet dialog box [4]. That combination makes the cell both uneditable and unselectable.

### How do I lock cells in Excel but still allow sorting and filtering?

The Protect Sheet dialog box lists the actions you can allow, including sorting and using AutoFilter. Select the options you want before you click OK. The defaults allow users to select locked and unlocked cells, and the rest depends on what you check [4]. If sorting is not allowed, Excel blocks it on the protected sheet.

### Can I lock cells in Excel for Mac the same way?

Yes. On the Format menu, click Cells, then click the Protection tab and make sure the Locked check box is selected [3]. To unlock cells, select them, open the same tab, and clear the check box. Then go to the Review tab and click Protect Sheet [3].

### Why does Excel say the cells are already locked?

Because they are. Every cell in a new worksheet has the Locked formatting applied by default, so the cells are ready to be locked the moment you protect the worksheet [1]. That is why the standard workflow is to unlock the input cells and leave the rest alone.

## References

1. [Lock cells to protect them in Excel | Microsoft Support](https://support.microsoft.com/en-us/excel/lock-cells-to-protect-them-in-excel)
2. [Protect a worksheet | Microsoft Support](https://support.microsoft.com/en-us/excel/protect-a-worksheet)
3. [Lock cells to protect them in Excel for Mac | Microsoft Support](https://support.microsoft.com/en-us/excel/lock-cells-to-protect-them-in-excel-for-mac)
4. [Lock or unlock specific areas of a protected worksheet | Microsoft Support](https://support.microsoft.com/en-us/excel/get-started/lock-or-unlock-specific-areas-of-a-protected-worksheet)
5. [Protect controls and linked cells on a worksheet | Microsoft Support](https://support.microsoft.com/en-us/excel/protect-controls-and-linked-cells-on-a-worksheet)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)

## Related Articles

- [How to Unlock an Excel Spreadsheet (Step by Step)](/blog/data-analysis/how-to-unlock-excel-spreadsheet)
- [How to Freeze Rows and Columns in Excel (Step by Step)](/blog/data-analysis/how-to-freeze-panes-in-excel)
- [How to Unhide Rows in Excel (Step by Step)](/blog/data-analysis/how-to-unhide-rows-in-excel)
- [How to Filter in Excel (Step by Step)](/blog/data-analysis/how-to-filter-in-excel)
- [How to Split a Cell in Excel (Step by Step)](/blog/data-analysis/how-to-split-a-cell-in-excel)