LIFT OFF in Excel: 10 Tipps die Excel schneller machen!

rocketlaunchsaturn

Wächst das Excel Datenmodell so kann es irgendwann passieren dass Excel extrem langsam wird.

Was vorher ein Bruchteil einer Sekunde dauerte benötigt plötzlich mehrere Minuten. Sehr oft beruht langsame Performanz auf dem Design der Datei oder den Einstellungen in Excel.

Deshalb hier die 10 Top Tipps welche Eurer Datei zum LIFT OFF verhelfen!

Tipp #1: Als Binäre Arbeitsmappe speichern

Ist die Datenmenge riesig, so lässt sich die Berechnungszeit, Speicherzeit, und Performanz oft durch einen ganz kleinen Trick in einem Schlag optimieren.

Man speichert die Datei als Binäre-Excel Datei. Welche Vor- und Nachteile das mit sich bringt kann habe ich bereits genauer in dem Artikel Excel Dateigrösse reduzieren beschrieben.

Fakt ist: als Binäre-Arbeitsmappe wird die Excel Datei nicht nur sehr viel kleiner, sondern auch noch massiv schneller beim Berechnen der Daten!

Tipp #2: Pivot Tabellen benutzen

Berechnungen in Pivot Tabellen sind um ein vielfaches schneller als die selben Funktionen auf dem normalen Tabellenblatt.

Wenn Möglich und Sinnvoll deshalb die Daten mit Pivot Tabellen auswerten!

Tipp #3: Formeln durch Werte ersetzen

Formeln in einer Liste die tausende mal kopiert und berechnet werden machen Excel langsam. Oft genügt es die Werte einmal zu berechnen und die Formeln dann durch Werte zu ersetzen.

Wenn ich diese Methode einsetze , dann lasse ich eine Formel in der obersten Zeile stehen (und markiere diese mit blauer Font – ist meine Vorliebe um Formeln im Tabellenblatt schnell zu sehen), damit ich diese einfach nochmals nach unten kopieren kann falls sich Fehler im Modell eingeschlichen haben oder ich die Daten nochmals neu laden muss!

Tipp #4: Volatile Funktionen vermeiden

Volatile Funktionen sind solche die sich bei jeder Änderung auf dem Tabellenblatt neu berechnen. Logischerweise werden sämtliche Zellen deren Berechnung an diese geknüpft sind ebenfalls neuberechnet! Die folgenden Ereignisse lösen eine Neuberechnung aus bei Volatilen Funktionen:

  • Zeilen oder Spalten Einfügen, Löschen, Ausblenden, oder Einblenden
  • Gewisse Filter setzen
  • Ein Tabellenblatt neu Bennen, verschieben, hinzufügen oder löschen
  • Benannte Bereiche im Namens-Manager ändern
  • Praktisch jedes Ereignisse (selbst ein Wert in eine Zelle einfüllen) wenn Excel im Automatischen Berechnugsmodus ist.

Hierzu zählen insbesondere Funktionen wie: SVERWEIS(), HVERWEIS(), BEREICH.VERSCHIEBEN(), JETZT(), HEUTE(), INDIREKT(), ZUFALLSZAHL(), ZUFALLSBEREICH(), etc. Falls die Formeln nicht vermieden werden können siehe Tipp#3!

Wer der Englischen Sprache mächtig ist findet bei Chandoo.org einen sehr guten Artikel zu dem Thema der von Jeff Weir geschrieben wurde.

Tipp #5: Berechnungsoption auf Manuell setzen

Im Formeln Menü unter Berechnung Berechnungsoptionen auf Manuell setzen!

Tipp #6:SUMMENPRODUKT() durch SUMMEWENNS() und ZÄHLENWENNS() ersetzen

Wer die SUMMENPRODUKT() Formel gut kennt und weiß was man damit alles machen kann wird dazu neigen sie überall einzusetzen. Obwohl ich ein riesen Fan dieser Formel bin muss ich davon abraten sie viel zu benutzen.

Insbesondere in großen Datenmodellen kann SUMMENPRODUKT() schnell zu einer Belastungsprobe für den Rechner werden!

Tipp #7: MATRIX Formeln vermeiden

Genau wie die SUMMENPRODUKT() Formel sollte in großen Modellen auch MATRIX Formeln spärlich eingesetzt werden. Der Effekt ist derselbe wie bei der SUMMENPRODUKT Formel.

