Excel Dropdown erstellen: Auswahlliste in 4 Schritten (mit Schichtkürzeln)

Wozu eine Dropdown-Liste im Dienstplan?

Excel unterscheidet nicht zwischen „F“, „f“, „Fr“ und „Früh“ – für eine Formel wie ZÄHLENWENN sind das vier verschiedene Werte. In einem Plan, den mehrere Personen über Monate pflegen, entstehen solche Varianten zwangsläufig, und mit ihnen falsche Stundensummen, Lücken in der Besetzungszählung und Farbregeln, die nicht greifen. Ein Dropdown setzt eine Stufe früher an: Es legt fest, welche Werte in einer Zelle überhaupt stehen dürfen, und bietet sie per Mausklick an.

Technisch ist das Dropdown kein eigenes Steuerelement, sondern eine Regel der Datenüberprüfung mit dem Kriterium Liste. Daraus folgen zwei Eigenschaften, die man kennen sollte: Die Regel hängt an der Zelle und wandert beim Kopieren mit – und sie prüft nur, was getippt oder ausgewählt wird. Wer Werte aus einer anderen Datei einfügt, umgeht sie. Darauf kommt der Abschnitt zu den häufigen Fehlern zurück.

Excel Dropdown erstellen in 4 Schritten

Die folgende Anleitung gilt für Excel unter Windows (Microsoft 365, 2021, 2024) und in gleicher Abfolge für Excel für Mac und Excel für das Web. Ausgangspunkt ist ein Dienstplan mit den Planzellen C5:AG14 und einem zweiten Blatt namens Listen.

1. Liste anlegen. Auf dem Blatt Listen die erlaubten Kürzel untereinander eintragen, ohne Leerzeilen, etwa A2:A6 mit F, S, N, U und K. Microsoft empfiehlt, die Einträge auf einem eigenen Blatt und idealerweise als Excel-Tabelle zu führen: eine Zelle im Bereich markieren und Strg+T drücken.
2. Zielzellen markieren und Datenüberprüfung öffnen. Auf dem Planblatt den Bereich C5:AG14 markieren, dann Daten → Datenüberprüfung wählen. Ist der Befehl ausgegraut, ist das Blatt laut Microsoft vermutlich geschützt oder freigegeben.
3. Liste als Kriterium wählen. Auf der Registerkarte Einstellungen unter Zulassen den Eintrag Liste auswählen. Die Kontrollkästchen Leere Zellen ignorieren und Zellendropdown aktiviert lassen – ohne Letzteres funktioniert die Prüfung, aber der Auswahlpfeil fehlt.
4. Quelle angeben. In das Feld Quelle klicken und auf dem Blatt Listen die Zellen A2:A6 markieren – ohne die Überschrift. Excel trägt =Listen!$A$2:$A$6 ein. Mit OK bestätigen.

Ab jetzt zeigt jede Planzelle beim Anklicken einen Pfeil; unter Windows öffnet Alt+Pfeil nach unten die Liste auch ohne Maus. Die übrigen zwei Registerkarten des Dialogs – Eingabemeldung und Fehlermeldung – sind optional, für einen gemeinsam genutzten Plan aber dringend zu empfehlen. Sie werden weiter unten beschrieben.

Woher die Liste kommt: vier Arten von Quellen

Das Feld Quelle akzeptiert mehrere Formen. Welche die richtige ist, hängt vor allem davon ab, wie oft sich die Liste ändert und wer sie pflegt.

Direkt eingetippte Werte sind schnell, aber unübersichtlich, sobald sich etwas ändert. Das Trennzeichen ist das Listentrennzeichen der Ländereinstellung: In deutschsprachigem Excel ist das in aller Regel das Semikolon, wie es Microsofts deutsche Anleitung zur Datenüberprüfung auch nennt; englischsprachige Anleitungen zeigen das Komma. Leerzeichen nach dem Trennzeichen werden Teil des Eintrags – „F; S“ erzeugt die Option „ S“ mit führendem Leerzeichen, die später in keiner Formel mehr trifft.

Ein benannter Bereich entsteht über Formeln → Namen definieren oder schneller, indem man die Listenzellen markiert und links neben der Bearbeitungsleiste im Namenfeld Kuerzel eintippt. Der Vorteil: Dieselbe Bezeichnung lässt sich in der Datenüberprüfung, in ZÄHLENWENN und in Regeln der bedingten Formatierung verwenden, und eine Änderung am Bezug wirkt überall. Namen dürfen keine Leerzeichen enthalten und nicht wie ein Zellbezug aussehen – F1 wäre als Name unzulässig.

