Subscribe

Lesson 67 of 104 7 min Updated Completed

How to Create a Drop-Down List in Excel

A drop-down list lets people pick a value from a list instead of typing it. It’s faster, and it keeps entries consistent.

Create a drop-down list

  1. Type your list, here in B3:B9, and select the cell for the drop-down, C11.

    The list in B3:B9 and cell C11 selected

    Note: The list can be anywhere: on the same sheet, on another sheet, or in hidden cells.

  2. On the Data tab, click Data Validation.

    The Data Validation button on the Data tab

  3. On the Settings tab, under Allow, choose List. Click in the Source box and select B3:B9.

    Choosing List with the source B3:B9

  4. Click OK. Click the arrow next to C11 to see the list.

    The drop-down list in C11

You can also type the items straight into the Source box, separated by commas, such as Yes,No,Maybe.

Items typed directly in the Source box

Allow entries that aren’t on the list

  1. By default, typing a value that’s not in the list shows an error.

    The error after typing a value not in the list

  2. To allow other values, select the cell and click Data Validation on the Data tab.

    Opening Data Validation again

  3. On the Error Alert tab, untick Show error alert after invalid data is entered.

    Unticking Show error alert

  4. Click OK. People can now pick from the list or type their own value.

    A value not in the list accepted

Add or remove items

You don’t need to open Data Validation to change the list.

  1. Select an item in the list, for example Coppell.

    The item Coppell selected in the list

  2. Right-click and click Insert.

    Choosing Insert from the right-click menu

  3. Choose Entire row and click OK.

    Choosing Entire row in the Insert dialog box

    Excel inserts an empty row above, and the list’s range grows automatically.

    An empty row added inside the list

  4. Type the new item in the empty cell (B4). It now appears in the drop-down.

    The new item in the drop-down list

To remove an item, right-click it, click Delete, choose Entire row, and click OK.

A drop-down list that grows automatically

To make new items at the end of the list appear in the drop-down on their own:

  1. Select cell D4.

    Cell D4 selected

  2. On the Data tab, click Data Validation.

    The Data Validation button on the Data tab

  3. Under Allow, choose List. In Source, type:

    =OFFSET($B$3,0,0,COUNTA($B:$B),1)

    The OFFSET formula in the Source box

    This starts at B3 and is as tall as the number of filled cells in column B. (See dynamic named ranges for how OFFSET works.)

  4. Click OK, then add new items at the end of the list.

    New items added at the end of the list appearing in the drop-down

Result: the drop-down includes the new items automatically.

Note: COUNTA counts every filled cell in column B. If there’s a heading or other text above B3, the list picks up empty rows at the end. Keep column B for the list only, or adjust the count, for example COUNTA($B:$B)-1.

Tip: An easier way: turn the list into a Table (Ctrl + T) and use the Table’s column as the source. New rows are included automatically.

Remove a drop-down list

  1. Select the cell with the drop-down, for example D4.

    Cell D4 with a drop-down list selected

  2. On the Data tab, click Data Validation.

    Opening Data Validation to remove the list

  3. Click Clear All, then OK.

    Clear All in the Data Validation dialog box

Next: make one list change based on another with dependent drop-down lists.