Pivot-Tabellen-Kosmetik 4

Hatte ich eigentlich schon einmal erwähnt, dass Pivot-Tabellen etwas Wunderbares sind? (ich fürchte, ich habe das schön öfter gesagt. Aber es stimmt halt…)

Damit die dargestellten Ergebnisse für den Anwender noch leichter zu interpretieren sind, gibt es einige Möglichkeiten, um den Zahlensalat optisch aufzuwerten. In diesem Artikel gebe ich dir ein paar Tipps, mit denen du bei deinem Chef noch mehr Eindruck bei deinen Pivot-Tabellen schinden kannst.

Und so geht’s:


Beispieldatei herunterladen

Berichtslayout anpassen

Beginnen wir mit den einfachen Dingen. Ich bekomme immer wieder Pivot-Tabellen zu sehen, die einfach mit den Excel-Standardeinstellungen erstellt und dann unverändert belassen wurden. Das sieht immer etwas langweilig und unprofessionell aus:

Pivot-Tabelle im standardmäßigen Kurzformat

Das liegt am sogenannten Berichtslayout. Excel verwendet in der Voreinstellung das Kurzformat, und das auch noch in einem blassen Blau.

Das Berichtslayout kann im Entwurf-Menü geändert werden

Über die Farbe kann man ja noch diskutieren, aber im Kurzformat steht anstelle vernünftiger Bezeichnungen oben nur „Zeilenbeschriftungen“ und „Spaltenbeschriftungen“, und das ist nun wirklich nicht schön. Mich persönlich stört auch, dass die Zwischenergebnisse immer oberhalb der Gruppe angezeigt werden. Eigentlich ist man es ja gewohnt, dass Summen immer unten stehen. Das lässt sich zwar über das Menü „Entwurf | Teilergebnisse | Alle Teilergebnisse in der Gruppe unten anzeigen“ schnell ändern, aber irgendwie begeistert mich das auch nicht so richtig:

Kurzformat + Zwischensummen unten anzeigen

Stattdessen empfehle ich das Berichtslayout „Im Tabellenformat anzeigen“. Wenn man sich dann noch eine andere PivotTable-Formatvorlage aussucht, sieht das gleich schon viel besser aus:

Alternativ: Tabellenformat und eine entsprechende Formatvorlage

Statt „Zeilenbeschriftungen“ und „Spaltenbeschriftungen“ heißt es jetzt korrekt „Region“ und „Verkäufer“, die Zwischensummen stehen automatisch unten und werden auch noch deutlicher hervorgehoben.

Ich weiß, über Geschmack lässt sich streiten, aber ich wollte es nur mal gesagt haben 😉

Bonus-Tipp:
Wenn du Microsoft 365 nutzt (wahrscheinlich geht das auch in Excel 2024), dann kannst du das Berichtslayout direkt in den Excel-Optionen fix einstellen:

Standardlayout in den Excel-Optionen fest hinterlegen

Danach werden alle neu erstellten Pivot-Tabellen immer in dem dort hinterlegten Berichtslayout angelegt. Diese Einstellung gilt dann auch nicht nur für die aktuelle Arbeitsmappe, sondern gleich für alle!

Pivot-Tabellen und bedingte Formatierung

Wie in normalen Excel-Tabellen lassen sich auch innerhalb von Pivot-Tabellen Regeln für eine bedingte Formatierung einbauen. Diesem Thema hatte ich vor längerer Zeit einen eigenen Artikel gewidmet, den ich dir gerne ans Herz legen möchte.

Dieser Tipp hier geht nochmal speziell auf die Datenbalken ein. Im folgenden Bild siehst du drei ganz einfache Pivot-Tabellen:

• Version 1: Die Standard-Tabelle, ohne irgendwelche zusätzlichen Formatierungen
• Version 2: Mit Datenbalken in der Wertespalte (per bedingter Formatierung)
• Version 3: Mit separaten Datenbalken NEBEN der Wertespalte

Drei Varianten für die gleichen Zahlen

Die Datenbalken in Version 2 sind schon ein guter optischer Indikator für die Höhe der Umsätze. Dazu bedarf es einer einfachen Formatierungsregel für die bedingte Formatierung:

Bedingte Formatierung: Datenbalken

Daher habe ich die dritte Variante erstellt, welche die Optik verbessert und zugleich die Lesbarkeit der Werte beibehält. Wie bekommt man nun aber die Balken in eine separate Spalte? Ganz einfach: Die Umsätze werden ein zweites Mal in der Pivot-Tabelle verwendet.

