Was wäre ein Fisch ohne Fahrrad? Eine Brezn ohne Salz (ja, in Bayern sagt man Brezn und nicht Brezel)? Ein Kaiserschmarrn ohne Rosinen? Oder ein Controller ohne Pivot-Tabellen?
Genau: Etwas, das man unbedingt ändern sollte 🙂
Pivot-Tabellen machen die Auswertung auch von großen Datenmengen schon unheimlich einfach. Und mit den folgenden 10 Tipps wirst du zukünftig noch mehr Freude damit haben.
Und so geht’s:
Beispieldatei herunterladen
Tipp 1: Umbenennen von Spaltenüberschriften
Wenn du noch nicht so routiniert im Umgang mit Pivot-Tabellen bist, hast du dich vielleicht über folgendes schon einmal gewundert oder geärgert:
Wenn man ein Feld in den Wertebereich zieht, wird bei Zahlenfeldern immer die Summe gebildet. Und in der Pivot-Tabelle steht dann als Überschrift „Summe von (Name des Feldes)“, also z.B. „Summe von Umsatz“:
Standard-Überschriften für Wertefelder
Das ist zum einen nicht sehr schön, zum anderen macht es die Spalte unnötig breit. Ändert man den Namen jetzt einfach auf „Umsatz“, wird man mit einer unsinnigen Fehlermeldung konfrontiert:
Umbenennen in den Feldnamen führt zu einer Fehlermeldung
Um das zu vermeiden, kann man einfach ein Leerzeichen an den Namen anhängen. Schon wird die neue Spaltenüberschrift akzeptiert.
Der Trick: Ein angehängtes Leerzeichen
Das lässt sich ändern. Mein halbtägiger Pivot-Einsteigerkurs beginnt wirklich ganz am Anfang und setzt keine Pivot-Kenntnisse voraus. Bei Interesse findest du hier alle weiteren Infos:
Daten analysieren mit Pivot-Tabellen
Tipp 2: Automatische Spaltenbreitenanpassung
Eine ebenfalls sehr nervige Voreinstellung in Pivot-Tabellen ist, dass sich nach dem Aktualisieren immer sämtliche Spaltenbreiten automatisch anpassen. Gerade wenn man mühsam die Spalten manuell auf die gewünschte Breite eingestellt hat, ärgert man sich bei der nächsten Aktualisierung ganz besonders.
Über eine kleine Einstellung lässt sich dieses Ärgernis aber abstellen. Rechtsklick in die Pivot-Tabelle, dann „PivotTable-Optionen“ auswählen und das Häkchen vor dieser Option entfernen „Spaltenbreiten bei Aktualisierung automatisch anpassen“:
Spaltenbreiten werden nicht mehr automatisch angepasst
Tipp 3: Zahlenformate ändern
Auch dieser Fehler unterläuft den meisten Pivot-Einsteigern. Man möchte das Zahlenformat beispielsweise auf Währung ändern, damit die Umsätze auch schön dargestellt werden. Und wie man es aus normalen Tabellen gewöhnt ist, würde man jetzt vermutlich alle Umsatz-Werte markieren und dann über das Menüband das Währungsformat auswählen.
Der nicht empfehlenswerte Weg zum Ändern von Formatierungen
Das Problem dabei:
Pivot-Tabellen sind dynamisch und können sich deshalb in der Länge und Breite ändern. Und damit kann es passieren, dass beim nächsten Aktualisieren nicht mehr jeder Umsatzwert im gewünschten Währungsformat angezeigt wird.
Die richtige Vorgehensweise wäre diese:
Man führt einen Rechtsklick auf einen beliebigen Umsatzwert aus (nein, es müssen nicht alle Werte markiert werden). Und wählt dann den Eintrag „Zahlenformat“ (nicht „Zellen formatieren…“!).
Der richtige Weg
Im nächsten Fenster wählt man das Währungsformat aus und das war’s auch schon. Alle Umsatzwerte werden jetzt im gewünschten Format dargestellt. Auch dann, wenn sich die Pivot-Tabelle zukünftig ausdehnen sollte.
Bonus-Tipp:
Wer Microsoft 365 im Einsatz hat, kann sich diese Anpassung in vielen Fällen sparen. Seit Kurzem wird beim Erstellen der Pivot-Tabelle das Zahlenformat aus der Quelltabelle übernommen. Wenn dort also bereits die Währung eingestellt ist, dann erscheint das auch automatisch in der Pivot-Tabelle.
Tipp 4: Filtern nach Top-3-Werten
Die Chefin möchte in der Pivot-Tabelle nur die Top-3-Produkte je Verkäufer sehen. Jetzt gibt es ja über den Feldern im Zeilenbereich die bekannten Filter-Dropdowns. Aber da kann man in meinem Beispiel nur nach den Produktbezeichnungen filtern. Und über den Wertespalten gibt es keine Filter-Dropdowns:
Kein Filterdropdown auf dem Wertefeld
Was mache ich also jetzt?
Ganz einfach: Ich öffne das Filterfeld beim Produkt und wähle dort „Wertefilter“ und dann „Top-10“. Im nächsten Fenster ändert man dann den Wert von 10 auf 3 und wählt ggf. noch das Umsatzfeld aus:
Der Wertefilter im Zeilenfeld bringt die Lösung
Fertig!
Die Top-3-Produkte je Verkäufer
Tipp 5: Formatvorlagen lassen sich nicht ändern
Du hast dir vielleicht eine eigene PivotTable-Formatvorlage erstellt und möchtest diese jetzt nochmal anpassen. Wenn nun nach einem Rechtsklick auf die Vorlage und „Ändern“ jedoch nichts passiert, was dann?
Eigene Formatvorlage lässt sich nicht mehr ändern
Das Ärgerliche dabei ist, dass es noch nicht einmal eine Fehlermeldung gibt! Die Ursache dafür ist in diesem Fall, dass irgendein Arbeitsblatt in deiner Datei einen aktivierten Blattschutz hat. Und das muss nicht einmal das Blatt mit der Pivot-Tabelle sein. Erst wenn kein einziges Blatt mehr geschützt ist, lässt sich die Formatvorlage wieder ändern.
Am schnellsten findest du den oder die Übeltäter über das Menü „Datei | Informationen“
Ursache: Arbeitsblätter mit aktiviertem Blattschutz
Hier kannst du über den entsprechenden Link den Schutz auch direkt aufheben – bei passwortgeschützten Blättern brauchst du natürlich das entsprechende Passwort.
Tipp 6: Drilldown
Die hast für die Chefin ganz stolz eine schöne Pivot-Tabelle vorbereitet. Und nun möchte sie wissen, welche Umsätze sich genau hinter einer bestimmten Zahl verbergen. Natürlich kannst du jetzt mühsam die Quelltabelle nach den entsprechenden Kriterien filtern.
Aber es geht deutlich schneller:
Einfach einen Doppelklick auf den gewünschten Wert ausführen, zum Beispiel auf den Umsatz für Verkäufer Fischer und das Produkt Tango. Und schön öffnet sich ein neues Arbeitsblatt, in dem fein säuberlich sämtliche Datensätze aufgelistet sind, die sich hinter dieser Zahl verbergen.
Doppelklick auf einen Wert erzeugt ein neues Datenblatt (Drilldown)
Das neue Blatt kannst du anschließend wieder gefahrlos löschen, es ist lediglich ein Auszug aus der Original-Tabelle.
Tipp 7: Gelöschte Werte entfernen
Wenn man länger mit der gleichen Pivot-Tabelle arbeitet und sich die Quelldaten schon mehrfach geändert haben, kann folgendes Phänomen auftreten:
In den Filtern tauchen immer noch Einträge auf, die in der Quelltabelle schon gar nicht mehr existieren und daher auch keine Werte mehr liefern (ich nenne so etwas „Filter-Leichen“).
Nicht mehr existierende Filtereinträge
Der Grund dafür liegt in einer Voreinstellung, die man ganz einfach ändern kann. Über einen Rechtsklick in die Pivot-Tabelle ruft man die PivotTable-Optionen auf. Dort ändert man im Register „Daten“ die Einstellung „Anzahl der pro Feld beizubehaltenden Elemente“ einfach auf „Keine“
Diese Einstellung beseitigt Filter-Leichen
Nach dem nächsten Aktualisieren der Pivot-Tabelle sind unsere Filter-Leichen dann endgültig begraben.
Tipp 8: Elemente ohne Daten anzeigen
Normalerweise werden in einer Pivot-Tabelle immer nur die Einträge angezeigt, die auch in den Quelldaten vorhanden sind. Gibt es beispielsweise für einen bestimmten Verkäufer keine Umsätze für ein bestimmtes Produkt, wird diese Zeile auch unterdrückt. In meinem Beispiel hat Verkäufer Fischer das Produkt Chromo nicht verkauft, das es bei den anderen Verkäufern gibt:
Kein Chromo-Umsatz bei Verkäufer Fischer
Manchmal ist es aber aus optischen Gründen oder aus Gründen der besseren Vergleichbarkeit gewünscht, dass es für alle Verkäufer gleich viele Zeilen gibt. Mit der folgenden Einstellung lässt sich das bewerkstelligen:
Rechtsklick auf einen beliebigen Eintrag in der Produktspalte und dann die Option „Feldeinstellungen…“ auswählen. Im Register „Layout&Drucken“ setzt man nun das Häkchen bei „Elemente ohne Daten anzeigen“:
Elemente ohne Daten anzeigen
Und erhält das gewünschte Ergebnis:
Produkt Chromo wird jetzt angezeigt
Tipp 9: Für leere Zellen einen Wert anzeigen
Soll anstelle einer leeren Zelle ein bestimmter Wert angezeigt werden, z.B. eine Null oder ein „-/-„, trägt man diesen Wert in den PivotTable-Optionen im folgenden Feld ein:
Eine Null für leere Wertezellen anzeigen lassen
Hinweis:
Ein einfacher Bindestrich wird zwar als Eingabe akzeptiert, führt aber in der Anzeige nur zu einer Null.
Tipp 10: Voreinstellungen für Pivot-Tabellen festlegen
Wer eine aktuelle Excel-Version im Einsatz hat (Microsoft 365, Excel 2021 oder Excel 2024), kann sich das Pivot-Leben noch ein wenig vereinfachen. Hier gibt es nämlich in den Excel-Optionen die Möglichkeit, bestimmte Voreinstellungen dauerhaft zu setzen.
Menü „Datei | Optionen | Daten | Standardlayout bearbeiten…“
Standardlayout für Pivot-Tabellen festlegen
Hier lässt sich beispielsweise das Berichtslayout dauerhaft auf das Tabellenformat einstellen. Über die Schaltfläche „PivotTable-Optionen…“ gelangt man dann in das Optionen-Fenster, das ich schon in den anderen Tipps gezeigt habe. Und so kann man die automatische Spaltenbreitenanpassung (was für ein Wort) dauerhaft abschalten und auch die Einstellung zu den „Daten-Leichen“ ändern (Anzahl der pro Feld beizubehaltenden Elemente…).
Auch PivotTable-Optionen lassen sich vordefinieren
Das Geniale dabei:
Diese Einstellungen sind global und gelten ab sofort für jede neu erstellte Pivot-Tabelle. Und zwar nicht nur in der aktuellen Arbeitsmappe, sondern auch in jeder anderen Datei!
Welcher der genannten Tipps war für dich neu oder ist besonders hilfreich? Schreib es gerne in die Kommentare!
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 Dank, Martin
Deine Tipps helfen mir, mich mit Pivot anzufreunden.
Sie zeigen eine Auswahl der vielen Möglichkeiten, die Pivot bietet.
Das wiederum motiviert mich, Zeit zu investieren und mit „meinen“ Daten ansprechende Auswertungen zu machen.
viele Grüsse
Hallo Tanja,
das hört man gerne. Es lohnt sich definitv, sich ein wenig mit Pivot-Tabellen zu beschäftigen. Manche Auswertungen werden so viel einfacher damit.
Schöne Grüße,
Martin
hallo Martin
Danke für die wunderbaren Tipps.
über die „Filter-Leichen“ habe ich mich schon oft geärgert. Gut zu wissen wie man die verhindert.
Danke
Hallo Christian,
danke fürs Feedback! Freut mich, wenn die Tipps weiterhelfen.
Schöne Grüße,
Martin
Hallo Martin,
Tipp 8 und 9 waren heute meine Favoriten – Danke – wieder was gelernt 🙂
Ich hätte noch gern, dass die Werte-Spalte automatisch als „Summe“ angezeigt wird und nicht als „Anzahl“ – geht das?
LG Dagmar
Hallo Dagmar,
wenn bei dir ein Wert mit Anzahl zusammengefasst wird, dann liegt das daran, dass in der betreffenden Spalte nicht nur Zahlen enthalten sind (das können ggf. auch Leerzeichen sein).
Reine Zahlenspalten werden automatisch mit Summe zusammengefasst.
Schöne Grüße,
Martin
Hallo Martin,
mein Favorit ist definitiv Tipp 10. Habe ich so auch gleich umgesetzt. Aber auch die anderen Tipps sind nicht zu verachten. Mit manchem Tipp hast du auch meine Erinnerungen wieder etwas aufgefrischt. Da war doch mal was!!!
Viele Grüße, Harald.
Hallo Harald,
ja, die Standardeinstellungen in den Excel-Optionen können einem wirklich einiges an Arbeit ersparen. Danke für dein Feedback!
Schöne Grüße,
Martin