Contains paid promotion

Advertisement

How to Create Dependent Dropdown Menus
in Excel 2016

In Excel, it may be useful to create a drop-down list that is based on a data source and dynamically adapts to a very long list in rows or columns.

But you may also want to create 2 or more lists, with the list below each dependent on the previous one.

Abhängige Dropdownlisten in Excel

How to create interdependent dropdown with dynamic source customization in Excel 2016 can be found in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

How to Create Dependent Dropdown Menus in Excel 2016

In Excel, it may be useful to create a drop-down list that is based on a data source and dynamically adapts to a very long list in rows or columns.

But you may also want to create 2 or more lists, with the list below each dependent on the previous one.

Abhängige Dropdownlisten in Excel

How to create interdependent dropdown with dynamic source customization in Excel 2016 can be found in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Create a dynamic table in Excel.

1. Create a dynamic table in Excel.

So that our dropdown list adapts dynamically to the data source, we first have to design the data source (s) dynamically.

In our first example we compile a list of states and (part of) their respective cities.

  • We mark our respective list.
  • Then click in the tab: “Paste” on “Table
  • And put there also the hook with “table has headings“.

Then we assign a name for each of our tables, which should match the title of each table.

See picture: (click to enlarge)

Create a dynamic spreadsheet in Excel
Assign table names in Excel
Show table names in Excel
Advertisement

So that our dropdown list adapts dynamically to the data source, we first have to design the data source (s) dynamically.

In our first example we compile a list of states and (part of) their respective cities.

  • We mark our respective list.
  • Then click in the tab: “Paste” on “Table
  • And put there also the hook with “table has headings“.

Then we assign a name for each of our tables, which should match the title of each table.

See picture:

Create a dynamic spreadsheet in Excel
Assign table names in Excel
Show table names in Excel
Advertisement

2. Create a drop-down list in Excel.

2. Create a drop-down list in Excel.

In order to create our first drop down list for the selection of the federal states click into the desired cell in which the selection is to be available, and then click in the register: “Data” on “data check

There, we first select “List” as an option for the data validation, mark our first list with the states as the corresponding data source, and confirm with “OK“.

See picture (click to enlarge)

Insert dropdown list in Excel
Set data source in Excel

In the next step, we limit the choices of the dependent drop-down list by referring to our previously set dynamic tables.

  • To do this, we first mark the cell in which our second drop-down list should be.
  • Go through the “Data” tab again in the “Data Review
  • And select “list” there.

To set the data source depending on the selection made earlier, we must use a small function and enter “= INDIRECT (G3)” in the data source field.
(we select the cell in which the upstream dropdown selection takes place)

It is important that we enter the cell as relative and not as absolute reference!

Correct: =INDIREKT(G3)
Wrong: =INDIREKT($G$3)

Advertisement

With this function, the list refers to the previously created tables.

So we select a table in the first drop-down list, and the second drop-down list uses the “Indirect” function to see which entries in this table (exclusive heading) exist, and also only selects them according to the upstream entry.

See picture: (click to enlarge)

Dependent drop-down list in Excel
Dependent drop-down list

In order to create our first drop down list for the selection of the federal states click into the desired cell in which the selection is to be available, and then click in the register: “Data” on “data check

There, we first select “List” as an option for the data validation, mark our first list with the states as the corresponding data source, and confirm with “OK“.

See picture:

Insert dropdown list in Excel
Set data source in Excel

In the next step, we limit the choices of the dependent drop-down list by referring to our previously set dynamic tables.

  • To do this, we first mark the cell in which our second drop-down list should be.
  • Go through the “Data” tab again in the “Data Review
  • And select “list” there.

To set the data source depending on the selection made earlier, we must use a small function and enter “= INDIRECT (G3)” in the data source field.
(we select the cell in which the upstream dropdown selection takes place)

It is important that we enter the cell as relative and not as absolute reference!

Correct: =INDIREKT(G3)
Wrong: =INDIREKT($G$3)

Advertisement

With this function, the list refers to the previously created tables.

So we select a table in the first drop-down list, and the second drop-down list uses the “Indirect” function to see which entries in this table (exclusive heading) exist, and also only selects them according to the upstream entry.

See picture:

Dependent drop-down list in Excel
Dependent drop-down list

3. Extend a dynamic table in Excel.

3. Extend a dynamic table in Excel.

The big advantage of dropdown menus related to dynamic lists is that they automatically adapt to the length of the list entries.

As soon as we add an entry, it will be available in the selection. And if we remove an entry, it also disappears just as quickly.

To look forward to the o.g. For example, we would have to refer to, for example, Add another state to create a new table (renamed), and easily include it in the drop-down selection.

To add new cities to existing ones, we extend the table either by:

  • Press the TAB key in the last cell of the table.
  • By dragging the list at the lower right (colored edge) while holding down the left mouse button.
  • Or by a new entry in an empty cell below the table.
    (here i.d.R the table is automatically extended and a query appears, if this was desired.)

Of course, this procedure of the dependent drop-down lists can be continued further and further.

So we could next, for example, depending on the city, still on the municipalities, and then on street directories continue to filter.

Print Friendly, PDF & Email

The big advantage of dropdown menus related to dynamic lists is that they automatically adapt to the length of the list entries.

As soon as we add an entry, it will be available in the selection. And if we remove an entry, it also disappears just as quickly.

To look forward to the o.g. For example, we would have to refer to, for example, Add another state to create a new table (renamed), and easily include it in the drop-down selection.

To add new cities to existing ones, we extend the table either by:

  • Press the TAB key in the last cell of the table.
  • By dragging the list at the lower right (colored edge) while holding down the left mouse button.
  • Or by a new entry in an empty cell below the table.
    (here i.d.R the table is automatically extended and a query appears, if this was desired.)

Of course, this procedure of the dependent drop-down lists can be continued further and further.

So we could next, for example, depending on the city, still on the municipalities, and then on street directories continue to filter.

Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T11:57:00+00:00By |Categories: Excel 2016, Office 2016|
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok