Ultimative Excel-Tipps zum Verringern der Dateigröße 10

Wie verkleinert man aufgeblähte Excel-Arbeitsmappen?
 

Was für ein großkotziger Titel, nicht wahr? Ich bin immer skeptisch, wenn ich etwas über “ultimative Tipps” lese – und du solltest es auch sein. Allerdings habe ich tatsächlich ein paar Stellschrauben in Excel gefunden, welche die Dateigröße deiner Arbeitsmappe massiv verringern können. Die Betonung liegt dabei auf “massiv” und auf “können”.

Die folgenden Tipps sind sicherlich nicht in jeder Situation anwendbar, aber es ist definitiv hilfreich, wenn man die Mechanismen kennt und weiß, wo man vielleicht ansetzen könnte, wenn die Arbeitsmappe wieder einmal zu aufgebläht wird.

Und so geht’s:

Das richtige Dateiformat

Obwohl das aktuelle XLSX-Format bereits mit Excel 2007 und damit vor mehr als 10 Jahren eingeführt wurde, stolpert man in freier Wildbahn immer noch auf Arbeitsmappen im alten XLS-Format. Das muss nicht zwingend falsch sein, denn manchmal gibt es gute Gründe dafür:

  • Datenaustausch mit Anwendern mit Uralt-Excel-Versionen
  • Exporte aus Warenwirtschaftssystemen, die nur das XLS-Format anbieten
  • Importe in ein externes System, das nur mit dem alten XLS-Format umgehen kann

Und sicherlich kann es noch ein paar andere Gründe geben. Wenn das aber alles bei dir nicht zutrifft, solltest du deine Excel-Dateien im XLSX-Format speichern. In sehr vielen Fällen wirst du hier durch reduzierte Dateigrößen belohnt – neben den anderen Vorteilen, die das XLSX-Format bringt (z.B. mehr als 65.536 mögliche Zeilen und mehr als 256 mögliche Spalten etc.)

Noch stärkere Verbesserungen hinsichtlich der Dateigröße bringt das binäre XLSB-Format. Darüber hatte ich im Excel-Quickie 101 schon kurz geschrieben. Zwar gibt es auch hier keine Garantie und insbesondere bei ohnehin kleineren Dateien (wenige MB) ist die Ersparnis überschaubar. Aber wenn sich deine Dateien im zweistelligen MB-Bereich bewegen, solltest du diese Option definitiv ausprobieren.

Zum Vergleich habe ich ein paar – zugegebenermaßen nicht repräsentative – Tests mit unterschiedlichen Dateigrößen, -inhalten und -formaten durchgeführt. Hier die Ergebnisse:

Dateigrößen in Abhängigkeit von Inhalten und Formaten

Dateigrößen in Abhängigkeit von Inhalten und Formaten

Was hier auffällt:

Arbeitsmappen mit vielen statischen Werten führen beim XLSX-Format tendenziell zu größeren Dateien, vergleichen mit dem alten XLS-Format (siehe lfd. Nr. 3, 4, 9 und 10). Kommen jedoch vielen Formeln zum Einsatz, schneidet das XLSX-Format deutlich besser ab (siehe lfd. Nr. 5 – 7 und 11).

In der Praxis konnte bei ich sehr großen Dateien (> 40 MB) beim Wechsel von XLSX auf das XLSB-Format Größenreduzierungen von 40 – 50 % erzielen.

 

Der richtige Umgang mit Formatierungen

Oftmals ist sich der Anwender gar nicht dessen bewusst, welchen Einfluss der leichtfertige Umgang mit Formatierungen auf die Dateigröße haben kann. Da werden gerne mal auf Vorrat ein paar Tausend Zeilen oder Spalten eingefärbt, denn die Tabelle könnte sich ja irgendwann mal erweitern. Auch wenn das in den meisten Fällen keine gravierenden Auswirkungen auf die Größe der Arbeitsmappe hat, verhält sich Excel hier manchmal etwas unberechenbar.

