Drop-down list in Excel

Using the drop-down list, we can choose elements from a predefined list. It is beneficial when for example we would like to show the efficiency of different departments within one company. It is worth using because when choosing its elements, the dashboard automatically refreshes and likewise the connecting charts. We cannot press it enough times that we save a considerable amount of space that is a great value when we create an excel dashboard.

Forget all user-generated errors! Drop-down list help us to avoid incorrect data entry! In the first example, you’ll learn how to create a drop-down list. After that, we will introduce the conditional drop-down list. Finally, we’ll show you how to use our free add-in to create a drop-down list using dynamic named ranges.

Steps to create a quick drop-down list in Excel

It’s a simple process to create this data validation feature. Because data validation is a built-in feature in Excel, we’ll create a list (source data) and an input cell for data entry. The tutorial shows how to implement a drop-down list in a Worksheet.

Click the drop-down list to display a list of items from “B2:B5”. If we try to enter something that isn’t on the list, we can not enter a new record. That’s the point!

To create a drop-down list to a Worksheet, follow these steps:

Time needed: 3 minutes.

Steps to create drop-down list in Excel

  1. Set up the list

    Create the list in cells B2:B5. You can choose a row as a range, for example, B2:E2. In this case, you should use the transpose function.
    setup the drop-down list 01

  2. Select the position of the drop-down list

    Select cell F2.select drop-down list place 02

  3. Apply Data Validation to cells

    On the Data Tab, select Data validation.data validation ribbon 03

  4. Add data validation criteria

    Select ‘List’ from the drop-down menu.select list option 04

  5. Add a data source

    Click ‘Source’ and select the range B2:B5. select drop-down list source 05

  6. Select the in-cell dropdown checkbox

    Make sure that the in-cell Dropdown option is checked.check drop-down list option 06

  7. Click OK

    The list is ready to use.

Build a conditional (dependent) drop-down list

In some cases, we want to use the conditional drop-down list.

Explanation: We have two lists, and the items are appearing in the second drop-down list depending on the selection we built in the first menu. This solution can be useful if you want to use classification in Excel.

For example, the first list contains 4 kinds of shoes; the second list contains 4 kinds of t-shirts. We want to divide all the elements into two section; Shoes and Shirts. The first list is the category selector. You can choose only those elements from the second list that are dependent on the first selection.

The options in the Products depend on the selection applied in the category. If you choose ‘Shoes’ in List1, you’ll see the shoe types. If you select ‘Shirts’ in List1, then the shirt types will be displayed in the Product list.

final drop-down llist 01

This tutorial explains how to create a dependent drop-down list in Excel.

Select the list headers and create names from the selection. This function generates names from the selected cells automatically.

create names from selection 02

The ‘Create Named from Selection’ dialog box appear. Check the Top row option only. Click OK.

create selection for headers 03

Now we have two names ranges (‘Shirts’ and ‘Shoes’). Shirts named range refers to all the shirt types in the list. Shoes named range refers to all the shoe types in the list.

created named ranges 04

Select cell E3 to place the first menu.

dependent-drop-down-list-05

To display the data validation dialog box go to Data Tab and choose Data Validation.

data-validation-06

First select List in the data validation on the Settings tab. The source range is B1:C1.

data validation source 07

Click OK.

To create second drop-down list select cell F2 and apply data validation. Use ‘=INDIRECT($E$2)’ formula. Finally, click OK.

apply indirect formula 08

How to handle errors? – Use the INDIRECT and SUBSTITUTE formulas

Explanation of INDIRECT() formula: The INDIRECT function gets the reference specified by a text string. If we select Shoes from the first list then the formula ‘=INDIRECT($E$2)’ returns the second lists contain the shoe types.

We would like to avoid some frustrating errors regarding multi-word category names. For example, if we want to create a two words category; ‘Running Shoes’ instead of ‘Shoes.’, it’s necessary to apply a different formula. In this case, we should use a combination of the INDIRECT formula and SUBSTITUTE formula.

Keep in mind, using spaces in named ranges is not allowed. The best solution to convert spaces into underscores:

=INDIRECT(SUBSTITUTE($E$2,” “,”_”))

This formula will replace the spaces and help to prevent unwanted issues.

A faster, automated way to create a drop-down list

If you are in a hurry, we can help you! Install our spreadsheet tools and create a drop-down list using a single userform. (the add-in will be available in a few days!)

use out free spreadsheet tools

As first, please check and enable the developer tab, if it does not appear. Go to the DataXL tab on the ribbon and choose the drop-down list icon from the Cells group.

A small popup window will appear. For example, select the cell B2 to place the list.

select the cell where create list 02

Now choose the data source. You have two options: use a predefined range or create a custom list.

create drop-down-list from range 03

The second example will show you how to create a drop-down list using a comma delimited text.

drop-down-list from custom list 04

The result is the same.

Conclusion

Drop-down lists are useful if you want to select an item from a fixed list. It’s an effective way to create forms using a graphically way and forcing users to choose a value from a list.