How to Create a Drop-Down List in Excel

The drop-down list is the most popular data validation tool in Microsoft Excel. This creates a drop-down list in cell(s) and ensures data entry is consistent, restricted to entries that are approved and free from spelling errors and typos. They help ensure only valid data is entered which is particularly helpful when multiple users are entering data on the same worksheet.

Items that display in drop-down lists can be populated from different source types:

• From a comma-separated list typed manually in the Data Validation Settings tab.
• From a list of values in a worksheet, supplied as a cell range.
• From a defined named range.
• From a table column.

Which method you use often depends on the type of items, the number of items and whether those items are static or likely to be added to over time.

Drop-down List from Comma Separated Values

This is useful for short lists such as Yes, No; Pass, Fail; or a list of account manager initials as shown in the example below.

1. Select the cell(s) to be validated.
2. On the Data tab, in the Data Tools group, select Data Validation.
3. On the Settings tab, select List from the options under Allow.
4. In the Source box, type the values you want to appear in your list separated by a comma.  It doesn’t matter if a space follows the comma.

5. Click OK.

Note: The items in your drop-down menu appear in the exact same order that you have typed them.

On some language versions of Excel, you need to use a semicolon (;) instead of a comma.

Drop-down List from a Range of Values

If the items that you want to display in the drop-down list are a longer list, you might choose to populate the list from a cell range in the workbook.

1. Select the cell(s) to be validated.
2. On the Data tab, in the Data Tools group, select Data Validation.
3. On the Settings tab, select List from the options under Allow.
4. In the Source box, click the icon to collapse the window, giving you space to select the cell range for your list:

      5. Select the cell range containing items for your drop-down list:

6. Press Enter to view the Data Validation window in full.
7. Optional – set up an Input message on the Input Message tab and/or on the Error Alert tab, check ‘Show error alert after invalid data is entered’.
8. Click OK.

The list is available when validated cells are selected and the items appear in the same order as the source range:

Drop-down List from a Named Range

The drop-down list can be populated from a named range elsewhere in the workbook.  Keep in mind, by nature named ranges are absolute. Therefore, if new items are added to your list, add them in the middle of the list and not outside the range.

It is not uncommon to manage sources for lists on an entirely new worksheet that can be hidden if required. 

Define a named range for a list of values using the Name Box

1. Enter the values you would like to see in your drop-down list.
2. Select the values.
3. Click in the Name Box top left of the worksheet and type a name for the values.

Note: the name cannot contain spaces. You can use underscore if necessary.

4. Press Enter to create the name.

Use the named range as a source for a validation list

1. Select the cell(s) to be validated.
2. On the Data tab, in the Data Tools group, select Data Validation.
3. On the Settings tab, select List from the options under Allow.
4. In the Source box, type = followed by your name
   OR
   Press F3 on your keyboard to show a list of defined names in the workbook.

5. Press Enter to view the Data Validation dialog box in full.
6. Optional – set up an Input message on the Input Message tab and/or set up an error message on the Error Alert tab.
7. Click OK.

Drop-down List that automatically includes new values (from Table)

If the list of values in your drop-down list is likely to expand regularly, you can base the list on an Excel table. By default, Excel Tables expand automatically when new rows are added. This makes them a great source for a dynamic drop-down list as items automatically also appear in the drop-down when new rows are entered in the table.

Format your list of values as an Excel Table

1. Enter the values you want to see in your drop-down list in a column.
2. Add a heading.
3. Select the values and on the Home tab, click Format as Table and click a table style.
4. In the Format As Table dialog box, tick My table has headers if you have a heading.

5. Click OK. Values are formatted as an Excel table.

Set up the Data Validation List

1. Select the cell(s) to be validated.
2. On the Data tab, in the Data Tools group, select Data Validation.
3. On the Settings tab, select List from the options under Allow.
4. In the Source box, click the icon to collapse the window, giving you space to select the table to be used for your list:

5. Select your table or table column if there is more than one column in the table.
6. Press Enter.
7. Click OK.

The Table column values populate the drop-down list of validated cells.  As new rows are added to the table, they also appear in the drop-down.

Dependent Drop Down Lists

Dependent Drop Down Lists in Excel are useful tools that link two or more drop-down lists together. When you select an option from the first list, it controls the options available in the second list. For example, if you choose a country in the first drop-down, the second drop-down will only show cities from that country.

1. First Drop-Down List:
   Select the cell for the first drop-down list.
   Go to Data > Data Validation.
   In the Data Validation dialog box, select the Settings tab and choose List.
   In the Source field, specify the range of items for the drop-down list (e.g., categories of vehicles).
   Click OK to create the first drop-down list.
2. Define Named Ranges:
   Select the entire data set (e.g., A1:B6).
   Go to Formulas > Defined Names > Create from Selection (or press ‘Ctrl + Shift + F3’).
   In the Create Names from Selection dialog box, check the Top row option and uncheck the others. This will create named ranges like ‘Cars’ and ‘Bikes’ based on your data.
   Click OK.
3. Dependent Drop-Down List:
   Select the cell for the dependent drop-down list (e.g., E3).
   Go to Data > Data Validation.
   In the Data Validation dialog box, select the Settings tab and choose List.
   In the Source field, enter the formula ‘=INDIRECT(D3)’, where D3 is the cell with the first drop-down list.
   Click OK.

Now, when you select an option in the first drop-down list, the dependent drop-down list will automatically update with the corresponding options. For example, if you select “Cars” in the first drop-down, the second drop-down might list specific car brands or models.

Using drop-down menus in Excel can be incredibly helpful and improves efficiency, accuracy, consistency, and usability in spreadsheets, making them an invaluable tool for business processes such as data entry, reporting, and decision-making. Practice these techniques with your data, and you’ll become proficient in no time.

Ready to enhance your Excel skills? Enquire about our Microsoft Excel training courses today.