Eine Tabellenkalkulation ist unberechenbar? Eigentlich ein Widerspruch in sich, aber schau dir das folgende Beispiel an:

Zur Veranschaulichung dient eine Datei mit 5.000 Zeilen und 30 Spalten. Enthalten sind nur statische, 6-stellige Zahlen. Die Dateigröße liegt momentan bei 1.172 KB.

Im ersten Schritt werden alle 5.000 x 30 gefüllten Zellen markiert (also A4:AD5003) und auf das Währungsformat umgestellt. Nach dem Speichern hat sich die Dateigröße nur minimal auf 1.195 KB erhöht.

Im zweiten Schritt werden “sicherheitshalber” für die 30 Spalten nochmal 5.000 leere Zeilen auf das Währungsformat umgestellt. Insgesamt also der Bereich A4:AD10003. Die Größe erhöht sich auf immerhin schon 1.569 KB, also um rund 30%!

In Schritt drei wird es interessant: Ausgehend von der Ursprungsdatei markiere ich jetzt die 30 Spalten bis ganz zum Ende der Tabelle, also A4:AD1048576 und stelle wieder auf das Währungsformat um. Speichern – Blick in den Windows-Explorer – Überraschung: Die Dateigröße hat sich wieder auf 1.196 KB verringert. Und das, obwohl sich die Anzahl der formatierten Zellen im Vergleich zu Schritt 2 um mehr als den Faktor 100 erhöht hat!

Merke: Mehr ist manchmal weniger!

Der absolute Hammer kommt aber jetzt.

In Schritt 4 nehme ich wieder die unformatierte Ausgangsdatei (1.172 KB), markiere alle Zeilen bis zum Ende der Tabelle und reduziere dann die Markierung um eine einzige Zeile, so dass die allerletzte Tabellenzeile nicht markiert ist (also A4:AD1048575):

Alles bis auf die letzte Zeile wird markiert

Alles bis auf die letzte Zeile wird markiert

Dann wird das Zahlenformat auf Währung geändert, die Datei gespeichert und die Luft angehalten: Die Dateigröße steigt auf unglaubliche 80.000 KB (in Worten: Achtzigtausend), also rund 80 Megabyte! Das heißt, eine Zeile weniger bezahlst du mit einer fast 80-fachen Dateigröße!

Hier noch einmal die Zusammenfassung:

Zusammenfassung der verschiedenen Schritte

Zusammenfassung der verschiedenen Schritte

Merke: Weniger ist manchmal mehr!

Ach ja: Wenn man die Monster-Datei jetzt im Binärformat XLSB speichert, werden aus den ursprünglich 80 MB deutlich überschaubarere 5,8 MB (ich sage nur “ultimative Tipps” 😉 )

Vergleich der Dateigrößen

Vergleich der Dateigrößen

Wenn du das Beispiel selbst gerne nachstellen möchtest, kannst du dir hier meine Beispieldatei herunterladen.

Ja, das ist sicherlich kein alltägliches und sehr realistisches Szenario. Aber ich wollte dir nur zeigen, dass Excel manchmal wirklich unberechenbar sein kann, und das auf eine Weise, die sich dem gesunden Menschenverstand nicht unmittelbar erschließt.

 

Pivot-Tabellen

Wie du vielleicht schon mitbekommen hast, sind Pivot-Tabellen ein kleines Steckenpferd von mir. So unglaublich praktisch und vielseitig sie sind, haben sie auch einen kleinen Nachteil: Eine Pivot-Tabelle kann die Dateigröße ganz beträchtlich erhöhen. Dies liegt daran, dass die Quelldaten, auf der die Pivot-Tabelle aufbaut, zusätzlich in einen separaten verborgenen Speicher kopiert werden, den sogenannten Pivot-Cache (gesprochen: Käsch). Tatsächlich greift die Pivot-Tabelle dann auf diesen Pivot-Cache zu und nicht auf die eigentlichen Quelldaten.

