[xllimit6]  Maximale Anzahl verschiedene Zellformate

Diese Limitation sollten Sie unbedingt kennen. Vor allem wenn Sie mit "grösseren" bzw. aufwändig gestalteten Arbeitsmappen arbeiten. Das Wort "grösseren" ist absichtlich in Anführungszeichen geschrieben, weil die hier vorgestellte Limitation eher bei Arbeitsmappen mit mehreren Tabellenblättern und vergleichsweise vielen Zellen zu einem Problem wird. Das Ganze muss jedoch relativ betrachtet werden, da bereits ca. 4'000 Zellen zum Erreichen der Limitation genügen. Wohlgemerkt sind 4'000 Zellen lediglich 0.023 Prozent eines einzigen Tabellenblattes!

Die Limitation und ihre Auswirkungen
Die maximale Anzahl verschiedener Zellformate in einer Arbeitsmappe liegt bei ca. 4'000 Formaten (gilt für alle Excelversionen). Bei Erreichen bzw. Überschreiten dieser Grenze erscheint die Fehlermeldung "Zu viele verschiedene Zellformate":

Abbildung: Fehlermeldung "Zu viele verschiedene Zellformate"

Die Höchstgrenze beträgt ungefähr 4'000 Formate, was somit bedeutet, dass die maximale Anzahl je nach Situation bzw. Inhalt der Arbeitsmappe leicht nach oben oder unten abweichen kann. Warum das so ist, was die exakte Anzahl beeinflusst und wieso nur ca. 4'000 Formate in einer Mappe benutzt werden können, lesen Sie weiter unten im Abschnitt "Wieso können nur ca. 4'000 verschiedene Zellformate verwendet werden?".

Wenn beispielsweise Zellen mit unterschiedlichen Hintergrund-, Rahmen- und Schriftfarben formatiert werden, so können 3'956 verschiedene Zellformate verwendet werden. Beim 3'957-sten Zellformat erscheint die oben abgebildete Fehlermeldung. Wenn man die Fehlermeldung mit OK bestätigt, kann man trotzdem weitere Zellen formatieren. Das ist aber nur möglich, wenn man bei den weiteren Zellen nur noch die Hintergrund- und die Rahmenfarbe einstellt. Die Schriftfarbe dagegen kann nicht mehr geändert werden (die obige Fehlermeldung würde auftreten). Auf diese Weise lassen sich zusätzliche 20 Zellen formatieren, wodurch man auf insgesamt 3'976 verschiedene Zellformate kommt. Das Formatieren von weiteren Zellen, beispielsweise nur noch mit der Hintergrundfarbe, ist nicht mehr möglich.

Bitte beachten Sie, dass Ihnen Excel mit der Fehlermeldung "Zu viele verschiedene Zellformate" mitteilt, dass bereits zu viele Zellformate existieren. Die Meldung lautet nicht etwa, dass keine weiteren Zellformate mehr hinzugefügt oder möglicherweise Formatierungen nicht angewendet werden können. Beim ersten Auftreten der Fehlermeldung sollten Sie unbedingt handeln. Versuchen Sie nicht Excel herauszufordern, indem Sie die Fehlermeldung einfach bestätigen und mit dem Formatieren von Zellen fortfahren, also so lange, bis gar nichts mehr geht. Wenn Sie dies tun und dann die Mappe speichern und schliessen, ist es durchaus möglich, dass die Mappe nicht bzw. nie mehr geöffnet werden kann.

Das Problem mit den zu vielen verschiedenen Zellformaten kann nur behoben werden, indem man die Formatierungseigenschaften von den Zellen vereinfacht. Dadurch werden nicht mehr so viele unterschiedliche Formatierungen verwendet und somit die Anzahl verschiedene Zellformate reduziert. Wie das geht, lesen Sie weiter unten im Abschnitt "Reduzieren der Anzahl Zellformate". Zuerst müssen Sie nämlich wissen, was ein Zellformat ist und aus was es sich zusammensetzt. Die wichtigsten Grundlagen werden im nächsten Abschnitt vorgestellt.

