So erstellen Sie eine Dropdown-Liste in Excel (die einzige Anleitung, die Sie benötigen)

Eine Dropdown-Liste ist eine hervorragende Möglichkeit, dem Benutzer die Möglichkeit zu geben, aus einer vordefinierten Liste auszuwählen.

Es kann verwendet werden, um einen Benutzer dazu zu bringen, ein Formular auszufüllen, oder beim Erstellen interaktiver Excel-Dashboards.

Dropdown-Listen sind auf Websites/Apps weit verbreitet und für den Benutzer sehr intuitiv.

Video ansehen - Erstellen einer Dropdown-Liste in Excel

In diesem Tutorial erfahren Sie, wie Sie eine Dropdown-Liste in Excel erstellen (dies dauert nur wenige Sekunden) zusammen mit all den tollen Dingen, die Sie damit machen können.

So erstellen Sie eine Dropdown-Liste in Excel

In diesem Abschnitt erfahren Sie die genauen Schritte zum Erstellen einer Excel-Dropdown-Liste:

1. Verwenden von Daten aus Zellen.
2. Daten manuell eingeben.
3. Verwenden der OFFSET-Formel.

#1 Daten aus Zellen verwenden

Angenommen, Sie haben eine Liste von Elementen wie unten gezeigt:

Hier sind die Schritte zum Erstellen einer Excel-Dropdown-Liste:

1. Wählen Sie eine Zelle aus, in der Sie die Dropdown-Liste erstellen möchten.
2. Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
3. Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste als Validierungskriterium aus.
   • Sobald Sie Liste auswählen, erscheint das Quellfeld.
4. Geben Sie im Quellfeld =$A$2:$A$6 ein oder klicken Sie einfach in das Quellfeld und wählen Sie die Zellen mit der Maus aus und klicken Sie auf OK. Dadurch wird eine Dropdown-Liste in Zelle C2 eingefügt.
   • Stellen Sie sicher, dass die Dropdown-Option In der Zelle aktiviert ist (die standardmäßig aktiviert ist). Wenn diese Option deaktiviert ist, zeigt die Zelle kein Dropdown-Menü an, Sie können die Werte jedoch manuell in die Liste eingeben.

Notiz: Wenn Sie Dropdown-Listen in mehreren Zellen gleichzeitig erstellen möchten, wählen Sie alle Zellen aus, in denen Sie sie erstellen möchten, und führen Sie dann die obigen Schritte aus. Stellen Sie sicher, dass die Zellbezüge absolut sind (z. B. $A$2) und nicht relativ (z. B. A2 oder A$2 oder $A2).

#2 Durch manuelle Dateneingabe

Im obigen Beispiel werden Zellbezüge im Feld Quelle verwendet. Sie können Artikel auch direkt hinzufügen, indem Sie sie manuell in das Quellfeld eingeben.

Angenommen, Sie möchten zwei Optionen, Ja und Nein, in der Dropdown-Liste in einer Zelle anzeigen. So können Sie es direkt in das Feld Datenvalidierungsquelle eingeben:

• Wählen Sie eine Zelle aus, in der Sie die Dropdown-Liste erstellen möchten (in diesem Beispiel Zelle C2).
• Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
• Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste als Validierungskriterium aus.
  • Sobald Sie Liste auswählen, erscheint das Quellfeld.
• Geben Sie in das Quellfeld Yes, No . ein
  • Stellen Sie sicher, dass die Dropdown-Option In der Zelle aktiviert ist.
• OK klicken.

Dadurch wird eine Dropdown-Liste in der ausgewählten Zelle erstellt. Alle im Quellfeld aufgelisteten Elemente werden durch ein Komma getrennt in verschiedenen Zeilen im Dropdown-Menü aufgelistet.

Alle im Quellfeld eingegebenen Elemente werden durch ein Komma getrennt in verschiedenen Zeilen in der Dropdown-Liste angezeigt.

Notiz: Wenn Sie Dropdown-Listen in mehreren Zellen gleichzeitig erstellen möchten, wählen Sie alle Zellen aus, in denen Sie sie erstellen möchten, und führen Sie dann die obigen Schritte aus.

#3 Verwendung von Excel-Formeln

Abgesehen von der Auswahl aus Zellen und der manuellen Dateneingabe können Sie auch eine Formel im Quellfeld verwenden, um eine Excel-Dropdown-Liste zu erstellen.

