Das ultimative Excel Dashboard zum nachbauen und downloaden

Excel Dashboard mit Pivot und Datenschnitt

Inklusive: Pivot Diagramme, Bullet Charts, Kartendiagramme, Piktogramme und Datenschnitt

Ich habe mich ja schon einmal dem Thema Excel Dashboard gewidmet in meinem Artikel "Dein persönliches Dashboard".

Die Excel Vorlage hierzu gehört zu den beliebtesten mit mittlerweile über 5.000 Downloads.

Daher dachte ich mir das Thema Dashboards hat eine Neuauflage verdient.

 

Doch diesmal gehen wir einen ganz anderen Weg:

Wir verwenden Pivottabellen, Pivotdiagramme und Datenschnitte, um unser Dashboard interaktiv zu gestalten.

 

Zusätzlich legte ich diesmal auch mehr Wert auf Design. Das Dashboard sollte so sexy aussehen, dass der Chef beim nächsten Berichtszyklus Purzelbäume schlägt vor Freude. Um dies zu erreichen setze ich auf viel Formatierungsarbeit und ausgefallene Diagrammarten, die aus der Reihe tanzen.

So sieht das ultimative Excel Dashboard aus

Das beste Excel Dashboard mit Pivottabellen, Pivotdiagramme und Datenschnitte

Das Excel Dashboard ist aufgeteilt in 5 Bestandteile, die unterschiedliche Informationen enthalten:

 

1. Finanzkennzahlen: Hier wird neben einem Umsatzverlauf (IST und PLAN) in einem Kombinationsdiagramm der Umsatz je Land in einem Kartendiagramm dargestellt

2. Prozesskennzahlen: Neben der Liefertermintreue siehst du auch die Retourenquote, die beide in einem Bulletchart visualisiert wurden.

3. Mitarbeiterkennzahlen: Zur Präsentation der Mitarbeiter je Standort wird auf ein Piktogram zurückgegriffen, das eine übersichtliche Darstellung der Mitarbeiter aufgeteilt nach Männern/Frauen je Standort zeigt.

4. Kundenkennzahlen: Mit dem geschickten Einsatz von gestapelten Balken wird die Kundenzufriedenheit je Produkt aufgezeigt.

 

Die Inhalte des Dashboards sind angelehnt an das Balanced Scorecard Konzept, das genau diese 4 Sichten (Finanzen, Prozess, Mitarbeiter, Kunde) als integratives Managementsystem nutzt. Das habe ich dem Dashboard insgeheim dem Namen Balanced Dashboard gegeben.

Anzeige:

Das Excel Dashboard ermöglicht eine verdichtete, übersichtliche Darstellung von steuerungsrelevanten Kennzahlen. Hierbei kann es auf unterschiedliche Metriken zugreifen und diese präzise und verständlich präsentieren. Aufgrund seiner Dynamik eignet es sich perfekt zur Analyse in Vorbereitung von z.B. Monatsabschluss Gesprächen. Es kann jedoch auch direkt für die Präsentation bei der Führungskraft oder der Geschäftsführung. Dank seiner kompakten Form, kann das Dashboard als One Pager / Einseiter ausgedruckt werden, entweder auf Papier oder als PDF zum Versand. 

Lade die Excel Vorlage herunter, bevor es mit dem Tutorial losgeht

Excel Vorlage downloaden

Hier geht's zum Video Tutorial

In 5 Schritten zum fertigen Balanced Dashboard

Damit du das Dashboard 1:1 nachbauen kannst, empfehle ich dir das Video Tutorial, da ich bei der Textzusammenfassung nicht jede Formatierung einzeln erfassen kann bzw. will ;)

Grundsätzlich lässt sich der Prozess in 5 Schritte unterteilen, die natürlich jeweils mehrere Aktionen beinhalten.

1. Schritt: Daten aufbereiten

Wie jedes Excel Projekt, beginnt auch dieses hier mit der Datenaufbereitung. Da wir im nächsten Schritt Pivottabellen erstellen möchten, benötigen wir unsere Daten in Listenform, wobei jede Spalte eine eindeutige Überschrift benötigt.

So sieht die verwendete Liste in unserem Fall aus:

Daten für Pivottabellen

Alle Daten sind miteinander verbunden, bis auf die letzten 3 Spalten. Diese lassen sich nicht logisch mit den vorherigen Spalten verknüpfen. Daher müssen wir diese Informationen im unteren Teil der Liste einfügen.

Daten für Excel Dashboard

Die Daten für Standort, Mitarbeiter (M), Mitarbeiter (F) sind auf Stichtage bezogen, die monatlich anfallen.

2. Schritt: Pivottabellen erstellen

