To lock cells in Excel, the surprise is that every cell in a new worksheet is already set to Locked, and that setting does nothing until you protect the sheet. So the usual goal, making only some cells editable, works in reverse: select the cells you want people to edit, press Ctrl+1, open the Protection tab, clear the Locked box, then on the Review tab select Protect Sheet. After that, the cells you left locked are read-only and the unlocked ones still accept typing.
If instead you want to keep a cell reference from shifting when you copy a formula down, that is a dollar-sign absolute reference ($A$1, set with the F4 key), not sheet protection, and it is covered in the cell references guide. This page is about the Format Cells Protection tab and Protect Sheet: why a lone lock seems to do nothing, how to unlock just the input cells, what the Protect Sheet dialog offers, how to hide a formula from the formula bar, how to unprotect, and where sheet protection stops being protection at all.
Why does locking a cell seem to do nothing?
Select any cell in a fresh worksheet, press Ctrl+1 to open Format Cells, and the Protection tab already shows the Locked box checked. Yet you can still type over that cell freely. That is expected: Locked is only a flag, and Excel enforces it once the sheet is protected. Microsoft’s note points out the same thing, that before you protect a sheet its cells already carry the lock, just waiting for protection to give it effect.
This is what trips people up. Checking Locked on a cell, or leaving the default in place, changes nothing on its own. The lock bites only after you go to Review > Protect Sheet. So the two settings work as a pair: the Locked flag decides which cells are meant to be read-only, and Protect Sheet is the switch that makes it real.
How do I make only some cells editable?
This is the common case: a form or a model where people should be able to type in a few input cells but not touch the labels or formulas around them. Because every cell starts out locked, you do not lock the cells you want to guard, you unlock the ones you want to open. Microsoft frames it as a sequence: “Worksheet protection is a two-step process: the first step is to unlock cells that others can edit, and then you can protect the worksheet with or without a password.”
Select the cells that others should be able to edit, right-click and choose Format Cells (or press Ctrl+1, Command+1 on a Mac), open the Protection tab, and clear the Locked box. You can pick several non-adjacent cells at once by holding Ctrl while you click. Then on the Review tab, in the Protect group, select Protect Sheet. Everything you did not unlock stays locked, so only the input cells accept typing.
| A | B | |
|---|---|---|
| 1 | Field | Your entry |
| 2 | Employee | Dana Reyes |
| 3 | Date | Jul 2 |
| 4 | Amount | 48.00 |
If it is the other way around, most cells should stay open and only a few need locking, the mirror route is quicker: select the whole sheet with Select All, clear Locked for everything, then select just the cells you want to guard and switch Locked back on before you protect the sheet.
What is in the Protect Sheet dialog?
The Protect Sheet dialog opens with Protect worksheet and contents of locked cells already checked at the top, which is the setting you want. Below it are two things to set.
First, the Password to unprotect sheet box. It is optional. Leave it empty and anyone can turn protection off in a couple of clicks; set a password and it has to be entered before the sheet can be unprotected. Excel does not treat this as a strong secret, but it does stop a casual reader from switching your protection off by accident.
Second, the Allow all users of this worksheet to list. Each checkbox is an action people are still allowed to take on the protected sheet, so you decide how locked-down it feels. Select locked cells and Select unlocked cells are on by default; the rest, such as Format cells, Sort, Use AutoFilter, and the insert and delete options for rows and columns, start unchecked. Leave a box unchecked and that action is blocked. Two of them carry a catch the checkbox does not fully lift: even with Sort ticked, a range that contains locked cells still cannot be sorted on a protected sheet; and even with Use AutoFilter ticked, no one can add or remove an AutoFilter on a protected sheet, that box only lets people work the filter arrows already in place. In Excel for the web these permission options live under Review > Manage Protection rather than a single Protect Sheet dialog.
How do I hide a formula from the formula bar?
A locked formula cell still shows its formula in the formula bar when someone clicks it. To hide the formula itself, the Protection tab has a second checkbox next to Locked: Hidden. Select the formula cells, press Ctrl+1, open the Protection tab, check the Hidden box, and select OK, then protect the sheet with Review > Protect Sheet.
Once the sheet is protected, those cells still show their result in the grid, but the formula bar is blank when you select them. Like Locked, the Hidden flag does nothing until the sheet is protected, and Microsoft notes it also keeps the cells that hold the formula from being edited. To reveal the formulas again later, unprotect the sheet and clear the Hidden box.
How do I unprotect the sheet?
On the Review tab, in the Protect group, select Unprotect Sheet. If you set a password when you protected the sheet, Excel asks for it first. Once the sheet is unprotected, every cell is editable again, and the Protect Sheet button reappears where Unprotect Sheet was, so the same button toggles the two states.
One caution: if you set a password and forget it, Microsoft cannot retrieve it for you, and you lose access to the protected sheet. Keep the password somewhere you can find it before you rely on it.
Is a protected sheet actually secure?
It helps to be clear-eyed here, because a password field looks reassuring. Microsoft is direct about the limit: “Worksheet level protection is not intended as a security feature.” Protection stops accidental edits to the cells you locked, but anyone with the file can select Unprotect Sheet, and if you left the password off, protection comes right back off. Even with a password, you should not assume a workbook or worksheet is secure just because it is protected.
The genuinely secure option is a different feature. A file-level password to open encrypts the whole workbook, so it cannot be opened at all without the password, which is not the same thing as protecting a sheet inside a file you can already open. Reach for sheet protection to keep collaborators from overwriting your formulas by mistake, and for file encryption when the contents themselves are sensitive.
The short version
- Locked is the default: every cell in a new sheet is already set to Locked, but that flag does nothing until you protect the sheet.
- Make only some cells editable: select those cells, press Ctrl+1, on the Protection tab clear Locked, then Review > Protect Sheet. Everything you did not unlock stays read-only.
- Protect Sheet dialog: an optional Password to unprotect sheet, plus the Allow all users of this worksheet to list of actions people may still take, such as select cells, format, sort, or filter.
- Hide a formula: Ctrl+1, then Protection > Hidden, then protect the sheet; the result still shows but the formula bar is blank.
- Unprotect: Review > Unprotect Sheet, and enter the password if you set one.
- Not security: protection prevents accidental edits, not a determined user; a file-level password to open is the encrypted option.
- For keeping a reference from shifting when you copy a formula, see cell references, a different meaning of “lock”.
