Das ultimative Excel Dashboard zum nachbauen und downloaden

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.

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.

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. 

Lerne dein perfektes Excel Dashboard zu bauen und spare künftig hunderte Stunden an manueller Arbeit dank Automatisierung. Begeistere Chefs und dein Team mit modernen Visualisierungen und neuen Analysenmöglichkeiten.

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.

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:

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.

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

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.

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:

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:

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. 

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:

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

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

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

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.

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:

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:

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

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:

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.

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.

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:

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.

Dann kannst du dir dein ultimatives Dashboard auch von mir bauen lassen! Jedes Excel Dashboard wird individuell an deine Ziele und dein Geschäftsmodell angepasst. Vor allem Geschäftsführer melden sich hiermit vermehrt bei mir, um einen gesamten Überblick über Ihr Geschäfts zu erhalten!

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.

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.

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.

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.

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.

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.

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 :) 

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!