How to use and create dependent drop-down lists in Excel

Who I am
Pavel Yosifovich
@pavelyosifovich
Author and references

Furthermore, if that information is used by multiple people, this also allows for limit your answers or choices that such users can perform. Hence, it has already been established that such lists are advantageous, but it is important to know exactly what they are and how they work.

In this sense, drop-down lists, as the name indicates, are a series of data that are listed, to choose from. However, when it comes to dependent dropdown lists in Excel, it refers to one list being dependent on another in terms of options displayed.



That is, the information contained in the first delimits the fields that can be written in the second. After knowing this, it is important to learn how to do it. As we have already mentioned, what was changed in the first list will affect the options presented in the second, allowing for a permanent consistency of give.

Main reason for seeking implementation of this mode. But before you can start taking advantage of it, you need to have a few things previous.

Menu `The curtain

First of all, you need to have a separate sheet where you will create the drop down list independent which will be used in the rest of the file. We will call this sheet «Data1» Now, one of the best examples to illustrate the process of creating such content is the use of certain countries and their capitals.

We will start by doing a list of all the countries (cell A) we need, as well as the capitals (cell B) correctly related. The next thing you need is a single country list, for which you copy all the list data from cell A and paste it into another column, for example D.



Then, shading all the characters in column D, in the " Data »(In the top menu) in the" Data tools "section, the option" Remove duplicates ". With this, the guide list of countries is created, which must be correctly identified. To make the next step easier, it's a good idea to sort them alphabetically.

Subsequently, all components of the second list are shaded (in this case, capital letters) and to the whole a name or rank must be assigned (which must exactly match that of the first list, the country).

This assignment is done by clicking the right mouse button (with shaded objects) and selecting the option » Define name ". The range should have no accents, spaces, or differ in any way in any of your presentations. If space is required, place " An example «


How to create dependent drop down lists in Excel?

In a new sheet, with cell A2 selected, go to the tab » Data »And look for the option» Data validation «, In the« Data tools »area. A window will open, which is a wizard in which you need to define the "Allow" field, choose "List" and the field " Origin »Is where to enter the name of the range to use.


Choosing from the previously created ranges, the name must be preceded by the symbol " = "Followed by the nation, eg " = Spain ", And press" OK ”To save the values.

From now on, when it is in cell A2, the drop down list which has been linked will be displayed automatically, with the options available for it. This process should be repeated as many times as necessary to have the necessary drop-down lists that fit your needs.

add a comment of How to use and create dependent drop-down lists in Excel
Comment sent successfully! We will review it in the next few hours.