Excel-Diagramme gestalten und steuernDropdown-gesteuerte Diagramme in Excel

Worum geht es bei der Steuerung mit einer Dropdownliste?

Ein gutes Diagramm zeigt nicht immer alle vorhandenen Daten gleichzeitig. Oft ist es sinnvoller, dem Anwender die Auswahl zu überlassen, welche Daten in einem Diagramm abgebildet werden. Mit einer Dropdownliste lässt sich ein Excel-Diagramm interaktiv steuern.

Dynamische Diagramme mit Excel-Tabellen wachsen automatisch mit neuen Daten. Dropdown-gesteuerte Diagramme gehen einen Schritt weiter: Sie verändern ihren Inhalt abhängig von einer Benutzerauswahl.

Der Anwender wählt beispielsweise in einer Zelle „Region Süd“ und das Diagramm zeigt automatisch nur die Umsatzentwicklung dieser Region. Wird anschließend „Region Nord“ ausgewählt, aktualisiert sich dasselbe Diagramm.

Dadurch muss nicht für jede Region, jedes Produkt oder jede Kennzahl ein eigenes Diagramm erstellt werden. Das Ergebnis ist ein kompakter, interaktiver Bericht.

Dropdown-gesteuerte Diagramme eignen sich besonders für Dashboards, Managementberichte, Vertriebsanalysen, Controlling, Produktvergleiche, Standortauswertungen sowie Monats- und Projektberichte.

Der Aufbau besteht im Kern aus drei Elementen:

• einer Dropdownliste,
• einem dynamischen Hilfsbereich,
• einem Diagramm, das diesen Hilfsbereich verwendet.

Was ist ein Dropdown-gesteuertes Diagramm?

Ein Dropdown-gesteuertes Diagramm ist kein eigener Excel-Diagrammtyp. Es handelt sich vielmehr um eine Kombination aus mehreren Excel-Funktionen. Ein typischer Aufbau sieht so aus:

1. Mit der Dropdownliste wird ein Wert ausgewählt.
2. Der Wert steht in einer Auswahlzelle.
3. Er ist Grundlage für eine Formel und eine Hilfstabelle.
4. Aus dieser wird dann das eigentliche Diagramm erstellt.

Die Dropdownliste bestimmt also nicht unmittelbar das Diagramm. Sie verändert zunächst einen Wert in einer Zelle. Dieser Zellwert wird anschließend von einer Formel verwendet, um die passenden Diagrammdaten bereitzustellen. Das Diagramm wiederum basiert auf diesen dynamisch erzeugten Daten.

Warum Dropdown-gesteuerte Diagramme sinnvoll sind

Angenommen, ein Unternehmen besitzt 4 Vertriebsregionen: Nord, Süd, Ost, West. Für jede Region liegen zwölf Monatswerte vor. Eine Möglichkeit wäre, alle fünf Datenreihen gleichzeitig in einem Liniendiagramm darzustellen. Das Ergebnis enthält fünf Linien.

Das kann noch funktionieren. Bei 15 Produkten, 25 Filialen oder 40 Mitarbeitenden wird ein solches Diagramm jedoch schnell unübersichtlich.

Alternativ könnte man für jede Kategorie ein eigenes Diagramm erstellen. Auch das ist selten optimal. Bei 25 Filialen wären 25 Diagramme notwendig.

Eine Dropdownsteuerung reduziert diese Vielzahl auf ein einziges Diagramm. Der Anwender entscheidet selbst, welche Datenreihe angezeigt wird.

Aus der Dropdown-Auswahl ein dynamisches Diagramm erstellen

Beispiel: Eine Datentabelle enthält monatliche Umsätze für vier Regionen.

Das Ziel lautet: Eine Dropdownliste soll die Region auswählen und ein Liniendiagramm automatisch die zwölf Monatswerte dieser Region anzeigen.

Sie benötigen vier Bereiche:

• Datenquelle: Die Tabelle mit den ursprünglichen Daten.
• Auswahlzelle: Hier befindet sich das Dropdown-Feld.
• Hilfstabelle (Bereich): Enthält die Daten, die mit dem Dropdown-Feld definiert werden.
• Diagramm: Basiert ausschließlich auf der Hilfstabelle.

Dropdownliste mit Datenüberprüfung erstellen

Die Dropdownliste wird mit der Excel-Funktion Datenüberprüfung erstellt.

Erstellen Sie zunächst eine Liste mit den möglichen Regionen, beispielsweise im Zellbereich G3:G6. Das ist die Liste, aus der Sie auswählen können.

Achten Sie darauf, dass die Liste keine überzähligen Leerzellen, keine führenden oder nachgestellten Leerzeichen und keine Dubletten enthält. Sonst erscheint im Dropdown ein leerer Eintrag oder die spätere Suche findet den Wert nicht.