Wir benötigen nun 6 Pivottabellen, also jeweils eine Pivottabelle für jedes Diagramm, das wir auf dem Dashboard ausweisen. In meiner Excel Vorlage habe ich hierzu 4 Tabellenblätter verwendet, um die 4 Sichten (Finanzen, Prozess, Mitarbeiter und Kunden) voneinander getrennt betrachten zu können.

Finanzkennzahlen

Da die Finanzsicht aus 2 Diagrammen besteht, benötigen wir hier auch 2 Pivottabellen.

Wir starten mit dem Umsatzverlauf, der später im Kombidiagramm gezeigt wird.

 

Markiere die gesamte Datenliste, und gehe dann über EInfügen --- PivotTable

Wähle dann beim Ziel das gewünschte Arbeitsblatt aus und drücke ok.

Als nächstes sortierst du die Feldliste der Pivot entsprechend nach dem Schema:

Pivot felder Auswahl
Feldlist Einstellungen
Ergebnis Pivottabelle
Ergebnis Pivottabelle

Nicht wundern, dass ab dem August keine Werte hinterlegt sind. Hier habe ich bewusst nur einen Teil des Jahres ausgewiesen (da dies im Normalfall ja auch so sein wird). Der Planwert wird jedoch für das komplette Jahr ausgewiesen, um direkt auf die nächsten Monate blicken zu können. 

Das gleiche machen wir nun auch für die Daten, die später in das Kartendiagramm übertragen werden sollen. Hier sieht die Feldliste und die fertige Pivot folgendermaßen aus:

Pivottabellen Felder für Kartendiagramm
Feldliste Einstellungen
Pivottabelle Kartendiagramm
Ergebnis Pivottabelle

Leider sind die Kartendiagramme nicht in der Lage Daten direkt aus Pivottabellen zu ziehen. Daher benötigen wir hier eine kleine HIlfsliste, die in die Pivottabelle verlinkt ist. 

Pivottabelle für Kartendiagramm
Hilfsliste für Kartendiagramm mit Pivottabelle verbunden

Prozesskennzahlen

Wir erstellen, wie bei den Finanzkennzahlen wieder 2 Pivottabellen, jeweils eine pro Diagramm. 

Da die Bulletcharts eine besondere Struktur benötigen, basteln wir uns auch hier noch HIlfszellen.

Das Ergebnis sieht dann folgendermaßen aus:

Pivottabellen für Prozesskennzahlen
Pivottabellen und Hilfszellen für Prozesskennzahlen

Der IST-Wert für die Liefertermintreue und die Retourenquote errechnet sich dann jeweils aus der Division der beiden Werte in der Pivottabelle:

Liefertermintreue: Pünktlich / Gesamtergebnis

Retourenquote: ja / Gesamtergebnis

Mitarbeiterkennzahlen

Auch die Mitarbeiterkennzahlen werden aus der selben Datenliste gespeist. Die Struktur der Pivottabelle sehen wir hier:

Pivottabelle Mitarbeiterkennzahlen
Pivottabelle Mitarbeiterkennzahlen

Kundenkennzahlen

Zu guter letzt bauen wir die Pivottabelle für die Kundenzufriedenheit. Gleiches Spiel anderes Ergebnis:

Pivottabelle Kundenzufriedenheit
Pivottabelle Kundenzufriedenheit

3. Schritt: Dashboard Hintergrund und Struktur

Nun fangen wir damit an das Format und die Struktur für das eigentlich Dashboard zu erstellen.Wir starten mit einem leeren Blatt.

Zunächst entfernen wir die lästigen Gitternetzlinien, hierzu entfernen wir den Haken in der Ansicht.

Gitternetzlinien entfernen bei Excel Dashboard
Gitternetzlinien entfernen

Nun suchen wir uns ein schönes Hintergrundbild aus. Hier kannst du natürlich von meinem Bild abweichen.

Um ein Hintergrundbild sauber einzufügen gehen wir über Seitenlayout - Hintergrund:

Hintergrund einfügen bei Excel Dashboard
Hintergrund einfügen

