How To Create Drop Down Lists in Excel

Creating a drop down list in Excel (also known as a picklist) makes data entry faster and improves usability.

A drop down menu in Excel 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 how to insert a drop down list in Excel 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 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 selection list in Excel so that the user selects the job instead of writing it manually.

Step 1: Prepare the source list.

Before you create drop a down menu in Excel, 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 and create list in Excel efficiently, 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 Excel dropdown 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 where you configure your Excel pick from drop down list settings.

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 Excel dropdown list in cell should now be active. Click on a cell to see a small arrow appear, then select it to excel choose from list.

Comparison of Drop-Down Methods

There are several ways to do a drop down list in Excel depending on your data needs. Here is a quick comparison to help you choose the best method.

Pro Tip: Create an Auto-Updating (Dynamic) Drop-Down List

The standard method above works great, but it has one flaw: if you add a new item to the bottom of your source list later, the drop down box in Excel won't automatically include it. You would have to update the range manually.

To fix this, turn your source list into an Excel Table:

1. Select your source list (e.g., the job titles in Sheet2).
2. Press Ctrl + T (or go to Insert > Table) and click OK.
3. Click inside the Data Validation Source box again.
4. Highlight the list items in the table (not the header). Excel will usually insert a specialized reference like =Table1[JobTitles].
   • Note: If Excel refuses the Table name reference directly in Data Validation (older versions), use the INDIRECT function: =INDIRECT("Table1[JobTitles]").

Now, whenever you type a new job title at the bottom of that table, your Excel drop down from list will instantly update to include it!

Using Spilled Arrays (Excel 365)

If you are using modern Excel functions like =UNIQUE() or =SORT() to generate your list, you can reference the entire "spilled" list using a single hash symbol (#).

1. Suppose your UNIQUE formula is in cell D1. The results "spill" down into D2, D3, etc.
2. In the Data Validation Source box, simply type: =$D$1#
3. This tells Excel to "look at D1 and everything that spills from it." If the formula results expand, your excel dropdown expands with them.

Alternative method: Enter list items directly.

Instead of referencing a range, you can type your items directly into the Source box to create options in Excel manually. 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 when creating dropdowns in Excel is that you can customize an input message to guide users and an error message to prevent invalid data entry.

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.
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 add picklist to Excel, creating an error message ensures data integrity. Here's how:

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:

Tip: How to Allow Users to Type Custom Text

Sometimes you want to suggest a list of options but still allow the user to type their own value (like "Other").

1. Go to the Error Alert tab in Data Validation.
2. Uncheck the box that says "Show error alert after invalid data is entered."
3. Click OK. Now users can pick from the list OR type a custom entry without receiving an error message.

How to Color-Code Your Drop-Down Selections

A common request is to have the cell change color based on the item selected (e.g., Green for "Approved," Red for "Rejected"). You can do this with Conditional Formatting.

1. Select the cell(s) containing your drop-down list.
2. Go to Home > Conditional Formatting > Highlight Cell Rules > Equal To...
3. Type the list item (e.g., "Approved") and choose a color (e.g., Green Fill).
4. Repeat this process for other items in your list. Now, your drop-down isn't just functional; it's visual!

How To Create a Dependent Drop Down List

A dependent drop down list is where options change based on a selection from another list. This is useful for hierarchical data like Category → Subcategory (e.g., selecting "Fruit" shows only fruits).

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" tab 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).

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.

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:

How to Edit or Change an Existing Drop-Down List

If you need to change drop down list in Excel, add items, or remove them, you don't need to start over. Here is how to update a drop down list in Excel:

1. Select the cell(s) containing the drop-down list.
2. Go to Data > Data Validation.
3. In the Source box, simply edit your range (e.g., change =$A$1:$A$5 to =$A$1:$A$10) or add new items to your comma-separated list.
4. Click OK.

Note: If you checked "Apply these changes to all other cells with the same settings," Excel will update every drop-down list that uses this source at once!

Quick Tip: The "Pick From Drop-down List" Feature

Did you know Excel has a hidden drop-down tool that requires zero setup? If you have a column of data (e.g., a list of cities in cells A1:A10) and you want to type one of those cities into cell A11, you don't need Data Validation.

1. Right-click the empty cell directly below your list.
2. Select Pick From Drop-down List... (or press Alt + Down Arrow).
3. Excel will automatically show a list of all unique values from the cells directly above.

Final Thoughts

Using excel set drop down list features 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. Using an Excel Table as your source is the best way to prevent this.

4. Can I enable searching in a drop-down list?

In newer versions of Excel (Excel 365), drop-down lists are searchable by default. As you type in the cell, the list filters to match your text. For older versions, you would need complex formulas or a "Combo Box" form control to achieve this.

5. Can I copy my drop-down list to other cells?

Yes. You can simply copy the cell with the drop-down (Ctrl + C) and paste it (Ctrl + V) into other cells. If you want to copy only the drop-down without changing the cell formatting, use Paste Special > Validation.

6. Can I use a drop-down list to control a chart?

Yes! This is a powerful feature for dashboards. By linking your drop-down list to a cell, and then driving your chart data from that linked cell (using formulas like XLOOKUP or INDEX/MATCH), you can create interactive charts that update instantly whenever a user selects a different option from the list.

7. How do I make a dynamic list without using Excel Tables?

If you cannot use Excel Tables (the preferred method), you can use the OFFSET function. You would define a Named Range using a formula like =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). This counts the non-blank cells in the column and adjusts the range height automatically, though it is more complex to set up than the Table method.