Markieren Sie die Auswahlzelle, zum Beispiel die Zelle I3. Wählen Sie: Daten > Datentools > Datenüberprüfung. Unter Zulassen wählen Sie: Liste.

Als Quelle verwenden Sie =$G$3:$G$6 und bestätigen mit OK. Die Zelle I3 enthält dann eine Dropdownliste.

Beachten Sie: Der Dropdownpfeil (rechts neben Zelle I3) erscheint in der Regel erst, wenn die Zelle aktiv ist. Deshalb sollte die Auswahlzelle zusätzlich beschriftet und optisch hervorgehoben werden.

Wenn der Anwender in H2 den Eintrag „Süd“ auswählt, steht anschließend tatsächlich der Text „Süd“ in dieser Zelle. Excel kann diesen Wert in Formeln verwenden, beispielsweise:

=WENN(H2="Süd"; 1; 0)

Das ist der zentrale Mechanismus hinter interaktiven Diagrammen: Die Auswahlzelle funktioniert wie ein Steuerparameter.

Die Hilfstabelle aufbauen

Neben dem Diagramm oder auf einem separaten Arbeitsblatt erstellen Sie eine Hilfstabelle. Die Monate können direkt auf die ursprüngliche Monatsspalte in der Datenquelle verweisen, beispielsweise in K2 mit =A2, anschließend nach unten kopieren.

In Spalte L sollen dagegen die Umsätze der ausgewählten Region erscheinen. Die Überschrift in Zelle L1 kann später als dynamischer Reihenname für das Diagramm dienen.

Um darunter die Umsätze der ausgewählten Region anzuzeigen, können Sie mit der Funktion XVERWEIS() arbeiten. XVERWEIS sucht nach einem Wert und liefert den dazugehörigen Wert beziehungsweise Bereich zurück.

Angenommen, die Regionsnamen stehen in B2:E2, die Monatswerte darunter in B2:E14 und das Dropdown in Zelle I3. In Zelle L3 verwenden Sie die Formel:

=XVERWEIS($I$3; $B$2:$E$2; B3:E3)

Die Formel besteht aus drei entscheidenden Teilen:

• Suchkriterium $I$3: Hier steht die Dropdownauswahl.
• Suchmatrix $B$2:$E$2: Hier befinden sich die Regionsnamen und hier wird das Suchkriterium gesucht.
• Rückgabematrix B2:E2: Hier befinden sich die Werte der jeweiligen Monatszeile. Beim Kopieren nach unten wird daraus B3:E3, B4:E4 und so weiter.

Das optionale Argument wenn_nicht_gefunden macht die Formel robuster:

=XVERWEIS($I$3; $B$2:$E$2; B3:E3; NV())

Wird keine Region gefunden oder ist Zelle I3 leer, liefert die Formel den Fehlerwert #NV statt einer Mischung aus Nullwerten und Fehlermeldungen. Für Diagramme ist #NV oft die bessere Wahl, weil Excel den Punkt dann in der Regel nicht als Null zeichnet.

Das Diagramm erstellen

Markieren Sie diesen Hilfsbereich und wählen Sie: Einfügen > Liniendiagramm. Das Diagramm zeigt die Werte aus Spalte L.

Wählen Sie anschließend im Dropdown „Süd“. Die Formeln berechnen die entsprechenden Umsatzwerte, das Diagramm aktualisiert sich unmittelbar. Damit ist das erste interaktive Diagramm fertig.

Prüfen Sie nach dem Einfügen die Datenreihe:

• Die x-Achse sollte die Monate enthalten, nicht die Werte.
• Der Reihenname sollte auf die Überschrift der Wertespalte oder auf die Auswahlzelle zeigen.
• Die Werte dürfen nur die Hilfsspalte umfassen, nicht versehentlich die Originaldaten.

Besonderheiten beim Einsatz von Dropdownlisten für die Diagrammsteuerung

Hilfstabelle oder Hilfsbereich nutzen

Man könnte versuchen, das Diagramm direkt mit komplexen Formeln zu steuern. In der Praxis ist ein Hilfsbereich meist wesentlich besser. Denn:

• Formeln bleiben nachvollziehbar,
• Fehler lassen sich leichter finden,
• Diagrammquellen sind transparent,
• neue Funktionen können leichter ergänzt werden und
• Datenbeschriftungen lassen sich einfacher erzeugen.

Ein Hilfsbereich ist deshalb kein unnötiger Umweg. Er trennt vielmehr Auswahllogik von Visualisierung.

Sichtbar bleiben muss er dabei nicht. Sie können ihn weit rechts im Arbeitsblatt platzieren, auf ein separates Blatt verschieben oder die Spalten ausblenden. Wichtig ist lediglich, dass die Daten für das Diagramm weiterhin vorhanden sind.