Wenn das vollbracht ist, platzieren wir die 6 Formen auf dem Arbeitsblatt, je nach Geschmack. Ich habe bei meinen Formen mein altbewährtes grün (#009f6d) verwendet und dabei die Transparenz auf 65% gestellt. Für die untere Form, in der später die Datenschnitte zu finden sind, habe ich ein schwarz gewählt mit leichter Transparenz.

Du solltest nun so weit sein:

Zwischenstand Excel Dashboard
Zwischenstand Excel Dashboard

Nun fügen wir das obere Logo ein (einfach Copy und anpassen) und geben unseren Formen eine passende Beschriftung (Schriftart hier: Arial Nova Cond Light)

Neben der Beschriftung, fügen wir passend zu den verschiedenen Sichten noch Icons/Symbole ein. 

Diese findest du unter: Einfügen - Piktogramme

Icons einfügen
Piktogramme von Excel

Bei der großen Auswahl ist bestimmt auch etwas für dich dabei. 

Dieses Feature ist nur bei einem Office 365 Paket oder den neuen Excel Version ab 2016 verfügbar.

Nun haben wir ein fertiges Gerüst, um unsere Diagramme einzufügen:

Zwischenstand Excel Dashboard nach Titel und Icons
Zwischenstand Excel Dashboard nach Titel und Icons

4. Schritt: Diagramme erstellen

Kombi Diagramm

Kombidiagramm für Umsatzverlauf
Kombidiagramm für Umsatzverlauf

Das Kombi Diagramm soll den Umsatzverlauf mit IST und Planwerten darstellen. Hierbei werden die IST Daten von Säulen dargestellt und die Planwerte mit Linien. Die genaue Formatierung kannst du dem Video entnehmen.

Kombi Diagramm erstellen für Umsatzverlauf
Kombi Diagramm erstellen für Umsatzverlauf

Als Datenbasis für das Kombidiagramm dient die Pivottabelle im Finanzreiter, die den Umsatz je Monat ausweist. Mit einem Klick erstellst du das gewollte Pivotdiagramm.

Kartendiagramm

Kartendiagramm für Umsatz je Land
Kartendiagramm für Umsatz je Land

Das Kartendiagramm ist leider nicht für ältere Excel Versionen verfügbar. Derzeit leider nur für Excel 2019 oder einem Office 365 Paket. Zusätzlich ist wichtig, dass du bei der initialen Erstellung des Kartendiagramms eine Internetverbindung vorweisen kannst. Dies ist nötig, da die Daten über Bing abgerufen werden müssen. Weitere Stolpersteine beim Kartendiagramm findest du bei Martin von tabellenexperten.de

Das Kartendiagramm soll den Umsatz je Land im Dashboard visualisieren. Je dunkler die Farbe, desto mehr Umsatz wird in diesem Land generiert.

Wir erstellen das Kartendiagramm auf Basis der Hilfszellen, die wir neben unsere Pivottabelle gepackt haben:

Erstellung Kartendiagramm
Erstellung Kartendiagramm

Solltest du über eine ältere Excel Version verfügen, dann setze doch einfach ein anderes Chart hierfür ein: z.B. ein Balkendiagramm, das den Umsatz je Land ausweist oder eine kumulierte Sicht des Umsatzes IST vs. PLAN.

Bullet Chart

Bullet Charts für Prozesskennzahlen
Bullet Charts für Prozesskennzahlen

Die Erstellung der Bulletcharts passiert anhand der beiden Hilfslisten für Liefertermintreue und Retourenquote. Ich habe zu der Erstellung von Bullet Charts einen separaten Blog Post, den du dir hier anschauen kannst. 

Hier solltest du dir vorab Gedanken darüber machen, welche Segmente du einfügen willst und vor allem, wie diese zu bewerten sind. Du kannst natürlich auch auf die Segmentierung verzichten und nur den IST-Wert gegen Ziel vergleichen. Das entzieht dem Diagramm jedoch Aussagekraft.

Piktogramm

Piktogramm für Mitarbeiterkennzahlen
Piktogramm für Mitarbeiterkennzahlen

Mit unserem Piktogramm zeigen wir einfach aber höchst ästethisch die Anzahl der Mitarbeiter je Standort aufgeteilt nach Männern und Frauen. Die Anzahl der Icons spiegelt hierbei den Wert wieder.

Das Piktogramm ist eigentlich ein formatiertes gestapeltes Balkendiagramm. Dieses erstellen wir als Pivotdiagramm direkt aus der Pivottabelle im MItarbeiterreiter.

Gestapeltes Balkendiagramm als Basis für das Piktogramm
Gestapeltes Balkendiagramm als Basis für das Piktogramm

Im nächsten Schritt suchst du dir deine Icons im Piktogramm Katalog von Excel aus.

Hast du das passende Symbol gefunden musst du es einfach nur in die Datenreihen des Balkendiagramms hineinkopieren. 

Wichtig ist bei der Formatierung danach, dass du die Option "Stapeln und teilen mit Einheit/Bild" anklickst ansonsten wird dein Piktogramm einfach nur gestreckt und das sieht najaaaa ... blöd aus. Mit dieser Anpassung wird gewährleistet, dass die Anzahl der Icons der tatsächlichen Einheit entspricht, die es darstellen soll, z.B. 6 Mitarbeiter = 6 Icons.

Gestapeltes Balkendiagramm (100%)

Gestapeltes Balkendiagramm (100%) für Kundenzufriedenheit
Gestapeltes Balkendiagramm (100%) für Kundenzufriedenheit

Das Balkendiagramm zeigt uns die Kundenzufriedenheit aufgeteilt nach den 6 Produkten auf. Hierbei wird der Balken segmentiert in 5 Einheiten: sehr unzufrieden, unzufrieden, neutral, zufrieden und sehr zufrieden.

 

Das Diagramm wird direkt aus der Pivottabelle im Kundenreiter erstellt.

 

Pass auf, dass die Reihenfolge bei der Pivottabelle direkt übereinstimmt mit deinem Zielbild, ansonsten musst du es später im Diagramm noch einmal anpassen.


Gestapeltes Balkendiagramm (100%) erstellen
Gestapeltes Balkendiagramm (100%) erstellen

5. Schritt: Datenschnitte einfügen und formatieren

Mit Datenschnitten in Excel erhalten wir eine praktische Filtermöglichkeit für Tabellen und Pivottabellen bzw. Pivotdiagramme. 

Dank der Datenschnitte in unserem Dashboard kann der Anwender sein Dashboard so anpassen, dass er nur die Informationen sieht, die relevant für ihn sind. Zusätzlich unterstützt dieses Feature die Analyse der vorhandenen Daten, da ein positives Ergebnis auf Gesamtebene (z.B. alle Länder) nicht ausschließt das einzelne Teilbereiche unter den Erwartungen liegen.

Datenschnitte zu erstellen ist simpel: Klicke einfach auf das Diagramm, auf das die Datenschnitte Einfluss nehmen soll und gehe über Einfügen --Datenschnitt.

Excel Datenschnitt in Dashboard einfügen
Datenschnitt in Dashboard einfügen

Bei unserem Dashboard verwenden wir 5 Datenschnitte: Produkt, Land, Kunde, Monate, Monate für Mitarbeiter. 

Damit die Filterung im Datenschnitt Einfluss auf mehrere Diagramme hat, müssen wir diese noch mit den gewünschten Pivots verbinden. 

Hierzu klickst du per Rechtsklick auf den Datenschnitt und gehst auf Berichtsverbindungen. Dort kannst du nun die Pivots auswählen, die gefiltert werden sollen.

Excel Datenschnitt Berichtsverbinunden für Dashboard
Berichtsverbindungen Datenschnitt

Du kannst beim Datenschnitt dann auswählen, ob mehrere Positionen gewählt werden können oder nur eine. Dies ist zum Beispiel beim Mitarbeiterdiagramm hilfreich, weil hier eine Auswahl von mehreren Monaten keinen Sinn macht.

Zum Resultat

Mit dem verknüpften Dashboard hast du nun die Interaktivität, die du dir wünscht. Dein Chef kann sich austoben und du hast deine Ruhe bis zum nächsten Monat :) 