Die Excel-Tabelle ist für einen Dienstplan meist die beste Wahl. Microsoft beschreibt es ausdrücklich: Wenn die Listeneinträge in einer Tabelle stehen, werden alle darauf beruhenden Dropdowns beim Hinzufügen oder Entfernen von Einträgen automatisch aktualisiert. Wer eine neue Schicht „M“ für Mitteldienst einführt, schreibt sie in die erste freie Zeile unter der Tabelle – fertig. Ein Hinweis aus der Praxis: Einen strukturierten Verweis wie =tblSchichten[Kürzel] nimmt das Quellfeld nicht direkt an. Entweder man markiert die Datenzellen der Spalte mit der Maus, oder man schreibt =INDIREKT("tblSchichten[Kürzel]").

Dynamische Dropdown-Liste mit EINDEUTIG und dem #-Operator

Manchmal steht die Liste nicht als gepflegte Stammdatenliste bereit, sondern soll aus vorhandenen Daten entstehen – etwa die Namen aller Mitarbeitenden aus einer Personaltabelle, in der jede Person mehrfach vorkommt, oder nur die aktiven Schichtkürzel. Ab Microsoft 365 sowie Excel 2021 und 2024 gibt es dafür die dynamischen Arrayfunktionen.

Auf dem Blatt Listen erzeugt die Formel in D2

=SORTIEREN(EINDEUTIG(tblPersonal[Name]))

eine alphabetische Liste ohne Dubletten, die nach unten in so viele Zellen „überläuft“, wie es Namen gibt. Als Quelle der Datenüberprüfung trägt man dann =Listen!$D$2# ein. Das Rautezeichen ist der Operator für übergelaufene Bereiche: Er verweist laut Microsoft auf den gesamten Überlaufbereich einer Arrayformel, auch wenn dieser wächst oder schrumpft. Kommt eine neue Person in die Personaltabelle, erscheint sie im Dropdown ohne weiteres Zutun.

Drei Dinge sind dabei zu beachten. Erstens braucht die Formel freie Zellen unter sich – steht dort schon etwas, liefert sie #ÜBERLAUF! und das Dropdown ist leer. Zweitens darf die Formel nicht innerhalb einer Excel-Tabelle stehen, denn Tabellen unterstützen keine überlaufenden Arrayformeln; sie gehört in einen normalen Bereich daneben. Drittens funktioniert der Weg nur in Versionen mit dynamischen Arrays. Wird die Datei in Excel 2019 oder älter geöffnet, ist die intelligente Tabelle der robustere Weg.

Mit FILTER lässt sich die Liste weiter eingrenzen. =SORTIEREN(FILTER(tblPersonal[Name];tblPersonal[Status]="aktiv")) zeigt im Dropdown nur Personen, die nicht ausgeschieden sind – ausgeschiedene Kolleginnen bleiben in der Personaltabelle erhalten, tauchen in neuen Plänen aber nicht mehr auf.

Praxisbeispiel: Dienstplan mit den Kürzeln F, S, N, U und K

Für einen Monatsplan mit drei Schichten und zwei Abwesenheitsarten genügt eine Legende mit fünf Zeilen. Sie steht auf dem Blatt Listen als Tabelle mit dem Namen tblSchichten (umbenennbar unter Tabellenentwurf → Tabellenname):

Die Zeiten und Nettostunden sind Beispielwerte; jeder Betrieb trägt seine eigenen Schichtzeiten ein. Die Planfläche C5:AG14 erhält das Dropdown mit den Datenzellen der Spalte Kürzel als Quelle, die Fehlermeldung vom Typ Stopp und eine Eingabemeldung mit der Kurzlegende. Damit ist sichergestellt, dass in der Planfläche nur die fünf Kürzel – oder nichts – stehen.

Weil jede Zelle jetzt einen eindeutigen Wert hat, funktionieren die Auswertungen ohne Sonderfälle. =ZÄHLENWENN($C5:$AG5;"N") zählt die Nachtdienste einer Person im Monat, =ZÄHLENWENN(C$5:C$14;"F") die Frühdienste eines Tages, und die Stunden je Zelle zieht =XVERWEIS(C5;tblSchichten[Kürzel];tblSchichten[Nettostunden];0) aus der Legende. Wie die Summen- und Stundenspalten im Einzelnen aufgebaut werden, beschreibt der Beitrag Dienstplan in Excel erstellen; die Grundlagen für Zeiten über Mitternacht stehen in Arbeitsstunden in Excel korrekt summieren.

