Excel-Diagramme gestalten und steuernScrollbare Diagramme in Excel – große Zeitreihen interaktiv darstellen

Was ist ein scrollbares Diagramm?

Ein Liniendiagramm mit zwölf Monatswerten ist problemlos überschaubar. Werden daraus 60 oder 120 Monate, verändert sich das Bild. Die Datenpunkte rücken immer enger zusammen, die Beschriftungen auf der horizontalen Achse werden schwer lesbar und einzelne Bewegungen gehen im Gesamtverlauf unter.

Eine elegante Lösung sind scrollbare Diagramme: Das Diagramm zeigt immer nur einen Ausschnitt, während der Anwender mit einer Scrollleiste durch die gesamte Zeitreihe navigiert. Das Diagramm selbst bleibt gleich groß. Lediglich die dargestellten Daten verschieben sich.

Ein scrollbares Diagramm oder Scrolling-Chart ist kein eigener Excel-Diagrammtyp. Technisch handelt es sich meist um ein gewöhnliches:

• Liniendiagramm,
• Säulendiagramm,
• Flächendiagramm.

Der Unterschied liegt in der Datenquelle. Das Diagramm verwendet nicht die komplette Zeitreihe, sondern einen dynamischen Hilfsbereich. Dieser enthält beispielsweise immer nur zwölf Werte. Eine Scrollleiste bestimmt, an welcher Position dieser Bereich beginnt.

Wann sind scrollbare Diagramme sinnvoll?

Angenommen, ein Unternehmen besitzt monatliche Umsatzwerte für fünf Jahre (5 Jahre × 12 Monate = 60 Werte). Alle 60 Werte könnten problemlos in einem Diagramm dargestellt werden. Die entscheidende Frage lautet jedoch: Hilft diese Darstellung noch bei der Datenanalyse?

Wenn die langfristige Entwicklung untersucht werden soll, kann ein vollständiges Diagramm sinnvoll sein. Wenn dagegen einzelne Monate, kurzfristige Schwankungen oder saisonale Muster analysiert werden sollen, ist ein kleineres Zeitfenster häufig besser.

Typische Einsatzgebiete sind Kennzahlen, die tagesgenau oder wochengenau ausgewertet werden sollen. Sie betreffen beispielsweise:

• Umsatzentwicklungen
• Kostenverläufe
• Plan-Ist-Vergleiche
• Produktionsdaten
• Auslastungen
• Anrufzahlen
• Energieverbrauch

Scrollen ist nicht dasselbe wie Zoomen

Bei einem scrollbaren Diagramm bleibt die Anzahl der sichtbaren Datenpunkte konstant (zum Beispiel 12 Monate). Das Zeitfenster wird lediglich verschoben (aus Jan 2023 – Dez 2023 wird Feb 2023 – Jan 2024).

Beim Zoomen würde dagegen die Zahl der sichtbaren Werte verändert: Aus 12 Monaten werden 24 Monate. Beide Techniken können kombiniert werden.

Ein Praxisbeispiel

Ihre Datentabelle enthält eine monatliche Umsatzentwicklung sowie Budgets über fünf Jahre. Die Daten stehen beispielsweise in den Spalten A bis C.

Insgesamt liegen 60 Datensätze vor. Das Diagramm soll jedoch immer nur 12 Monate gleichzeitig anzeigen. Der Anwender soll mit einer horizontalen Scrollleiste durch die Zeitreihe navigieren können.

In der Praxis bleiben Datenbestände selten bei exakt 60 Zeilen. Mit Strg + T wird aus einem normalen Bereich eine dynamische Excel-Tabelle (zum Beispiel tblUmsatz).

Neue Datensätze werden automatisch Bestandteil der Tabelle und stehen den Formeln sofort zur Verfügung.

Wichtig für Formeln: Speichern Sie die Monatsspalte als echte Excel-Datumsangaben im Format TT.MM.JJJJ und blenden Sie sie nur als Monat/Jahr ein. Texte wie „Jan 2021“ funktionieren nicht zuverlässig in Datumsformatierungen.

Die Scrollleiste als Steuerelement

Für die klassische Lösung verwenden Sie eine Scrollleiste als Formularsteuerelement. Diese finden Sie unter: Entwicklertools > Einfügen > Formularsteuerelemente > Scrollleiste.

Platzieren Sie anschließend eine horizontale Scrollleiste unterhalb des späteren Diagramms.

Hinweis: Scrollleisten aus den Formularsteuerelementen reagieren in Excel nicht auf das Mausrad. Der Anwender muss zwingend auf die Pfeile oder den Schieber klicken.

