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 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.
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)
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:
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)
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)
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)
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)
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)
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)
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.
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.
Popular Posts:
Ad-free home network: Install Pi-hole on Windows
Say goodbye to ads on smart TVs and in apps: Pi-hole software turns your Windows laptop into a network filter. This article explains step-by-step how to install it via Docker and configure the necessary DNS settings in your FRITZ!Box.
How to tune your FRITZ!Box into a professional call server
A professional telephone system can be built using a FRITZ!Box and a laptop. This article shows step by step how to use the free software "Phoner" to schedule announcements and record calls – including important legal information (§ 201 StGB).
Why to-do lists are a waste of time
Do you feel unproductive at the end of the day, even though you've worked hard? Your to-do list is to blame. It tempts you to focus on easy tasks and ignores your limited time. This article explains why lists are "self-deception" and why professionals use a calendar instead.
Smartphone Wi-Fi security: Public hotspots vs. home network
Is smartphone Wi-Fi a security risk? This article analyzes in detail threats such as evil twin attacks and explains protective measures for when you're on the go. We also clarify why home Wi-Fi is usually secure and how you can effectively separate your smart home from sensitive data using a guest network.
Warum dein Excel-Kurs Zeitverschwendung ist – was du wirklich lernen solltest!
Hand aufs Herz: Wann hast du zuletzt eine komplexe Excel-Formel ohne Googeln getippt? Eben. KI schreibt heute den Code für dich. Erfahre, warum klassische Excel-Trainings veraltet sind und welche 3 modernen Skills deinen Marktwert im Büro jetzt massiv steigern.
Cybersicherheit: Die 3 größten Fehler, die 90% aller Mitarbeiter machen
Hacker brauchen keine Codes, sie brauchen nur einen unaufmerksamen Mitarbeiter. Von Passwort-Recycling bis zum gefährlichen Klick: Wir zeigen die drei häufigsten Fehler im Büroalltag und geben praktische Tipps, wie Sie zur menschlichen Firewall werden.
Offers 2024: Word & Excel Templates
Popular Posts:
Ad-free home network: Install Pi-hole on Windows
Say goodbye to ads on smart TVs and in apps: Pi-hole software turns your Windows laptop into a network filter. This article explains step-by-step how to install it via Docker and configure the necessary DNS settings in your FRITZ!Box.
How to tune your FRITZ!Box into a professional call server
A professional telephone system can be built using a FRITZ!Box and a laptop. This article shows step by step how to use the free software "Phoner" to schedule announcements and record calls – including important legal information (§ 201 StGB).
Why to-do lists are a waste of time
Do you feel unproductive at the end of the day, even though you've worked hard? Your to-do list is to blame. It tempts you to focus on easy tasks and ignores your limited time. This article explains why lists are "self-deception" and why professionals use a calendar instead.
Smartphone Wi-Fi security: Public hotspots vs. home network
Is smartphone Wi-Fi a security risk? This article analyzes in detail threats such as evil twin attacks and explains protective measures for when you're on the go. We also clarify why home Wi-Fi is usually secure and how you can effectively separate your smart home from sensitive data using a guest network.
Warum dein Excel-Kurs Zeitverschwendung ist – was du wirklich lernen solltest!
Hand aufs Herz: Wann hast du zuletzt eine komplexe Excel-Formel ohne Googeln getippt? Eben. KI schreibt heute den Code für dich. Erfahre, warum klassische Excel-Trainings veraltet sind und welche 3 modernen Skills deinen Marktwert im Büro jetzt massiv steigern.
Cybersicherheit: Die 3 größten Fehler, die 90% aller Mitarbeiter machen
Hacker brauchen keine Codes, sie brauchen nur einen unaufmerksamen Mitarbeiter. Von Passwort-Recycling bis zum gefährlichen Klick: Wir zeigen die drei häufigsten Fehler im Büroalltag und geben praktische Tipps, wie Sie zur menschlichen Firewall werden.
Offers 2024: Word & Excel Templates