Hier liegt ein häufiger Stolperstein: Excel blendet Daten aus ausgeblendeten Zeilen und Spalten im Diagramm standardmäßig oft aus. Nach dem Ausblenden des Hilfsbereichs kann das Diagramm leer wirken.

Klicken Sie deshalb auf das Diagramm und wählen Sie: Diagrammentwurf > Daten auswählen > Ausgeblendete und leere Zellen. Aktivieren Sie dort: Daten in ausgeblendeten Zeilen und Spalten anzeigen.

Zusätzlich können Sie festlegen, wie leere Zellen dargestellt werden: als Lücken, als Null oder mit einer verbindenden Linie. Für Umsatzreihen mit echten Lücken sind Lücken meist ehrlicher als eine Linie zur Nullachse.

Dynamischer Diagrammtitel und Datenreihenname

Das Diagramm sollte deutlich zeigen, welches Merkmal mit der Dropdownliste gerade ausgewählt wurde. Ein statischer Titel wie „Umsatzentwicklung“ ist zwar korrekt, aber nicht optimal.

Besser wäre „Umsatzentwicklung – Süd“. Dafür verwenden wir eine Hilfszelle, beispielsweise L2:

="Umsatzentwicklung – "&L2

Enthält Zelle I3 den Wert „West“, zeigt L2 den Text „Umsatzentwicklung – West“.

Klicken Sie nun den Diagrammtitel an und anschließend in die Bearbeitungsleiste. Geben Sie ein:

='Blattname'!$L$2

Der Diagrammtitel ist damit mit L2 verbunden. Ändert sich die Dropdownauswahl, verändert sich automatisch auch der Titel. Das wirkt deutlich professioneller als ein statischer Diagrammtitel.

Denselben Gedanken sollten Sie auf den Reihennamen übertragen. Unter Daten auswählen > Reihe bearbeiten > Reihenname kann ebenfalls ein Zellbezug stehen.

Wichtig: Excel verlangt für den Diagrammtitel und die Bezeichnung der Datenreihen einen vollqualifizierten Bezug mit Blattnamen. Sonst bleibt in der Legende ein generischer Name stehen, während nur der Titel dynamisch ist.

Dropdownquelle als dynamische Excel-Tabelle und benannter Bereich

Die Dropdownquelle, der Zellbereich G3:G6 im Beispiel oben, ist ein statischer Zellbereich. Wenn später eine weitere Auswahlmöglichkeit hinzukommt, müsste der Datenüberprüfungsbereich angepasst werden.

Eleganter ist eine dynamische Excel-Tabelle. Werden Elemente hinzugefügt oder entfernt, können darauf basierende Dropdownlisten automatisch aktualisiert werden.

Erstellen Sie daher eine Liste mit der Überschrift „Region“ und wandeln Sie den Zellbereich G3:G6 mit Strg + T in eine Tabelle um. Nennen Sie die Tabelle beispielsweise tblRegionen.

Achtung: In das Quellenfeld der Datenüberprüfung können Sie den strukturierten Tabellenverweis =tblRegionen[Region] in vielen Excel-Versionen nicht direkt eintragen. Excel erwartet dort einen klassischen Bereich oder einen Namen.

Der Umweg über einen benannten Bereich löst das Problem:

1. Wählen Sie: Formeln > Definierte Namen > Namen definieren.
2. Tragen Sie als Namen lstRegionen ein.
3. Tragen Sie unter Bezieht sich auf den Verweis =tblRegionen[Region] ein.
4. Bestätigen Sie mit OK.
5. Öffnen Sie die Datenüberprüfung der Auswahlzelle und tragen Sie als Quelle =lstRegionen ein.

Die Lösung wird dadurch verständlicher, leichter wartbar und weniger abhängig von Zellpositionen. Dieser Umweg über einen Namen ist der robuste Standard, wenn Dropdownquelle und Dashboard auf unterschiedlichen Blättern liegen.

Zwei Dropdownlisten verwenden

Die Interaktivität lässt sich leicht erweitern. Angenommen, der Anwender soll nicht nur die Region auswählen, sondern auch die Kennzahl – etwa Umsatz, Kosten oder Gewinn. Das Diagramm zeigt dann beispielsweise „Gewinn – Region Nord“.

Eine entsprechende Datentabelle als dynamische Excel-Tabelle tblDaten könnte so aussehen:

Zwei Auswahllisten erstellen

Dann erstellen Sie zwei getrennte Auswahllisten über die Funktion Daten > Datenüberprüfung > Liste. Die erste bezieht sich auf die vier Regionen, die zweite auf die drei Kennzahlen. Im Beispiel werden diese in den Zellen I3 und I7 angelegt.