Ziehe also im ersten Schritt das Umsatzfeld, das bereits in der Pivot-Tabelle angezeigt wird, ein zweites Mal in den Wertebereich:

Umsatz-Feld kommt zweimal in den Wertebereich

Lege dann eine neue Formatierungsregel an über das Menü „Start | Bedingte Formatierung | Neue Regel…“ und nimm dabei folgende Einstellungen vor:

Formatierungsregel: Nur Balken anzeigen

Jetzt gibt es nur noch zwei kleine Schönheitsfehler. Die unschöne Überschrift über der Balkenspalte muss weg und auch das Gesamtergebnis sollte nicht nochmal angezeigt werden. Beides lässt sich über das benutzerdefinierte Zahlenformat ;;; bewerkstelligen. Markiere also die Spaltenüberschrift und das Gesamtergebnis und rufe über einen Rechtsklick das Menü „Zelle formatieren“ auf:

Überschrift und Gesamtergebnis ausblenden

Dort markierst du links die Kategorie „Benutzerdefiniert“ und gibst oben in das Typ-Feld einfach drei Semikolons ein:

Benutzerformat mit drei Semikolons

Fertig. Damit hast du sowohl lesbare Zahlen als auch eine saubere Spalte mit Datenbalken.

Sieht gleich besser aus…

Benutzerdefinierte Zahlenformate

Der zweite Kosmetik-Tipp (wie sich das schon anhört, vielleicht sollte ich doch noch eine Karriere als Beauty-Influencer in Betracht ziehen…) geht in eine ähnliche Richtung.

Manchmal sollen in Pivot-Tabellen Veränderungen dargestellt werden, wie zum Beispiel aktuelle Umsätze im Vergleich zum Vorjahr. Auch hier habe ich wieder drei Varianten vorbereitet. Die erste Variante ist eine „nackte“ Pivot-Tabelle mit den Umsätzen für die Jahre 2018 bis 2020 und den jeweiligen prozentualen Veränderungen zum Vorjahr.

Veränderungen zum Vorjahr

Um die Veränderungen anzuzeigen, ziehst du wie im ersten Beispiel das Umsatzfeld ein weiteres Mal in den Wertebereich. Dann öffnest du die Werteeinstellungen für dieses Feld und klickst dort auf die Registerkarte „Werte anzeigen als“. Aus der Liste der Anzeigevarianten wählst du den Eintrag „% Differenz von“ und markierst unten das Basisfeld „Jahr“ und rechts das Basiselement „(Vorheriger)“. Damit wird immer der Bezug zum jeweiligen Vorjahr hergestellt.

Wertfeldeinstellungen anpassen

Vielleicht interessiert dich nun jedoch nicht der konkrete prozentuale Wert, sondern du möchtest nur die generelle Richtung sehen. Und hier reicht es, wenn zwar alle Umsatzrückgänge hervorgehoben werden aber die Steigerungen nur dann, falls es sich beispielsweise um mehr als 5 % zum Vorjahr handelt.

Dafür richtest du für die Spalte mit den Differenzen wieder eine bedingte Formatierungsregel ein und erhältst als Ergebnis die folgende Darstellung:

Einstellungen für Symbolsätze anpassen

Als Formatstil wählst du aus der Liste die Symbolsätze aus unter „Symbolart“ den Eintrag mit den drei farbigen Pfeilen:

Zuerst den Symbolsatz auswählen

Ein grüner Pfeil soll ab +5% Veränderung zum Vorjahr angezeigt werden, daher gibst du als Wert 0,05 ein und änderst den Typ auf „Zahl“. Damit die Steigerungen unter 5% nicht grafisch dargestellt werden, klickst du auf das Auswahlfeld neben dem gelben Pfeil und wählst den Eintrag „Kein Zellensymbol“. Als Wert gibst du 0 ein und als Typ wieder „Zahl“. Und nicht vergessen: Häkchen setzen bei „Nur Symbol anzeigen“. Fertig.

Die fertig angepasste Regel

Falls dir die Pfeile nun optisch zu dominant sind und du lieber eine etwas dezentere Variante hättest oder du stattdessen ein paar exotischere Symbole verwenden möchtest, für die keine Symbolsätze angeboten werden, musst du etwas tiefer in die Trickkiste greifen:

Ein paar alternative Darstellungen

Der Weg zu diesen beiden Alternativen führt nicht über die bedingte Formatierung, sondern über benutzerdefinierte Zahlenformate.

Entferne also die bedinge Formatierungsregel, so dass in der Pivot-Tabelle wieder die Prozentsätze angezeigt werden. Führen dann auf einen der Prozentsätze einen Rechtsklick aus und wähle im Kontextmenü den Eintrag „Zahlenformat…“

Das Zahlenformat wird geändert

Und jetzt kommt der Trick:
Markiere links die Kategorie „Benutzerdefiniert“ und leere das Eingabefeld „Typ“ und stelle dann den Cursor in das leer Typ-Feld. Danach drückst du die Tastenkombination Windows + Punkt, was ein neues Fenster mit allen möglichen und unmöglichen Symbolen öffnet.

Eigene Symbole mit Windows+Punkt einfügen

Nachdem du das Symbol „Pfeil nach rechts oben“ ausgewählt hast tippst du ein Semikolon in das Typ-Feld und holst dir dann das Symbol „Pfeil nach rechts unten“. Zur Erläuterung: Der erste Eintrag im Typ-Feld steht für positive Werte, der zweite für negative. Bei Bedarf kannst du für Nullwerte einen dritten Eintrag festlegen, der dann wieder durch ein Semikolon getrennt ist.
Für unsere Zwecke reichen aber zwei Einträge. Danach sieht alles so aus:

Das (noch unfertige) Benutzerformat

Geht schon in die richtige Richtung, aber eben noch nicht perfekt. Es fehlen zum einen die Farben Rot und Grün und außerdem werden jetzt für alle Einträge Pfeile angezeigt. Für die Steigerungen von 0 – 5% wollen wir aber keine. Daher muss noch ein wenig nachjustiert werden. Also öffne nochmal das Fenster mit den Zahlenformaten, wähle den eben erstellten benutzerdefinierten Eintrag aus und nimm folgende Änderungen vor:
[Grün][>0,05]↗;[Rot][<0]↘;

Besser: Mit Farben und Bedingungen

Wichtig:
Nicht das ; am Ende vergessen, sonst wird bei den Werten außerhalb des definierten Bereichs (also zwischen 0 und 5 %) nur #-Zeichen angezeigt.

Was bedeutet das jetzt?

Wie unschwer zu erkennen ist, kann man in die eckigen Klammern vor das jeweilige Symbol eine Farbangabe schreiben, die dann gleichzeitig mit dem Symbol angewendet wird. Erlaubt sind hier die Farben Schwarz, Grün, Weiß, Blau, Magenta, Gelb, Zyan und Rot.

Wer noch feinere Abstufungen benötigt: Excel erlaubt hier insgesamt 56 verschiedene Farbcodes, die dann als [Farbe1] bis [Farbe56] angegeben werden können. Für ein etwas dunkleres Grün sieht der Code so aus:
[Farbe10][>0,05]↗;[Rot][<0]↘;

Die komplette Farbtabelle findest du übrigens in der Beispieldatei.

Wenn du generell mehr über die benutzerdefinierten Formate erfahren möchtest, empfehle ich dir diesen Artikel: Wer eine Extrawurst braucht: Benutzerdefinierte Formate (und im Zweifel auch die Kommentare dort lesen. Hier finden sich weitere Hinweise auf Möglichkeiten und Grenzen).

Mit dem zweiten eckigen Klammerpaar lassen sich Bedingungen definieren, damit das jeweilige Format angewendet wird. Damit können wir dann genau unsere 5%-Regel abbilden. Und mit den Symbolen sind deiner Fantasie fast keine Grenzen gesetzt:

Fast alles ist möglich

Fazit

Wie du gesehen hast, lässt sich bereits durch die Wahl des Berichtslayouts und einer geeigneten PivotTable-Formatvorlage schon eine deutliche Verbesserung der Optik erzielen.

Wer es noch ein bisschen extravaganter möchte, kann durch bedingte Formatierungen und/oder benutzerdefinierte Zahlenformate mit wenig Aufwand noch mehr herausholen. Aber wie bei allen Dingen im Leben gilt auch hier:
Übertreibe es nicht. Schließlich soll aus deinen Pivot-Tabellen kein Comic werden. Oder vielleicht doch?

Welcher Pivot-Tabellen-Fraktion gehörst du an:
Standardeinstellungen unverändert übernehmen oder doch ein wenig aufhübschen? Lass es uns in den Kommentaren wissen!

Das könnte dich auch interessieren:

Vorher schon wissen, was hinterher rauskommt

Eingabemöglichkeiten einschränken

Dem Verbrecher auf der Spur

Das doppelte Lottchen, oder: Dublettensuche mit Excel