How To Create A Drop-Down List In Excel (2026)

A drop-down list lets people pick an entry from a menu instead of typing it, which keeps spelling and wording consistent.

In Excel you build one with Data Validation, using a list you type on a sheet or a list you type straight into the rule.

The steps below follow Microsoft’s support pages, including how to edit or remove the list later and why the command is sometimes greyed out.

Quick answer: make a drop-down list

What you wantWhere to go
Create the listData > Data Validation > Settings > Allow: List
Point to your entriesClick in the Source box and select the list range
Add or remove itemsEdit the source list, or edit the Source box for a typed list
Remove the drop-downData > Data Validation > Clear All
Command greyed outUnprotect the sheet or stop sharing the workbook

Prepare your list of entries

Type the entries you want in the menu into a column on a worksheet.

Microsoft recommends turning the list into an Excel table by selecting a cell in the range and pressing Ctrl+T.

A drop-down built on a table updates automatically when you add or remove items.

Microsoft also recommends sorting the list.

Create the drop-down list step by step

  1. Select the cell where you want the drop-down.
  2. Go to the Data tab and select Data Validation.
  3. On the Settings tab, in the Allow box, select List.
  4. Click in the Source box and select your list range, leaving out the header row.
  5. Tick Ignore blank if people can leave the cell empty.
  6. Tick In-cell dropdown.
  7. Select OK.

Microsoft’s example uses the range A2:A9 on a sheet named Cities as the source.

What each setting does

SettingWhereWhat it does
Allow: ListSettings tabTurns the cell into a menu of choices
SourceSettings tabThe range that holds the entries
Ignore blankSettings tabLets people leave the cell empty
In-cell dropdownSettings tabShows the arrow beside the cell
Show input message when cell is selectedInput Message tabShows a prompt you write, up to 225 characters
Show error alert after invalid data is enteredError Alert tabShows a message when someone types a value that is not in the list

Choose what happens when someone types a wrong entry

On the Error Alert tab, pick a Style and write your own title and message.

StyleEffect
StopBlocks entries that are not in the list
WarningShows a warning icon but does not block other entries
InformationShows an information icon but does not block other entries

If you leave the title or text blank, Excel uses Microsoft Excel as the title and a default message saying the value is not valid.

Add or remove items later

First select a cell with the drop-down, go to Data > Data Validation, and look at the Source box on the Settings tab to see how the list was built.

List typeHow to add an itemHow to remove an item
Table columnType the new item at the end of the list and Excel updates the drop-downSelect the cell and press Delete
Named rangeType at the end, then update the range under Formulas > Name Manager in the Refers to boxDelete the item, then update the named range the same way
Range of cellsType at the end, then reselect the cells in the Source boxRight-click the cell, choose Delete, then OK to shift cells up
Typed listEdit the Source box and separate items with commas, no spaces, such as Yes,No,MaybeDelete the item from the Source box

For a range of cells, you can tick Apply these changes to all other cells with the same settings to update every cell that uses the drop-down.

Use a list from another sheet

If the entries live on a different sheet, Microsoft suggests naming the range with Define Name on the Formulas tab, in the Defined Names group.

You can then hide that sheet and protect the workbook so users cannot get at the list.

Microsoft also suggests hiding and protecting the list sheet if you want to keep people from changing it.

Remove a drop-down list

  1. Select the cell with the drop-down, or hold Ctrl and click several cells.
  2. Click Data, then Data Validation.
  3. On the Settings tab, click Clear All.
  4. Click OK.

If you do not know where the drop-downs are, press Ctrl+G, click Special, choose Data Validation, and select All or Same, then repeat the steps above.

If Data Validation is greyed out

Microsoft says the command may be unavailable because the sheet is protected or the workbook is shared.

Unprotect the sheet or stop sharing the workbook, then try again.

For a drop-down in a protected sheet, set up the validation first, then protect the sheet, and unlock the validated cells or users will not be able to choose in them.

See our guide to fixing an Excel file locked for editing if the workbook is open as read-only.

Pasting can bypass the list

Microsoft says validation messages appear only when someone types directly into a cell.

Copied or filled data does not trigger them.

To block fill and drag-and-drop, clear Enable fill handle and cell drag-and-drop under File > Options > Advanced > Editing options, then protect the worksheet.

Find cells that already have validation

On the Home tab, in the Editing group, click Find & Select, then Data Validation.

To check existing entries against a rule, select the cells and go to Data > Data Tools > Data Validation > Circle Invalid Data.

The circles disappear automatically when an entry is corrected, and the feature also works on cells that held data before validation was applied.

Other rules you can set with Data Validation

The Allow box offers more than List.

Allow optionUse it to
Whole NumberAccept whole numbers that meet a condition
DecimalAccept decimal numbers that meet a condition
DateAccept dates that meet a condition
TimeAccept times that meet a condition
Text LengthLimit how long an entry can be
CustomUse a formula as the rule

To keep a header row in view while you pick from the list, see how to freeze rows and columns in Excel.

To stop others changing the rest of the sheet, see our guide to password protecting a Word or Excel file.

If a workbook locks up while you edit it, read fixing Excel not responding or freezing.

Sources

Microsoft Support: Create a drop-down list.

Microsoft Support: Add or remove items from a drop-down list.

Microsoft Support: Remove a drop-down list.

Microsoft Support: Apply data validation to cells.

Microsoft Support: More on data validation.

Leave a Reply

Your email address will not be published. Required fields are marked *