Alte Werte in einer Pivot-Tabelle entstehen meist nicht durch einen Fehler in der Auswertung, sondern durch den PivotCache. Excel speichert darin Elemente, die früher in der Quelldatenliste vorhanden waren. Selbst wenn du diese Einträge inzwischen löschst, können sie in Filtern oder der Feldliste weiter erscheinen. Prüfe zuerst die Datenquelle und aktualisiere anschließend die Pivot-Tabelle. Wenn die alten Einträge danach noch sichtbar sind, muss die Speicherung nicht mehr verwendeter Elemente angepasst oder der PivotCache neu aufgebaut werden.
Bevor du Daten löschst oder die Pivot-Tabelle neu erstellst, speichere eine Kopie der Arbeitsmappe. Bei größeren Berichten können mehrere Pivot-Tabellen denselben Cache verwenden. Änderungen an der Quelle oder am Cache wirken dann möglicherweise auf mehr als eine Auswertung.
Welche Aufgabe der PivotCache übernimmt
Eine Pivot-Tabelle liest ihre Daten nicht bei jeder Filteraktion vollständig neu aus dem Arbeitsblatt. Excel legt zunächst eine interne Zwischenspeicherung an, den PivotCache. Dieser enthält die Quelldaten beziehungsweise die daraus ermittelten Feld- und Elementinformationen. Die Pivot-Tabelle greift beim Sortieren, Filtern und Gruppieren auf diese Struktur zurück.
Wird ein Wert aus der Quelldatenliste entfernt, verschwindet er deshalb nicht zwingend sofort aus dem Cache. Das gilt besonders für Einträge, die zuvor in einem Zeilen-, Spalten- oder Berichtsfilter verwendet wurden. Ein Aktualisieren übernimmt zwar neue oder geänderte Quelldaten, entfernt aber je nach Einstellung nicht automatisch alle früher gespeicherten Elemente.
Wichtig ist die Unterscheidung zwischen drei Fällen:
- Der alte Wert steht noch in der tatsächlichen Datenquelle. Dann ist die Quelle selbst zu bereinigen.
- Der Wert ist aus der Quelle verschwunden, wird aber weiterhin im Filter angeboten. Dann handelt es sich wahrscheinlich um einen gespeicherten Cache-Eintrag.
- Der Wert erscheint weiterhin als Ergebniszeile. Dann kann zusätzlich ein Filter, eine Gruppierung, eine berechnete Struktur oder eine zweite Datenquelle beteiligt sein.
Erst die Datenquelle und den Filter prüfen
Klicke in die Pivot-Tabelle und öffne im Menüband die Registerkarte PivotTable-Analyse. Je nach Excel-Version kann sie auch Analysieren heißen. Über Datenquelle ändern siehst du, welcher Bereich, welche Excel-Tabelle oder welches Datenmodell verwendet wird.
Prüfe dort, ob die gelöschten Datensätze tatsächlich außerhalb der Quelle liegen. Bei einem festen Zellbereich kann ein alter Wert noch in einer nicht bemerkten Zeile stehen. Bei einer als Tabelle formatierten Quelle kontrollierst du die Tabelle selbst. Entferne außerdem leere oder ausgeblendete Zeilen, wenn sie einen unerwünschten Eintrag enthalten.
Danach aktualisierst du die Auswertung über einen Rechtsklick in der Pivot-Tabelle und Aktualisieren. Alternativ findest du den Befehl im Menüband. Kontrolliere anschließend den betreffenden Filter. Taucht der alte Wert nur dort auf, nicht aber in der Quelldatenliste, liegt die Ursache sehr wahrscheinlich in der Aufbewahrung alter Cache-Elemente.
Alte Elemente im PivotCache nicht mehr aufbewahren
Die entscheidende Einstellung befindet sich in den PivotTable-Optionen. Klicke in die Pivot-Tabelle, öffne über das Menüband die Optionen und wechsle zum Bereich Daten. Suche nach der Einstellung Anzahl der pro Feld beizubehaltenden Elemente oder einer ähnlich benannten Option. Stelle sie auf Keine.
Die genaue Bezeichnung kann sich zwischen Excel-Versionen unterscheiden. Gemeint ist die Einstellung, mit der Excel alte Elemente eines Feldes im PivotCache aufbewahrt. Bei einer Einstellung wie Automatisch oder einer hohen Anzahl können frühere Werte weiterhin in Auswahlfeldern erscheinen.
Bestätige die Änderung mit OK und aktualisiere danach die Pivot-Tabelle. In vielen Fällen verschwinden die alten Werte jetzt aus den Filtern. Bei mehreren Pivot-Tabellen wiederholst du die Einstellung für jede Auswertung, sofern sie nicht denselben Cache verwenden.
So gehst du bei verschiedenen Excel-Versionen vor
In aktuellen Desktop-Versionen von Excel findest du die Optionen meist über einen Rechtsklick in der Pivot-Tabelle und den Eintrag PivotTable-Optionen. Im Register Daten liegt die Einstellung für alte Elemente. Die Registerkarten und Bezeichnungen können je nach Sprache, Version und PivotTable-Typ leicht abweichen.
In Excel für das Web stehen nicht alle Optionen der Desktop-Version zur Verfügung. Wenn die Einstellung dort fehlt, öffnest du die Datei nach Möglichkeit in Excel für Windows oder Excel für macOS. Eine Aktualisierung allein kann in der Webversion ausreichen, wenn der Wert noch in der Quelle steckt; einen bereits gespeicherten Cache-Eintrag kannst du dort aber möglicherweise nicht vollständig steuern.
Bei Pivot-Tabellen auf Basis des Datenmodells gelten teilweise andere Regeln. Die Option für klassische PivotCaches ist dann nicht immer verfügbar oder hat nicht dieselbe Wirkung. In diesem Fall prüfst du die Tabellen und Beziehungen im Datenmodell und aktualisierst anschließend die Daten über Alle aktualisieren.
Wenn alte Einträge trotz der Einstellung bleiben
Bleibt ein Wert sichtbar, prüfst du zunächst, ob wirklich die richtige Pivot-Tabelle bearbeitet wurde. Mehrere Auswertungen können nebeneinander liegen und unterschiedliche Einstellungen besitzen. Klicke in jede betroffene Tabelle und öffne deren Optionen.
Auch verbundene Filter, sogenannte Datenschnitte, können einen alten Eintrag anzeigen. Aktualisiere die zugehörige Pivot-Tabelle und kontrolliere, ob der Datenschnitt mit weiteren Pivot-Tabellen verbunden ist. Über Berichtsverbindungen lässt sich feststellen, welche Auswertungen denselben Datenschnitt verwenden.
Eine weitere Ursache ist ein nicht vollständig angepasster Quellbereich. Wenn die Quelle als fester Bereich wie A1:H500 hinterlegt ist, bleiben Inhalte in diesem Bereich relevant, auch wenn sie am Rand der sichtbaren Liste übersehen wurden. Verwende für regelmäßig wachsende Daten möglichst eine Excel-Tabelle als Quelle. Neue Zeilen werden dann leichter einbezogen, ohne den Bereich jedes Mal manuell zu verändern.
Falls die Pivot-Tabelle beschädigt oder ungewöhnlich alt ist, kann ein Neuaufbau helfen. Erstelle zunächst eine Kopie der Datei, notiere Felder, Filter und Berechnungen und lege danach eine neue Pivot-Tabelle aus einer bereinigten Quelle an. Das ist aufwendiger, entfernt aber häufig veraltete Strukturen, die sich mit einer normalen Aktualisierung nicht beseitigen lassen.
PivotCache per VBA zurücksetzen
Für einzelne Arbeitsmappen kann VBA helfen, wenn viele Pivot-Tabellen überprüft werden müssen. Die Datei sollte vorher als Kopie gespeichert werden. Makros können Arbeitsmappen verändern und sind nur sinnvoll, wenn du die Struktur der Datei kennst.
Mit dem folgenden Makro wird die Aufbewahrung alter Elemente für alle Pivot-Tabellen der aktiven Arbeitsmappe deaktiviert und anschließend aktualisiert:
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.RefreshTable
Next pt
Next ws
Den Code fügst du im VBA-Editor in ein Standardmodul ein. Dafür öffnest du den Editor mit Alt + F11, wählst Einfügen und danach Modul. Je nach Excel-Version und PivotTable-Typ wird die Eigenschaft nicht unterstützt. Treten dabei Fehler auf, brichst du den Vorgang ab und verwendest die Einstellungen der PivotTable-Optionen.
Ein Makro ist nicht erforderlich, wenn nur eine einzelne Auswertung betroffen ist. Die manuelle Einstellung ist übersichtlicher und reduziert das Risiko, unbeabsichtigt andere Berichte in derselben Datei zu verändern.
Warum Aktualisieren manchmal nicht genügt
Beim Aktualisieren berechnet Excel die Pivot-Tabelle mit der hinterlegten Quelle neu. Das bedeutet nicht automatisch, dass alle früher bekannten Elemente aus den internen Listen gelöscht werden. Die Aktualisierung und die Aufbewahrung alter Elemente sind zwei getrennte Vorgänge.
Außerdem kann eine Pivot-Tabelle zwar aktualisiert sein, aber weiterhin einen Filterzustand speichern. Setze den betreffenden Filter zurück oder wähle Alle löschen, sofern dieser Befehl in deiner Version angezeigt wird. Prüfe danach die Ergebnisse erneut. Ein alter Wert im Auswahlfeld ist nicht dasselbe wie ein alter Wert, der tatsächlich als Datenzeile ausgewertet wird.
Prüfliste für saubere Pivot-Auswertungen
- Arbeitsmappe als Kopie sichern.
- Quelldaten auf den alten Eintrag und versteckte Zeilen prüfen.
- Über Datenquelle ändern den tatsächlich verwendeten Bereich kontrollieren.
- Pivot-Tabelle aktualisieren und Filterzustand zurücksetzen.
- In den PivotTable-Optionen die Aufbewahrung alter Elemente auf Keine stellen.
- Weitere Pivot-Tabellen und verbundene Datenschnitte prüfen.
- Bei Datenmodellen die Quelle, Beziehungen und Aktualisierungseinstellungen kontrollieren.
- Erst danach einen Neuaufbau oder ein VBA-Makro erwägen.
Häufige Fragen zu alten Pivot-Werten
Warum zeigt Excel gelöschte Werte weiterhin im Filter an?
Excel kann frühere Elemente im PivotCache speichern, auch wenn sie aus der Quelldatenliste entfernt wurden. Dadurch erscheinen sie weiterhin in Filterlisten, obwohl sie nicht mehr als aktueller Datensatz vorhanden sind.
Gehen Daten verloren, wenn alte Cache-Elemente entfernt werden?
Das Entfernen nicht mehr verwendeter Elemente löscht keine aktuellen Quelldaten. Es verändert nur die interne Liste der bekannten Werte. Eine Sicherung ist trotzdem sinnvoll, bevor du Einstellungen auf viele Pivot-Tabellen anwendest.
Kann ich den PivotCache direkt löschen?
Ein einzelner Cache lässt sich nicht wie eine normale Datei löschen. Du entfernst alte Elemente über die PivotTable-Optionen oder baust die Pivot-Tabelle aus einer bereinigten Quelle neu auf.
Warum erscheint der alte Wert nur in einem Datenschnitt?
Ein Datenschnitt kann mit mehreren Pivot-Tabellen verbunden sein und seine Auswahl aus einer gemeinsamen Struktur beziehen. Prüfe die Berichtsverbindungen und aktualisiere alle betroffenen Auswertungen.
Funktioniert die Einstellung auch bei Power Pivot?
Bei Pivot-Tabellen aus dem Datenmodell gelten teilweise andere Cache- und Aktualisierungsregeln. Die klassische Option für fehlende Elemente ist dort nicht immer verfügbar. Prüfe deshalb das Datenmodell und aktualisiere die zugrunde liegenden Tabellen.
Was hilft, wenn nach dem Zurücksetzen weiterhin alte Werte erscheinen?
Kontrolliere die Datenquelle, ausgeblendete Zeilen, weitere Pivot-Tabellen und gespeicherte Filter. Wenn die Struktur weiterhin falsche Einträge liefert, kann das Erstellen einer neuen Pivot-Tabelle aus einer bereinigten Excel-Tabelle die zuverlässigste Vorgehensweise sein.
Die passende Reihenfolge für die Reparatur
Beginne mit Quelle, Filter und Aktualisierung, bevor du in die interne Struktur eingreifst. In den meisten Fällen reicht es, die Aufbewahrung alter Elemente zu deaktivieren und die Pivot-Tabelle erneut zu aktualisieren. Erst wenn mehrere Tabellen, Datenschnitte oder ein Datenmodell beteiligt sind, lohnt sich eine weitergehende Prüfung oder ein kontrollierter Neuaufbau.
