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 gibt es tatsächlich ein paar Stellschrauben in Excel, über die sich die Dateigröße deiner Arbeitsmappe teilweise massiv verringern kann. Die Betonung liegt dabei auf „massiv“ und auf „kann“.
Nicht jeder der folgenden Tipps ist auch in jeder Situation anwendbar. Wer aber die Mechanismen und Tricks kennt und weiß, wo man überall ansetzen kann, wird aufgeblähten Arbeitsmappen zukünftig nicht mehr hilflos gegenüberstehen.
Und so geht’s:
Tipp 1: Power Query
Früher – damit meine ich die Zeit vor Power Query – blieb einem Excel-Anwender praktisch nichts übrig, als sämtliche für seine Reports und Analysen benötigten Daten direkt in Excel zu speichern. Da wurden (und werden leider immer noch) oftmals riesige Tabellen mit den Rohdaten in separaten Arbeitsblättern vorgehalten, über mehr oder weniger komplizierte Formeln aufbereitet, transformiert oder sonstwie umgemodelt, um hinterher einen klitzekleinen Bruchteil davon in irgendwelchen Auswertungen zu verwenden.
Mit Power Query jedoch hat Microsoft einen echten Game-Changer eingeführt. Damit hat man endlich ein Werkzeug in die Hand bekommen, um die ganzen Vorarbeiten an den Rohdaten elegant in den Hintergrund zu verlagern und schließlich nur noch die tatsächlich benötigten Daten in Excel-Tabellen zu laden. Oder gleich komplett auf solche Tabellen zu verzichten und die Daten direkt und ohne Umwege in Pivot-Tabellen zu laden.
Leider sind viele Analysten, die in der Vor-Power-Query-Zeit Excel-mäßig domestiziert wurden (dazu zähle ich mich auch!), immer noch in den erlernten Routinen steckengeblieben und verschenken oft aus reiner Unwissenheit immensens Potenzial. Daher kann ich jedem ernsthaften Excel-Anwender nur dringend empfehlen, ein paar Stunden zu investieren und die Grundlagen von Power Query zu erlernen. Belohnt wird man dadurch nach kürzester Zeit durch nie gekannte Automatismen, Möglichkeiten der Datentransformation und – um den Bogen zurück zum Artikel zu schlagen – ein einfaches Mittel, um seine Excel-Dateien gar nicht erst auf unnatürliche und völlig unnötige Größen anwachsen zu lassen.
Tipp 2: 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
Was hier auffällt:
Arbeitsmappen mit vielen statischen Werten führen beim XLSX-Format tendenziell zu größeren Dateien, verglichen 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.
Tipp 3: 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
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
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
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.
Tipp 4: 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
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
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
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
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
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 | PivotTable | Aus externer Datenquelle“
Pivot-Tabelle aus externer Datenquelle
Im sich nun öffnenden Fenster wähle ich die Option „Externe Datenquelle verwenden“ und klicke dann auf die Schaltfläche „Verbindung auswählen…“
Verbindung auswählen
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
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
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
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
Und hier noch einmal alle Varianten in der Übersicht:
Die unterschiedlichen Varianten bei Pivot-Tabellen
Tipp 5: Bilder komprimieren
Wer viele Bilder in seinen Excel-Dateien nutzt, kann unter Umständen mit diesem Tipp allerhand Speicherplatz sparen. Gerade wenn größere Bilder eingefügt und später durch Zuschneiden auf das gewünschte Format zusammengestutzt wurden, gibt es hier unter Umständen einiges an Einsparungen zu holen. Denn Excel speichert im Hintergrund standardmäßig auch weiterhin die abgeschnittenen Bildbereiche.
Zugeschnittene Bilder
Über das Menü „Bildformat | Bilder komprimieren“ lässt sich zum einen die Speichergröße reduzieren, zum anderen werden auch die erwähnten zugeschnittenen Bildbereich entfernt:
Bilder komprimieren
Letzteres lässt sich auch global für alle Bilder in den Excel-Optionen ändern. Dort gibt es in den erweiterten Einstellungen die Option „Bearbeitungsdaten verwerfen“:
Bearbeitungsdaten verwerfen
Wenn diese Einstellung aktiviert wird, dann werden die abgeschnittenen Bildteile endgültig gelöscht. Je nach Anzahl und Größe der Bilder kann das durchaus einen Gewinn an Speicherplatz bringen.
Tipp 6: Den „Used Range“ verkleinern
Eine besonders hinterhältige Eigenschaft in Excel ist der sogenannte Used Range. Er gibt an, welcher Teil eines Arbeitsblatts tatsächlich genutzt wird und hat damit indirekt Auswirkungen auf die Größe der Datei. Um den Used Range herauszufinden, drückt man einfach die Tastenkombination Strg+Ende. Im Idealfall wird man damit auf der letzten befüllten Zelle im Arbeitsblatt landen:
Die letzte verwendete Zelle
Der Used Range entspricht in diesem Beispiel genau dem, was man auch erwartet hat.
Es kommt aber auch vor, dass man mit dieser Tastenkombination viel weiter unten und/oder weiter rechts in seinem Blatt landet, wo sich eigentlich schon lange keine Daten mehr befinden. Dies ist unter anderem schon dann der Fall, wenn man irgendwo mal eine Formatierung geändert hat (z.B. eine andere Schriftfarbe) und später dort nur die Zelleninhalte gelöscht, die Formatierung an sich aber beibehalten hat.
Leere Zelle, aber mit Formatierung
Für Excel bedeutet das, dass sich an dieser Stelle immer noch Informationen befinden, wenn auch nicht sofort sichtbar. Dadurch wird der Used Range entsprechend erweitert und die Dateigröße verändert sich unter Umständen ebenfalls mehr oder weniger dramatisch.
Falls bei dir der Used Range weit außerhalb des erwarteten Bereichs liegt, lässt sich das normalerweise relativ einfach beheben. Markiere dazu sämtliche Zeilen unterhalb der tatsächlich letzten benutzten Zelle. Am schnellsten geht das über die Tastenkombinationen Strg+Umschalt+Pfeil nach unten, danach Umschalt+Leertaste. Auf diesem Weg sollten jetzt alle Zeilen unterhalb des Ausgangspunkts komplett markiert sein. Jetzt rufst du das Menü „Start | Löschen | Alle Löschen“ auf:
Löscht alle Inhalte und Formate
Nun wiederholst du das Ganze für die Spalten rechts von der letzten tatsächlich verwendeten Zelle:
Strg+Umschalt+Pfeil nach rechts, danach Strg+Leertaste
Jetzt sind alle entsprechenden Spalten markiert und du kannst wieder über „Start | Löschen | Alle Löschen“ sämtliche Inhalte und Formatierungen entfernen. Anschließend solltest du die Datei speichern, schließen und wieder neu öffnen. Wenn du jetzt den Used Range mit Strg+Ende kontrollierst, sollte sich die aktive Zelle an der richtigen Stelle befinden. Die Dateigröße müsste sich ebenfalls verringert haben.
Wichtig:
Jedes Arbeitsblatt hat natürlich seinen eigenen, individuellen Used Range!
Ob überhaupt und wie groß der Einspareffekt durch einen angepassten Used Range ist, hängt natürlich vom Einzelfall ab. Auch die eingesetzte Excel-Version scheint eine Rolle zu spielen. Nach meiner Erfahrung hat Microsoft in den neueren Versionen (Microsoft 365) das Speichermanagement optimiert. Der Used Range besteht natürlich weiterhin und wird wie beschrieben z.B. durch unbeabsichtigte Formatierungen beeinflusst. Das Problem mit der damit verbundenen ausufernden Dateigröße scheint aber nicht mehr so häufig aufzutreten. Aber es schadet nicht, gelegentlich einmal mit Strg+Ende den tatsächlichen Used Range zu kontrollieren und ggf. zu optimieren.
Tipp 7: Der Neuanfang
Manchmal lässt sich der Used Range nicht verkleinern, obwohl kein Grund dafür ersichtlich ist. Und auch die anderen Tipps bringen vielleicht nicht den gewünschten Effekt und die Datei bleibt aus unerfindlichen Gründen aufgeblasen. Dann kann es einfach helfen, über einen Neustart nachzudenken. Also die tatsächlich benötigten Teile der vorhandenen Arbeitsmappe in eine neue, leere Datei zu kopieren und dann mit dieser weiterzuarbeiten. Damit lässt man auf einen Schlag die Altlasten zurück und übernimmt nur den Teil, der wirklich notwendig ist.
Dabei sollte man nach Möglichkeit nicht komplette Arbeitsblätter kopieren, sondern immer nur die Zellenbereiche, die man tatsächlich benötigt. Ansonsten läuft man Gefahr, etwaigen Datenmüll wieder mitzunehmen. Ich weiß, dass das nicht immer eine Option ist und vor allem bei vielen Formeln mit Bezug auf andere Arbeitsblätter bedeutet das auch einen entsprechenden Anpassungs- und Korrekturaufwand. Denn man muss aufpassen, dass man beim Kopieren keine unerwünschten Verknüpfungen zur alten Arbeitsmappe erzeugt. Aber in einigen Fällen kann ein Neustart die Lösung sein.
Falls du übrigens schon mit unerwünschten Dateiverknüpfungen zu kämpfen hast, dann empfehle ich dir die folgenden Artikel:
- Die Suche nach der externen Verknüpfung
- Fantastische Verknüpfungen und wo man sie in Excel findet
- Liste der Dateiverknüpfungen
Bonus-Tipp: Inquire-Add-In
Wer eine Excel-Version aus einem Office Professional Plus-Paket oder einer Microsoft-365-Enterprise-Version im Einsatz hat, kann das dort verfügbare Inquire-Add-In nutzen, um weitere Optimierungspotenziale zu identifizieren.
Das Add-In muss zunächst in den Excel-Optionen aktiviert werden: Unter „Datei | Optionen | Add-Ins“ gibt es die COM-Add-Ins. Dort muss der Haken neben Inquire gesetzt werden, danach erscheint im Menüband ein neues Registerblatt.
Mit Inquire lassen sich Arbeitsmappen auf vielfältige Weise analysieren und bestimmte Probleme beheben.
Leider ist dieses Add-In nicht in „normalen“ Office-Paketen und auch nicht in den Microsoft-365-Home/Single/Family und auch nicht in den verschiedenen Microsoft-365-Business-Versionen enthalten, sondern eben nur in den Professional Plus-Varianten.
Weitere Informationen dazu findest du unter diesen Links:
Aktivieren des Inquire-Add-Ins
Compare workbooks using Spreadsheet Inquire
Lesertipp
Der Leser Karl Hegetschweiler aus dem Bayerischen Wald hat mir noch einen weiteren interessanten Tipp geschickt (danke nochmal dafür!). Er hat eine extrem aufgeblähte Excel-Datei (55 MB) in Libre-Office Calc geöffnet und dort als Kopie unter einem neuen Namen wieder im XLSX-Format gespeichert. Danach war die Datei auf 144 KB geschrumpft! Ob sich dieser Effekt bei jeder Datei erreichen lässt und ob es in manchen Fällen vielleicht irgendwelche Nebenwirkungen hat, kann man natürlich nicht vorhersagen und muss im Einzelfall geprüft werden. Aber einen Versuch ist es sicherlich wert, oder?
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.
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.
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.
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
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
Hallo Lutz,
das hört man gerne, wenn die Tipps geholfen haben.
Schöne Grüße,
Martin
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 🙂
Hallo Robert,
vielen Dank für das schöne Feedback!
Schöne Grüße,
Martin
Danke Martin,
das war mal wieder ein sehr interessanter Beitrag! Schön, dass du Excel von allen Seiten beleuchtest.
Beste Grüße,
Florian
Hallo Florian,
gern geschehen. Ein bisschen Abwechslung kann ja nicht schaden 🙂
Schöne Grüße,
Martin
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
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
Gerade beim Formatieren ist mir aufgefallen, dass es oft besser ist z.B. eine ganze Spalte zu formatieren, als nur die benötigten Zellen. Offensichtlich merkt er sich die Einzelheiten dann auch speziell für die jeweils ausgewählten Zellen. Diese Zusatzinfo scheint viel auszumachen. Also nicht H2 ist Währung, H3 ist Währung, H4 ist Währung usw., sondern Spalte H ist Währung. Dein „Schritt 4“ ist wohl ein gutes Beispiel für diese Theorie.
Hallo Hannes,
ja, im Zweifelsfall scheint es tatsächlich besser zu sein, das Format für eine komplette Spalte zu ändern, als nur für einzelne Bereiche. Das Verhalten von Excel scheint an dieser Stelle nicht immer ganz logisch.
Schöne Grüße,
Martin
Der Tipp mit der Formatierung ist super! Formatierungen gelöscht und schon von 27.161 KB auf 966 KB runter. Danke, Nina
Hi Nina,
das ist ja mal eine richtige Verbesserung, freut mich zu hören!
Schöne Grüße,
Martin
Hallo euch allen,
Ich finde diesen Beitrag wirklich hilfreich. Ich hatte vor ein paar Wochen immer wieder das Problem das das Abschicken von Email abgestürzt ist: Anhang zu groß. Da habe ich ein Programm gefunden das heißt ApowerCompress. Blitzschnell, hängt sich nicht auf und schon war die Email unterwegs. Kann ich nur empfehlen.
Vielen Dank für die hilfreichen Tipps! Gerade in einer Datei, die aus mir unerfindlichen Gründen auf einmal eine Größe von 15 MB hatte, ein Tabellenblatt mit über 100.000 Zeilen und tausenden Spalten entdeckt. Eigentlich sollte dort bei V 63 Schluss sein. Benötigte Daten in ein neues Blatt kopiert, ursprüngliches Blatt gelöscht, Dateigröße jetzt 230 KB 🙂
Hallo Daniel,
das nenne ich mal eine echte Größenreduktion 🙂
Und danke für die Ergänzung. Auch das ist ein guter Ansatz: Im Zweifelsfall die benötigten Daten einfach in ein neues Blatt oder eine neue Datei kopieren.
Schöne Grüße,
Martin
Hallo Martin,
ich nutze excel-Dateien für die Begleitung von Bekleidungsproduktionen. Sie bestehen in der Basis aus Maßtabellen (mit Formeln), Stücklisten und pro Zwischenkontrollmuster aus weiteren Tabellenblättern. Zur Dokumentation ist es unerlässlich auch Fotomaterial mit einfließen zu lassen. Ich habe auch schon versucht, alle Fotos raus zu nehmen und auch diese klein zu rechnen. Aber richtig vorwärts brachte es das volumen nicht.
Am Ende sprengen oft die Datenmengen das verträgliche Volumen was für Emails notwendig ist.
Aktuell liegt eine Datei bei 280MB.
Ich habe den Eindruck, dass die Basisdateien durch mehrfaches Adaptieren bestehender Dateien sich das volumen immer mehr erhöht (bei Produkten ähnlicher Machart übernimmt man bestehende Unterlagen einer alten Produktion und modifiziert lediglich ein paar Bereiche, speichert sie dann neu ab). Ist das möglich?
In jedem Fall wäre ich dankbar für einen Tipp, wie ich meine Excel Dateien verkleinern kann. Ich arbeite übrigens mit der Mac Version Office365.
Viele Grüße
Eva Hett
Hallo Eva,
aus der Ferne ist es immer sehr schwer, einen konkreten Tipp zu geben, wenn man die Datei nicht sieht. Ja, Fotos dürften das Volumen grundsätzlich schon ziemlich aufblähen. Und ja, manchmal übernimmt man Altlasten aus einer Vorgängerdatei. Dadurch kann es sein, dass einzelne Tabellen mehr Speicher einnehmen, als notwendig. Es gibt in Excel einen sogenannten „Used Range“. Das ist normalerweise immer der Bereich bis zur letzten Zelle, die verwendet wird.
Über die Tastenkombination Strg+Ende (keine Ahnung, wie das auf dem Mac heißt) springt man in die allerletzte Zelle. Wenn dies dann tatsächlich eine genutzte Zelle ist, dann passt alles. Wenn davor aber viele leere Zeilen und Spalten liegen, stimmt etwas nicht und es haben sich die gefürchteten Altlasten eingeschlichen. Manchmal kann man diese Bereiche löschen und die Datei wird wieder kleiner. Manchmal hilft aber auch nur ein Neuanfang in einer leeren Datei.
Schöne Grüße,
Martin
eine Möglichkeit wäre, die Bilder gar nicht in die Datei zu schreiben und stattdessen Hyperlinks einzufügen. Ich habe das mal aus anderen Gründen gemacht und die Größenreduktion war nur ein beachtlicher Nebeneffekt.
Bei mir war in den Daten jeweils eine Artikelnummer je Zeile. Daneben habe ich dann eine Hyperlink-Formel gesetzt, die im übergeordneten Verzeichnis die Bilddatei öffnet, wenn man draufklickt. Also etwa
=HYPERLINK(„./“&A1&“.jpg“;“Link zum Bild“)
(Voraussetzung: Der Name des Bildes entspricht dem Inhalt (z. B. Artikelnr.), der in A1 steht). Die Formel ist nicht getestet, sollte aber so funktionieren.
Der Vorteil: Der Link lässt sich automatisch für jede Zeile erstellen und die Datei bleibt klein und übersichtlich.
Der Nachteil ist natürlich, dass man die Bilder nicht auf einen Blick hat, sondern einzeln aufrufen muss. Außerdem kann man es nicht so leicht als Anhang verschicken, sondern der Empfänger muss den ganzen Ordner bekommen, z. B. als ZIP, das er irgendwohin entpackt
Hallo Tourenplaner,
stimmt, die beste Variante ist, die Bilder nach Möglichkeit gar nicht erst einzufügen. Und mit den Hyperlinks hast du eine sehr gute Alternative aufgezeigt, vielen Dank!
Schöne Grüße,
Martin
Hallo!
Bin das erste Mal hier, drauf gestoßen bei einer Google-Suche nach „microsoft excel 2007 dateigröße“ (2007 – ich weiß). Gefällt mir sehr gut, diese Seite, und ich werde sicherlich wieder mal reinschauen. 🙂
Habe mich auch das erste Mal mit dem Binär-Dateiformat beschäftigt und dadurch bisher eine (variable) Dateigrößenreduktion auf ca. 60% erlangt. Danke für den Tipp, Martin.
In dem Zusammenhang eine interessante (und mir nicht so leicht erklärkliche) Beobachtung: Meine Ausgangstabelle beinhaltet sieben Spalten und eine Million Zeilen. Ich benutze spaltenweise Formatierungen. Noch ist nur die erste Spalte gefüllt, und zwar mit den aufsteigenden Zahlenwerten (keine Formeln) von 5,000,000 bis 5,999,999. Diese Tabelle habe ich dann kopiert für den 6-Mio.-Bereich, die für den 7-Mio.-Bereich, die wiederum für 8 Mio. und die dann schließlich für 9 Mio. Also fünf Tabellen, die alle bis auf die erste Stelle eines jeden Eintrags identisch sind. Lustigerweise haben aber nur die ersten drei Tabellen auch eine identische Dateigröße, die für 8 Mio. ist ca. ein halbes Prozent kleiner und die für 9 Mio. dann noch einmal fast ein halbes Prozent kleiner als die für 8 Mio. Hat vermutlich damit zu tun, wie solche Zahlen „binär“ behandelt werden.
Es gibt mehr Ding‘ im Excel, als unsere Schulweisheit sich träumt …
Grüße, cCC
Sorry, enthalten natürlich „eine Million Zeilen“ plus eins. 😉
Hallo cCC,
freut mich, wenn der Artikel hilfreich war. Ein interessantes Phänomen, das du da beschreibst, für das ich auch keine Erklärung habe. Aber vielleicht hat es tatsächlich etwas mit der binären Behandlung der Zahlen zu tun.
Schöne Grüße,
Martin
Ich bin begeistert!
Solche Tipps findet man viel zu selten, da sich die meisten Beiträge nur mit „Excel-Light“ beschäftigen und z.B. nur das Prinzip einer Pivot-Tabelle erörtern. Ich arbeite gerade an einem Dashboard und dahinter stehen >500.000 Zeilen, was dank mehrerer Pivot-Tabellen zu einer Dateigröße von gut 80mb geführt hat. Dank der Auslagerung sind es jetzt nur noch ein paar kb. Vielen Dank!
Hallo Erik,
vielen Dank für das tolle Feedback. Und diese Reduzierung der Dateigröße hat sich ja wirklich gelohnt!
Schöne Grüße,
Martin
Gute Tipps hier!
Ergänzungen
1. Für das Reduzieren des Used Range eignet sich das MS-AddOn Inquriry bestens:
https://support.microsoft.com/de-de/office/aktivieren-des-inquire-add-ins-6bc668e2-f3c6-4729-8ce1-75ea20aa9d90
Dort dann der Befehl „Übermäßige Zellformtierung entfernen“
2. Der Speicherbedarf wird viel mehr durch die Spaltenanzahl bestimmt als von der Zeilenanzahl. Wenn es die konkrete Nutzung nicht behindert, kann Transponieren daher wahre Wunder bewirken.
Hallo Geraldino,
danke für den Hinweis. Für alle, die jetzt aber krampfhaft nach dem AddOn suchen: Es ist leider nicht in jeder Excel- bzw. Office-Version enthalten. Soweit ich es sehen kann, gibt es das Inquire-AddOn nur in den Professional Plus-Versionen bzw. in den Microsoft 365-Enterprise-Lizenzen. Nicht jedoch in den Standard- bzw. Microsoft-365-Business-Versionen.
Schöne Grüße,
Martin
Hallo Martin,
Vielen herzlichen Dank für diesen Artikel!
Durch Deine Erklärungen zu dem Pivot-Cache konnte ich meine Excel-Datei extrem verkleinern (nur dadurch, dass die Datenquellen auf einen bestimmten Bereich limitiert wurden).
Viele Grüße, Matthias
Hallo Matthias,
danke für’s Feedback, freut mich zu hören!
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die tollen Tipps, die gängigen waren mir auch soweit bekannt, außer die mit den Pivot-Tabellen, weil bisher auch noch nicht angewandt.
Eine an sich gern von mir genutzte Funktion ist die ‚Bedingte Formatierung‘, welche aber beim Kopieren von Zeilen, Spalten oder einzelnen Zellen in einer ’normalen‘ Tabelle zu eine Vervielfachung der Einträge innerhalb der ‚Bedingten Formatierungen‘ führt, welche sich u.U. nur auf einzelne Zellen beziehen, obwohl die Zelle darüber oder darunter die identische ‚Bedingt Formatierung‘ besitzt.
Dadurch erhöht sich u.U. auch die Dateigröße und vor allem wird die Neuberechnung sehr stark verlangsamt.
Eine sehr lästige Aufgabe ist im Nachgang die einzelne Formatierungen wieder zu bereinigen…
In ‚Als Tabelle formatierten‘ Tabellen wird die Funktion etwas besser, sodass nicht zusätzliche ‚Bedingte Formatierungen‘ entstehen.
Vielleicht gibt es einen weiteren Tipp von dir, wie dieses zusätzliche Kopieren innerhalb der ‚Bedingten Formatierungen‘ vermieden werden kann?
Grüße Ralf
Hallo Ralf,
dank für die Ergänzung mit den bedingten Formatierungen. Ärgerlicher als eine eventuelle Erhöhung der Dateigröße ist wirklich der Wildwuchs der Formatierungsregeln. Hier habe ich leider auch keinen Tipp, wie man das zuverlässig vermeiden kann.
Ich glaube irgendwo gelesen zu haben, dass es demnächst in der M365-Version von Excel ein Änderung geben soll, um solche unnötigen/doppelten Formatierungen zu bereinigen. Wann das genau der Fall sein wird, kann ich jedoch auch nicht sagen.
Schöne Grüße,
Martin
Hallo Martin,
noch ein kleiner Nachtrag zu Tipp 6 (Used Range). Beim Speichern der Datei sollte der Cursor innerhalb des Used Range (z.B. auf A1) stehen, sonst kann es sein, dass auch nach dem Speichern der Used Range „groß“ bleibt.
Viele Grüße
Dietmar
Hallo Dietmar,
vielen Dank für die Ergänzung, man lernt nie aus!
Schöne Grüße,
Martin
Die Sache mit dem „used range“ hat meine Datei von 22 MB auf 48 kB eingedampft 😉 Die Größe kommt bei mir daher, dass ich aus einer „Masterdatei“ (die schon ein xlsb ist) per VBA-Skript nach bestimmten Kriterien Daten in ein separates Blatt kopiere und dieses dann in eine neue Datei verschiebe. Und weil ich am Ende für ganze Spalten die Spaltenbreite mit „Columns(„C:C“).ColumnWidth = 12″ etc. einstelle, erzeuge ich natürlich eine Menge leere Zeilen 🙁
Jetzt müsste ich die Reduktion des Used Range noch ins VBA einbauen…
Hallo Martin,
auch von mir vielen Dank für diesen Artikel!
Du beschäftigst Dich ja mit Excel-Interna, an die ein gelegentlicher Nutzer gar nicht denkt.
In meiner „Haushaltsbuch“-Datei (ohne Pivot) war mir schon seit längerem aufgefallen, dass die bisher benutzten rd. 1000 Zeilen in der rechten Scroll-Leiste nur ein winziges Stück der Datei belegen. Ich hatte eine Beschränkung des Scroll-Bereiches implementiert – dummerweise von A4:K65536! Noch ein Überbleibsel aus der XL2002-Zeit, aber schon damals unnötig groß! Maximal brauche ich 3000 Zeilen . . .
Der Tipp mit UsedRange und dem kompletten Löschen aller unnötigen Zeilen und Spalten, also komplett mit Formatierungen, hat geholfen. Allerdings musste ich erst mein ScrollArea deaktivieren, sonst funktionierten dieTastatur-Shortcuts zum Markieren der nunnötigen Zeilen u. Spalten nicht! Und leider habe ich vergessen, die Dateigröße vorher zu notieren! Aber der UsedRange funktioniert jetzt, und auch die Scroll-Leiste 🙂
Viele Grüße!
Jörg
Hallo Jörg,
freut mich zu hören, dass du dein Größenproblem damit lösen konntest!
Schöne Grüße,
Martin
Hallo,
hört sich alles toll an, funktioniert jedoch nicht mit meinem Excel 2019, Dateigröße als xlsb 5’451 kb.
Markieren der riesigen Bereiche dauert ewig und löschen lassen die sich mit keiner Methode.
Beim Löschversuch von gefüllten, aber nicht mehr benötigten Spalten oder Zeilen stürzt Excel grundsätzlich ab.
Nein, nichts ist einfach und das Problem sitzt auch nicht vor dem PC.
Kann die Datei gern mal zuschicken (ist auch öffentlich im Netz)
Hans
Hallo Hans,
ja, das Problem sitzt nicht immer vor dem Computer. Und manchmal helfen diese Tipps auch einfach nicht weiter. Ich kann gerne mal unverbindlich einen Blick auf die Datei werfen, wenn du sie mir per E-Mail zuschickst (E-Mail-Adresse findest du im Impressum). Und schreibe ein paar Erläuterungen dazu, welche Bereiche du genau löschen möchtest. Vielleicht fällt mir ja etwas ein.
Schöne Grüße,
Martin
Hallo Martin,
ich bin gerade zufällig über deine Tipps zur Dateigröße gestolpert, als ich was anderes gesucht habe. Ich habe eine Datei, in der ich per PowerQuery ins Datenmodell Daten aus einer Access-Datenbank lade und auf das Datenmodell dann mit verschiedenen Formeln, Pivot-Grafiken und Pivot-Tabellen zugreife.
Ich wollte jetzt hier mal testen, ob dein Tip 4 evtl. hier auch zu einer Verkleinerung der Dateigröße führt. Jedoch ist dies scheinbar nicht möglich. Ich kann zwar einstellen, dass automatisch beim Starten der Datei die Daten aktualiisert werden sollen, jedoch läßt sich der Haken bei „Daten beim Speichern aus externen Datenberiech entfernen“ nicht setzen – da dieses Feld ausgegraut ist.
Habe ich da irgendwo was übersehen bzw. falsch eingestellt? Oder ist das tatsächlich nicht möglich?
Danke für deine Antwort.
Viele Grüße,
Matthias
Hallo Matthias,
nein, du hast nichts übersehen. Bei Pivot-Tabellen, die aus dem Datenmodell heraus erstellt wurden, ist diese Option tatsächlich nicht verfügbar.
Schöne Grüße,
Martin
Ich hatte mal bei einem Kunden den Fall, dass durch ein Makro ein Bereich von 12 Spalten und 10 Zeilen aktualisiert wurde. Irgendwann dauerte dieser Vorgang ca. 2,5 Std. Wir fanden heraus, dass durch jeden Makrodurchlauf bedingte Formatierungen verdoppelt wurden. Am Ende waren es mehrere 10000! Da diese bedingten Formatierungen keinen Effekt hatten, haben wir sie gelöscht. Das Makro war dann wieder so schnell, dass wir es nicht einmal mehr messen konnten.
Hallo Thomas,
das nenne ich mal einen positiven Effekt! Bedingte Formatierungen können manchmal wirklich ein Quell der Freude sein…
Danke für die Ergänzung!
Schöne Grüße,
Martin