Ein Wort zu den Abwesenheitskürzeln: Der Plan zeigt, dass jemand fehlt. Ob ein Ausdruck des Plans am Schwarzen Brett ein „K“ zeigen sollte, ist eine Datenschutzfrage – Gesundheitsdaten sind nach Art. 9 DSGVO besonders geschützt. Viele Betriebe verwenden deshalb für den Aushang ein neutrales Kürzel für jede Abwesenheit und führen die Unterscheidung nur in der internen Datei.

Eingabemeldung und Fehlermeldung einrichten

Die Eingabemeldung erscheint als kleines Hinweisfeld, sobald jemand eine Zelle mit Dropdown anklickt. Sie eignet sich für eine Kurzlegende oder einen Hinweis wie „Tausch nur nach Rücksprache mit der Leitung“. Microsoft nennt für Titel und Text eine Länge von bis zu 225 Zeichen. Die Meldung verschwindet, wenn die Zelle verlassen oder Esc gedrückt wird.

Die Fehlermeldung bestimmt, was passiert, wenn jemand einen Wert eintippt, der nicht in der Liste steht. Excel kennt drei Typen:

Für die Planfläche ist Stopp der einzig sinnvolle Typ. Warnung und Information lassen abweichende Werte durch, und genau diese Werte sind es, die später Summen und Farben verfälschen. Ein eigener Fehlertext hilft mehr als die Standardmeldung: „Bitte nur F, S, N, U oder K eintragen – neue Schichten über die Legende auf dem Blatt Listen anlegen“ sagt der Person am Bildschirm, was sie tun soll.

Abhängige Dropdowns mit INDIREKT

Arbeitet ein Betrieb mit mehreren Bereichen, die unterschiedliche Schichten kennen, kann das zweite Dropdown vom ersten abhängen: Erst wird der Bereich gewählt, dann zeigt die Schichtzelle nur die Kürzel dieses Bereichs. Das Werkzeug dafür ist die Funktion INDIREKT, die laut Microsoft einen als Text angegebenen Bezug in einen echten Bezug umwandelt.

1. Auf dem Blatt Listen für jeden Bereich eine Spalte mit dessen Kürzeln anlegen, etwa Station mit F, S, N, Kueche mit F1, F2, S und Empfang mit F, M, S.
2. Jede dieser Spalten als benannten Bereich definieren – der Name muss exakt dem Bereichsnamen entsprechen, also Station, Kueche, Empfang. Leerzeichen und Bindestriche sind in Namen nicht erlaubt; „Küche Nord“ wird zu Kueche_Nord.
3. Eine vierte Liste mit den drei Bereichsnamen anlegen und als Bereiche benennen.
4. In Spalte B des Plans ein Dropdown mit der Quelle =Bereiche einrichten.
5. Für die Planzellen ab C5 ein Dropdown mit der Quelle =INDIREKT($B5) anlegen. Das Dollarzeichen vor dem B sorgt dafür, dass alle Tageszellen einer Zeile auf den Bereich derselben Person schauen.

Steht in B5 „Kueche“, bietet jede Tageszelle der Zeile nur F1, F2 und S an. Ist B5 noch leer, meldet Excel beim Einrichten, dass die Quelle einen Fehler ergibt – die Meldung kann mit Ja übergangen werden. Zwei Grenzen gehören dazu: Wird der Bereich in Spalte B nachträglich geändert, bleiben bereits eingetragene Kürzel stehen, auch wenn sie im neuen Bereich nicht vorkommen; Daten → Datenüberprüfung → Ungültige Daten einkreisen macht sie sichtbar. Und INDIREKT liefert laut Microsoft #BEZUG!, wenn die Liste in einer anderen, geschlossenen Arbeitsmappe liegt – die Listen gehören deshalb in dieselbe Datei.

Schichtfarben per bedingter Formatierung

Das Dropdown sorgt für saubere Werte; die Farbe macht den Plan lesbar. Beides zusammen ergibt die typische Ansicht eines Schichtplans, in der Früh-, Spät- und Nachtdienste als farbige Blöcke erkennbar sind. Für jedes Kürzel wird eine Regel angelegt:

1. Die Planfläche C5:AG14 markieren.
2. Start → Bedingte Formatierung → Regeln zum Hervorheben von Zellen → Gleich… wählen.
3. F eintragen und unter Benutzerdefiniertes Format eine Füllfarbe wählen.
4. Für S, N, U und K wiederholen.

Wer mehr Kontrolle braucht, nutzt Neue Regel → Formel zur Ermittlung der zu formatierenden Zellen verwenden mit =C5="N". Ohne Dollarzeichen passt Excel den Bezug für jede Zelle des markierten Bereichs an. So lassen sich auch kombinierte Bedingungen formulieren, etwa =UND(C5="N";D5="F") für einen Frühdienst direkt nach einem Nachtdienst – eine Konstellation, bei der die elfstündige Ruhezeit nach § 5 ArbZG nicht eingehalten sein kann und die deshalb rot hervorgehoben werden sollte.

