Drop-down lists are a great way to facilitate data entry. Choosing from a drop-down list is faster and ensures the input is accurate. Additionally, if you’re using Microsoft Excel 365, the list will be searchable so that you can speed things up even further. The source for your drop-down list can be a manually typed list, a range of cells, or an Excel table. The latter is the best option as it will create a dynamic list that shrinks or expands as the data in the original table changes. You can also choose what happens when a user attempts to input invalid data: show a warning message or reject the input. In either case, you can customize the message that is displayed.

In this guide, you will learn 4 different ways to create drop-down lists in Microsoft Excel. First, you will learn to create a drop-down list by manually inputting the items. Second, you will learn to create a drop-down list using an existing range. Third, you will learn how to use an Excel table for your drop-down list to make it dynamic. Fourth, you will learn how to create a dependent drop-down list using the INDIRECT function. Finally, you will review how to edit, copy, and remove drop-down lists.

How to Create a Drop-down List in Excel?

There are multiple ways of creating drop-down lists in Microsoft Excel, depending on what you need. In this section, you have step-by-step instructions on inputting the items manually, using a range of cells as the source, or using an Excel table to get a dynamic drop-down list. If you use Excel 365, the drop-down is also searchable, so you can find what you need quickly in longer lists. In the next section, you will learn how to create a dependent drop-down list.

Manually Input Items for Drop-down List

If you only have a few items on your list, you can type them in directly when you add data validation.

1. 1. Select the cell or cells where you want to add the drop-down list. In the ‘Data’ tab, click on ‘Data Validation’.

1. 2. Under ‘Allow’, select ‘List’.

1. 3. In the ‘Source’ input box, type the items separated by semicolons. Click ‘OK’ to apply or choose one of the other tabs for more settings.

1. 4. Click the ‘Input Message’ tab to specify the message that should be shown when the cell is selected.

1. 5. Click the ‘Error Alert’ tab to customize the warning message when invalid data is entered.

1. 6. Once the data validation is applied, you will see the input message when you select any of the cells with the drop-down list.

1. 7. When you try to enter invalid data, you will see an error alert.

Use Range as Source of Drop-down List

As mentioned, you can also use a range of cells. The range doesn’t have to be in the same sheet or even the same workbook as the drop-down list.

1. 1. Select the cells where you want the drop-down list and go to Data > Data Validation.

1. 2. Select ‘List’ under ‘Allow’ and click the button to select the range, as shown below.

1. 3. Select the range of cells containing the items for your drop-down.

1. 4. Click ‘OK’ to apply data validation. Alternatively, you can set up the input message and the error alert as shown in the previous example.

1. 5. That’s it. The drop-down list appears in the same order as in the range.

Use Excel Table as Source of Drop-down List

The main benefit of using an Excel table as the source of your drop-down list is that it will be dynamic. As soon as the table data changes, your list will shrink or expand accordingly.

1. 1. Select the range of cells and press Ctrl/Cmd + t to convert it into an Excel table.

1. 2. Select any cell in the table and click the ‘Table’ tab. Type a name for the table in the text box, as shown below.

1. 3. Select the cells where you want the drop-down list and go to Data > Data Validation.

1. 4. Under ‘Allow’, select ‘List’. Under ‘Source’, type the equal sign followed by the INDIRECT function. Within the parentheses, type the name of the table between quotation marks. Click ‘OK’ to apply.

1. 5. The drop-down list with the colors is available in the cell.

1. 6. As soon as you add an item to the table, it instantly appears in the drop-down list.

How to Create a Dependent Drop-down List in Excel?

Follow the steps below to create a drop-down list that depends on one another. First, you will create the main drop-down and then use the INDIRECT function to create the dependent drop-down. For this example, I want the first drop-down to provide a list of items. The second list should display the colors available for the item selected in the first list.

1. 1. Structure the data as shown below, with the headers containing the items for the first list. The rows under each item should contain the items for the second list; in this case, the colors.

1. 2. Select the cell where you want the first drop-down and go to Data > Data Validation.

1. 3. Allow ‘List’ and select the headers as the source. Click ‘OK’ to apply.

1. 4. Select the cell where you want the dependent list and go to Data > Data Validation.

1. 5. Under ‘Allow’, select ‘List’. In the ‘Source’ input box, type the equal sign followed by the INDIRECT function. Between the parentheses, type the reference to the cell containing the first drop-down list. Click ‘OK’ to apply.

1. 6. That’s it. Your dependent drop-down list is ready.

How to Edit a Drop-down List in Excel?

Editing Excel drop-down lists is very easy. Select the cell or cells with the drop-down list you want to edit and go to Data > Data Validation. You can change any of the settings from this window.

How to Copy a Drop-down List in Excel?

Cells containing drop-down lists can be copied and pasted like any other cell. You can use the menu options, the Ctrl/Cmd + c shortcut, or use the Fill tool to copy it to adjacent cells. You can paste the drop-down by using the simple paste menu option or the keyboard shortcut (Ctrl/Cmd + v).

How to Remove a Drop-down List in Excel?

To remove a drop-down list in Excel, select the cells containing it and go to Data > Data Validation. Click ‘Clear All’ to remove the selected drop-down lists.

Conclusion

Drop-down lists are very useful data entry tools, allowing you to speed up the process and avoid mistakes. You now know how to create drop-down lists by typing the items, selecting a range of cells, or using an Excel table as the source.

You know how to create a dependent drop-down list using the INDIRECT function. Using an Excel table will create dynamic drop-down lists that update automatically to reflect changes. If you use Excel 365, the list will also be searchable.

You also know how to customize the input message and the error alert that shows when invalid data is entered. Finally, you know how to edit, copy, and remove drop-down lists in Microsoft Excel.

To learn about data validation in Google Sheets, check out our guides on:

• How to Add a Drop-Down List in Google Sheets
• How to Add a Checkbox in Google Sheets
• How to Use Data Validation in Google Sheets