How to create a drop-down list in Excel

Method 1: Type the items into the rule

Your team tracks tasks in a sheet, and the Status column is a mess: "Done", "done ", "In Progress", "Started". Nobody meant to make it messy. A drop-down list stops it, because people pick from a list instead of typing.

For a short list that never changes, like Yes and No or a few statuses, type the items straight into the rule. The limit is 255 characters. If commas don't work, your regional settings use another list separator, often a semicolon.

1. Select the cells that get the drop-down, for example B2:B50.
2. Go to Data > Data Validation (in the Data Tools group).
3. On the Settings tab, set Allow to List and leave In-cell dropdown ticked.
4. In Source, type Not started,In progress,Waiting,Done and click OK.
5. Click a cell and use the arrow, or press Alt+Down Arrow to open the list from the keyboard.

Method 2: Point to a range, or a table that grows

For a list you'll edit, keep the items in cells. Here they sit in E2:E5. In Source, select the range or type =$E$2:$E$5. The list can live on another sheet too, like =Lists!$A$2:$A$20, which keeps it out of the way.

A plain range has one catch: add a fifth status in E6 and the drop-down doesn't see it. Turn the list into a table instead (click it and press Ctrl+T), then select the table's data cells as the Source. When you type a new item below the table, the table grows and the drop-down includes it.

Once the list exists, you can check typed data against it. =COUNTIF($E$2:$E$5,B5)>0 is TRUE for "In Progress", because COUNTIF ignores capitals, and FALSE for "done " with its trailing space.

Method 3: A sorted list of unique values (Excel 365)

Sometimes the choices come from data you already have, like the client names in an orders log in column G. They repeat, they're out of order and one has a stray space. In Excel 365 or 2021, build the list with a formula in a spare cell, say I2.

Watch the blank row. =SORT(UNIQUE(G2:G10)) on its own returns a 0 for the empty G6 and keeps "Acme Dental " as a separate name from "Acme Dental". FILTER drops the blank and TRIM merges the twins, leaving four clean names.

In the Data Validation Source box, type =$I$2#. The # means "the whole spill", so when a new client appears in the orders the drop-down gets longer by itself.

Input message and error alert

Two other tabs in the same Data Validation box make the drop-down friendlier. Input Message shows a note when someone selects the cell, like "Pick the task's current status". Error Alert controls what happens when someone types something that isn't on the list.

Stop (the default) blocks the entry. Warning asks "Continue?" and lets people keep it, and Information just tells them. Use Stop when other formulas depend on exact values, such as a COUNTIF that counts "Done".

Data Validation only checks typing. A value pasted into the cell replaces the rule along with the content, and values that were there before you added the rule are never checked. To find those, use Data > Data Validation (the arrow) > Circle Invalid Data, or list them with a formula like this one.

Copy, find or remove a drop-down

To give more cells the same drop-down, copy a cell that has it, select the new cells and use Home > Paste > Paste Special > Validation. That copies only the rule, not the value.

To find every cell with a drop-down on a sheet, use Home > Find & Select > Data Validation. To remove one, select the cells, open Data > Data Validation and click Clear All, then OK. The values in the cells stay; only the arrow and the check go.

Ignore blank, on the Settings tab, is ticked by default. It lets people leave the cell empty. A blank cell inside the source range shows up as an empty choice in the list, which is another reason to keep the list in a table with no gaps.