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 zeige ich dir ein paar nicht ganz so offensichtliche Tricks, mit denen du bei deinem Chef noch mehr Eindruck bei deinen Pivot-Tabellen schinden kannst.
Und so geht’s:
Pivot-Tabellen und bedingte Formatierung
Wie auch in normalen Excel-Tabellen lassen sich auch innerhalb von Pivot-Tabellen Regeln für eine bedingte Formatierung einbauen. Diesem Thema hatte ich vor einiger Zeit Bedingte Formatierung in Pivot-Tabellen, 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 Beispieldatei zu diesem Artikel kannst du dir übrigens hier herunterladen.
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
Allerdings werden dadurch die Zahlen schlechter lesbar. Und der echte Controller liebt natürlich die Zahlen 🙂
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
Das Wichtigste hier ist, dass das Häkchen bei der Option „Nur Balken anzeigen“ gesetzt ist.
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…
Bedingte Formatierung vs. 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 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 (der nur mit Windows 10 funktioniert!):
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.
Mit Windows+Punkt ein Symbolfenster einblenden
Dort findest du beispielsweise unter der Kategorie mit den Herzen verschiedene Pfeilsymbole, die du durch einfaches Anklicken in das Typ-Feld übernehmen kannst.
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
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 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
Aber wie fast bei allen Dingen im Leben gilt auch hier:
Übertreibe es nicht. Schließlich soll aus deinen Pivot-Tabellen kein Comic werden. Oder vielleicht doch?
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Vielen lieben Dank, bin Dein Fan☺️??
Hallo Peter,
freut mich sehr, wenn der Artikel geholfen hat.
Schöne Grüße,
Martin
Hallo Martin,
super Sache, doch leider kann ich die Symbole nicht aus der „Emoji“ Tabelle übernehmen. Hast du eine Idee?
Hallo Gabizi,
wenn du zuerst den Cursor in das Typ-Feld im Format-Dialog stellst, dann sollten die Emojis einfach durch Anklicken dorthin übernommen werden. Vielleicht warst du einfach nicht im Feld gestanden.
Schöne Grüße,
Martin
Hallo Martin,
der Curser war schon an der richtigen Stelle 😉 Es liegt an meiner Verbindung im Homeoffice. Vor Ort hat es wunderbar geklappt. Danke für den Tipp.
Schöne Grüße
Gabizi
Hallo Martin,
super Beitrag, vielen Dank – ich habe eine Menge neues kennengelernt.
Ich habe ein Rätsel, das ich selbst nicht lösen kann: Ich habe eine Pivot-Tabelle, in den Zeilen habe ich Marken (um es Konkret zu machen, Milka, Lindt, Alpia).
In den Werten habe ich 2 Spalten: Sorten (z.B. Vollmilch, Zartbitter, Nuss) und „getestet“. D.h. es sieht etwa so aus:
Lindt | 12 | 4
Milka | 18 | 8
Alpia | 7 | 3
Könnte ich in einer weiteren Spalte eine Berechnung einfügen, wie viel % der Sorten getestet wurden? Ich habe mit „Daten zeigen als“ … „% von“ experimentiert, aber ich glaube da liege ich falsch.
Hallo Chris,
danke für das tolle Feedback. Ich kann jetzt nur spekulieren, wie deine Quelltabelle aufgebaut ist, aber ich nehme mal an es sind ebenfalls drei Spalten und wenn eine Sorte getestet wurde, dann gibt es in der entsprechenden Spalte ein Kreuzchen oder etwas ähnliches. Sprich: Es handelt sich immer um Texteinträge, richtig?
Dann sieht es leider schlecht aus mit der Anteilsberechnung. Grundsätzlich wäre so etwas mit einem berechneten Feld möglich. Dort kann man beispielsweise einfache Division oder ähnliches mit zwei Spalten anstellen. Aber das geht eben nur dann, wenn die Spalten numerische Werte enthalten, nicht jedoch bei Texten.
Schöne Grüße,
Martin
Hallo Martin,
danke für die Erklärung, das hatte ich fast befürchtet. Da die Daten ursprünglich aus einer MySQL-Datenbank sind, habe ich die Berechnungen direkt in der SQL-Query durchgeführt. In Excel wäre es mir lieber gewesen, weil dann die Empfänger der Daten sehen, wie die Berechnungen sind. Aber es ist auch immer gut, einzusehen, wenn man ein Tool missbraucht 😉
Viele Grüße
Chris
Gibt es auch eine Möglichkeit, die Spalten gezielt zu formatieren? Konkret habe ich den Fall, dass ich in den Spalten sowohl Jahre als auch Monate ausgebe. Ich hätte jetzt gerne die Bänderungen nicht nur je Spalte, sondern für die Jahre noch anders formatierte Bänder. Ich hatte vermutet, dass beim Format ändern der Punkt „Stripeset der zweiten Spalte“ angepasst werden muss – das dort hinterlegte Format mit anders gefährbten Bändern wird in der Vorschau auch dargestellt, aber in der tatsächlichen Pivot bleibt lediglich das Format des Stripesets der ersten Spalte. Habe ich das falsch verstanden? Bin bei der Recherche leider auf keine brauchbaren Ergebnisse gekommen, vielleicht lohnt sich ein Beitrag mit Erklärungen und Anwendungsbeisipielen zu allen Tabellenelementen der Pivot-Tabelle 🙂
Hallo Julius,
ich denke, du bist grundsätzlich schon auf der richtigen Spur. Wenn die Formatierung nur in der Vorschau angezeigt wird, dann musst du vermutlich nur noch im Menüband unter „Entwurf“ das Häkchen bei „Gebänderte Spalten“ setzen.
Schöne Grüße,
Martin
Hi Martin!
Super Sache mit der Kosmetik, jedoch stehe ich dabei vor folgendem Problem in der PIVOT.
Ich habe ein PIVOT mit Jahr und Monat und Werten, die man dann auf- und zuklappen kann.
Selbst wenn ich die Bedingte Formatierung über die komplette Spalte erstelle, wird die Formatierung bei jedem auf-/zuklappen wieder aus den Zellen entfertn, bzw ist nicht mehr sichtbar, sondern es erscheinen die reinen Zahlen.
Hallo Maik,
das klingt für mich etwas merkwürdig, denn bedingte Formatierungen verschwinden eigentlich nicht durch ein- oder ausklappen von Zeilen oder Spalten. Das passiert normalerweise höchstens dann, wenn die Pivot-Tabelle zwischenzeitlich „umgebaut“ wird, also irgendwelche Felder ausgetauscht werden.
Ansonsten habe ich spontan auch keine Erklärung für das beschriebene Verhalten…
Schöne Grüße,
Martin
Lieber Martin,
erstmal vielen Dank für deine Arbeit! Sie hat mir schon oft und viel geholfen und Du erklärst auch super – nicht einfach „klick hier, klick da“, sondern „klicke hier, damit… und dann da, weil…“. Das erweitert das Verständnis doch erheblich.
Bei Pivot-Tabellen brauche ich in ungefähr 0,3% der Fälle die Standardeinstellung im Gliederungsformat und vielleicht in 25% der Fälle die Zwischensummen. Ich meine mich dunkel dran zu erinnern, dass Du irgendwo mal geschrieben hast, dass man ab Excel 365 diese Voreinstellung ändern kann. Gefunden habe ich dazu leider nichts, nicht in deinen Artikeln und nicht im Web und unter Start/Optionen…, wo ich solche Einstellungen vermuten würde, sind sie nicht da.
Kannst Du hier aushelfen?
Hallo Detlef,
erst einmal vielen Dank für das tolle Feedback, so etwas lese ich natürlich immer gerne 🙂
Was die Standardeinstellungen für Pivot-Tabellen angeht: Ja, in Excel 365 kann man da etwas machen. Du findest das in den Excel-Optionen unter der Kategorie „Daten“. Dort gibt es gleich am Anfang eine Schaltfläche „Standardlayout bearbeiten…“. In dem dann folgenden Fenster lässt sich das gewünschte Layout und die Teilergebnisse/Gesamtergebnisse einstellen. Und in diesem Fenster gibt es auch
eine weitere Schaltfläche „PivotTable-Optionen…“, mit der sich noch andere Dinge vorbelegen lassen, wie z.B. dass sich die Spaltenbreiten beim Aktualisieren nicht automatisch anpassen soll und ähnliches.
Schöne Grüße,
Martin