Was ist ein Zellformat?
Im Microsoft Knowledge Base-Artikel Nr. 213904 (http://support.microsoft.com/?scid=kb;de;213904) gibt es eine gute Beschreibung. Leider enthält der aus dem Englischen übersetzte deutsche Text zwei Fehler (Anmerkung des Autors: Auch der englische Text enthält einen Fehler, auf den ich hier aber nicht eingehe). Hier die Originalfassung von Microsoft (die beiden Fehler sind mit den Ziffern 1 und 2 markiert):

Dieses Problem [gemeint ist die Fehlermeldung] tritt auf, wenn die Arbeitsmappe mehr als ca. 4'000 verschiedene Kombinationen von Zellformaten¹ enthält. Eine Kombination wird definiert als eindeutige Menge von Formatierungselementen, die auf eine Zelle angewendet werden. Sie enthält die gesamte Schriftartenformatierung² (z.B. Schriftart, Schriftgrad, kursiv, fett, unterstrichen), Rahmen (z.B. Position, Stärke und Farbe), Zellmuster, Zahlenformate, Ausrichtung und Zellschutz.

Die beiden Fehler sind:
1) Statt "Zellformaten" muss es "Zellformatierungen" heissen
2) Statt "Schriftartenformatierung" muss es "Schriftformatierung" heissen

Ich habe basierend auf der Microsoft-Beschreibung eine etwas genauere und meiner Meinung nach bessere Beschreibung verfasst:

Ein Zellformat ist die Kombination der Formatierungselemente einer Zelle. Eine Kombination wird definiert als eindeutige Menge von Formatierungselementen, die auf eine Zelle angewendet werden. Sie enthält die gesamte Schriftformatierung (z.B. Schriftart, Schriftgrad, kursiv, fett, unterstrichen), Rahmen (z.B. Position, Stärke und Farbe), Zellmuster, Zahlenformate, Ausrichtung und Zellschutz. Wenn zwei oder mehr Zellen exakt dieselbe Formatierung haben, verwenden sie dieselbe Formatierungskombination und somit das gleiche Zellformat. Wenn es zwischen den Zellen jedoch Unterschiede in der Formatierung gibt, verwendet jede Zelle eine andere Kombination. Wenn eine Arbeitsmappe mehr als ca. 4'000 verschiedene Formatierungskombinationen, d.h. Zellformate enthält, tritt die Fehlermeldung "Zu viele verschiedene Zellformate" auf.

Die Formatierungselemente von Zellen sind in sechs Gruppen eingeteilt:
1) Zahlenformat
2) Ausrichtung
3) Schrift
4) Rahmen
5) Muster
6) Schutz

Die Namen der Gruppen entsprechen den Beschriftungen der Registerseiten im Dialogfenster "Zellen" (siehe nächste Abbildung). Die einzige Ausnahme ist die Gruppe "Zahlenformat", die im Dialogfenster als "Zahlen" angegeben ist.

Abbildung: Registerseite "Zahlen" des Dialogfensters "Zellen" (Zellformat)

Jede Gruppe bzw. Registerseite enthält bestimmte Formatierungsmerkmale. Die Gruppe "Zahlenformat" beispielsweise enthält das Merkmal "Zahlenformat". Die Gruppe "Schrift" umfasst insgesamt 8 Merkmale: Schriftart, Schriftschnitt, Schriftgrad, Unterstreichung, Durchgestrichen, Hochgestellt, Tiefgestellt und Schriftfarbe.

Abbildung: Registerseite "Schrift" des Dialogfensters "Zellen" (Zellformat)

Sämtliche auf dem Dialogfenster "Zellen" angezeigten Einstellungen sind ein Bestandteil des Zellformates. Wenn Sie eine Zelle formatieren, indem Sie die Einstellungen einer Eigenschaft ändern (z.B. das Zahlenformat von "Standard" auf "Text" ändern), und die Kombination aller eingestellten Eigenschaften wurde bisher noch nie in der Arbeitsmappe verwendet, dann entsteht ein neues Zellformat.

