Pivot-Tabelle erstellen: sechs Handgriffe bis zur Auswertung
Der Aufwand steckt nicht im Erstellen, sondern in der Quelle, denn die braucht genau eine Kopfzeile, keine verbundenen Zellen und keine eingestreuten Zwischensummen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Fast jeder Pivot-Fehler steckt schon in der Liste darunter
Eine Pivot-Tabelle ist in zwei Minuten gebaut. Der Ärger kommt danach: In der Auswertung steht Anzahl von Umsatz statt einer Summe, neue Zeilen aus der Quelle tauchen nicht auf, oder Excel meldet beim Anlegen, der PivotTable-Feldname sei ungültig. Alle drei Fälle haben nichts mit der Pivot zu tun, sondern mit der Liste, auf der sie sitzt.
Die Ursachen wiederholen sich: eine leere Überschrift, verbundene Zellen in der Kopfzeile, ein einziges n.v. in einer Zahlenspalte, Zwischensummen zwischen den Datenzeilen oder ein fester Zellbereich als Quelle, der bei Zeile 4000 endet. Jede dieser Kleinigkeiten kostet Suchzeit, weil man sie in der Auswertung sucht statt in den Rohdaten.
Richtig teuer wird aber der Fall, in dem nichts kaputt aussieht. Sitzt die Pivot auf einem festen Bereich und sind im Februar dreihundert Zeilen dazugekommen, liefert sie weiterhin ein plausibles Ergebnis, nur eben das falsche. Solche Zahlen laufen ohne Rückfrage in Berichte und fallen oft erst auf, wenn jemand von außen nachrechnet.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Schritt für Schritt
- 1
Quelle in Form bringen
Entfern Zwischensummen und Leerzeilen, lös verbundene Zellen in der Kopfzeile auf und gib jeder Spalte eine eigene, eindeutige Überschrift.
Geschafft, wenn: Jede Spalte trägt eine Überschrift, keine Zeile enthält eine Zwischensumme.
- 2
Als Tabelle formatieren
Markier die Liste, drück Strg+T und vergib unter Tabellenentwurf > Tabellenname einen sprechenden Namen wie tblUmsatz.
Geschafft, wenn: Der Name steht im Feld Tabellenname, die Liste hat Filterpfeile.
- 3
Gerüst anlegen
Geh auf Einfügen > PivotTable > Aus Tabelle/Bereich und leg die Auswertung auf einem neuen Blatt an.
Geschafft, wenn: Auf dem neuen Blatt steht ein leerer Rahmen, rechts die Feldliste.
- 4
Felder verteilen
Zieh das Gruppierungsmerkmal nach Zeilen und die Kennzahl nach Werte. Datumsfelder zerlegst du über die rechte Maustaste und Gruppieren in Jahre, Quartale und Monate.
Geschafft, wenn: In der Auswertung stehen Zeilenbeschriftungen und mindestens eine Zahl.
- 5
Berechnung, Format und Layout setzen
Öffne die Wertfeldeinstellungen, wähl unter Werte zusammenfassen nach die richtige Berechnung und setz das Zahlenformat über die Schaltfläche im selben Dialog, nicht über die Zellformatierung. Soll aus der Auswertung weitergerechnet werden, stell zusätzlich unter Entwurf > Berichtslayout auf Tabellenformat, aktivier Alle Elementnamen wiederholen und schalt die Teilergebnisse ab.
Geschafft, wenn: In der Wertespalte steht die gewünschte Berechnung im richtigen Format, und jede Zeile ist vollständig gefüllt.
- 6
Filtern und aktualisieren
Füg über PivotTable-Analyse > Datenschnitt einfügen die Filter zum Anklicken hinzu und verbinde sie über die Berichtsverbindungen mit allen Auswertungen des Blattes.
Geschafft, wenn: Ein Klick im Datenschnitt schaltet das ganze Blatt gleichzeitig um.
Sechs Schritte von der Rohliste zur Auswertung
- 01 Die Quelle hat genau eine Kopfzeile und keine Zwischensummen dazwischen.
- 02 Strg+T macht daraus eine Tabelle, die mit neuen Zeilen mitwächst.
- 03 Einfügen > PivotTable legt das leere Gerüst auf einem neuen Blatt an.
- 04 In Zeilen kommt, wonach du gruppierst, in Werte, was gerechnet wird.
- 05 Die Wertfeldeinstellungen legen Berechnungsart und Zahlenformat fest.
- 06 Ein Datenschnitt steuert über Berichtsverbindungen mehrere Auswertungen.
Danach baust du Auswertungen, die auch nächsten Monat noch stimmen
Die Reihenfolge ist wichtiger als die Klicks. Erst die Quelle in Form bringen, dann als Tabelle formatieren, dann das Gerüst anlegen und die Felder verteilen. Wenn die ersten beiden Schritte sitzen, ist der Rest in wenigen Minuten erledigt und bleibt bei jeder Aktualisierung stabil.
Die Quelle beurteilen
Du erkennst in wenigen Sekunden, ob eine Liste als Pivot-Quelle taugt: eine Kopfzeile, je Zeile ein Vorgang, keine verbundenen Zellen, keine Zwischensummen. Kreuztabellen schickst du vorher durch Andere Spalten entpivotieren.
Tabelle statt Zellbereich
Mit Strg+T und einem sprechenden Tabellennamen wächst die Quelle mit jeder neuen Zeile mit. Damit entfällt das Nachjustieren über Datenquelle ändern, das man ohnehin irgendwann vergisst.
Die vier Bereiche zuordnen
In Zeilen kommt, wonach du gruppierst, in Werte, was gerechnet wird. Mit Spalten gehst du sparsam um, weil jede Ausprägung eine weitere Spalte erzeugt.
Anzahl statt Summe reparieren
Du weißt, dass eine Textzelle oder eine Leerzelle im Wertebereich die Ursache ist, und findest sie über einen Filter auf die Quellspalte. Danach stellst du in den Wertfeldeinstellungen wieder auf Summe.
Anteile ohne Formel
Über Werte anzeigen als bekommst du den Anteil am Gesamtergebnis oder am Ergebnis der übergeordneten Zeile. Damit beantwortest du die Frage nach dem Anteil innerhalb einer Produktgruppe ohne eine einzige Hilfsspalte.
Ein Datenschnitt für mehrere Pivots
Über die Berichtsverbindungen eines Datenschnitts steuerst du alle Auswertungen eines Blattes gleichzeitig, sofern sie dieselbe Quelle nutzen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Quelle entscheidet über alles Weitere
Eine Pivot-Tabelle will eine Liste: eine einzige Kopfzeile, darunter je Zeile ein Vorgang, je Spalte ein Merkmal. Jede Spalte braucht eine eigene, eindeutige Überschrift. Ist eine Überschrift leer, meldet Excel beim Erstellen "Der PivotTable-Feldname ist ungültig", und das ist fast immer die Ursache, nicht die Formel weiter unten. Verbundene Zellen in der Kopfzeile führen zum selben Ergebnis. Zwischensummen und Leerzeilen mitten in der Liste zählt die Pivot einfach mit.
Markier die Liste und drück Strg+T. Damit wird daraus eine formatierte Tabelle, und über Tabellenentwurf > Tabellenname gibst du ihr einen sprechenden Namen wie tblUmsatz. Der Nutzen zeigt sich beim ersten Nachschub: Neue Zeilen gehören automatisch zur Tabelle und damit zur Pivot-Quelle. Bei einem festen Zellbereich müsstest du dagegen jedes Mal über PivotTable-Analyse > Datenquelle ändern nachjustieren, und wer das vergisst, wertet stillschweigend einen alten Stand aus.
Kommt der Bericht als Kreuztabelle, mit den Monaten nebeneinander als eigene Spalten, ist er als Quelle unbrauchbar. Solche Daten müssen erst in eine Liste zurück, in Power Query über Transformieren > Andere Spalten entpivotieren.
Felder in die vier Bereiche ziehen
Einfügen > PivotTable > Aus Tabelle/Bereich legt das Gerüst auf einem neuen Blatt an. Rechts erscheint die Feldliste mit den vier Bereichen Filter, Spalten, Zeilen und Werte. Die Zuordnung ist einfacher, als sie aussieht: In Zeilen kommt, wonach du gruppierst, in Werte, was gerechnet wird. Mit Spalten geh sparsam um, denn jede Ausprägung erzeugt eine weitere Spalte, und bei einem Feld mit vierzig Kunden ist die Auswertung nicht mehr lesbar.
Datumsfelder lassen sich über die rechte Maustaste und Gruppieren in Jahre, Quartale und Monate zerlegen, Excel legt dafür eigene Felder an. Voraussetzung ist ein echtes Datum. Erkennen kannst du das in der Quelle daran, dass die Werte ohne Formatierung rechtsbündig stehen. Linksbündige Datumsangaben sind Text und lassen sich nicht gruppieren.
Für alles, was danach weiterverarbeitet wird, stell das Layout um: Entwurf > Berichtslayout > In Tabellenformat anzeigen, dazu "Alle Elementnamen wiederholen" und Entwurf > Teilergebnisse > Teilergebnisse nicht anzeigen. Damit bekommst du eine vollständig gefüllte Tabelle statt der Gliederungsansicht mit Lücken, aus der sich nicht sinnvoll weiterrechnen lässt.
Anzahl statt Summe, und die anderen Klassiker
Wenn in Werte plötzlich "Anzahl von Umsatz" steht, liegt es nie an der Pivot, sondern an der Spalte. Excel wählt Anzahl, sobald mindestens eine Zelle im Wertebereich leer ist oder Text enthält, und ein einziges "n.v." unter zehntausend Zahlen reicht dafür. Setz einen Filter auf die Quellspalte und sortier absteigend, dann stehen die Textwerte gesammelt oben oder unten. Nach der Reparatur stellst du über Wertfeldeinstellungen > Werte zusammenfassen nach wieder auf Summe.
Das Zahlenformat gehört ebenfalls in die Wertfeldeinstellungen, dort gibt es die Schaltfläche Zahlenformat. Formatierst du stattdessen die Zellen im Blatt, verlierst du die Einstellung, sobald du ein Feld umbaust oder die Pivot größer wird. Über Werte anzeigen als bekommst du außerdem Anteile ohne eine einzige Formel, etwa "% des Gesamtergebnisses" oder "% des Ergebnisses in der übergeordneten Zeile", was bei Produktgruppen und Produkten die Frage nach dem Anteil innerhalb der Gruppe beantwortet.
Dasselbe Feld darf mehrfach im Wertebereich liegen. Zieh den Umsatz dreimal hinein und stell einmal Summe, einmal Anzahl und einmal Anteil am Gesamtergebnis ein, dann stehen Umsatzsumme, Zahl der Vorgänge und Anteil nebeneinander in einer Auswertung.
Filtern mit Datenschnitten und aktualisieren
Datenschnitte sind Filter zum Anklicken. Du fügst sie über PivotTable-Analyse > Datenschnitt einfügen hinzu, für Datumsfelder gibt es daneben die Zeitachse. Der eigentliche Gewinn kommt bei mehreren Auswertungen auf einem Blatt: Markier den Datenschnitt, geh auf die Registerkarte Datenschnitt und öffne Berichtsverbindungen, dort hakst du alle Pivot-Tabellen an, die er steuern soll. Danach schaltet ein Klick das ganze Blatt um. Voraussetzung ist, dass die Pivots dieselbe Quelle nutzen.
Aktualisiert wird über PivotTable-Analyse > Aktualisieren, per Tastatur mit Alt+F5 für die aktuelle und Strg+Alt+F5 für alle Auswertungen der Mappe. Zwei Einstellungen ersparen dir dabei Ärger, beide unter PivotTable-Optionen. Auf der Registerkarte Layout & Format nimmst du den Haken bei "Spaltenbreiten bei Aktualisierung anpassen" heraus, sonst springt dein Layout bei jedem Durchlauf. Auf der Registerkarte Daten setzt du "Anzahl der pro Feld beizubehaltenden Elemente" auf Keine, damit gelöschte Kunden nicht monatelang in den Filterlisten stehen bleiben.
Dazu passende Kurse
Der Aufbau ist in einer Stunde erklärt, die Fälle aus dem Alltag brauchen Übung, deshalb kannst du Pivot-Auswertungen im Excel-Seminar lernen und dort deine eigenen Berichte mitbringen.
Wenn eine Auswertung nicht nur schnell, sondern auch belastbar sein soll, helfen Kurse rund um Datenqualität und Kennzahlen beim Blick auf die Zahlen dahinter.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Der passende Lernpfad
Wenn du nicht nur ein Thema abhaken, sondern eine Rolle ausfüllen willst, zeigt dir der Lernpfad die Kurse in der Reihenfolge, in der sie aufeinander aufbauen.
Excel: Datenanalystin & Datenanalyst
Dieser Lernpfad richtet sich an alle, die bereits mit Excel arbeiten und nun lernen möchten, wie sie Daten systematisch analysieren, interpretieren und in fundierte Entscheidungen überführen können. Er ist ideal für angehende Datenanalysten, Business Professionals, Controller und alle, die große Datenmengen effizient verarbeiten möchten. Starte einfach durch!
Die Grundlagen
Daten und Pivot
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Sehr gut vorbereitetes Seminar mit hilfreichen Informationen und anschaulicher Vermittlung. Vielen Dank!
Häufige Fragen
Warum tauchen neue Zeilen aus der Quelle in der Pivot nicht auf?
Kann ich innerhalb der Pivot-Tabelle rechnen?
Wie zähle ich Kunden ohne Dubletten?
Kann ich das Layout einer Pivot dauerhaft festlegen?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Excel-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Von der ersten Pivot zur Auswertung, der du im Bericht vertraust
Wie du Quelle, Layout und Berechnungsart in den Griff bekommst, übst du bei cmt an eigenen Daten statt an Beispieltabellen.