Tipp #8: Die Bezugsbereiche in Formeln auf die notwendigen Daten einschränken

Wer SUMME(A:A) eingibt befiehlt Excel nicht nur die ersten 20 Zeilen welche einen Wert enthalten zu summieren, sondern alle 1‘048‘576 Zellen in der Spalte A (bei Excel 2010). Das selbe gilt natürlich für Zeilen z.B. SUMME(1:1).

Wenn diese Bezugsbereich mit Volatilen Funktionen verknüpft werden wird die Performanz ganz schlecht!

Tipp #9: Formatierung auf die notwendigen Daten einschränken

Wenn eine ganze Spalte oder Zeile Formatier wird muss sich Excel die Formatierung für die ganze Spalte oder Zeile merken. Für die Formatierung gilt das selbe wie für Bezugsbereiche in Formeln.

Formatierung nur auf die wirklich vorhandenen Daten anwenden!

Auf diese Regel sollte man insbesondere bei der Bedingten Formatierung achten!

Tipp #10: Einfache VBA Optimierung

Bei VBA Code der langsam läuft kann man mit Application.ScreenUpdating etwas Effizienz gewinnen (insofern der Code etwas mit Objekten auf dem Tabellenblatt macht)

Application.ScreenUpdating = false

Wenn Dir dieser Excel Tipp geholfen hat, dann wird Dir mein Buch 101 Excel Tipps für Power User noch viel mehr helfen!

Was sind Eure Tipps?

Habt Ihr weiter Tipps zur Optimierung von Excel? Welches sind Eure Lieblings Tricks um die Rechengeschwindigkeit zu steigern?

 

Comments

  • Super – Danke!

    AnonymousJanuary 17, 2015
    • :-) freut mich wenn es Dir geholfen hat!

      Lukas RohrJanuary 21, 2015
  • Lösche alle bedingten Formatierungen, dann hast du wieder eine Rakete

    AnonymousFebruary 1, 2015
  • September 28, 2015

    […] Nicht befolgen dieses Rates führt zu explodierenden Excel Dateigrössen und kriechend langsamen Excel Modellen. […]

  • Eben, bedingte Formatierung ist ebenfalls volatil. Was jedoch sollen Matrix-Funktionen sein? Index() kennt auch eine Matrixvariante.

    JohnOctober 8, 2015
  • Ich muss mal eine Lanze für bedingte Formatierungen brechen – in Zeitstrahldiagrammen und Ähnlichem spart eine bedingte Formatierung vom Typ “Formel … verwenden” über viele Zellen Wahnsinnig viel Platz gegenüber einer Formel. Ich habe sogar das Gefühl, dass die Tabelle dann schneller läuft.

    Ist aber sicher eine Spezialanwendung.

    PeterMay 3, 2016
    • Hi Peter, interessant was Du da schreibst. Werde es mir bei Gelegenheit mal genauer ansehen. Ich frage mich wie man am besten Testen könnte… hast Du eine Idee?

      Lukas RohrMay 3, 2016
  • Bei Tipp #1 schreiben Sie, dass bei binärem Dateiformat die Berechnung von Daten MASSIV schneller wird.

    Klicke ich den dortigen Link ‘Excel Dateigröße reduzieren’ an, lese ich ich genau das Gegenteil:
    “Auf Berechnungen die ausgeführt werden in Excel hat das Dateiformat keinen nennenswerten Effekt. In einem kurzen Test den ich durchgeführt habe war die Berechnung im Binären-Format ein klein wenig schneller. “

    BerndJuly 12, 2016
    • Recht hast Du Bernd! Tatsächlich muss ich den Post korrgieren, bez. eine Notiz hinzufügen. Als ich den Beitrag geschrieben und Tests durchgeführt habe erkannte ich keinen nennenswerten effect auf Formeln. Spätere Erfahrung zeigte mir dann dass es trotzdem einen Einfluss hat. Allerdings muss ich gestehen das ich noch nicht einschränken kann wann genau es hilfreich ist… ich vermute es Betrifft sogenannte “Volatile” Funktionen.

      Lukas RohrAugust 2, 2016
  • Gute Tipps! Danke!

    Jeder, der mit folgendem Fehler kämpft „Für EXCEL waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.“ wird dankbar sein!

    MfG,
    Christian

    ChristianAugust 7, 2016

Leave a Reply