Ein kleiner Exkurs: Zellformat oder Zellformatierung?
An dieser Stelle möchte ich kurz den Unterschied zwischen Zellformat und Zellformatierung erläutern. Oft wird nämlich der Begriff "Zellformat" verwendet, wenn eigentlich "Zellformatierung" gemeint ist - oder umgekehrt. Zugegeben erkennt man meist am Zusammenhang, was genau gemeint ist. Dennoch ist es meiner Meinung nach wichtig, dass zumindest die Definition der beiden Begriffe klar ist.
» Die Zellformatierung besagt, wie eine Zelle formatiert ist. Das heisst, wie die zur Verfügung stehenden Eigenschaften zum Formatieren von Zellen bei der jeweiligen Zelle eingestellt sind.
» Das Zellformat ist die Kombination aller Formatierungsmerkmale einer Zelle. Alle Zellen, deren Formatierungsmerkmale absolut identisch eingestellt sind, besitzen das gleiche Zellformat.

Wie viele verschiedene Zellformate sind überhaupt möglich?
Vielleicht haben Sie sich schon mal gefragt, wie viele verschiedene Zellformate anhand den zur Verfügung stehenden Formatierungseigenschaften überhaupt gebildet werden können. Nun, wenn man weiss, dass die Limitation bei etwa 4'000 Zellformaten liegt, so würde man - ganz spontan - die mögliche Anzahl auf vielleicht 100'000 schätzen. Wenn man sich für eine Schätzung ein bisschen mehr Zeit lässt und sich in Gedanken das Excel-Dialogfenster "Zellformatierung" mit seinen sechs Registerseiten Zahlen, Ausrichtung, Schrift, Rahmen, Muster und Schutz vorstellt, so wird man sicher eine erheblich grössere Zahl schätzen, also eine Anzahl von vielleicht 100 Millionen oder 1 Milliarde. Auch wenn 1 Milliarde unterschiedliche Formatierungen nach sehr viel tönt, ist auch diese Schätzung weit neben der tatsächlichen Anzahl möglicher Zellformate.

Gemäss meiner letzten Berechnung existieren ungefähr 1.22889E+37 verschiedene Möglichkeiten, wie man die Formatierungsmerkmale einer einzigen Zelle kombinieren kann. Die Zahl sieht ohne Potenz geschrieben so aus:

12'288'894'881'881'900'000'000'000'000'000'000'000

Eine Zahl mit 36 Stellen heisst Sextillion (10³⁶). Es existieren somit ca. 12 Sextillionen mögliche Zellformate.

Die Wahrscheinlichkeit, dass Sie im Lotto den Hauptpreis gewinnen und am gleichen Tag vom Blitz getroffen werden, und am nächsten Tag in einer anderen Lotterie wieder den Hauptpreis holen und erneut am selben Tag vom Blitz getroffen werden, liegt ungefähr bei 1E+33. Zum Vergleich: Die Anzahl möglicher Zellformate ist 1.2E+37, also etwa 10'000 mal grösser.

Hier die Zahlennamen zwischen Milliarde und Sextillion:

Wieso können nur ca. 4'000 verschiedene Zellformate verwendet werden?
Der Grund liegt am Design des von Arbeitsmappen verwendeten Dateiformats BIFF. Die Antwort auf die Frage, weshalb nur ca. 4'000 verschiedene Zellformate pro Arbeitsmappe verwendet werden können, fällt demzufolge sehr technisch aus:
   Jedes Zellformat wird anhand eines XF-Records in der Exceldatei abgelegt. Die Informationen über Zellen werden in ROW-Records gespeichert. Jeder ROW-Record enthält eine Referenz (man könnte auch sagen Pointer) auf denjenigen XF-Record, der die Formatierungseinstellungen für die Zellen enthält, welche alle identisch formatiert sind. Für die Referenz stehen im ROW-Record 12 Bits zur Verfügung, wobei eine sogenannte Maske (englisch "Mask") verwendet wird. Die Maske sieht so aus: "0FFF0000". Es existieren somit hex F x F x F Möglichkeiten (= 4'096), d.h. dez 16 x 16 x 16, zum Abbilden der Referenz. Man kann diese Anzahl auch anhand den verfügbaren Bits berechnen. Wie erwähnt stehen 12 Bits zur Verfügung. Ein Bit kann bekanntlich 0 oder 1 enthalten. Folglich gibt es 2¹² Möglichkeiten (das ergibt ebenfalls 4'096).

