How to Create a Drop-Down List in Excel

Published October 18, 2026 · By Ahmad Parvez

Short answer: select the cells, go to the Data tab, click Data Validation, choose List under Allow, and type your items separated by commas in the Source box (or point the Source at a cell range). Click OK — to use the drop-down list, just click any of those cells and pick from the menu.

People phrase this task a dozen ways — create a drop-down list, add a drop-down, make a drop-down, insert a drop-down menu or drop-down box — but it is always the same feature: Data Validation with the type set to List. This guide covers the whole thing: creating lists, Yes/No menus, dependent lists, changing and removing them, and the edge cases most tutorials skip.

Once you have the steps, creating, making, or building a drop-down menu, box, or list takes under a minute — whether you want to do a simple drop-down in one cell or set one up for a whole column.

How to create a drop-down list in Excel, step by step

The fastest way to add a drop-down list — for a Yes/No choice, a status column, a list of departments, or anything short — is to enter the items straight into the Data Validation dialog:

Select the cell or cells that should get the drop-down. To make a single cell a drop-down, select just that cell; to make a whole column a drop-down, click the column letter first so the rule applies to every row.
Go to the Data tab on the ribbon and click Data Validation (in Excel for the web, the same Data > Data Validation path works).
In the Allow box on the Settings tab, choose List.
In the Source box, enter your items separated by commas, for example: Pending,In Progress,Done. Do not add spaces after the commas unless you want the spaces in the choices.
Make sure In-cell dropdown is checked, then click OK.
Click any of the cells — the drop-down arrow appears; make your selection from the menu.

Regional separator note: in some countries (including much of Europe), the list separator is a semicolon rather than a comma. If your typed list shows as a single garbled entry, try Pending;In Progress;Done instead. This follows your Windows regional settings, not your Excel language.

Typing items into the Source box creates a custom list of allowed values — perfect for a few short items that will never change. Every item you add becomes a selectable drop-down option, so the cell's selection is then limited to exactly those choices.

Typed lists have one real limit: the Source box holds only about 255 characters in total. For a handful of short options that is plenty, but a long list of product names or cities will silently get cut off. When your list is long or likely to change, use a cell range as the source instead — covered next.

Use a cell range as the source (the better way)

To create, make, or set up a drop-down list you maintain over time (sometimes called a pick list), put your options in cells — say Z1:Z20 on a spare sheet — and point the Data Validation Source at that range (=$Z$1:$Z$20):

Updating is automatic. To update the drop-down later — adding, changing, modifying, or removing its options — just edit the range cells; every drop-down list using that range follows without reopening the dialog.
Long lists work. A range source has no practical length limit, unlike the 255-character typed box.
It survives typos. Options spelled once in a range are spelled the same everywhere, so filters and COUNTIF summaries stop splitting "In Progress" from "In Progress".

Tip: give the range a name (select it, type a name like StatusList into the name box left of the formula bar) and use =StatusList as the Source. Named ranges are easier to read in the dialog and make dependent drop-downs possible.

How to remove a drop-down list in Excel

To get rid of a drop-down list (or drop-down menu) — which is the same thing as removing its data validation rule or turning data validation off for those cells:

Select the cells that have the drop-down.
Go to the Data tab and click Data Validation.
Click Clear All at the bottom of the dialog to clear the drop-down, then OK.

Two things to know: Clear All removes every validation setting on those cells, not just the list, and it does not delete the values already in the cells — they stay put. It is also the fastest way to clear data validation and remove data validation restrictions (input messages, error alerts) from cells in one step. If you want to change the data validation instead of deleting it, reopen the dialog and adjust the drop-down list's settings rather than clearing. If the arrows are gone from the whole column but reappear when you open the dialog on one cell, you may have missed some cells: re-select the full column, tick Apply these changes to all other cells with the same settings, and Clear All again.

Hide the arrow, keep the rule: if you want the list to keep validating entries but not show arrows, open Data Validation, go to the Settings tab, and uncheck In-cell dropdown. Typing is still restricted to your list; users just do not get the clickable menu. This is also how to get rid of drop-down arrows without removing the rule.

How to make a Yes/No drop-down (and add color)

A Yes/No menu is the single most common drop-down in Excel — used for approvals, attendance, and simple checklists. It is the easiest way to create or make a checklist-style tracker without touching form controls, and the standard way to create a Yes/No drop-down list with color (add the color with conditional formatting, covered below). Whether you call it a yes/no, yes or no, or yes-no drop-down, the setup is the same: select the cells, open Data > Data Validation, choose List, and type Yes,No in the Source box.

To make the answers visually scannable, add color with conditional formatting:

Select the Yes/No cells.
Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
Type Yes, choose a green fill, and click OK. Repeat with No and a red fill.

Data validation itself cannot color individual choices — the formatting layer does that job. The same trick works for any status list: High,Medium,Low with red, amber, and green fills, for example. (For a true checklist with tick boxes instead of a Yes/No drop-down, use Developer > Insert > Check Box — a Yes/No menu is simpler, but a checkbox gives you a one-click tick.)