Alternative: Dropdown-Feld

In manchen Unternehmensumgebungen sind die Entwicklertools gesperrt oder das Menüband ist stark eingeschränkt. Als Alternative zur Scrollleiste kann ein Dropdown-Feld über Datenüberprüfung > Liste erstellt werden. Der Anwender wählt daraus die gewünschte Startposition aus.

• Vorteil: funktioniert in jeder Excel-Umgebung, keine Steuerelement-Berechtigungen nötig. Die Datenüberprüfung bleibt außerdem – anders als das Formularsteuerelement – auch in Excel im Web bedienbar.
• Nachteil: kein komfortables Scrollen per Pfeiltaste, sondern Auswahl aus einer Liste.

Die Formellogik im Hilfsbereich (INDEX, SEQUENZ bzw. WEGLASSEN/ÜBERNEHMEN) bleibt dabei identisch – lediglich die Zellverknüpfung zeigt statt auf die Scrollleiste auf die Dropdown-Zelle.

Scrollleiste konfigurieren

Klicken Sie mit der rechten Maustaste auf die Scrollleiste und wählen Sie: Steuerelement formatieren. Dann legen Sie fest:

• Aktueller Wert
• Minimalwert
• Maximalwert
• Schrittweite
• Seitenwechsel
• Zellverknüpfung

Als Zellverknüpfung verwenden Sie beispielsweise $F$2. Bewegen Sie anschließend die Scrollleiste, verändert Excel den Wert in Zelle F2 (1, 2, 3, 4 …). Diese Zahl dient später als Startposition des sichtbaren Zeitfensters.

Die Schrittweite steuert, um wie viele Positionen sich der Wert ändert, wenn auf einen Pfeil geklickt wird. Der Seitenwechsel steuert denselben Sprung beim Klick in die Leiste zwischen Schieber und Pfeil.

• Die Schrittweite 1 bedeutet: Ein Klick auf einen Pfeil verschiebt das Diagramm um einen Monat.
• Der Seitenwechsel 12 bedeutet: Ein Klick in die Leiste neben dem Schieber springt um ein ganzes Zwölfmonatsfenster.

Den richtigen Maximalwert berechnen

Der Maximalwert ist für die gesamte Lösung entscheidend. Bei insgesamt 60 Datensätzen von denen immer 12 Datensätze dargestellt werden sollen, lautet der letzte mögliche Startpunkt:

Maximalwert =
60 – 12 + 1 = 49

Wenn das Fenster bei Position 49 beginnt, verwendet es die Datensätze 49 bis 60 und umfasst damit genau zwölf Werte. Ein Start bei Position 50 wäre nicht mehr möglich, weil dann ein 61. Datensatz benötigt würde. Die Suchfunktion INDEX() würde in diesem Fall #BEZUG! liefern.

Die allgemeine Formel lautet daher:

Maximalwert =
Anzahl der Datensätze - Anzahl anzuzeigende Werte + 1

Beachten Sie: Der eingestellte Maximalwert der Scrollleiste wächst nicht mit – er bleibt bei 49, auch wenn Ihre Datentabelle verändert wird. Sie müssen den Maximalwert von Hand korrigieren oder per Makro nachführen lassen.

Grenzen des Formularsteuerelements

Keine Formeln in den Eigenschaften: Das Formularsteuerelement akzeptiert für Minimalwert und Maximalwert nur feste Zahlen, keine Zellformeln. Wächst die Datenbasis oder ändert sich die Fenstergröße, muss der Maximalwert manuell in den Steuerelementeigenschaften korrigiert oder per VBA-Makro nachgeführt werden.

Maximalwert bis 30.000: Das Formularsteuerelement verarbeitet ausschließlich ganze Zahlen bis 30.000. Bei Monatswerten ist das bedeutungslos, bei Tages-, Stunden- oder Messwerten kann diese Grenze erreicht werden. Das ActiveX-Pendant aus der Steuerelement-Toolbox erlaubt deutlich größere Werte, setzt dafür aber eine Arbeitsmappe mit Makros und VBA-Kenntnisse voraus.

Kein Betrieb in Excel im Web: Formularsteuerelemente lassen sich in Excel für das Web nicht bedienen; Excel weist beim Klick darauf hin, dass die Datei dafür in der Desktop-App geöffnet werden muss. Wenn Ihr Dashboard über SharePoint, OneDrive oder Teams im Browser genutzt werden soll, ist die Dropdown-Variante die bessere Wahl.

