How to Create a Drop Down List in Excel


*This post may contain affiliate links. As an Amazon Associate we earn from qualifying purchases.

One of the easiest ways to restrict the data that goes into your Excel spread sheet is by creating a drop down list. In this article, we’ll show you step by step how to create a drop down list in Excel for your spreadsheets to help keep them organized. This solution is compatible with multiple versions of Excel, including both Excel 2016 for PC and Mac.

How to Create a Drop Down List in Excel Using Data Validation

1. Select the cells that you would like to contain the list.
2. On the menu tab or ribbon, click the Data tab, then select Data Validation from the menu that appears just beneath.
3. On the Data Validation interface screen that pops up, select List from the Allow drop down menu.
4. To create a delimited list, type your options into the Source field, separating them with commas. You can capitalize them if you like and you can also use numbers in this field.
5. Once you’re done entering your options into the Source field, click the Okay button at the bottom of the interface.

How to Create a Drop Down List in Excel Using Separate Worksheet

1. In a new worksheet, enter the options you would like to include in your drop down list. Make sure your entries are all within the same row or column, taking care not to include any blank cells.
2. Highlight all of your new entries to select them, right click on these fields, and select Define Name.
3. In the first field, for “Name“, you can enter a name that applies to your entries and then click Okay. Make sure there are no spaces in the name.
4. Click a cell where you would like this list to be available as a drop down.
5. Select Data from the menu bar and then select Data Validation.
6. This opens the Data Validation interface, where you will select List from the Allow drop down menu.
7. In the Source box at the bottom of the interface, type an equal sign (=) and then type the name you gave to your list in step 3. Make sure there are no spaces between the equal sign and the list name.
8. Click the check box next to In-cell drop down if you would like this list to appear as a drop down menu. You can also select the Ignore blank box if a blank selection is permissible.
9. Click okay and start using your new drop down menu!

Users can encounter issues learning how to create a drop down list in Excel. You can visit Microsoft’s Support library in case that happens. Since there are multiple versions of Office, the process might slightly change in t

Recent Posts