To let people select only editable cells in Excel for Microsoft 365 on Windows, unlock the cells they may change, then go to Review > Protect Sheet and leave Select unlocked cells checked while clearing Select locked cells. This protects the rest of the worksheet and keeps users from selecting locked cells with the mouse or keyboard.
What “Select Unlocked Cells” Means
Excel distinguishes between a cell’s locked status and whether a user can select it after worksheet protection is turned on. By default, cells are marked as locked, but that setting has no effect until you protect the sheet. Once protection is active, the options in the Protect Sheet dialog determine which cells users may select and which actions they may perform.
Select unlocked cells allows users to move to cells that are unlocked. It does not itself make a cell editable: the cell must also be unlocked, and the sheet must be protected. Conversely, Select locked cells allows users to select cells that remain locked, but it does not grant permission to change their contents. Clearing this option is useful when you want a simple form or template with a clear set of input fields.
The feature is often described as “protect sheet, select unlocked cells,” but it involves two separate steps: assigning the unlocked status to input cells and then applying worksheet protection with the appropriate selection permissions.
Unlock the Cells Users May Edit
New Excel cells are generally locked by default. Before protecting the sheet, select the cells intended for data entry and turn off their Locked setting. For example, if a weekly report has labels and formulas in columns A and C, but users enter quantities in B2:B20, unlock B2:B20 while leaving the formulas and headings locked.
- Select the intended input cells. To select a range such as B2:B20, click the Name Box to the left of the formula bar, type
B2:B20, and press Enter. - Open Home > Format > Format Cells. You can also press Ctrl+1 on Windows.
- In the Format Cells dialog, select the Protection tab, clear Locked, and select OK.
- Repeat for any other input ranges that users should be able to change.
Clearing Locked does not visibly change the cells, and it does not protect anything on its own. It only marks them as editable when sheet protection is later enabled. On Excel for Mac, open Format Cells with Command+1; the dialog also has a Protection tab. Ribbon labels or dialog layout can vary slightly between releases.
Protect the Sheet and Set Selection Permissions
After unlocking the input cells, configure protection. In Excel for Microsoft 365 on Windows, open the Review tab and choose Protect Sheet. The Protect Sheet dialog includes a list of actions that users may perform on the protected worksheet. The two selection choices are separate from permissions such as formatting cells or sorting data.
- In the dialog, keep Protect worksheet and contents of locked cells selected.
- Optionally enter a password. A password is not required to turn on protection.
- In the permissions list, leave Select unlocked cells checked.
- Clear Select locked cells if users should not be able to select locked cells.
- Review any other actions users need, then select OK. If you entered a password, Excel asks you to confirm it.
The result is that users can select and edit unlocked cells, while locked cells cannot be selected through ordinary worksheet navigation. If you leave both selection options checked, users can select both kinds of cells, but they still cannot edit locked cells unless another permission or configuration permits it. To stop accidental edits, it is the combination of unlocked input cells and protected locked cells that matters.
Excel for Mac also provides worksheet protection through the Review tab, though the dialog’s appearance may differ. Excel for the web supports worksheet protection, but the available controls can differ from the Windows desktop dialog. If you need to control both selection checkboxes precisely, set up and verify the protection in the desktop version.
Choose Other Permissions Carefully
The Protect Sheet dialog offers additional permissions, such as formatting cells, inserting rows, deleting columns, using AutoFilter, or sorting. These permissions do not replace the selection settings. Choose only the actions that fit the sheet’s purpose. For instance, a data-entry form might allow users to select unlocked cells and use an existing filter, but not format cells, insert rows, or delete columns.
Consider a stock-count sheet with locked product names in A2:A50, unlocked count fields in B2:B50, and locked totals in C2:C50. Users can enter counts in column B. If they also need to filter product rows, enable the relevant filter permission and set up the filter before protecting the sheet. Protection does not automatically create a filter or decide which operations are appropriate for your process.
Test the choices using a realistic user workflow. If people must copy values from locked labels, preventing selection of locked cells may make that task inconvenient. If they only need to fill in designated boxes, allowing selection of unlocked cells alone can make the intended path clearer.
Check That Protection Works as Intended
After setting protection, test both the allowed and blocked behavior. Click an input cell: it should be selectable and accept an entry. Then try to select a locked heading or formula cell. If you cleared Select locked cells, Excel should prevent that selection while the sheet remains protected. Try navigating with Tab as well, since users may move between input fields without using the mouse.
For a more reliable check, test with a copy of the workbook and enter sample data into each kind of cell. Confirm that formulas still calculate and that the unlocked fields are easy to find. A fill color, border, or label such as “Enter quantity” can help, but visual formatting is only a cue; it does not control edit permissions. The actual permissions come from the Locked setting and worksheet protection.
Protection is applied to the worksheet, not just to the selected range. If a cell was accidentally left unlocked, users may be able to edit it after protection is on. Review the input ranges before sharing the file, especially if the worksheet contains formulas, instructions, or fixed values that should not change.
Common Problems and Fixes
- Users can still edit a cell they should not change: Unprotect the sheet, check the cell’s Locked setting through Format Cells > Protection, and protect the sheet again. Remember that Locked status only takes effect while the sheet is protected.
- Users cannot edit an input cell: Check that the cell is actually unlocked and that Select unlocked cells is enabled. Also confirm the sheet is not protected in a way that prevents the required action.
- Users can select locked cells: Open Review > Unprotect Sheet, then protect it again with Select locked cells cleared. If a password was set, Excel requests it when you unprotect the sheet.
- The Protect Sheet command asks for a password: The sheet is already protected. You need the password to unprotect it and change the selection permissions; Excel does not provide a built-in way to retrieve a forgotten sheet-protection password.
- Users need to sort or filter but cannot: Select the necessary permissions in the Protect Sheet dialog and make sure the sort or filter is configured appropriately. Selection permission alone does not grant those actions.
When changing a protected workbook, keep an unprotected backup if you are authorized to do so. Do not assume that protection is a secure way to conceal sensitive information: worksheet protection is designed mainly to prevent or discourage changes, not to encrypt workbook contents. Use file-level encryption and appropriate sharing controls for confidential data.
Sheet Protection Versus Workbook Protection
Protect Sheet applies to one worksheet’s cells and allowed actions. Protect Workbook, by contrast, can protect the workbook’s structure, such as preventing users from adding, deleting, moving, hiding, or renaming sheets. These controls solve different problems. If users should enter data in specific cells but must not alter formulas, protect the sheet. If they must not rearrange worksheets, consider workbook-structure protection as well.
Neither control is a substitute for a password that encrypts the file. A worksheet password is primarily a barrier against ordinary editing in Excel; it should not be treated as a guarantee that someone cannot access or recover the underlying data. For sensitive workbooks, use Excel’s file encryption options and limit access to trusted people.
When to Use Select Unlocked Cells
This setting is most useful for structured worksheets where users have a defined job, such as filling in a request form, entering monthly figures, or updating a small set of status fields. It reduces accidental clicks into formulas and fixed headings, and makes keyboard navigation more focused. It is also helpful when a sheet will be reused as a template and its layout should remain stable.
It may be less suitable for worksheets where users need to inspect or copy locked information, select whole ranges for analysis, or work flexibly across the sheet. In those cases, leave Select locked cells enabled while keeping locked cells protected from editing. That preserves the ability to select and copy information without allowing routine edits to protected cells.
Frequently Asked Questions
How do I allow users to select only unlocked cells in Excel?
Unlock the intended input cells through Format Cells > Protection, then choose Review > Protect Sheet. Keep Select unlocked cells checked and clear Select locked cells.
Can users edit unlocked cells without protecting the sheet?
Yes, but the Locked setting does not restrict editing until sheet protection is on. If the sheet is unprotected, users can generally edit both locked and unlocked cells.
Does protecting a sheet make the workbook secure?
No. Sheet protection is intended to limit changes to worksheet content and actions, not to encrypt the file or conceal confidential data. Use file encryption and controlled sharing for sensitive information.
Why is Select Unlocked Cells grayed out?
The sheet may already be protected, or the dialog may not be in the state needed to change permissions. Choose Review > Unprotect Sheet first; if prompted, provide the password, then protect the sheet again with the desired options.
Can I protect a sheet but still let people copy locked cells?
Yes. Leave Select locked cells checked while protecting the sheet. Users can select locked cells, but the protection still prevents ordinary editing of those cells.
Related
- Vertex42 Debt Snowball Spreadsheet: A Practical Guide
- Vertex42 Budget Spreadsheet: How to Get and Use It
- Vertex Debt Snowball Spreadsheet: How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Power Query: What It Contains and How It WorksPower Query contains data connectors, a visual editor for importing and transforming data, and the M language that…
- Excel Power Query Not Responding? Fixes That WorkIf Excel Power Query is not responding, wait briefly to see whether a large refresh is still running,…
- Excel Shortcut for Filtering a Table: Keys and TipsIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for the…
- How to Delete a Page in Word With a HeaderTo delete a page in Word that has a header, remove the page’s body text or the break…
- How to Create a Checklist Template in WordTo create a reusable checklist in Microsoft Word, add your task labels and checkbox controls, format the page,…
- How to Check Archived Emails in Outlook and Find MailTo check archived emails in Outlook, open the Archive folder in the mailbox where you archived them, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.