Den Hilfsbereich konfigurieren

Das Diagramm greift nicht direkt auf die vollständigen 60 Werte zu. Stattdessen erstellen Sie einen Hilfsbereich. Dieser Bereich enthält immer genau zwölf Monatswerte. Welche zwölf Werte dort stehen, entscheidet der Wert in der Zellverknüpfung der Scrollleiste.

Schreiben Sie beispielsweise in Zelle H2 und Zelle I2 die Überschriften „Monat“ und „Umsatz“. Sie werden später als Reihenname und als Grundlage der Diagrammbeschriftung benötigt. Die eigentlichen Werte beginnen damit in Zeile 3 und enden in Zeile 14.

Ein sichtbarer oder separat abgelegter Hilfsbereich macht sofort sichtbar, welche Daten aktuell an das Diagramm übergeben werden. Das erleichtert Fehlersuche, Erweiterungen, Datenbeschriftungen und zusätzliche Vergleichsreihen. Der Hilfsbereich kann später auf ein separates Berechnungsblatt verschoben werden.

Klassische Lösung mit INDEX

Für die klassische Variante verwenden Sie die Funktion INDEX(). Dabei gilt:

• A3:A62 = Monate
• B3:B62 = Umsätze
• F2 = Startposition

Die Formel für den ersten sichtbaren Monat lautet:

=INDEX($A$3:$A$62; $F$2)

Steht in Zelle F2 eine 1, liefert Excel den ersten Monat. Steht dort eine 2, wird der zweite Monat zurückgegeben.

Zwölf Monate automatisch erzeugen

Anstatt zwölf einzelne Formeln manuell anzupassen, nutzen Sie eine Formel, die nach unten kopiert werden kann. Angenommen, die erste Monatsformel steht in Zelle H3; der erste Wert steht also in der 3. Zeile. Dann lautet die Formel, die Sie einfach kopieren können:

=INDEX($A$3:$A$62; $F$2+ZEILE()-3)

In der ersten Zelle H3 gilt ZEILE()-3 = 0. Damit wird der Wert aus Zelle F2 zurückgegeben. Eine Zeile tiefer liefert ZEILEN() das Ergebnis 1 und liest somit automatisch den nächsten Datensatz aus.

Kopieren Sie die Formel insgesamt zwölf Zeilen nach unten.

Umsatzwerte dynamisch auslesen

Für die Umsätze verwenden Sie dieselbe Logik. In Zelle I3 schreiben Sie also:

=INDEX($B$3:$B$62; $F$2+ZEILE()-3)

Kopieren Sie auch diese Formel zwölf Zeilen nach unten. Der Hilfsbereich enthält nun immer genau zwölf Monate und die dazugehörigen Umsätze.

Den Hilfsbereich richtig formatieren

Weisen Sie den Werten in der Hilfstabelle noch die passenden Formate zu. Dem Monat also das Datumsformat MMM JJJJ und dem Umsatz das Format Währung ohne Nachkommastellen.

Was passiert beim Scrollen?

• F2 = 1 → Hilfsbereich zeigt Jan 2021 – Dez 2021
• F2 = 2 → Hilfsbereich zeigt Feb 2021 – Jan 2022
• H2 = 3 → Hilfsbereich zeigt Mrz 2021 – Feb 2022

Der Hilfsbereich wandert Monat für Monat durch die komplette Zeitreihe. Da der Hilfsbereich immer nur 12 Werte enthält, zeigt die x-Achse automatisch nur diese 12 Beschriftungen.

Diagramm erstellen

Markieren Sie nun den Hilfsbereich H2:I14 und wählen Sie: Einfügen > Liniendiagramm.

Das Diagramm basiert ausschließlich auf den zwölf Werten aus dem Hilfsbereich. Bewegen Sie anschließend die Scrollleiste, verändert sich der Wert in Zelle F2. Die Funktion INDEX() gibt andere Daten zurück und das Diagramm aktualisiert sich automatisch.

Dynamischen Diagrammtitel erstellen

Damit der dargestellte Zeitraum auch im Diagramm eindeutig erkennbar ist, erstellen Sie in Zelle F6 einen dynamischen Diagrammtitel. Voraussetzung: Die Zellen H3 und H14 enthalten echte Datumswerte, keine Monatstexte.

Dann schreiben Sie in Zelle F6:

="Umsatzentwicklung "&TEXT(H3; "MMM JJJJ")&" bis "&TEXT(H14; "MMM JJJJ")