Hilfstabelle erzeugen

Die Hilfstabelle muss anschließend beide Entscheidungen berücksichtigen. In einer langen Tabelle filtert zuerst die Region, danach wählt die Formel die Kennzahlspalte. So bleiben die Aufgaben getrennt und prüfbar.

In der ersten Spalte erzeugen Sie eine Liste mit den Monatsnamen. Zum Beispiel über:

=EINDEUTIG(tblDaten[Monat])

In der zweiten Spalte sollen nun die gemäß der Auswahlfelder eingestellten Daten angezeigt werden. Das gelingt mit dieser Formel:

=WEGLASSEN(FILTER(tblDaten; (tblDaten[Region]=I3)*(tblDaten[Kennzahl]=I7); NV()); ; 3)

Die Funktion FILTER() ist in diesem Fall übersichtlicher als eine komplexe Formel mit WENNS() und XVERWEIS(). Durch die Funktion WEGLASSEN() wählen Sie gezielt nur die Spalte Wert der Datentabelle aus.

Schließlich erzeugen Sie aus der Hilfstabelle noch das gewünschte Diagramm. Zum Beispiel ein Säulendiagramm, das Sie dann mit den beiden Auswahlfiltern steuern können.

Die Spalte mit den Werten erhält als Überschrift in Zelle L2 die dynamische Bezeichnung in Abhängigkeit von den Auswahlfiltern. Dazu geben Sie in L2 ein:

=I7&" Region "&I3

Dies wird automatisch zum Diagrammtitel.

Zwei Dropdownlisten reichen in den meisten Berichten. Jedes weitere Auswahlfeld erhöht den Erklärungsbedarf. Die Kennzahlliste sollte außerdem nur Größen mit derselben Einheit enthalten, solange dasselbe Diagramm und dieselbe Achse verwendet werden.

Vergleich zweier Regionen

Eine weitere interessante Erweiterung besteht darin, zwei Dropdownlisten für dasselbe Datenmerkmal einzusetzen, etwa Region 1 und Region 2.

Das Diagramm zeigt dann beide Säulen gleichzeitig. Der Anwender kann Nord gegen Süd, Süd gegen West oder Ost gegen Nord vergleichen. Ein einziges Diagramm ersetzt damit zahlreiche statische Vergleichscharts.

Für den Vergleich brauchen Sie im Hilfsbereich zwei Wertespalten. Beide Formeln unterscheiden sich nur durch die Steuerzelle. Die Achse sollte in diesem Fall eher fest sein, sonst täuscht der optische Abstand zwischen den Linien.

Typische Fehler bei Dropdown-gesteuerten Diagrammen

• Das Dropdown wird als direkte Diagrammsteuerung verstanden: Die Dropdownzelle allein verändert kein Diagramm. Es wird eine Verbindung über Formeln oder einen Hilfsbereich benötigt.
• Statische Dropdownquelle: Neue Produkte oder Regionen erscheinen nicht automatisch in der Liste. Eine dynamische Tabelle oder eine dynamische Liste mit EINDEUTIG ist robuster.
• Hilfsbereich ausgeblendet, Diagramm leer: Ohne die Option Daten in ausgeblendeten Zeilen und Spalten anzeigen verschwinden die Werte nach dem Ausblenden.
• Spill-Bereich ist blockiert: FILTER brauchen freien Platz. Sonst erscheint #ÜBERLAUF!, und das Diagramm hat keine Daten.
• Die Diagrammquelle wächst nicht mit: Ein fest eingetragener Bereich L2:M13 ignoriert neue Monate. Nutzen Sie eine dynamische Tabelle.
• Monatsnamen werden alphabetisch sortiert: SORTIEREN auf „Februar“, „Januar“, „März“ ergibt die falsche Zeitreihe. Sortieren Sie nach Monatsnummer oder Datum.
• Unsichtbare Leerzeichen verhindern den Treffer: Süd in der Überschrift und Süd in der Liste sind für Excel nicht identisch. Bereinigen Sie die Quelldaten, bevor Sie die Formeln suchen lassen.
• Das Diagramm reagiert gar nicht mehr: Steht die Berechnung auf Manuell, aktualisieren sich Formeln und Diagramm erst mit F9. Prüfen Sie unter Formeln > Berechnungsoptionen, ob Automatisch aktiv ist. Das ist besonders bei geerbten Arbeitsmappen eine häufige Ursache.
• Das Dashboard ist technisch zu komplex: Nicht jede Interaktion benötigt mehrere Dropdowns. Zu viele Auswahlfelder überfordern den Anwender – und er muss außerdem sofort erkennen, welche Zellen überhaupt interaktiv sind.