Dies ist auch der Grund, warum du zum Auffrischen der Pivot-Tabelle immer die Aktualisieren-Schaltfläche klicken musst, wenn sich an den Quelldaten etwas geändert hat. Denn erst dadurch wird auch der Pivot-Cache aktualisiert. Soviel kurz zum Hintergrund.

Zur Veranschaulichung habe ich eine Arbeitsmappe mit einer formatierten Tabelle erstellt, die aus 20.000 Datensätzen mit je 31 Spalten besteht.

Formatierte Tabelle als Ausgangsbasis

Formatierte Tabelle als Ausgangsbasis

Die Dateigröße der Arbeitsmappe liegt im XLSX-Format bei 4.864 KB, im binären XLSB-Format bei 3.410 KB.

Im nächsten Schritt werde ich auf Basis dieser formatierten Tabelle eine Pivot-Tabelle in einem eigenen Arbeitsblatt einfügen:

Die neue Pivot-Tabelle

Die neue Pivot-Tabelle

Nach dem Speichern der Arbeitsmappe zeigt ein Blick in den Windows-Explorer, dass ich die Dateigröße um mehr als 50% erhöht hat: Auf 7.450 KB im XLSX-Format und auf 5.748 KB im XLSB-Format. Der Grund dafür ist der eingangs beschriebene Pivot-Cache.

Erhöhter Speicherbedarf durch die Pivot-Tabelle

Erhöhter Speicherbedarf durch die Pivot-Tabelle

In diesen Dimensionen mag das noch kein Problem sein, bei umfangreicheren Dateien im zweistelligen Megabyte-Bereich lassen sich solche Dateien dann vielleicht schon nicht mehr per E-Mail versenden. Was also tun?

Option 1

Es gibt in den Pivot-Tabellen eine Einstellung, die beim Speichern der Datei den Pivot-Cache wieder löscht. Die Pivot-Tabelle an sich bleibt dabei aber erhalten. Dazu führst du nur einen Rechtsklick irgendwo innerhalb der Pivot-Tabelle aus und wählst aus dem Kontext-Menü den Eintrag “PivotTable-Optionen”

PivotTable-Optionen aufrufen

PivotTable-Optionen aufrufen

Dort öffnest du die Registerkarte “Daten” und entfernst das Häkchen bei “Quelldaten mit Datei speichern” und setzt dafür das Häkchen bei “Aktualisieren beim Öffnen der Datei”:

Daten-Optionen anpassen

Daten-Optionen anpassen

Nun wird beim Speichern der Datei der Pivot-Cache gelöscht – die eigentliche Datentabelle bleibt selbstverständlich erhalten. Dadurch reduziert sich die Dateigröße in meinem Beispiel auf 4.904 KB (XLSX) bzw. 3.445 KB (XLSB) und erreicht damit fast wieder die Dateigröße ohne Pivot-Tabelle!

Gleichzeitig sorgt das zweite gesetzte Häkchen dafür, dass sich der Pivot-Cache beim Öffnen der Datei automatisch wieder füllt, so dass man ohne Probleme mit der Pivot-Tabelle arbeiten kann.

Option 2

Eine ganz andere Variante wäre, die Pivot-Tabelle und die Datentabelle in komplett unterschiedlichen Arbeitsmappen zu speichern und die Pivot-Tabelle nur über eine Datenverbindung zu befüllen. Wie geht man dazu vor?

Die Datentabelle ohne Pivot-Tabelle befindet sich bei mir in der Ausgangsdatei “Testdatei 1a.xlsx”. Dann lege ich eine neue leere Arbeitsmappe an und füge dort eine Pivot-Tabelle ein: Menüband Einfügen | Pivot-Tabelle

