The Excel drop-down list is a great tool that belongs to the data validation group. Can be used almost all of the making of interactive dashboard in Excel. Its main function is the limitation of data input, in a given cell we can only chose the elements of one fixed list.
The drop-down list can be imagined like a little menu from where we can only chose the value that was previously specified.
Just think about all the mistakes and errors we can avoid with the help of this method! There is no mistakenly entered name anymore, the specified list will not allow incorrect data entry.
In the first tutorial we will show in detail the creation of a drop-down list.
In the second part we will introduce the dependent (conditional) drop-down list. This will be a little more complicated task. Nonetheless don’t need to be afraid of the task! With the help of our step-by-step tutorial we will expound in minutes what is it all about!
Prepare the worksheet in seconds!
1: First of all we have to create a sheet with all the names of our products for the 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)
3: The Data Validation: Go to ribbon and select the “Data” tab and click it. Click the icon “Data validation” on the ribbon.
4: – In the pop-up window, we have to click the drop-down menu to select what kind of data validation method you 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
This will create a drop-down list that lists all the product names.
What is the dependent drop-down list?
It might happen that we need a solution that the simple list is not able to solve. How can the list be a two-level dependent list?
This means that there are two drop-down menus, and the elements appearing in the second one depending on the choices we made in the first one.
A little explanation:
Just think of a simple example. We have 20 kinds of different vegetables and 20 different fruits. We would like to classify all the elements into two categories. The first list contains two categories, these are the vegetable and fruit. In the second list only those elements will appear that are dependent on the first list.
So when the first category is fruit than amongst the elements of the second list we will not see for example the pepper, only the fruits. The vegetable category works exactly the same way.
Finally let’s see this method with the help of an example:
This example describes how to create dependent drop-down lists in Excel. Here’s what we have to do.
The user selects Fruits from a drop-down list.
As a result, a second drop-down list contains the Fruits items.
1: On the second sheet, create the following named ranges. Select the range then type name into the Name Box finally press enter.
2: On the first sheet, select cell C1.
3: Go to Data tab on the Ribbon, in the Data Tools group, click Data Validation.
The ‘Data Validation’ dialog box appears.
In the Allow box, click List. Click in the Source box and type =Food.
4: Click OK and check the result.
5: Next, select cell E2.
6: In the Allow box, click List.
7: Click in the Source box and type =INDIRECT($B$2).
Click OK. See the results on the picture below!
Just a few words about 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. =INDIRECT($B$1) returns the Fruits reference. As a result, the second drop-down lists contains the Fruits items.
Let’s assess what have we learned today! More and more people get to know and use the possibilities of data validation. Of these 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 everyone inputs the data in a different way? Only you can use it but would like to filter your own mistyping? Maybe you send the Excel table to partners and you want to be sure they fill out the correct information? Data validation is the solution of all these cases. Download the sample workbook!