Don’t forget to share this post

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’.
How to Create a Drop down List in Excel 4 Easy Methods Select Cells
How to Create a Drop-down List in Excel? 4 Easy Methods - Select Cells
  1. 2. Under ‘Allow’, select ‘List’.
How to Create a Drop down List in Excel 4 Easy Methods Allow List
How to Create a Drop-down List in Excel? 4 Easy Methods - Allow 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.
How to Create a Drop down List in Excel 4 Easy Methods Type Items
How to Create a Drop-down List in Excel? 4 Easy Methods - Type Items
  1. 4. Click the ‘Input Message’ tab to specify the message that should be shown when the cell is selected.
How to Create a Drop down List in Excel 4 Easy Methods Input Message
How to Create a Drop-down List in Excel? 4 Easy Methods - Input Message
  1. 5. Click the ‘Error Alert’ tab to customize the warning message when invalid data is entered.
How to Create a Drop down List in Excel 4 Easy Methods Error Alert
How to Create a Drop-down List in Excel? 4 Easy Methods - Error Alert
  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.
How to Create a Drop down List in Excel 4 Easy Methods Drop down List Input Message
How to Create a Drop-down List in Excel? 4 Easy Methods - Drop-down List Input Message
  1. 7. When you try to enter invalid data, you will see an error alert.
How to Create a Drop down List in Excel 4 Easy Methods Error Alert 2
How to Create a Drop-down List in Excel? 4 Easy Methods - Error Alert
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
How to Create a Drop down List in Excel 4 Easy Methods Select Cells 2
How to Create a Drop-down List in Excel? 4 Easy Methods - Select Cells
  1. 2. Select ‘List’ under ‘Allow’ and click the button to select the range, as shown below.
How to Create a Drop down List in Excel 4 Easy Methods Select Source Range
How to Create a Drop-down List in Excel? 4 Easy Methods - Select Source Range
  1. 3. Select the range of cells containing the items for your drop-down.
How to Create a Drop down List in Excel 4 Easy Methods Select Range
How to Create a Drop-down List in Excel? 4 Easy Methods - Select Range
  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.
How to Create a Drop down List in Excel 4 Easy Methods Apply Data Validation
How to Create a Drop-down List in Excel? 4 Easy Methods - Apply Data Validation
  1. 5. That’s it. The drop-down list appears in the same order as in the range.
How to Create a Drop down List in Excel 4 Easy Methods Drop down List from Range
How to Create a Drop-down List in Excel? 4 Easy Methods - Drop-down List from 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.
How to Create a Drop down List in Excel 4 Easy Methods Create Excel Table
How to Create a Drop-down List in Excel? 4 Easy Methods - Create 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.
How to Create a Drop down List in Excel 4 Easy Methods Name Table
How to Create a Drop-down List in Excel? 4 Easy Methods - Name Table
  1. 3. Select the cells where you want the drop-down list and go to Data > Data Validation.
How to Create a Drop down List in Excel 4 Easy Methods Data Validation
How to Create a Drop-down List in Excel? 4 Easy Methods - 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.
How to Create a Drop down List in Excel 4 Easy Methods INDIRECT Function
How to Create a Drop-down List in Excel? 4 Easy Methods - INDIRECT Function
  1. 5. The drop-down list with the colors is available in the cell.
How to Create a Drop down List in Excel 4 Easy Methods Drop down List from Table
How to Create a Drop-down List in Excel? 4 Easy Methods - Drop-down List from Table
  1. 6. As soon as you add an item to the table, it instantly appears in the drop-down list.
How to Create a Drop down List in Excel 4 Easy Methods Dynamic Drop down List
How to Create a Drop-down List in Excel? 4 Easy Methods - Dynamic Drop-down List
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

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.
How to Create a Drop down List in Excel 4 Easy Methods Structure Data
How to Create a Drop-down List in Excel? 4 Easy Methods - Structure Data
  1. 2. Select the cell where you want the first drop-down and go to Data > Data Validation.
How to Create a Drop down List in Excel 4 Easy Methods Data Validation 2
How to Create a Drop-down List in Excel? 4 Easy Methods - Data Validation
  1. 3. Allow ‘List’ and select the headers as the source. Click ‘OK’ to apply.
How to Create a Drop down List in Excel 4 Easy Methods Select Source
How to Create a Drop-down List in Excel? 4 Easy Methods - Select Source
  1. 4. Select the cell where you want the dependent list and go to Data > Data Validation.
How to Create a Drop down List in Excel 4 Easy Methods Data Validation 3
How to Create a Drop-down List in Excel? 4 Easy Methods - 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.
How to Create a Drop down List in Excel 4 Easy Methods INDIRECT Function 2
How to Create a Drop-down List in Excel? 4 Easy Methods - INDIRECT Function
  1. 6. That’s it. Your dependent drop-down list is ready.
How to Create a Drop down List in Excel 4 Easy Methods Dependent Drop down List
How to Create a Drop-down List in Excel? 4 Easy Methods - Dependent Drop-down List

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 Create a Drop down List in Excel 4 Easy Methods Edit Drop down List
How to Create a Drop-down List in Excel? 4 Easy Methods - Edit Drop-down List

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.

How to Create a Drop down List in Excel 4 Easy Methods Remove Drop down List
How to Create a Drop-down List in Excel? 4 Easy Methods - Remove Drop-down List

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:

Maria Del Olmo
Originally published Mar 29 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets