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?