Pivot-Tabellen-Kosmetik 5

Mit ein paar Kniffen lassen sich nüchterne Pivot-Tabellen ein wenig aufhübschen
 

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

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

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

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

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

Ü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

Benutzerformat mit drei Semikolons

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

Sieht gleich besser aus...

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

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

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

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

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

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

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

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

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

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

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

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?

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Pivot-Tabellen-Kosmetik

    • Martin Weiß Autor des Beitrags

      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

      • Gabizi

        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