Bleibt noch die Frage offen, wieso nur zirka 4'000 Zellformate möglich sind, wo doch laut der obigen Beschreibung 4'096 Referenzen gespeichert werden können. Diese Frage kann nur zum Teil beantwortet werden:
   Standardmässig liegt die maximale Anzahl Zellformate bei 4'029 Stück. Wenn Sie also eine neue, leere Arbeitsmappe anlegen, kann die Mappe in diesem Moment 4'029 verschiedene Zellformate aufnehmen (Anmerkung des Autors: Die maximale Anzahl Zellformate kann sich während der Bearbeitung der Arbeitsmappe ändern). 4'096 minus 4'029 ergibt 67. Was ist also mit den restlichen 67 Referenzen für Zellformate? Microsoft Excel benötigt ein paar dieser Referenzen für interne Zwecke, nämlich für Hyperlinks und Gruppierungen (bzw. Gliederungsebenen). Hyperlinks beanspruchen zwei Referenzen - für die Formatvorlagen "Hyperlink" und "Besuchter Hyperlink", und Gliederungsebenen beanspruchen total 14 Referenzen - je sieben für Zeilenebenen (Formatvorlagen "Zeilenebene_1" bis "Zeilenebene_7") und für Spaltenebenen (Formatvorlagen "Spaltenebene_1" bis "Spaltenebene_7"). Insgesamt werden Excel-intern somit 16 Referenzen benötigt. Bleiben noch 51 Referenzen übrig (67 - 16 = 51). Wofür diese verwendet werden beziehungsweise ob diese überhaupt verwendet werden, ist nicht bekannt.

Informationen über das Dateiformat BIFF8 von Exceldateien erhalten Sie hier:

Weitere Informationen The SOSCQ Page: Excel-Dateiformat BIFF

Wie viele verschiedene Zellformate wurden bereits verwendet?
Das ist die zentrale und alles entscheidende Fragen. Nur wenn man weiss, wie viele Zellformate in einer Arbeitsmappe verwendet werden, kann man feststellen,
- ob die Limitation von ca. 4'000 Zellen demnächst erreicht wird, d.h. ob möglichst bald eine Reduktion durchgeführt werden muss und
- ob die ganzen Bemühungen bezüglich Reduzieren der Anzahl Zellformate überhaupt erfolgreich sind (sozusagen ein Vorher-Nachher-Vergleich erstellen).

Das Problem ist, dass Microsoft Excel keine Funktion zur Verfügung stellt, mit der man die Anzahl Zellformate herausfinden kann. Es gibt aber ein Excel Add-In, das die benötigten Informationen liefert. Gemeint ist der Cell Format Counter, ein Tool aus der CEFA-Toolbox. Der Cell Format Counter ist ein Add-In für Microsoft Excel ab Version 97. Das Tool benötigt zum Feststellen der Anzahl verwendete und verfügbare Zellformate sowie maximale Anzahl Zellformate lediglich etwa 2 Sekunden. Das Beste ist: Je mehr Zellformate in der Arbeitsmappe bereits verwendet wurden, desto kürzer dauert die Ermittlung der Messwerte. Bei vielen Zellformaten benötigt das Tool daher kaum mehr als eine Sekunde. Der Cell Format Counter verwendet zur Berechnung der verschiedenen Kennzahlen eine einzigartige Technik und hochoptimierte Programmroutinen (Anmerkung des Autors: Konzeption und Realisierung dieser Programmroutinen kosteten mich rund 6 Monate Arbeit).

Hier eine Abbildung der Cell Format Counter-Benutzeroberfläche (aktuelle Programmversion 2.0):

Abbildung: Das Excel Add-In "Cell Format Counter"

Diese Links führen zu weiteren Informationen über den Cell Format Counter, die CEFA-Toolbox und zur Downloadseite:
Produkt-Beschreibung des Cell Format Counter
Bedienungsanleitung des Cell Format Counter
Produkt-Website der CEFA-Toolbox
Downloadseite von xlam

Reduzieren der Anzahl Zellformate

