How To Create Drop Down Lists in Excel

Adding a drop-down list in Excel makes data entry faster and improves usability.

A drop-down list allows users to select a value from a predefined list instead of manually typing it. It helps streamline data entry, reduce errors, and ensure consistency in spreadsheets. This is particularly useful for forms, reports, and data validation requiring standardized inputs.

This guide will walk you through everything you need to know, from creating a basic drop-down list to advanced techniques like dynamic and dependent drop-down lists.

Creating Simple Drop-Down Lists

You don't need to be a tech expert to add a drop-down list in Excel. Excel's Data Validation feature makes the process super easy.

Our sample file contains the names of people. We want to write each individual's job and create a drop-down list so that the user selects the job instead of writing it manually.

Step 1: Prepare the source list.

Before creating the drop-down list, you need to organize the source data that will populate the list. We recommend that you do this in a new sheet. For example, if you are doing the main work in Sheet1, you should enter your source list in Sheet2.

To make your work smarter, select the entire list (e.g., A1:A7 in Sheet2). Click the Name Box (the small box to the left of the formula bar). Type a name (e.g., JobList) and press Enter. This makes it easier to reference the list later.

Step 2: Select the target cells.

Return to the main sheet and select the cell(s) where you want the drop-down list to appear. You can select single or multiple cells, depending on your needs.

Step 3: Open the Data Validation menu.

Click on the Data tab in the ribbon. Select Data Validation from the Data Tools group. This will open the Data Validation dialog box.

Step 4: Set up the validation criteria.

In the Data Validation window, go to the Settings tab. Under Allow, choose List from the drop-down menu. Then, click inside the Source box and type the formula =JobList (if you named your range earlier). If you didn’t name the range, use this format instead: =Sheet2!A1:A7. Finally, ensure Ignore blank and In-cell dropdown are checked.

Step 5: Test the drop-down list.

Your drop-down list should now be active. Click on a cell to see a small arrow appear, then select it to choose an option.

Alternative method: Enter list items directly.

Instead of referencing a range, you can type your items directly into the Source box. This is useful for short lists that won’t change often. Ensure you separate the items with commas. For example:

Adding Input Messages and Error Messages Using Data Validation

Another cool feature of drop-down lists is that you can create and customize an input message to guide users on what to enter and an error message in case a user inputs invalid data that could ruin your tables or formulas.

How to add an input message.

This instructs Excel to show a small pop-up displaying a message whenever a user selects a cell. Follow the steps below:

1. Select the cell(s) where you have applied Data Validation.

2. Go to the Data tab in the Excel ribbon and click Data Validation (in the Data Tools group).

3. In the Data Validation window, go to the Input Message tab.

4. Check the "Show input message when cell is selected" box.

5. Enter a title (optional, e.g., "Select a Category").

6. Enter the message in the text box (e.g., "Please select a category from the drop-down list.").

7. Click OK.

   

Result:

How to add an error message.

When you create an error message, Excel will display it whenever a user enters invalid data. Here's how to create one:

1. Select the cell(s) with the drop-down list.

2. Go to the Data tab and click Data Validation.

3. Open the Error Alert tab.

4. Check the "Show error alert after invalid data is entered" box.

5. Choose a Style:

   • Stop (default) – Prevents users from entering invalid data.

   • Warning – Displays a warning but allows invalid data.

   • Information – Informs users but still accepts invalid data.

6. Enter a title (e.g., "Invalid Entry").

7. Enter the message (e.g., "Please select a valid category from the drop-down list. Do not type your own value.").

8. Click OK.

   

Result:

How To Create a Dependent Drop Down List

A dependent drop-down list is a list where the options change based on the selection from another drop-down list. This is useful when organizing hierarchical data, such as Category → Subcategory (e.g., selecting a "Fruit" category shows only fruits in the next drop-down).

Step 1: Prepare your data.

Structure your data properly before setting up the dependent drop-down list.

1. Create the main category list (e.g., in Sheet2, Column A):

2. Create subcategory lists for each category in separate columns:

Step 2: Name the ranges.

Named ranges make it easier to reference the subcategories.

1. Select the main category list (e.g., A2:A4).

2. Go to the "Formulas" and select "Define Name".

   

3. Enter a name (e.g., Category) and click OK.

   

Now, define named ranges for each subcategory:

1. Select the subcategory items under "Fruits" (B2:B4).

   

2. Go to "Formulas" → "Define Name" and enter Fruits as the name (it must match the category name exactly).

   

3. Repeat this for Vegetables (C2:C4) and Grains (D2:D4), naming them as Vegetables and Grains, respectively.

Step 3: Create the first drop-down list (Main category).

It's time to create the first drop-down list:

1. Go to the sheet where you want the drop-down lists.

2. Select the cells to which you want to apply the first dropdowns. 

   

3. Click Data → Data Validation.

4. Under Allow, select List.

5. In the Source field, enter: =Category

6. Click OK.

   

Result:

Step 4: Create the dependent drop-down list.

Let's finish up by creating the dependent drop-down lists:

1. Select the cells for the dependent drop-downs.

   

2. Click Data → Data Validation.

3. Under Allow, select List.

4. In the Source field, enter: =INDIRECT(A1)

5. Select OK.

   

Result:

Final Thoughts

Using drop-down lists in Excel standardizes data entry, reduces errors, and improves user experience. Excel offers multiple ways to customize drop-downs to your needs, and we have covered each of them. Start with a basic list, and as you grow comfortable, explore dynamic and dependent lists for more automation and efficiency.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions (FAQ)

1. How do I remove a drop-down list in Excel?

To remove a drop-down list, select the cell(s), go to Data > Data Validation, and click Clear All in the dialog box. This removes the drop-down but keeps existing values in the cells.

2. Can I create a drop-down list with multiple selections?

Excel does not support multi-select drop-downs by default, but you can use VBA (Visual Basic for Applications) to allow multiple selections in a single cell. Alternatively, consider using checkboxes for better usability.

3. Why is my drop-down list not showing all options?

This can happen if the source range is incorrect or not formatted as a dynamic list. Check the Data Validation settings and ensure your range includes all intended items.

Related Articles

How to Make an Excel Spreadsheet Live

How to Add Months to Date in Excel

How to Print Labels From Excel Spreadsheet