Interaktives Excel dashboard mit Datenschnitten
Fertiges Interaktives Dashboard im Einsatz

Du kannst die Excel Dashboard Vorlage herunterladen und sie einfach an deine Datenlage anpassen. Das Wissen hierzu hast du jetzt da.

Ich wünsche viel Erfolg beim exceln!

Weitere Tutorials

Kommentar schreiben

Kommentare: 4
  • #1

    Dominik (Freitag, 02 August 2019)

    Lieber Artur,

    ich verneige mich vor dem Excel König, der nach jahrelanger Abstinenz eine pompöse Rückkehr feiert und die Herzen der der Excel-User in jedweder Form hochjubeln lässt! Einfach Klasse und vielen Dank für deine Mühe! Ich habe dein Skillset vermisst und freue mich auf die wöchentlichen Aufgaben und Features von deiner Seite her präsentiert!

    Beste Grüße,

    Dominik

  • #2

    Artur (Freitag, 02 August 2019 09:09)

    Hi Dominik, jetzt werde ich gleich noch rot :)

    Danke für das positive Feedback. Immer gut zu hören, dass die Tutorials weiterhelfen!

    Schönes Wochenende!
    Artur

  • #3

    Paul (Samstag, 03 August 2019 23:36)

    Einfach Hammer!!!
    Auf dem ersten Bild dachte ich es ist ein teures Programm, jetzt sitze ich am PC und versuche mich an deinen Vorgaben. �

    Vielen Dank dass du dein Wissen mit der Welt teilst, hier kann wirklich jeder noch was lernen �

  • #4

    Artur (Sonntag, 04 August 2019 19:01)

    Hi Paul,
    danke für dein positives Feedback.
    Ich hoffe du baust dein persönliches ultimatives Dashboard :)

    Viel Erfolg!
    Artur