Einleitung und Vorwort
Zuerst sollten Sie wissen, dass das Reduzieren der Anzahl verschiedener Zellformate eine äusserst schwierige, aufwändige und mühsame Arbeit ist. Das tönt vielleicht etwas allzu sehr pessimistisch, ist aber leider Tatsache. Das Reduzieren der Anzahl Zellformate ist ohne Hilfe von geeigneten Tools kaum machbar. Das Optimieren, also sozusagen das optimale Reduzieren, ist ohne Einsatz von zusätzlichen Programmen schlichtweg unmöglich. Welche unterstützende Tools eingesetzt werden können (bzw. müssen), ist weiter unten in diesem Kapitel erläutert.

Grundsätzliches Vorgehen und Vorbereitung
Es gibt acht Punkte, die Sie in Bezug auf vorbereitende Massnahmen unbedingt beachten und durchführen müssen:

1) Bevor Sie sich überlegen, welche Zellen anders formatiert werden könnten, oder sogar schon mit dem Ändern von Formatierungen beginnen, müssen Sie zuallererst die Arbeitsmappe speichern, schliessen und neu öffnen! Dadurch wird vereinfacht ausgedrückt der Excel-interne Zellformatzähler initialisiert. Unmittelbar nach dem Öffnen einer Datei ist der einzige Zeitpunkt, wo der Zähler die tatsächliche, korrekte Anzahl verschiedener Zellformate enthält.

2) Nur wenn Sie wissen, wie viele Zellformate in der Mappe momentan verwendet werden, können Sie herausfinden, ob die ganzen Bemühungen bezüglich Zellformat-Reduktion überhaupt erfolgreich sind. Das Problem ist jedoch, dass man in Microsoft Excel die Anzahl Zellformate nicht herausfinden kann. Sie benötigen daher ein entsprechendes Tool, und zwar das Excel-Add-In Cell Format Counter. Es ist das weltweit einzige existierende Tool, mit dem man die Anzahl verwendete, verfügbare und maximale Zellformate einer Arbeitsmappe herausfinden kann. Laden Sie das Add-In herunter (es ist Freeware) und binden Sie es wie gewohnt in Excel über den Add-In-Manager ein (Menübefehl Extras/Add-In-Manager).

3) Während der Überarbeitung der Zellformatierungen muss die Mappe häufig gespeichert, geschlossen und neu geöffnet werden wird. Bis Sie mit der Überarbeitung fertig sind, sollte auf alles verzichtet werden, was diese Arbeit erschwert. Mit anderen Worten sollte die Arbeitsmappendatei möglichst klein und einfach geöffnet bzw. gespeichert werden können. Anderenfalls wird jedes erneute Öffnen mühsam, zum Beispiel weil jedesmal ein Dateikennwort eingegeben werden muss oder das Öffnen wegen umfangreichen Formelberechnungen sehr lange dauert. Machen Sie daher unter anderem folgendes (Liste nicht abschliessend):
- Entfernen Sie sämtliche allfällig vorhandene Kennwörter, d.h. Dateikennwort, Schreib-, Freigabe-, Mappen- und Blattschutzkennwort.
- Stellen Sie den Berechnen-Modus der Mappe auf 'Manuell', damit beim Öffnen keine Wartezeit durch Berechnungen entsteht.
- Benutzen Sie kein duales Dateiformat, damit das Speichern der Mappe beschleunigt wird.
- Deaktivieren Sie die beim Öffnen, Speichern und Schliessen ausgeführten VBA-Makros.
- Entfernen Sie Projektverweise auf andere Mappen, damit diese beim Öffnen der Arbeitsmappe nicht automatisch geöffnet werden.
- Deaktivieren Sie die Option "Externe Verknüpfungswerte speichern", damit weniger Daten gespeichert werden müssen.
- Deaktivieren Sie die Option "Fernbezüge aktualisieren" bzw. "Remotebezüge aktualisieren", damit die Mappe schneller geöffnet wird.
- Legen Sie die Exceldatei auf einem schnellen Laufwerk ab (vorzugsweise auf einer lokalen Festplatte), damit das Öffnen und Speichern schnell abläuft.
- Schalten Sie einen allfällig verwendeten AutoFilter aus, damit beim Öffnen der Mappe keine Daten gefiltert werden müssen.
- Heben Sie die Freigabe der Arbeitsmappe auf, damit keine gleichzeitige Bearbeitung durch andere Benutzer erfolgen kann.

