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
- 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.
- Select the position of the drop-down list
Select cell F2.
- Apply Data Validation to cells
On the Data Tab, select Data validation.
- Add data validation criteria
Select ‘List’ from the drop-down menu.
- Add a data source
Click ‘Source’ and select the range B2:B5.
- Select the in-cell dropdown checkbox
Make sure that the in-cell Dropdown option is checked.
- 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.
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.
The ‘Create Named from Selection’ dialog box appear. Check the Top row option only. Click OK.
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.
Select cell E3 to place the first menu.
To display the data validation dialog box go to Data Tab and choose Data Validation.
First select List in the data validation on the Settings tab. The source range is B1:C1.
To create second drop-down list select cell F2 and apply data validation. Use ‘=INDIRECT($E$2)’ formula. Finally, click OK.
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:
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!)
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.
Now choose the data source. You have two options: use a predefined range or create a custom list.
The second example will show you how to create a drop-down list using a comma delimited text.
The result is the same.
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.