Im sich nun öffnenden Fenster wähle ich die Option “Externe Datenquelle verwenden” und klicke dann auf die Schaltfläche “Verbindung auswählen…”

Eine externe Datenquelle verwenden

Eine externe Datenquelle verwenden

Im Fenster mit den vorhandenen Verbindungen klickst du auf “Nach weiteren Elementen suchen…” und wählst dann die Arbeitsmappe mit der Quelldatentabelle aus:

Die gewünschte Datenquelle auswählen

Die gewünschte Datenquelle auswählen

Nach einem Klick auf die “Öffnen”-Schaltfläche musst du gegebenenfalls noch das richtige Arbeitsblatt mit der Datentabelle auswählen. In meinem Beispiel gibt es nur ein Blatt, so dass ich nur noch auf OK klicken muss:

Quelltabelle auswählen

Quelltabelle auswählen

Nachdem ich auch das Fenster zum Erstellen der Pivot-Tabelle mit OK geschlossen habe, wird nach einem mehr oder weniger kurzen Augenblick eine leere Pivot-Tabelle erzeugt, in dem mir alle Felder aus der Quelldaten-Tabelle angeboten werden.

Die leere Pivot-Tabelle

Die leere Pivot-Tabelle

Nun kann ich meine Pivot-Tabelle wie gewünscht modellieren und abspeichern. Ein Blick in den Windows-Explorer zeigt eine erfreulich kleine Datei: Im XLSX-Format sind es 2.574 KB, das binäre XLSB-Format begnügt sich sogar mit 2.328 KB. Also etwa nur noch ein Drittel der ursprünglichen Variante Datentabelle + Pivot-Tabelle in einer Datei!

Beim erneuten Öffnen der Datei wirst du je nach deinen Sicherheitseinstellungen vermutlich noch einen Hinweis auf eine deaktivierte Datenverbindung erhalten, die du dann natürlich aktivieren musst. Danach hast du keine Einschränkungen mehr in der Arbeit mit der Pivot-Tabelle und kannst sogar per Doppelklick auf einen Wert die Drilldown-Funktion nutzen!

Option 3

Das Beste kommt natürlich zum Schluss. Jetzt kombinieren wir die Option 1 mit der Option 2. Also eine Pivot-Tabelle mit lediglich einer Datenverbindung zur Quelltabelle UND die PivotTable-Option, dass die Quelldaten nicht gespeichert werden sollen. Das heißt, die Arbeitsmappe wird ohne Pivot-Cache gespeichert.

Und was jetzt noch für eine Dateigröße übrigbleibt, wirst du nicht glauben: Gerade einmal 14 KB in der XLSX-Version und 12 KB in der XLSB-Version. Mehr geht wirklich nicht!

Geradezu winzige Dateien mit Pivot-Tabelle

Geradezu winzige Dateien mit Pivot-Tabelle

Und hier noch einmal alle Varianten in der Übersicht:

Die unterschiedlichen Varianten bei Pivot-Tabellen

Die unterschiedlichen Varianten bei Pivot-Tabellen

Du siehst schon, es gibt einige Möglichkeiten, wie man aufgeblähte Excel-Dateien deutlich kleiner bekommt. In der Praxis wird nicht jede Arbeitsmappe dafür geeignet sein, aber das Bewusstsein für die eine oder andere Stellschraube kann ganz hilfreich sein.

Welche Excel-Entblähungstipps kennst du noch? Lass es uns unten in den Kommentaren wissen.

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.

