Protect a Sheet and Lock Cells in Excel

To lock cells in Excel, you need two steps: decide which cells stay editable by unlocking them, then protect the sheet. The surprise for most people is that every cell is already locked, but the lock does nothing until protection is on. This guide shows the full process on Windows and Mac, how to hide formulas, and what sheet protection can and can't do.

How locking and protection work together#

Each cell has a Locked setting, and it is on for every cell in a new workbook. On its own it changes nothing: you can still type anywhere. Once you turn on Protect Sheet, Excel blocks changes to every cell marked Locked, and lets people edit only the cells you unlocked.

So the job is always:

1. Unlock the cells people should be able to change, such as inputs.
2. Protect the sheet.

Here is an order form. Staff should enter quantities in column B, but the prices and formulas must not change.

Step 1: Unlock the input cells#

1. Select the cells people should edit, here B2:B5. Our guide to selecting ranges fast shows keyboard ways to do this.
2. Press Ctrl+1 on Windows or ⌘1 on a Mac to open Format Cells. (You can also right-click and choose Format Cells.)
3. Click the Protection tab.
4. Untick Locked and click OK.

Nothing visible changes yet. That's normal.

Step 2: Protect the sheet#

1. Go to Review > Protect Sheet.
2. Type a password if you want one. It's optional: without a password, anyone can click Unprotect Sheet, but the protection still stops accidental changes.
3. In the list, choose what people may still do. Select locked cells and Select unlocked cells are ticked by default. You can also allow actions such as Format cells, Insert rows, Sort and Use AutoFilter.
4. Click OK, and confirm the password if you set one.

Now try typing in D2: Excel shows a message that the cell is on a protected sheet. Typing in B2 still works, and the totals update.

A useful side effect: on a protected sheet, Tab moves between unlocked cells only, so people filling in a form jump straight from input to input.

Lock only the formulas#

On a big sheet, unlocking inputs one block at a time is slow. It's often quicker to unlock everything, then lock only the formulas:

1. Select the whole sheet with Ctrl+A (Mac: ⌘A), press Ctrl+1 (Mac: ⌘1) and untick Locked.
2. Select only the formula cells. Go To Special does this in one step: press F5, click Special, choose Formulas and click OK. See our guide to Go To Special for the details.
3. Press Ctrl+1 (Mac: ⌘1) again and tick Locked.
4. Protect the sheet.

Hide formulas from the formula bar#

To stop people seeing how a result is calculated, tick Hidden as well as Locked on the Protection tab, then protect the sheet. The cell still shows its result, but the formula bar stays empty when the cell is selected.

Hiding formulas doesn't make them secret. Anyone who unprotects the sheet sees them again, so use it to keep a sheet tidy, not to protect sensitive logic.

Let some people edit some ranges#

On Windows, Review > Allow Edit Ranges lets you give a range its own password while the rest of the sheet stays locked. For example, the sales team can edit their targets with one password, while only the finance team can change the prices. Set up the ranges first, then protect the sheet; the ranges have no effect until protection is on.

For most simple forms, unlocking the input cells is enough. Use Allow Edit Ranges only when different people need to edit different parts.

How to unprotect a sheet#

Go to Review > Unprotect Sheet. If you set a password, Excel asks for it. Make your changes, then protect the sheet again.

Microsoft can't recover a lost sheet protection password. Store it somewhere safe, such as your organisation's password manager, and share it with whoever maintains the file.

Protect the workbook structure#

Sheet protection stops changes to cells. It doesn't stop someone deleting, renaming or moving a whole sheet. For that, use Review > Protect Workbook, which protects the workbook's structure: no adding, deleting, renaming, moving, hiding or unhiding sheets.

This is also how you stop people from simply unhiding a sheet you hid. Our guide to hiding and unhiding rows and columns covers hiding in more detail.

What sheet protection does not do#

Sheet protection is designed to prevent accidental edits, not to keep data private. Know its limits:

• It doesn't encrypt the file. Anyone who can open the file can read every value. To keep a file private, use File > Info > Protect Workbook > Encrypt with Password on Windows. That password is needed to open the file at all.
• It doesn't stop copying. People can copy cells they can select and paste them into another workbook.
• Some features stop working. On a protected sheet, people can't insert rows, sort or filter unless you allowed it in the Protect Sheet dialog.

If something seems broken on a shared file, check whether the sheet is protected before anything else. The Review tab shows Unprotect Sheet when protection is on. To undo an accidental edit on an unprotected sheet, see edit, undo and redo without the mouse, and to move around a form quickly, our guide to navigating Excel with the keyboard helps.

Practise it#

Try the exercise on this page to build the locked total for the order form. Then practise editing, clearing and undoing changes in a live spreadsheet, the everyday skills you need before you lock a sheet for others.