How to Create a Drop-Down List in Excel: Set Up, Add, Remove and Dependent Lists

1. 1

   Select the range of cells that should get the drop-down

2. 2

   Click "Data Validation" on the "Data" tab

   On Windows you can also open it with Alt → A → V → V.
3. 3

   On the "Settings" tab, set "Allow" to "List"

4. 4

   Enter the choices in "Source", for example Done,Not started,On hold

   Separate them with commas.
5. 5

   Make sure "In-cell dropdown" is checked, then click OK

Because the entries are always spelled the same, nothing gets missed when you count them later with a filter or COUNTIF. Select a cell and press Alt + ↓ (on a Mac, Option + ↓) to open the list without touching the mouse.

When there are many choices, or more will be added later, type them as a list somewhere in the workbook and point to it.

1. 1

   Type the list of choices in a column, for example on another sheet

   Example: department names in A2:A6 on a sheet called "Settings"
2. 2

   Click in the "Source" box and drag across the list

   It is entered as =Settings!$A$2:$A$6.

To change every cell with the same drop-down at once, check "Apply these changes to all other cells with the same settings" in the Data Validation dialog.

1. 1

   Select the cells to clear

2. 2

   Open Data Validation

3. 3

   Click "Clear All" at the bottom left, then click OK

If you are not sure where the drop-downs are, press F5 → "Special" → "Data validation" to select every cell that has one.

Choose "Fruit" in the first list and the second list offers "Apple" and "Orange". Give each list of choices a name, then use INDIRECT to pick the right one.

1. 1

   Make a list like the one above and select all of it, including the headers

2. 2

   On the "Formulas" tab, click "Create from Selection", check only "Top row", then click OK

   Each column's range is saved under its header name: "Fruit", "Vegetables" and "Fish".
3. 3

   In the first cell (e.g. A2), create a drop-down whose source is the header row

   If the list is on a sheet called "Lists", the source is =Lists!$A$1:$C$1.
4. 4

   In the second cell (e.g. B2), create a drop-down with the source =INDIRECT($A2)

   The range whose name matches the text in A2 becomes the list of choices.

On the "Error Alert" tab of Data Validation, choose what happens when someone enters a value that is not on the list.

• ✓Choices are separated by commas
• ✓The referenced range has no blank cells in it
• ✓For dependent lists, the names match the first list's choices exactly