10 Gedanken zu “Ultimative Excel-Tipps zum Verringern der Dateigröße

  • Andi

    Hallo Martin,
    besonders hilfreich ist ein löschen aller leeren (zumindest für den user) Zellen unter und neben dem Arbeitsbereich (wenn es denn funktioniert). Ich hab eine 2,4MB Datei bekommen, die schon verdächtig lange braucht, bis sie offen ist. Ein strg+ENDE bringt Licht ins Dunkel – die letzte Zelle ist für Excel AO65535 obwohl es bei M112 aus sein sollte.
    Ergebnis: Wenn man nur eine einzige Zeilen unterhalb der letzten löschen will, quitiert Excel das ganze mit “Der Vorgang wird lange dauern…” und danach “Zu wenig Ressourcen..” – Crash. Im Taskmanager genehmigt sich Excel + diese Mappe nichtmal 40MB.
    Lösung: Im Arbeitsblatt habe ich bei dieser speziellen Mappe keine Idee. Kopiert man jedoch den eigentlichen Bereich in ein neues Blatt und löscht das “verteufelte”, reduziert sich die Dateigröße auf schlanke 61KB (!).
    Im Normalfall hilft jedoch alle Zeilen darunter und daneben löschen sowie danach zu speichern.

    • Martin Weiß Autor des Beitrags

      Hallo Andi,

      ja, das ist das Problem mit dem Used Range. Manchmal hilft es, die betroffenen, eigentlich leeren Zellen zu markieren und über “Start | Löschen | Alle löschen” tatsächlich leer zu bekommen. Danach sollte Strg+Ende hoffentlich auf die echte letzte Zelle springen. Aber das hilft leider nicht immer.

      Schöne Grüße,
      Martin

  • Lutz Wörner

    Hallo Martin,
    danke für den “Ultimativen Tipp”!
    Auch bei mir sind tatsächlich “sehr große” Excel-Dateien immer wieder ein Problem. Jahrestabellen mit knapp 170 MB sind keine Seltenheit und bringen meine IT-Abteilung immer wieder an den Rand der Verzweifelung.
    Eine kleinere Tabelle habe ich gestern einmal nach der Option 3 bearbeitet und kann von einer Totalreduzierung von 34,2% (von 35,1 MB auf 23,1 MB) berichten.
    Vielen Dank für diesen Tipp.
    Bitte weiter so!
    Beste Grüße
    Lutz

  • Robert

    Hallo Martin

    Vielen Dank für deine grossartige Arbeit!
    In diesem Beitrag gefällt mir besonders der Tipp mit dem “Pivot Quelldaten nicht speichern”.
    Das mit der Formatierung und auch das Aufteilen in Datendatei und Pivotdatei ist aber auch ein Punkt der gut zu wissen ist!
    Also Danke nochmal und ich freue mich schon auf den nächsten Beitrag 🙂

  • Florian

    Danke Martin,

    das war mal wieder ein sehr interessanter Beitrag! Schön, dass du Excel von allen Seiten beleuchtest.

    Beste Grüße,

    Florian

    • Martin Weiß Autor des Beitrags

      Hallo Florian,

      gern geschehen. Ein bisschen Abwechslung kann ja nicht schaden 🙂

      Schöne Grüße,
      Martin

  • Hans-Joachim Dammer

    Hallo Herr Weiß,
    oftmals habe ich Excel-Dateien mit vielen Grafiken / Bildern. Diese blähen eine Excel-Datei enorm auf.
    Über “Format” und “Bilder komprimieren” lassen sich “riesige Bilder” vom Platzbedarf her deutlich reduzieren.
    Dies ermpfiehlt sich aber nur bei einigen wenigen Bildern.
    Beim Abspeichern der Datei gehe ich auf “Speichern unter” und dann auf “Tools”.
    Dort gibt es die Optin “Bilder komprimieren”. Hiermit werden alle Bilder in der Arbeitsmappe reduziert.
    Viele Grüße aus Euskirchen sendet Ihnen Ihr treuer Leser
    Hans-Jaochim Dammer

    • Martin Weiß Autor des Beitrags

      Hallo Herr Dammer,

      das ist wirklich ein guter Tipp, Bilder sind tatsächlich ein häufig übersehener Speicherfresser. Vielen Dank dafür!

      Schöne Grüße,
      Martin