Ergebnis ist der Eintrag (je nach Einstellung der Scrollleiste): Umsatzentwicklung Apr 2024 bis Mrz 2025

Verknüpfen Sie anschließend den Diagrammtitel mit der Zelle F6: Diagrammtitel anklicken, in die Bearbeitungsleiste = Scrollsteuerung!F6 eingeben und mit Enter bestätigen.

Tipp: Bei interaktiven Diagrammen sollte immer eindeutig erkennbar sein, welcher Zeitraum gerade dargestellt wird. Ein dynamischer Diagrammtitel verhindert Missverständnisse.

Mehrere Datenreihen, Vorjahreswerte oder Plan-Ist-Vergleiche

Die Technik funktioniert genauso mit mehreren Datenreihen (zum Beispiel Monat, Umsatz, Kosten, Gewinn) oder mit einer zweiten Vergleichsreihe aus Vorjahr oder Budget. Der Hilfsbereich wird einfach um weitere Spalten erweitert.

Da alle Formeln dieselbe Startposition aus H2 verwenden, werden sämtliche Reihen synchron verschoben. Genau das macht die Technik für das Controlling wertvoll: Abweichungen zwischen Plan und Ist oder gegenüber dem Vorjahr bleiben über die gesamte Zeitreihe hinweg vergleichbar, ohne dass der Anwender mehrere Diagramme nebeneinanderlegen muss.

Alternative Berechnung der Hilfstabelle

Kompakte Variante mit INDEX und SEQUENZ

Übergibt man der Funktion INDEX() statt einer einzelnen Zeilennummer ein ganzes Array von Zeilennummern, gibt die Funktion in einem Schritt alle zugehörigen Werte zurück. Die Zeilennummern erzeugt die Funktion SEQUENZ().

In dieser Variante geben Sie in die Zellen H3 und I3 ein:

=INDEX($A$3:$A$62; SEQUENZ(12; ; F2))

=INDEX($B$3:$B$62; SEQUENZ(12; ; F2))

SEQUENZ(12;;F2) erzeugt eine Zahlenreihe, die mit dem Wert in Zelle F2 beginnt und dann jeweils den Wert 1 hinzuzählt, bis zwölf Werte erreicht sind.

Wie bei jeder Arrayformel gilt: Die Zielzellen darunter müssen frei sein, sonst erscheint #ÜBERLAUF!. Zudem muss das Diagramm über einen benannten Bereich angebunden werden.

Das Diagramm über einen benannten Bereich anbinden

Dieser Schritt wird häufig übersehen und ist der Grund, warum die Arrayvariante nicht funktioniert. Excel-Diagrammreihen können einen Array-Bereich nämlich nicht direkt als Datenquelle nutzen.

Verändert sich der Array-Bereich, bleibt das Diagramm auf dem Bereich stehen, der beim Erstellen markiert war.

Der Umweg führt über benannte Bereiche, die auf den Array-Bereich zeigen. Legen Sie über Formeln > Namen definieren zwei Namen an. Zum Beispiel:

• ChartMonate bezieht sich auf =Scrollsteuerung!$H$3#
• ChartUmsatz bezieht sich auf =Scrollsteuerung!$I$3#

Anschließend öffnen Sie Diagrammentwurf > Daten auswählen > Bearbeiten und tragen die Namen ein.

Wichtig: In einer Diagrammreihe muss dem Namen der Dateiname vorangestellt werden. Also:

• Achsenbeschriftungen: ='Scrollbare-Diagramme.xlsx'!ChartMonate
• Reihenwerte: ='Scrollbare-Diagramme.xlsx'!ChartUmsatz

Das Format der x-Achse und y-Achse kann vom Format der Werte in der Hilfstabelle abweichen (zum Beispiel englisches Währungs- und Datumsformat). Es muss dann bei den Diagrammeinstellungen manuell korrigiert werden.

Checkliste für scrollbare Diagramme

• Ausgangsdaten chronologisch sortiert?
• Monatsspalte als echtes Datum gespeichert?
• Hilfsbereich mit Überschriften und passendem Zahlenformat versehen?
• Scrollleiste korrekt eingerichtet und verknüpft?
• Maximalwert berechnet (Anzahl − Fenstergröße + 1) und nach Erweiterung der Datentabelle nachgeführt?
• Hilfsbereich prüft alle Positionen korrekt – inklusive Startposition 1?
• Bei Arrayformeln: Diagramm über benannten Bereich mit dem Array-Bereich verbunden?
• Diagrammtitel dynamisch verknüpft?
• Geprüft, ob die Mappe im Browser genutzt werden soll?