How to Create Dropdown Lists in Excel Easily

Dropdown lists can be created in Excel to allow a user to select from a list of items during data entry. Creating a dropdown list in Excel is quite simple. One of the benefits of using a drop-down list as opposed entering data manually is to prevent entry errors. In addition, drop down lists ensure that data is entered uniformly throughout the worksheet. If a user tries to enter data in a cell for which a dropdown menu exists, Excel rejects the entry unless it is identical to one of the options in the dropdown menu. Continue reading to find out how to create dropdown lists in Excel.

How to Create Dropdown Lists in Excel

For this tutorial, the data to be displayed when a drop-down menu is clicked on will be entered in cells A1:A5. You can either create the items in a single column or a single row. For example, instead of A1:A5, it can be A1:E1. We will be creating a dropdown list in cell F6 using the data entered in A1:A5. Let’s get started.

1. Enter the dropdown list items in cells A1:A5.
2. Select cell F6.
3. Click on Data tab from the Excel Ribbon.
4. Click on Data Validation arrow key and select Data Validation from the list.
5. Click on the dropdown list under Allow and select List. Also ensure that the Ignore Blank and In-cell dropdown check boxes are checked (selected).
6. Click on the Source Control icon and click and drag the cells where your drop-down list items appear, in our case, it’s A1:A5. You can also manually type in the Source which would be =$A$1:$A$5.
7. Hit Enter.
8. Click OK.

The dropdown arrow becomes visible when the cell containing the list is selected or active. Otherwise, the cell appears as a normal cell.

Method 2: Entering the List of Options Directly Into Source Field

You don’t necessarily have to create a list of options separately to create a dropdown list. If you have a relatively small list of options, you can directly enter the list items in the Source field under Data Validation dialog box. Here are the steps:

1. Select the cell where you want to insert a drop down list.
2. Click on Data tab from the Excel Ribbon.
3. Click on Data Validation arrow key and select Data Validation from the list.
4. Click on the dropdown list under Allow and select List. Also ensure that the Ignore Blank and In-cell dropdown check boxes are checked (selected).
5. In the Source field, enter your list of options separated by commas. Thus, entering Option 1, Option 2, Option 3 will results in a dropdown list with those items.
6. Click OK to create the dropdown list.

Entering the Same Dropdown List to Multiple Cells

You have the option to create the dropdown list in multiple cells at the same time. Select as many cells as you want, either by clicking and dragging the mouse or simply pressing down Ctrl key and clicking on the specific cells you want. Then follow steps 3 to 8 to enter the dropdown list in all those cells at once.

Entering a Custom Error Alert for Dropdown Lists

When an error alert is setup for the dropdown list, it will generate a message for Excel users when they try to enter something manually into a cell for which a dropdown menu exists. Follow these steps to enter a custom error message for dropdown lists in Excel:

1. Create a dropdown list using any of the methods listed above.
2. Open the Data Validation dialog box and click on Error Alert tab.
3. Ensure the Show error alert after invalid data is entered box is checked.
4. Select a Style.
5. Give your error message a title.
6. Enter the custom error message you want the users to see when they enter an invalid entry.
7. Click OK.

There you go folks. This is how you enter drop down lists in Excel. If you found this post useful, please leave a comment.