Jede Formel, die eine Werteliste zurückgibt, kann verwendet werden, um eine Dropdown-Liste in Excel zu erstellen.

Angenommen, Sie haben den Datensatz wie unten gezeigt:

Hier sind die Schritte zum Erstellen einer Excel-Dropdown-Liste mit der OFFSET-Funktion:

• Wählen Sie eine Zelle aus, in der Sie die Dropdown-Liste erstellen möchten (in diesem Beispiel Zelle C2).
• Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
• Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste als Validierungskriterium aus.
  • Sobald Sie Liste auswählen, erscheint das Quellfeld.
• Geben Sie im Feld Quelle die folgende Formel ein: =OFFSET($A$2,0,0,5)
  • Stellen Sie sicher, dass die Dropdown-Option In der Zelle aktiviert ist.
• OK klicken.

Dadurch wird eine Dropdown-Liste erstellt, die alle Fruchtnamen auflistet (wie unten gezeigt).

Notiz: Wenn Sie eine Dropdown-Liste in mehreren Zellen auf einmal erstellen möchten, wählen Sie alle Zellen aus, in denen Sie sie erstellen möchten, und führen Sie dann die obigen Schritte aus. Stellen Sie sicher, dass die Zellbezüge absolut sind (z. B. $A$2) und nicht relativ (z. B. A2 oder A$2 oder $A2).

Wie funktioniert diese Formel??

Im obigen Fall haben wir eine OFFSET-Funktion verwendet, um die Dropdown-Liste zu erstellen. Es gibt eine Liste von Elementen aus dem ra . zurück

Es gibt eine Liste von Elementen aus dem Bereich A2:A6 zurück.

Hier ist die Syntax der OFFSET-Funktion: =OFFSET(Referenz, Zeilen, Spalten, [Höhe], [Breite])

Es braucht fünf Argumente, wobei wir die Referenz als A2 (den Startpunkt der Liste) angegeben haben. Zeilen/Spalten werden als 0 angegeben, da wir die Referenzzelle nicht versetzen möchten. Die Höhe wird mit 5 angegeben, da die Liste fünf Elemente enthält.

Wenn Sie diese Formel jetzt verwenden, gibt sie ein Array zurück, das die Liste der fünf Früchte in A2:A6 enthält. Beachten Sie, dass Sie, wenn Sie die Formel in eine Zelle eingeben, sie auswählen und F9 drücken, sehen, dass sie ein Array der Fruchtnamen zurückgibt.

Erstellen einer dynamischen Dropdown-Liste in Excel (mit OFFSET)

Die obige Technik der Verwendung einer Formel zum Erstellen einer Dropdown-Liste kann erweitert werden, um auch eine dynamische Dropdown-Liste zu erstellen. Wenn Sie die OFFSET-Funktion verwenden, wie oben gezeigt, wird das Dropdown-Menü nicht automatisch aktualisiert, auch wenn Sie der Liste weitere Elemente hinzufügen. Sie müssen es jedes Mal manuell aktualisieren, wenn Sie die Liste ändern.

Hier ist eine Möglichkeit, es dynamisch zu machen (und es ist nichts anderes als eine kleine Änderung in der Formel):

• Wählen Sie eine Zelle aus, in der Sie die Dropdown-Liste erstellen möchten (in diesem Beispiel Zelle C2).
• Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
• Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste als Validierungskriterium aus. Sobald Sie Liste auswählen, erscheint das Quellfeld.
• Geben Sie im Quellfeld die folgende Formel ein: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100”,”))
• Stellen Sie sicher, dass die Dropdown-Option In der Zelle aktiviert ist.
• OK klicken.

In dieser Formel habe ich das Argument 5 durch COUNTIF($A$2:$A$100””) ersetzt.

Die ZÄHLENWENN-Funktion zählt die nicht leeren Zellen im Bereich A2:A100. Daher passt sich die OFFSET-Funktion selbst an, um alle nicht-leeren Zellen einzuschließen.

Notiz:

• Damit dies funktioniert, dürfen sich zwischen den gefüllten Zellen KEINE leeren Zellen befinden.
• Wenn Sie eine Dropdown-Liste in mehreren Zellen gleichzeitig erstellen möchten, wählen Sie alle Zellen aus, in denen Sie sie erstellen möchten, und führen Sie dann die obigen Schritte aus. Stellen Sie sicher, dass die Zellbezüge absolut sind (z. B. $A$2) und nicht relativ (z. B. A2 oder A$2 oder $A2).