Wichtig für die Reihenfolge: Treffen mehrere Regeln dieselbe Zelle und widersprechen sich die Formate, gewinnt im Regel-Manager die weiter oben stehende Regel. Die Warnregel für die Ruhezeit gehört deshalb über die Farbregeln der Schichten. Wie Wochenenden und Feiertage als weitere Ebene dazukommen, zeigen Excel: Wochentag aus Datum anzeigen und Feiertage in Excel markieren.

Dropdown bearbeiten, erweitern oder entfernen

Wie eine bestehende Liste geändert wird, hängt von ihrer Quelle ab. Microsoft unterscheidet vier Fälle:

Bei Zellbereichen und eingetippten Listen bietet der Dialog das Kontrollkästchen Diese Änderungen auf alle Zellen mit den gleichen Einstellungen anwenden. Ohne dieses Häkchen ändert man nur die gerade markierten Zellen – und hat danach zwei verschiedene Listen im selben Plan.

Entfernen lässt sich ein Dropdown, indem man die Zellen markiert, Daten → Datenüberprüfung öffnet und auf der Registerkarte Einstellungen auf Alle löschen klickt. Die Inhalte der Zellen bleiben dabei erhalten, nur die Regel verschwindet. Um alle Zellen mit Datenüberprüfung auf einem Blatt zu finden, führt Microsoft den Weg über Strg+G → Inhalte… → Datenüberprüfung → Alle; die Option Gleiche markiert nur Zellen mit derselben Regel wie die aktive Zelle.

Excel 365, Excel für das Web, Mac und Google Sheets

Das Grundprinzip ist überall gleich, die Details unterscheiden sich. Die Tabelle fasst zusammen, was beim Wechsel zwischen den Programmen auffällt:

Für einen Dienstplan, der zwischen mehreren Personen und Geräten wandert, ist die Excel-Tabelle als Quelle die sicherste Wahl, weil sie in allen Excel-Varianten funktioniert und keine dynamischen Arrays voraussetzt. Wer die Datei über OneDrive gemeinsam bearbeitet, findet die Voraussetzungen und Fallstricke im Beitrag Excel-Dienstplan im Team teilen.

Google Sheets geht einen eigenen Weg. Die Dropdowns erscheinen standardmäßig als farbige „Chips“, denen man je Option eine Farbe zuweisen kann – für einen Schichtplan erspart das die bedingte Formatierung. Ungültige Eingaben werden nach Google-Hilfe standardmäßig abgelehnt; unter Erweiterte Optionen lässt sich stattdessen Warnung anzeigen wählen. Beim Import einer Excel-Datei bleiben Listenregeln in der Regel erhalten; Konstruktionen mit INDIREKT und strukturierten Verweisen sollte man nach dem Import aber prüfen.

Häufige Fehler bei Excel-Dropdowns

Der heimtückischste Fehler ist der fünfte. Ein Plan kann wochenlang sauber aussehen, bis jemand die Woche aus einer alten Datei hineinkopiert – mitsamt „Fr“ und „Nacht“. Weil die Datenüberprüfung beim Einfügen nicht anspringt, fällt das erst in der Monatssumme auf. Eine kurze Kontrolle mit Ungültige Daten einkreisen vor jedem Monatsabschluss findet solche Werte in Sekunden. Weitere Konstruktionsfehler, die nichts mit Dropdowns zu tun haben, sammelt der Beitrag Die häufigsten Fehler in Excel-Dienstplänen.

Eine Grenze bleibt: Das Dropdown prüft, ob ein Wert erlaubt ist, nicht ob er im Zusammenhang passt. Es merkt nicht, dass eine Person in drei aufeinanderfolgenden Wochen jedes Wochenende eingeteilt ist oder dass am Samstag niemand im Nachtdienst steht. Solche Prüfungen lassen sich mit Zählformeln und Farbregeln teilweise nachbauen – mit jedem weiteren Prüfschritt wächst aber der Pflegeaufwand der Datei.

Weiterlesen

Den kompletten Aufbau eines Plans mit Legende, Stundenformeln und Druckbereich beschreibt Dienstplan in Excel erstellen. Wie dieselbe Kürzel-Logik in einer Jahresmatrix funktioniert, zeigt Urlaubsplaner in Excel bauen, und eine Anwesenheitsliste mit Kürzeln und ZÄHLENWENN steht im Beitrag Anwesenheitsliste in Excel. Fertige Raster mit hinterlegter Datenüberprüfung finden sich in der Vorlagenübersicht und in der Schichtplan-Excel-Vorlage.