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.

Moreover, it is worth using because when choosing its elements, the dashboard and, likewise, the connecting charts automatically refresh. Its main function is to limit data input; in a given cell, we can only choose the elements of one fixed list. The drop-down list can be imagined as a little menu from which we can only choose the previously specified value.

How to create a drop-down list in Excel?

To create a drop-down list, follow the steps below:

  1. Select the cell where you want the drop-down list to appear.
  2. Go to the Data tab in the ribbon.
  3. Click on Data Validation.
  4. In the dialog box, set the Allow box to List.
  5. In the Source box, select a range of cells containing the values.
  6. Click OK to create the drop-down list.

Steps to create a drop-down list

1. First, we have to create a sheet with all the names of our products for further calculations.

2: Create a new sheet, ’Drop-down list’, then create the drop-down menu in cell D2. Select a cell where you want to put the drop-down list (cell D2 in our example)

drop-down-list-02

3: The Data Validation: Go to the ribbon and select the “Data” tab and click it. Click the icon “Data validation” on the ribbon.

drop-down-list-03

4: – In the pop-up window, we have to click the drop-down menu to select the data validation method we want to use. Okay, choose “List” from the menu.

There are more validation options: (Any value, Whole number, Decimal, Date, Time, Text length, and Custom)

In the Source field, add a range =Sheet1!$B$2:$B$6

Click OK.

drop-down-list-04

This will create a drop-down list that lists all the product names.

drop-down-list-05

Build a conditional (dependent) drop-down list

We might need a solution that the simple list cannot solve. For example, how can the list be a two-level dependent list?

This means that there are two drop-down menus, and the elements appear in the second one depending on the choices we made in the first one.

Explanation:

Just think of a simple example. We have 20 kinds of different vegetables and 20 different fruits. We want to classify all the elements into two categories. The first list contains two types: vegetables and fruit. Only those elements dependent on the first list will appear in the second list.

So when the first category is fruit, amongst the elements of the second list, we will not see, for example, the pepper, only the fruits. The vegetable category works the same way. Finally, let’s examine this method using an example; we describe how to create dependent drop-down lists in Excel.

The user selects Fruits from a drop-down list.

drop-down-list-D-06

As a result, a second drop-down list contains the Fruits items.

drop-down-list-D-07

1: On the second sheet, create the following named ranges. Select the range, type a name into the Name Box, and press enter.

drop-down-list-D-08

2: On the first sheet, select cell C1.

3: Go to the Data tab on the ribbon; click Data Validation in the Data Tools group.

drop-down-list-D-09

The ‘Data Validation’ dialog box appears.
In the Allow box, click List. Next, click in the Source box and type =Food.

drop-down-list-D-10

4: Click OK and check the result.

drop-down-list-D-11

5: Next, select cell E2.
6: In the Allow box, click List.
7: Click the Source box and type =INDIRECT($B$2).

drop-down-list-D-12

Click OK. See the results in the picture below!

drop-down-list-D-07

Just a few words about the INDIRECT() function:

The INDIRECT function returns the reference specified by a text string. For example, the user selects Fruits from the first drop-down list. Therefore, =INDIRECT($B$1) returns the Fruits reference. As a result, the second drop-down list contains the Fruits items.

Conclusion

Let’s assess what we have learned today! First, more and more people are getting to know and using the possibilities of data validation. The most common is the drop-down regulation, when we prescribe the input or choices of a roll-down menu that can be entered into a chosen cell.

Do many people use the file, and does everyone input the data differently? Only you can use it, but would you like to filter your mistyping? Maybe you can send the Excel table to partners to ensure they fill out the correct information. Data validation is the solution to all these cases.

Download the practice file.

Related articles: