Skip to content
AnIntent

How to Lock Only Some Cells in Excel (Unlock Inputs, Then Protect)

Unlock the input cells, lock the formula cells, then protect the sheet so others can type in Excel without breaking your formulas.

AnIntent Editorial · · 4 min read

Person working on a laptop with a spreadsheet outdoors
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. Why every cell starts out locked
  2. Steps to lock only your formula cells
  3. Sorting stops working on the protected sheet
  4. Letting specific people edit specific ranges
  5. Protect Sheet and Protect Workbook do different jobs
  6. Keep the password somewhere safe
  7. Mac and Excel for the web
Share this article

To lock cells in Excel so other people can fill in the input cells but can't touch your formulas, do it in two passes. First unlock the cells others should edit. Then protect the sheet. The order matters because of how Excel treats cells by default.

Why every cell starts out locked

In Excel, every cell is marked Locked by default, but that setting does nothing until you protect the sheet. Microsoft's page on locking specific areas of a protected worksheet puts it plainly: by default, protecting a worksheet locks all cells, so none of them are editable. Microsoft's Protect a worksheet page describes the same two-step process: unlock the cells others can edit, then protect the sheet, with or without a password.

If you skip the unlock step and go straight to Protect Sheet, the whole sheet becomes read-only. That is the most common mistake.

Steps to lock only your formula cells

These steps come from Microsoft's support page, which applies to Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on the desktop.

  1. If the sheet is already protected, go to Review > Unprotect Sheet (in the Protect group). Enter the password if prompted.
  2. Click Select All.
  3. On the Home tab, open the Font Settings launcher, or press Ctrl+Shift+F or Ctrl+1.
  4. In Format Cells, open the Protection tab, uncheck Locked, and click OK. Every cell is now unlocked.
  5. Select only the cells you want locked, such as your formulas.
  6. Reopen Format Cells with Ctrl+Shift+F.
  7. On the Protection tab, check Locked and click OK.
  8. Go to Review > Protect Sheet.
  9. In the Allow all users of this worksheet to list, choose what users may change, add a password if you want one, and confirm.

In that list, Microsoft's table shows Select locked cells allowed by default, alongside Select unlocked cells. If you don't want people clicking into your formulas at all, untick Select locked cells.

The Protect a worksheet page adds a handy detail: by default, users can select unlocked cells and press Tab to move between them. That makes a protected sheet behave like a simple form.

If your formulas are lookups, such as the ones in How to Use INDEX and MATCH in Excel, locking them stops someone from overwriting a result with a typed value.

Sorting stops working on the protected sheet

This catches a lot of people. According to Microsoft, users can't sort ranges that contain locked cells on a protected sheet, and that holds regardless of the sort setting in the Protect Sheet list.

So if a table mixes locked formula columns with unlocked input columns, ticking the sort option won't help. Your choices are to unprotect, sort, and protect again, or to keep the range people need to sort made up of unlocked cells only.

Letting specific people edit specific ranges

Excel also has Review > Allow Edit Ranges, listed on Microsoft's restrict changes page next to Protect Sheet and Protect Workbook. It lets you open certain ranges to certain users while the rest stays locked.

A few limits, from Microsoft's page on protected worksheet areas:

  • Granting specific users edit rights requires a computer running Windows XP or later that is on a domain.
  • Allow Edit Ranges is available only when the sheet is not protected. If the button looks greyed out, unprotect the sheet first.
  • You finish by choosing Protect Sheet in the Allow Users to Edit Ranges dialog.
  • If a cell belongs to more than one range, a user authorised for any of those ranges can edit it.

Protect Sheet and Protect Workbook do different jobs

On the Review tab, Protect Sheet controls what can be changed inside one worksheet. Protect Workbook protects the workbook's structure and windows, per Microsoft. More options sit under File > Info > Protect Workbook. Locking formula cells is a Protect Sheet job.

Keep the password somewhere safe

Microsoft's Protect a worksheet page is direct: "If you forget your password, Microsoft cannot retrieve it." Store it in a password manager before you share the file. Microsoft also notes that sheet protection only prevents users from modifying locked cells, so treat it as a guard against accidental edits rather than a way to keep data private.

Mac and Excel for the web

The Microsoft steps above are for desktop Excel on Windows. Menu names on Excel for Mac and Excel for the web may differ, so check the Review tab in your version if a command isn't where these steps say.

Common questions

Why is my whole sheet read-only after I protected it?

All cells are Locked by default. Unprotect the sheet, select all, uncheck Locked on the Protection tab of Format Cells, re-lock only the formula cells, then protect again.

Why is Allow Edit Ranges greyed out?

According to Microsoft, Allow Edit Ranges is available only when the sheet is not protected. Use Review > Unprotect Sheet first.

Can I sort data on a protected sheet?

Not if the range contains locked cells. Microsoft says users can't sort such ranges on a protected sheet regardless of the sort setting.

I forgot my sheet password. Can Microsoft unlock it?

No. Microsoft's support page says that if you forget your password, Microsoft cannot retrieve it.

What is the difference between Protect Sheet and Protect Workbook?

Protect Sheet controls edits inside a worksheet. Protect Workbook protects the workbook's structure and windows.

Person working on a laptop with a spreadsheet outdoors
How-to

How to Transpose in Excel for Mac (and Fix Greyed-Out Transpose)

Flip rows to columns in Excel for Mac with Paste > Transpose, and learn why Cut fails and why Transpose is greyed out for tables.

· 3 min

More in Cloud & Productivity Software