4) Blenden Sie alle verborgenen Elemente der Arbeitsmappe ein, sodass sämtliche Zellen sichtbar werden. Dazu gehört insbesondere das Einblenden von ausgeblendeten Zeilen und Spalten. Ebenfalls müssen sämtliche Arbeitsblätter vom Typ "Tabelle" eingeblendet werden (Diagramm-, Makro- und Dialogblätter dürfen ausgeblendet bleiben, da diese keine Zellen enthalten). Denken Sie daran, dass es auch unsichtbare Blätter geben kann. Solche Blätter müssen via Eigenschaften-Fenster des VBA-Editors sichtbar gemacht werden. Kontrollieren Sie daher im VBA-Editor die Visible-Eigenschaft der Tabellenblätter - sie darf nicht auf xlSheetVeryHidden gestellt sein. Auch AutoFilter und Gruppierungen/Gliederungen sollten entfernt oder zumindest ausgeschaltet werden, da diese Funktionen zu ausgeblendeten Zeilen/Spalten führen.

5) Erstellen Sie zuerst eine Sicherungskopie der Arbeitsmappe (quasi ein Master-Backup). Behalten Sie diese Kopie so lange, bis Sie mit der Überarbeitung der Zellformatierungen fertig und mit dem Resultat zufrieden sind. Legen Sie ausserdem während der Überarbeitung gelegentlich weitere Sicherungskopien an (Master-Backup nicht überschreiben!). Kontrollieren Sie dabei jedesmal die Dateigrösse der erstellten Sicherungskopie. Es kann nämlich vorkommen, dass die Datei durch das Löschen von Zellformatierungen plötzlich erheblich grösser wird (das zwei- oder dreifache der ursprünglichen Grösse). Wenn dies der Fall ist, arbeiten Sie mit der vorhergehenden Sicherungskopie weiter. Ausführliche Informationen über die Problematik der Dateigrössenzunahme finden Sie auf der Seite Dateigrösse von Arbeitsmappen.

6) Verschaffen Sie sich einen möglichst genauen Überblick über sämtliche Tabellenblätter der Arbeitsmappe, bevor Sie mit der Überarbeitung der Zellformatierungen beginnen. Da das Problem mit den zu vielen Zellformaten vor allem bei grösseren Arbeitsmappen auftritt, ist es natürlich nicht möglich, alle Zellen der Mappe im Detail zu kennen. Am einfachsten wäre die Reduktion der Anzahl Zellformate, wenn man wissen würde, welche Zellen das gleiche Zellformat verwenden. Nehmen wir mal an, die Mappe enthält 20 Tabellenblätter und auf jedem Blatt wurden lediglich 1/20 der verfügbaren Zellen benutzt. Der benutzte Zellbereich eines einzelnen Tabellenblattes umfasst somit 1'677'721 Zellen (16'777'216 / 10). Bei 20 Blättern sind es folglich 33'554'420 Zellen (20 x 1'677'721). Man müsste also genau wissen, welches der über 4'000 Zellformate von welchen der über 33.5 Millionen Zellen benutzt wird. Das ist schlicht unmöglich.

7) Bedenken Sie, dass die Überarbeitung der Zellformatierungen viel Zeit in Anspruch nimmt. Reservieren Sie sich am besten zwei oder mehr Stunden für diese Arbeit. Wenn Sie nämlich die Arbeit unterbrechen und zu einem späteren Zeitpunkt, beispielsweise am nächsten Tag, fortführen möchten, müssen Sie sich zuerst wieder einen Überblick über die Arbeitsmappe verschaffen, da man oft nicht mehr weiss, welche Zellen bzw. Zellformatierungen bereits überarbeitet wurden.

8) Machen Sie sich bei Bedarf während der Arbeit Notizen. Schreiben Sie jeweils auf, welche Zellen bzw. Zellformatierungen bereits erledigt wurden. Das hilft Ihnen zu vermeiden, dass bestimmte Blattbereiche ein weiteres Mal betrachtet werden, obwohl sie bereits überarbeitet wurden.