Create Linked Dropdown Lists in Excel - The Easiest Way!

PRESENT THE SITUATION: We want to create a small table in Excel where you can select a country and the corresponding city. At the same time, using drop-down lists, it is necessary to limit the options available to users of countries and cities from which they can choose. In the first cell, we will select a country, and in the second cell, only cities belonging to the selected country will be available. I think it's understandable?

So let's start our simple example with how you can create a linked (or dependent) drop down list in Excel? In a cell B1 we will choose a country, and in the cell B2- the city that belongs to her, as in the example:

First you need to create a database. On the second sheet, I entered a list of countries that I want to give users a choice in the first drop-down list, and in the adjacent column I indicated a numerical index that corresponds to one of the lists of cities. Lists of cities are located to the right in columns D, F And H. So, for example, next to France costs index 2 , which matches the list of cities 2 . Later you will see how this index will be used.

If you are working in Excel 2010, you can create a source sheet in a separate workbook. If you have a version of Excel 2003, and you plan to use a named range, then the values ​​\u200b\u200bmust be in the same workbook, or on a different sheet.

We will use named ranges and make these linked dropdowns work in all versions of Excel. The next step is to create named ranges for our lists. On the tab Formulas(Formulas) have a command name manager(name manager). Clicking on it will open a dialog box. name manager(name manager).

Click the button New(New) to add a new named range. A dialog box will open new name(Creating a name).

In field Name(Name) enter a name Country for our first named range, and in the field Refers to(Range) select the one that stores the list of countries:

Sheet3!$A$3:$A$5

Ranges containing cities can be named in exactly the same way.

Now we can create drop-down lists in those cells where we planned to select data. Highlight a cell B1(in it we will select the country), open the tab Data(Data), click Data Validation(Data Validation), and then from the drop-down menu select Data Validation(Data checking).

A dialog box will open Data Validation(Checking input values).

We want to give the user a list of options to choose from, so in the field allow(data type) select List(List). This will activate the field Source(Source), where you need to specify the name of the range with countries. Enter "=Country" in this field and click OK. Now we need to make a second dropdown so users can select a city. We will put this dropdown in a cell B2. And now attention - focus! We need to check the contents of the cell with the name of the country (cell B1) to get the index corresponding to the city database. If the user chooses Portugal, then we must refer to the base with the index 3 , which stores the names of cities in Portugal. We will use the function VLOOKUP(VLOOKUP) to look up a value from a cell B1 in a table with country names. Once the index is known, we will select a list that will be the data source for our second drop down list. To do this, we write the following formula:

CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=SELECT(VLOOKUP(B1;Sheet3!$A$3:$B$5;2;FALSE);England;France;Portugal)

What does this formula do? She is looking for a value from a cell B1 in the list of countries and returns the corresponding index, which is then used by the function CHOOSE(SELECT) to select the 1st, 2nd, or 3rd named range.

This is what our second dropdown will look like:

As a result, we will get two linked (or dependent) drop-down lists. If we choose a country France, in the linked list we will only have cities from France.

In this article, you learned how to make simple linked dropdown lists in Microsoft Excel. You can take this simple example and use it to solve real problems.