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.
Forget all user-generated errors! Drop-down lists 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.
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.
Steps to create a quick drop-down list
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:
1. Set up the list first. 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.
2. Select the position of the drop-down list, in the example, cell F2.
3. To apply data validation to cells, jump to the Data Tab and select Data validation.
4. Add data validation criteria! Select ‘List’ from the drop-down menu.
5. Click ‘Source’ and select the range B2:B5 to add a data source. Select the in-cell drop-down checkbox. Make sure that the in-cell Drop-down option is checked.
6. Click OK, and the list is ready-to-use.
Build a conditional (dependent) drop-down list
Sometimes 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 sections; 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.
Steps 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 appears. 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.
Select list in the data validation on the Settings tab. The source range is B1:C1.
Click OK. To create a second drop-down list, select cell F2, and apply data validation.
Use the formula below.
Finally, click OK.
Error handling using 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 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 next 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.