Create a List
Three unrelated things are called a list here, and only one of them is a feature you switch on. The one most people arrive wanting is the drop-down, which is really a validation rule: nothing is stored in the cell except a restriction and a pointer at wherever the options live. Bullets are not a feature at all — Excel ships no button for them, so the character goes in by hand and the items are separated by hand too. Numbering is the one to build rather than type, because typed digits do not repair themselves when a row is removed and a formula reading its own row always does.
The formula
Data > Data Validation > List drop-down; Source takes "Red,Green,Blue" or =$H$2:$H$20
Alt+7 (numeric keypad) a bullet character, then Alt+Enter for the next item
=ROW()-1 numbering that renumbers itself when a row is deleted
Ctrl+T turn the range into a table so the list grows on its own
=COUNTIF($B$2:$B$500,A2)=0 flag entries in list A that are missing from list BA worked example
H2:H6 holds five department names, and every cell in C2:C200 should offer them as a drop-down.
Select C2:C200, Data > Data Validation, Allow: List, Source: =$H$2:$H$6
Every cell in C2:C200 gains an arrow offering the five departments. Adding a sixth department in H7 will not show up, because the Source is fixed at H2:H6 — make the source range a table, define a name for its column under Formulas > Define Name, and use that name as the Source instead, since Data Validation will not accept a structured reference typed in directly.
Which one do I need?
| If you want to… | Use |
|---|---|
| A drop-down list of permitted values in a cell | Data > Data Validation > Allow: List, with the options typed in or a range as the Source |
| A bulleted list inside one cell | Alt+7 on the numeric keypad for the bullet, Alt+Enter between lines |
| A numbered list down a column | Type 1 and 2, select both and drag the fill handle — or =ROW()-1 so a deleted row renumbers the rest |
| A list that grows by itself and can be referred to by name | Select the data and press Ctrl+T to turn it into a table |
| Your own fill or sort order (Mon, Tue, Wed, or team names) | File > Options > Advanced > Edit Custom Lists — the entries then work with the fill handle and as a sort order |
| Checking two lists against each other | =COUNTIF($B$2:$B$500,A2)=0 flags what is missing; Conditional Formatting > Duplicate Values highlights the overlap |
| Shuffling a list into a random order | Add a column of =RAND() and sort by it, or =SORTBY(A2:A20,RANDARRAY(19)) in Microsoft 365 |
Frequently asked questions
Can a drop-down list allow multiple selections?
Not by itself. Data Validation accepts one value per cell, and multi-select needs VBA hooked to the sheet's Change event or an Office Script doing the same job. Where there are only a handful of realistic combinations, listing the combinations themselves as validation entries is far less fragile than the macro.
How do I make a drop-down list grow as I add options?
Base it on a table. Select the source range and press Ctrl+T, then use Formulas > Define Name with the table column as the reference, and put that name in the validation Source box. Data Validation refuses a structured reference typed directly into it, but it accepts a defined name that resolves to one, and the list then picks up new entries automatically.
How do I make a bulleted list in Excel?
There is no bullet button. Inside a cell, press Alt+7 on the numeric keypad (Alt+0149 also works) for the bullet character, then Alt+Enter to start each new line. If the bullets are one per cell down a column, a custom number format is tidier: Ctrl+1 > Custom and enter "• "@ so every text entry displays with a bullet without one being stored in the data.
How do I compare two lists to see what is missing?
Put =COUNTIF($B$2:$B$500,A2)=0 beside list A and fill it down: TRUE marks every entry that does not appear in list B, and swapping the ranges tests the other direction. For a quick visual check instead, select both lists and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, which colours everything the two share.
New guides and tools, once a month
DE + EN · double opt-in · no spam