Dependent (cascading) drop-down lists with INDIRECT

To create a dependent drop-down list — one whose options change based on another cell: pick Fruit in A2 and the B2 menu offers apples and bananas; pick Vegetable and B2 offers carrots and peas. The classic way to build this uses named ranges and INDIRECT:

Create the main list (say Fruit and Vegetable) as a range, and give the main cell (A2) a drop-down from it.
For each main choice, create a named range whose name exactly matches the choice — e.g. a range named Fruit holding the fruits, and one named Vegetable holding the vegetables. Names cannot contain spaces, so use underscores (Ice_Cream) for multi-word choices.
Give B2 a Data Validation list whose Source is =INDIRECT(A2). Excel looks up the name matching A2's value and uses that range as the menu.

If B2 shows an error before A2 has a value, that is normal — INDIRECT of an empty cell resolves to nothing. To understand exactly how INDIRECT resolves names, see the Excel Formula Builder's INDIRECT entry, which walks through its arguments and common errors.

Things that trip people up (and most guides skip)

Drop-downs are simple until one of these bites you. Here are the edge cases worth knowing:

Behavior	Why it happens	What to do
The arrow only appears when the cell is selected	That is by design — Excel shows the arrow for the active cell only.	Nothing to fix. If arrows never appear, check In-cell dropdown is ticked in Data Validation.
You want a blank choice in the menu	Ignore blank does not add a blank option — it only lets people leave the cell empty without an error.	Include an empty cell in your source range; it becomes a blank entry in the menu.
People paste over your drop-down and the rule vanishes	Pasting replaces the validation rule along with the value — validation cannot block a paste.	To reapply the rule: copy a good cell, select the pasted cells, right-click, Paste Special > Validation.
Typed options get cut off mid-list	The Source box holds about 255 characters; extras are silently dropped.	Move the options to a cell range and use it as the Source.
You want a helpful hint on the cell	Many people never notice the Input Message tab.	In Data Validation, open the Input Message tab, tick Show input message when cell is selected, and add a title and message.
You want control over wrong entries	The Error Alert tab defaults to Stop, which blocks invalid entries outright.	Change the style to Warning (lets the user proceed after confirming) or Information (just notifies), and customize the message text.
The arrows sit at the top of your table, not inside the cells	Those are filter drop-downs — created by Data > Filter (AutoFilter), a different feature from data validation lists. Adding a filter is not the same as adding a drop-down list.	Turn filter arrows off with Data > Filter again. If you want a real data validation drop-down, follow the steps at the top of this guide instead.

Frequently asked questions

How do I remove a drop-down list in Excel?

Select the cells with the drop-down, go to the Data tab and click Data Validation. In the dialog, click Clear All and then OK. This removes the list and every validation rule on those cells. If the cells keep their current values, that is normal - Clear All removes the rule, not the data.

How do I add an option to an existing drop-down list?

Adding, creating, or making new drop-down options depends on how the list was built. If the source is a cell range, just add your new item to that range - the drop-down updates automatically. If the items were typed directly into the Source box, select the cell, open Data Validation, and type the new item into the Source list with the existing separators. To make future additions automatic, switch to a range-based source instead.

How do I make a Yes/No drop-down list in Excel?

Select the cells, go to Data, Data Validation, choose List under Allow, and type Yes,No into the Source box. Click OK and each cell now offers Yes and No. To color the answers, select the cells, go to Home, Conditional Formatting, Highlight Cells Rules, Equal To, and create one rule for Yes and one for No.

Why is the drop-down arrow not showing in Excel?

Two common reasons. First, the arrow only appears when the cell is selected, so click the cell and look again. Second, the In-cell dropdown option may be turned off: open Data Validation, open the Settings tab, and check that In-cell dropdown is ticked. If the arrow is still missing, the data validation rule may have been cleared - check by opening Data Validation again and looking at the Allow setting.

How do I add color to drop-down list choices?

Data validation alone cannot color individual choices, so use conditional formatting. Select the drop-down cells, go to Home, Conditional Formatting, Highlight Cells Rules, Equal To, type the choice text, and pick a fill color. Repeat for each choice you want colored.

Why is my drop-down list cutting off options?

If you typed the options directly into the Source box, you probably hit its roughly 255-character limit - extra items are silently dropped. The fix is to put the options in a cell range and point the Source at that range instead. A range has no practical length limit.

How do I add a calendar date-picker drop-down in Excel?

Excel has no built-in calendar control to insert as a data validation drop-down, so there is no native date-picker drop-down. The usual workaround is a drop-down of dates: list your dates in a cell range, then use Data Validation with List and point the Source at that range.

How do I copy a drop-down list to other cells?

Copy a cell that already has the drop-down, select the destination cells, and paste - the validation rule comes along with the paste. If you only want the rule without copying the value, use Paste Special and choose Validation.

Can I create a drop-down list in Excel for the web?

Yes. In Excel for the web, select your cells, go to the Data tab, and click Data Validation. The settings work the same as desktop: choose List under Allow and type your options or point the Source at a range. Lists, Yes/No menus, and dependent drop-downs all work in the browser.