How to Lock Cells in Excel (Protect Formulas, Allow Typing)

You've built a form, a timesheet or a budget template. Other people need to type into some cells, but you don't want them overwriting your formulas. Locking cells lets you protect the important parts and leave the rest open.

Every cell in Excel is locked by default. But locking does nothing until you protect the sheet.

So the process is backwards to what you'd expect:

1. Unlock the cells people should be able to edit.
2. Protect the sheet.

Everything you didn't unlock is now locked.

1. Select the input cells, the ones people should be able to type in. Hold Ctrl to select cells in different places.
2. Press Ctrl + 1 to open Format Cells (on a Mac, Command + 1).
3. Click the Protection tab.
4. Untick Locked.
5. Click OK.

Tip: give the unlocked cells a light fill colour so people can see where to type.

1. Go to the Review tab.
2. Click Protect Sheet.
3. Optionally, type a password. Without one, anyone can unprotect the sheet with a click, but it still prevents accidents.
4. Under Allow all users of this worksheet to, tick what people can still do. Select unlocked cells should be ticked. You might also allow Format columns, Sort or Use AutoFilter.
5. Click OK (and retype the password if you set one).

Now try typing in a formula cell. Excel shows a message saying the cell is protected. The unlocked cells work as normal.

⚠️ Don't forget your password. Microsoft can't recover it for you.

To protect every formula on a sheet without selecting them one by one:

1. Press Ctrl + A to select the whole sheet, open Ctrl + 1 > Protection, and untick Locked. Click OK. Everything is now unlocked.
2. Go to Home > Find & Select > Go To Special, choose Formulas and click OK. Excel selects every formula cell.
3. Press Ctrl + 1 > Protection, tick Locked and click OK.
4. Go to Review > Protect Sheet.

If you don't want people to see how a formula works, tick Hidden as well as Locked on the Protection tab before protecting the sheet. The result still shows in the cell, but the formula bar stays empty.

On a protected sheet, press Tab to jump straight to the next unlocked cell, skipping everything that's locked. It's perfect for forms.

Go to Review > Unprotect Sheet and enter the password if there is one.

Review > Protect Workbook is different. It stops people adding, deleting, renaming, moving or unhiding sheets, but it doesn't lock any cells. Use both together for a fully locked-down template.

Sheet protection is designed to prevent accidents, not to keep secrets. Anyone determined can get around it. To stop people opening the file at all, use File > Info > Protect Workbook > Encrypt with Password.

I protected the sheet but can still edit everything. You probably unlocked the whole sheet and forgot to re-lock the formula cells. Unprotect, check Locked on those cells, and protect again.

I can't sort or filter on the protected sheet. Unprotect and protect again, ticking Sort and Use AutoFilter. Sorting only works if the cells being sorted are unlocked.

Protect Sheet is greyed out. Several sheets are grouped together. Right-click a sheet tab and choose Ungroup Sheets.

Unlock the cells people should use, then Review > Protect Sheet. That one habit will save your templates from accidental damage.