Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

Create dependent dropdown menus in Excel

In Excel, it may make sense to create a drop-down list that is based on a data source and also adapts dynamically instead of creating a very long list in rows or columns. You may also want to create 2 or more lists, with the list below each dependent on the previous one.

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

Create dependent dropdown menus in Microsoft Excel

Topic Overview

Anzeige

Create dependent dropdown menus in Excel

In Excel, it may make sense to create a drop-down list that is based on a data source and also adapts dynamically instead of creating a very long list in rows or columns. You may also want to create 2 or more lists, with the list below each dependent on the previous one.

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

Create dependent dropdown menus in Microsoft Excel

Topic Overview

Anzeige

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 dynamically design the data source (s) ourselves.

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)

Dynamische Tabelle in Excel erstellen
Tabellennamen in Excel vergeben
Tabellennamen in Excel anzeigen
Advertisement

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

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:

Dynamische Tabelle in Excel erstellen
Tabellennamen in Excel vergeben
Tabellennamen in Excel anzeigen
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 tab: “Data” on “data check”

There we select first as an option the data check “list”, mark our first list with the federal states as an associated data source, and confirm with “OK”.

See picture: (click to enlarge)

Dropdownliste in Excel einfügen
Datenquelle in Excel festlegen

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

For this purpose, we first mark the cell in which our second drop-down list should be.

  • Go over the “Data” tab again in the “Data Review”
  • And there select “list”.

In order 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: = INDIRECT (G3)
  • Wrong: = INDIRECT ($ G $ 3)
cshow
Ads

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

We therefore select a table in the first drop-down list, and the second drop-down list uses the “Indirect” function to see which entries exist in this table (exclusive heading), and only selects these according to the preceding entry.

See picture: (click to enlarge)

Abhängige Dropdownliste in Excel
Abhängige Dropdownliste

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 tab: “Data” on “data check”

There we select first as an option the data check “list”, mark our first list with the federal states as an associated data source, and confirm with “OK”.

See picture: (click to enlarge)

Dropdownliste in Excel einfügen
Datenquelle in Excel festlegen

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

For this purpose, we first mark the cell in which our second drop-down list should be.

  • Go over the “Data” tab again in the “Data Review”
  • And there select “list”.

In order 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: = INDIRECT (G3)
  • Wrong: = INDIRECT ($ G $ 3)
cshow
Ads

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

We therefore select a table in the first drop-down list, and the second drop-down list uses the “Indirect” function to see which entries exist in this table (exclusive heading), and only selects these according to the preceding entry.

See picture: (click to enlarge)

Abhängige Dropdownliste in Excel
Abhängige Dropdownliste

3. Extend a dynamic table in Excel

3. Extend a dynamic table in Excel

The big advantage of drop-down 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 when we remove an entry, it also disappears just as quickly.

To look forward to the o.g. For example, we would need 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 bottom right (colored reinforced edge) with pressed left mouse button.

Or by a new entry in an empty cell below the table.
(iD.R., the table is automatically expanded here, and a query appears if so desired.)

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

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

Blogverzeichnis Bloggerei.de

The big advantage of drop-down 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 when we remove an entry, it also disappears just as quickly.

To look forward to the o.g. For example, we would need 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 bottom right (colored reinforced edge) with pressed left mouse button.

Or by a new entry in an empty cell below the table.
(iD.R., the table is automatically expanded here, and a query appears if so desired.)

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

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

Blogverzeichnis Bloggerei.de

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top