How to Lock a Drop-Down List in Excel

To protect a drop-down list in Excel, unlock the cells where people should make a selection, then use Review > Protect Sheet to prevent changes to the list source and other locked cells. If you want to stop people changing a selection after it has been made, leave that drop-down cell locked before protecting the sheet. These are different outcomes: an unlocked, protected cell still lets someone choose from its drop-down, while a locked cell does not.

The steps below are for Excel for Microsoft 365 on Windows. You will need a data-validation drop-down and a worksheet protection password if you want one. Worksheet protection helps prevent accidental or casual edits; it is not a secure way to keep sensitive information confidential.

What You’ll Need

An Excel workbook containing a drop-down list, or the values you want to use to create one.
A clear idea of what should be protected: the choices in the source list, the selected value, or both.
Optional: a password you can safely store if you plan to protect the sheet. Excel cannot recover a forgotten protection password for you.

Step-by-Step

Identify the drop-down cell and its source. Click a cell with a drop-down arrow, then choose Data > Data Validation. On the Settings tab, check that Allow is set to List. The Source box shows where the choices come from. It may contain typed values, such as Red,Green,Blue, or a cell reference such as =$H$2:$H$5. Note the source range before changing protection settings. If the selected cell is not a list validation cell, this guide’s protection steps can still lock it, but first create or locate the actual drop-down.
Decide whether users should be able to make a selection. If users need to select or change an item, the drop-down cell must be unlocked before you protect the worksheet. If the cell already contains a final value and users must not change it, leave it locked. You can also allow users to select an item during data entry, then lock the cell afterward. Decide which cells are inputs and which are fixed results before applying protection.
Unlock cells that should remain editable. Select the drop-down input cell or range. To select several separate cells, hold Ctrl while clicking them. On the Home tab, open the Format Cells dialog using the small dialog launcher in the Number group, or press Ctrl+1. Open the Protection tab, clear Locked, and choose OK. Cells are marked locked by default, but that setting has no effect until sheet protection is turned on. On Mac, select the cells and use Format Cells > Protection; the dialog-launcher location and keyboard shortcut can differ by keyboard settings.
Keep the source choices in protected cells. If the list values are on the same worksheet, leave their source cells locked. If the choices are on another worksheet, that source worksheet can be protected separately. For a list that refers to a range on another sheet, use a defined name for the source range: select the values, choose Formulas > Define Name, give the range a simple name such as ColorChoices, and use =ColorChoices in the validation Source box. This avoids entering a direct cross-sheet reference in the list’s Source field. Keep the source range locked so ordinary users cannot overwrite the choices.
Protect the worksheet. Go to Review > Protect Sheet. In the dialog, enter and confirm an optional password. Review the permitted actions. Keep Select unlocked cells enabled so users can reach editable drop-down cells. You can clear Select locked cells if you do not want users to move the selection into protected cells, although this can make the sheet less convenient to navigate. Leave other permissions, such as formatting cells or inserting rows, cleared unless users genuinely need them. Choose OK and confirm the password if prompted.
Protect the source worksheet too, if it is separate. Go to that sheet and repeat Review > Protect Sheet. Protecting only the sheet with the drop-down does not prevent edits to values on an unprotected source sheet. If users should not be able to reveal or rename hidden source sheets, consider hiding the source sheet and protecting the workbook structure through Review > Protect Workbook. Hiding a sheet is for tidiness, not strong security; users with sufficient access may still be able to inspect workbook contents.
Test the result as a user would. Click an unlocked drop-down cell and choose a different valid entry. Confirm that typing an item not in the list is rejected if the validation error alert is configured to stop invalid entries. Then try editing a locked source cell or a locked final-value cell; Excel should block that edit while protection is active. If you need to adjust the setup, choose Review > Unprotect Sheet, enter the password if requested, make the change, and protect the sheet again.

To configure the validation error alert, unprotect the sheet first if necessary, then select the drop-down cells and open Data > Data Validation. On the Error Alert tab, enable the alert and choose Stop to reject invalid entries. Protection controls whether users can edit cells; validation controls which values can be entered. Using both gives you better control than relying on either setting alone.

Common Mistakes

Protecting the sheet before unlocking input cells. This commonly makes the drop-down appear unusable. Unprotect the sheet, clear Locked for the cells people need to change, then protect it again.
Unlocking the source list instead of the input cells. Users may then be able to alter the choices themselves. Keep the source range locked and unlock only the cells where selections are entered.
Expecting protection to stop every invalid value. Cell protection is not data validation. Make sure the drop-down has a list rule and that its error alert is enabled and set to Stop if invalid entries must be rejected.
Protecting only one sheet. The drop-down sheet can be protected while its source values remain editable on another sheet. Protect each sheet that contains information you need to preserve.
Assuming a password makes the workbook secure. Worksheet protection is designed mainly to prevent accidental edits and limit routine changes. Do not use it as a substitute for access controls or encryption when information is sensitive.

Tips

Use separate cells for inputs and fixed results. Put user selections in unlocked cells and formulas or reference values in locked cells. This makes it easier to protect the parts of a worksheet that should not change without blocking normal data entry.
Use a defined name for a reusable source range. A name such as DepartmentChoices is easier to understand in the validation Source box than a long cell reference. It is also a practical way to refer to a list stored on another worksheet.
Keep a record of protection passwords. Store them somewhere appropriate for your organization. If the password is lost, you may need to recreate the worksheet or restore an earlier copy to change protected settings.
Keep the validation alert useful. On the Input Message tab in Data Validation, you can give users a brief instruction such as “Choose a department from the list.” A clear message reduces mistyped entries and questions.
Check the sheet after making layout changes. Inserting, deleting, or moving source cells can affect a validation reference. Test a few drop-downs after editing the workbook, especially if the source range is maintained manually.
Use a copy for important changes. Before changing protection on a shared workbook, save a copy. That gives you a way back if you accidentally lock the wrong range or remove a needed validation rule.

Troubleshooting

The drop-down arrow is missing or the list will not open. First check whether the cell has list validation: select it and open Data > Data Validation. Confirm that Allow is set to List and that In-cell dropdown is selected. If the sheet is protected, confirm the cell is unlocked and Select unlocked cells is allowed. The arrow is normally shown when you select the cell, not all the time.

I can open the list but cannot change the selected value. The cell is probably locked. Choose Review > Unprotect Sheet, unlock the input cell through Format Cells > Protection, then protect the worksheet again. Check that you did not select a nearby cell or an incomplete portion of the input range.

Users can type over the list choices. Check whether the source cells are unlocked or located on an unprotected sheet. Lock the source cells and protect that sheet. If the source is on a separate sheet, make sure the validation uses the intended named range and that the source values are not duplicated in an editable area.

Invalid values are still accepted. Select the cells, open Data > Data Validation, and check that the rule is a list and that the error alert is enabled with the Stop style. If users paste data into cells, validation may not prevent every way of changing cell contents. Restrict editing to the intended input range and check pasted data as part of your process.

Excel says the sheet is protected, but I need to edit the rule. Unprotect the sheet first. If you do not know the password, contact the workbook owner or use an authorized unprotected backup. Do not assume that renaming or hiding a sheet removes its protection.

The instructions do not match my Excel menus. In Excel for Mac, the protection and validation features are available, though dialog placement and labels can vary slightly by version. In Excel for the web, protection and validation options may be more limited than in the Windows desktop app. If a setting you need is unavailable, open the workbook in Excel for Microsoft 365 desktop, apply the protection there, save it, and then check the result in the web version.

Frequently Asked Questions

Can I lock a drop-down list but still let people choose an option?
Yes. Unlock the cells containing the drop-down, leave the list source locked, and protect the worksheet. Users can make selections in the unlocked cells, while the protected source cells cannot be edited through normal worksheet use.

Can I stop people changing a selected value after they choose it?
Yes. Lock the cell after the value is selected, then protect the worksheet. A locked cell cannot be changed through its drop-down while sheet protection is on. If different people need to select values in other rows, unlock those input cells instead of locking the entire column.

Does Protect Sheet prevent users from choosing an item?
It does if the drop-down cell is locked. Unlock the cell before protecting the sheet and allow selection of unlocked cells in the protection settings. Protection does not automatically make every cell editable or preserve every validation rule; check the specific cells and test them.

Can I protect the list choices when they are on another worksheet?
Yes. Protect the worksheet that contains the source values. For the validation rule, define a name for the source range and enter that name, preceded by an equals sign, in the Source box. Protecting the drop-down sheet alone does not protect a separate source sheet.

Can I protect only the drop-down cells?
Excel’s Protect Sheet command protects the worksheet while allowing selected actions; it does not switch protection on for just one cell. You can unlock the cells users need and leave other cells locked, then protect the sheet. This creates the practical effect of protecting selected areas while permitting input elsewhere.

Is worksheet protection a security feature?
It is useful for preventing accidental edits and controlling ordinary worksheet changes, but it should not be treated as strong security for confidential data. Use appropriate file access controls and encryption for sensitive information, and share only the workbook content each person is authorized to see.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.