Kopieren und Einfügen von Dropdown-Listen in Excel

Sie können die Zellen mit der Datenvalidierung kopieren und in andere Zellen einfügen, und die Datenvalidierung wird ebenfalls kopiert.

Wenn Sie beispielsweise eine Dropdown-Liste in Zelle C2 haben und diese auch auf C3:C6 anwenden möchten, kopieren Sie einfach die Zelle C2 und fügen Sie sie in C3:C6 ein. Dadurch wird die Dropdown-Liste kopiert und in C3:C6 verfügbar gemacht (zusammen mit der Dropdown-Liste wird auch die Formatierung kopiert).

Wenn Sie nur das Dropdown-Menü und nicht die Formatierung kopieren möchten, gehen Sie wie folgt vor:

• Kopieren Sie die Zelle mit der Dropdown-Liste.
• Wählen Sie die Zellen aus, in die Sie das Dropdown-Menü kopieren möchten.
• Gehen Sie zu Home -> Einfügen -> Inhalte einfügen.
• Wählen Sie im Dialogfeld Inhalte einfügen die Option Validierung in den Einfügeoptionen aus.
• OK klicken.

Dadurch wird nur das Dropdown-Menü und nicht die Formatierung der kopierten Zelle kopiert.

Vorsicht beim Arbeiten mit der Excel-Dropdown-Liste

Sie müssen vorsichtig sein, wenn Sie mit Dropdown-Listen in Excel arbeiten.

Wenn Sie eine Zelle (die keine Dropdown-Liste enthält) über eine Zelle kopieren, die eine Dropdown-Liste enthält, geht die Dropdown-Liste verloren.

Das Schlimmste daran ist, dass Excel keine Warnung oder Aufforderung anzeigt, den Benutzer darüber zu informieren, dass ein Dropdown-Menü überschrieben wird.

So wählen Sie alle Zellen aus, die eine Dropdown-Liste enthalten

Manchmal ist es schwer zu wissen, welche Zellen die Dropdown-Liste enthalten.

Daher ist es sinnvoll, diese Zellen zu markieren, indem Sie ihnen entweder einen deutlichen Rahmen oder eine Hintergrundfarbe geben.

Anstatt alle Zellen manuell zu überprüfen, gibt es eine schnelle Möglichkeit, alle Zellen auszuwählen, die Dropdown-Listen (oder eine Datenvalidierungsregel) enthalten.

• Gehen Sie zu Home -> Suchen & Auswählen -> Gehe zu Special.
• Wählen Sie im Dialogfeld Gehe zu Spezial die Option Datenvalidierung
  • Die Datenvalidierung hat zwei Optionen: Alle und Gleich. Alle würden alle Zellen auswählen, auf die eine Datenvalidierungsregel angewendet wurde. Dasselbe würde nur die Zellen auswählen, die dieselbe Datenvalidierungsregel wie die der aktiven Zelle haben.
• OK klicken.

Dies würde sofort alle Zellen auswählen, auf die eine Datenvalidierungsregel angewendet wurde (dies umfasst auch Dropdown-Listen).

Jetzt können Sie die Zellen einfach formatieren (einen Rahmen oder eine Hintergrundfarbe geben), damit sie optisch sichtbar sind und Sie nicht versehentlich eine andere Zelle darauf kopieren.

Hier ist eine weitere Technik von Jon Acampora, mit der Sie das Dropdown-Pfeilsymbol immer sichtbar halten können. In diesem Video von Mr. Excel können Sie auch einige Möglichkeiten sehen, dies zu tun.

Erstellen einer abhängigen / bedingten Excel-Dropdown-Liste

Hier ist ein Video zum Erstellen einer abhängigen Dropdown-Liste in Excel.

Wenn Sie lieber lesen als sich ein Video anzusehen, lesen Sie weiter.

Manchmal haben Sie möglicherweise mehr als eine Dropdown-Liste und möchten, dass die in der zweiten Dropdown-Liste angezeigten Elemente davon abhängen, was der Benutzer in der ersten Dropdown-Liste ausgewählt hat.

Diese werden als abhängige oder bedingte Dropdown-Listen bezeichnet.

Unten ist ein Beispiel für eine bedingte/abhängige Dropdown-Liste:

Im obigen Beispiel, wenn die in „Drop Down 2“ aufgelisteten Elemente von der in „Drop Down 1“ getroffenen Auswahl abhängen.

Sehen wir uns nun an, wie Sie dies erstellen.

Hier sind die Schritte zum Erstellen einer abhängigen / bedingten Dropdown-Liste in Excel:

• Wählen Sie die Zelle aus, in der die erste (Haupt-) Dropdown-Liste angezeigt werden soll.
• Gehen Sie zu Daten -> Datenvalidierung. Dadurch wird das Dialogfeld zur Datenvalidierung geöffnet.
• Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste aus.
• Geben Sie im Feld Quelle den Bereich an, der die Elemente enthält, die in der ersten Dropdown-Liste angezeigt werden sollen.
• OK klicken. Dadurch wird das Dropdown-Menü 1 erstellt.
• Wählen Sie den gesamten Datensatz aus (A1:B6 in diesem Beispiel).
• Gehen Sie zu Formeln -> Definierte Namen -> Aus Auswahl erstellen (oder verwenden Sie die Tastenkombination Strg + Umschalt + F3).
• Aktivieren Sie im Dialogfeld "Namen aus Auswahl erstellen" die Option Obere Zeile und deaktivieren Sie alle anderen. Dadurch werden 2 Namensbereiche erstellt („Obst“ und „Gemüse“). Der benannte Bereich Obst bezieht sich auf alle Früchte in der Liste und der benannte Bereich Gemüse bezieht sich auf alle Gemüse in der Liste.
• OK klicken.
• Wählen Sie die Zelle aus, in der die Dropdown-Liste Abhängig/Bedingt angezeigt werden soll (in diesem Beispiel E3).
• Gehen Sie zu Daten -> Datenvalidierung.
• Stellen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen sicher, dass Liste ausgewählt ist.
• Geben Sie im Feld Quelle die Formel =INDIREKT(D3) ein. Hier ist D3 die Zelle, die das Haupt-Dropdown enthält.
• OK klicken.

Wenn Sie nun die Auswahl in Drop-Down 1 treffen, werden die in Drop-Down-Liste 2 aufgelisteten Optionen automatisch aktualisiert.

Laden Sie die Beispieldatei herunter

Wie funktioniert das? - Die bedingte Dropdown-Liste (in Zelle E3) bezieht sich auf =INDIREKT(D3). Das heißt, wenn Sie in Zelle D3 „Früchte“ auswählen, verweist die Dropdown-Liste in E3 auf den benannten Bereich „Früchte“ (über die INDIREKTE Funktion) und listet daher alle Artikel in dieser Kategorie auf.

Wichtiger Hinweis beim Arbeiten mit bedingten Dropdown-Listen in Excel:

• Wenn Sie die Auswahl getroffen haben und dann die übergeordnete Dropdown-Liste ändern, würde sich die abhängige Dropdown-Liste nicht ändern und wäre daher ein falscher Eintrag. Wenn Sie beispielsweise die USA als Land und dann Florida als Bundesstaat auswählen und dann zurückgehen und das Land in Indien ändern, bleibt der Bundesstaat Florida. Hier ist ein großartiges Tutorial von Debra zum Löschen abhängiger (bedingter) Dropdown-Listen in Excel, wenn die Auswahl geändert wird.
• Besteht die Hauptkategorie aus mehr als einem Wort (z. B. 'Saisonal Fruits' statt 'Obst'), dann müssen Sie die Formel =INDIRECT(SUBSTITUTE(D3“, „“, _“)) anstelle des . verwenden oben gezeigte einfache INDIREKTE Funktion. Der Grund dafür ist, dass Excel keine Leerzeichen in benannten Bereichen zulässt. Wenn Sie also einen benannten Bereich mit mehr als einem Wort erstellen, fügt Excel automatisch einen Unterstrich zwischen den Wörtern ein. Die als „Seasonal Fruits“ benannte Range wäre also „Seasonal_Fruits“. Die Verwendung der SUBSTITUTE-Funktion innerhalb der INDIRECT-Funktion stellt sicher, dass Leerzeichen sind in Unterstriche umgewandelt.