Home HomeXLimits - Hauptseite


[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":

Fehlermeldung "Zu viele verschiedene Zellformate"
Abbildung: Fehlermeldung "Zu viele verschiedene Zellformate"

Sprache Fehlermeldung
Deutsch Zu viele verschiedene Zellformate
Englisch Too Many Different Cell Formats
Französisch Nombre de formats de cellule différents trop élevé
Spanisch Hay demasiados formatos de celda diferentes
Polnisch Za duzo róznych formatów komórek

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.

To Top

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 Zellformaten1 enthält. Eine Kombination wird definiert als eindeutige Menge von Formatierungselementen, die auf eine Zelle angewendet werden. Sie enthält die gesamte Schriftartenformatierung2 (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.

Dialogfenster "Zellen" (Zellformat)
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.

Dialogfenster "Zellen" (Zellformat)
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.

To Top

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.

To Top

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 (1036). 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:

1 Sextillion  1036 = 1000 Quintilliarden
1 Quintilliarde  1033 = 1000 Quintillionen
1 Quintillion  1030 = 1000 Quadrilliarden
1 Quadrilliarde  1027 = 1000 Quadrillionen
1 Quadrillion  1024 = 1000 Trilliarden
1 Trilliarde  1021 = 1000 Trillion
1 Trillion  1018 = 1000 Billiarden
1 Billiarde  1015 = 1000 Billionen
1 Billion  1012 = 1000 Milliarden
1 Milliarde  109 = 1000 Millionen

To Top

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 212 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

To Top

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):

Das Excel Add-In "Cell Format Counter"
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

To Top

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.

To Top

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.

To Top

Seiten-Info

Artikel-ID xllimit6
Limitation-ID 25, 182

 

Verwandte Limitationen

Artikel-ID xllimit12
xllimit17
Limitation-ID 71, 110

 

Links

Liste der Limitationen-Artikel
Liste der Excel-Limitationen
Liste der Excel-Seiten
XLimits (Home)
xlam (Home)

Was man nicht darf
Bevor das Vorgehen zur Reduktion der Anzahl verschiedener Zellformate vorgestellt wird, sollten Sie unbedingt wissen, was man nicht tun darf:

Sie dürfen auf keinen Fall die Blätter der Arbeitsmappe in eine neue oder andere Arbeitsmappe kopieren beziehungsweise verschieben, egal ob die Blätter später wieder in die ursprüngliche Mappe zurückkopiert/zurückverschoben werden. Es ist dabei unerheblich, ob es sich bei einem kopierten/verschobenen Arbeitsblatt um ein Tabellen-, Diagramm-, Makro- oder Dialogblatt handelt.
- Erstens wird dadurch die Anzahl Zellformate nicht reduziert, weil bei diesem Vorgang sämtliche Formatierungsinformationen mit übernommen werden.
- Zweitens können dadurch andere, nicht weniger schwerwiegende Probleme entstehen (Stichwort 'Formatvorlagen' und 'benutzerdefinierte Zahlenformate' - eine Beschreibung dieser Probleme folgt in Kürze...).
- Drittens gehen durch das Kopieren/Verschieben der Blätter eine Vielzahl Informationen verloren. Mehr dazu erfahren Sie auf der Seite Dateigrösse von Arbeitsmappen, Frage Nr. 11 "Darf man sämtliche Blätter in eine neue Mappe kopieren, damit die Datei kleiner wird?".

Auch das Markieren und Kopieren/Einfügen eines gesamten Tabelleninhaltes oder Zellbereiches in eine neue Tabelle, egal ob sich diese in der gleichen oder in einer anderen Mappe befindet, ist keine gute Lösung. Dadurch werden zwar die oben erwähnten Probleme vermieden, eine Reduktion der Zellformate findet jedoch nicht statt, weil auch bei diesem Kopiervorgang sämtliche Zellformate mit übernommen werden.

To Top

Empfehlungen
Die Zellen von verbundenen Zellen (bzw. von einem zu einer einzigen Zelle verbundenem Zellbereich) können unterschiedlich formatiert sein. In Excel ist dies schlecht erkennbar. Die unterschiedliche Formatierung sieht man nur im Zellformatierungsdialogfenster "Zellen", weil in diesem Fall die nicht einheitlichen Formatierungseinstellungen nicht angezeigt werden können. Wenn die verbundenen Zellen beispielsweise zwei oder mehrere verschiedene Schriften verwendet wurden, ist das Eingabefeld "Schriftart" leer. In der Format-Symbolleiste dagegen wird diejenige Schriftart angezeigt, die von der aktiven Zelle verwendet wird.

Das Formatieren einzelner Wörter oder Zeichen eines Zelltextes ist wenn immer möglich zu vermeiden. So sollte beispielsweise im Zelltext "Summe aller Beiträge" das Wort "aller" nicht unterstrichen werden, weil dadurch die Zelle zwei verschiedene Formate (und Schriften) verwendet.

Arbeiten Sie am besten mit Formatvorlagen. Sie können jederzeit eigene Formatvorlagen erstellen (über Menübefehl Format/Formatvorlage, Schaltfläche "Hinzufügen") und diese dann beliebigen Zellen zuweisen. Achten Sie aber darauf, dass Sie keine Formatvorlagen quasi auf Vorrat anlegen.

Durch Ersetzen eines wenig benutzten Zellformates durch ein häufig benutztes Zellformat erreicht man am schnellsten eine Reduktion der Anzahl verschiedener Zellformate.

Löschen Sie sämtliche Merkmale (Inhalte, Formate, Kommentare, bedingte Formatierungen etc.) von unbenutzten, leeren Zellen anhand des Menübefehls Bearbeiten/Löschen/Alles.

Weisen Sie denjenigen Zellen, die kein bestimmtes Format mehr benutzen sollen, grundsätzlich die Formatvorlage "Standard" zu.

Übertragen Sie Formatierungen von einer Zelle auf andere Zellen durch Benutzen der Schaltfläche "Format übertragen". Sie befindet sich gewöhnlich unmittelbar rechts neben der Schaltfläche "Einfügen" auf der Standard-Symbolleiste. Durch das Übertragen eines bereits verwendeten Formates wird verhindert, dass ein neues Zellformat entsteht.

Vermeiden Sie die Format-Symbolleiste. Verwenden Sie nicht die Formatierungsschaltflächen wie Fett, Kursiv, Unterstrichen, Linksbündig, Zentriert, Rechtsbündig, Währungsformat, Prozentformat, Dezimalstelle hinzufügen, Einzug vergrössern etc., das Schriftart- und das Schriftgrad-Kombinationsfeld sowie die Schriftfarbe-, Füllmuster- und Rahmen-Schaltflächen auf dieser Symbolleiste.

Verwenden Sie nicht die Symbolleisten-Schaltfläche "Verbinden und zentrieren", wenn Sie mehrere Zellen zu einer einzigen Zelle zusammenführen möchten, die Zelle jedoch nicht zentriert sein soll. Benutzen Sie stattdessen die Schaltfläche "Zellen verbinden" oder aktivieren im "Zellen"-Dialogfenster auf der Registerseite "Ausrichtung" die Einstellung "Zellen verbinden". Wenn sich die zu verbindenden Zellen allesamt auf der gleichen Zeile befinden, können Sie auch die Schaltfläche "Zusammenführen über" verwenden.
  Die Funktion "Verbinden und zentrieren" müsste genau genommen "Zentrieren und verbinden" heissen. Bevor die markierten Zellen zusammengeführt werden, wird die horizontale Ausrichtung von jeder dieser Zellen auf "zentriert" gestellt. Erst dann werden die Zellen verbunden. Falls mehr als eine Zelle einen Wert besitzen, erscheint die Sicherheitsrückfrage "Die Auswahl enthält mehrere Werte. Wenn die Zellen verbunden werden, wird nur der Wert aus der obersten linken Zelle erhalten.". Das Problem ist hier, dass die Meldung erst angezeigt wird, nachdem die einzelnen Zellen bereits zentriert wurden. Man kann den Meldungsdialog zwar mit Abbrechen schliessen, sodass die Zellen nicht verbunden werden und die Zentrierung rückgängig gemacht wird, programmintern wurde die Anzahl Zellformate jedoch bereits erhöht. Beim Verbinden von beispielsweise 12 Zellen entstehen somit 12 neue Zellformate, auch wenn man Abbrechen wählt.

Benutzen Sie zum Ändern der Zellformatierungen das Dialogfenster "Zellen". Stellen Sie die gewünschten Formatierungen nicht in mehreren Schritten ein, d.h. schliessen Sie das Fenster erst dann mit OK, wenn alle neuen Formatierungseinstellungen festgelegt sind (nicht mehrmals das Fenster öffnen und schliessen).

Wenn Sie im Dialogfenster "Zellen" lediglich etwas nachschauen wollen, dann verlassen Sie das Fenster über die Abbrechen-Schaltfläche oder die Schliessen-Schaltfläche mit dem Kreuz-Symbol in der rechten oberen Ecke des Fensters und nicht über die OK-Schaltfläche.

Speichern, schliessen und öffnen Sie die Mappe möglichst oft, weil dadurch der Excel-interne Zähler für die Zellformate initialisiert wird. Beim Öffnen einer Arbeitsmappe lädt Excel die in der Datei gespeicherten Zellformate in den Arbeitsspeicher. Jedes Zellformat wird gewissermassen als Record in eine Tabelle eingetragen. Am Schluss merkt sich Excel, wieviele Datensätze die Tabelle enthält (das entspricht der Anzahl verschiedene Zellformate in der Mappe). Jedesmal wenn eine Zelle eine neue Formatierung erhält, erstellt Excel mit der neuen Formatierung einen Record und legt ihn in der Tabelle ab. Wenn der gleiche Record bereits existiert, tritt ein Fehler auf, wodurch der Record nicht eingetragen werden kann. Tritt kein ein Fehler auf, wird der Record eingetragen, wodurch die Anzahl Records in der Tabelle um 1 grösser wird.

Es müssen sämtliche Zellen berücksichtigt werden, die sich innerhalb des benutzten Bereiches eines Tabellenblattes befinden. Mit der Tastenkombination Strg+Ende wird die letzte Zelle eines Tabellenblattes selektiert. Durch Drücken der Taste Ende und anschliessendes Drücken der Eingabe-Taste wird eine Zelle der letzten benutzten Spalte eines Tabellenblattes selektiert.

Damit die Anzahl Zellformate effektiv reduziert wird, sollten Sie die Formatierung von möglichst vielen Zellen zusammen ändern. Markieren Sie am besten einen grösseren Zellbereich und ändern Sie dann die Formatierungseigenschaften. Das geht am einfachsten im Dialogfenster "Zellen", welches über den Menübefehl Format/Zellen oder die Tastenkombination Strg+1 geöffnet wird. Denken Sie daran, dass, wenn auch nur eine einzige nicht markierte Zelle das ursprüngliche Zellformat besitzt, die Anzahl Zellformate nicht reduziert wird.

Denken Sie immer daran, dass auch Zellen berücksichtigt werden müssen, die durch Objekte wie eingebettete Diagramme, Grafiken, AutoFormen, Steuerelemente, permanent angezeigte Kommentare und dergleichen verdeckt sind. Stellen Sie bei Bedarf die Objektanzeige-Option auf "Alle ausblenden" (Optionen-Dialog, Registerseite "Ansicht", Objekt-Option "Alle ausblenden"). Vergessen Sie nicht, die Option nach erledigter Zellformat-Überarbeitung wieder auf die ursprüngliche Einstellung zu setzen.

Wenden Sie sich zuerst "einfachen" Formatierungen zu: Farben (Automatisch, Keine), Unterstreichungen, nicht verfügbare Schriftarten, Schriftgrössen mit Dezimalstellen, Text-Ausrichtung Links statt Standard, etc.

Setzen Sie unterstützende Tools ein, wie beispielsweise die Microsoft Excel Add-Ins Style Expert, UsedRange Optimizer, UsedRange Expert, DataList Expert und ColumnFormat Optimizer. Informationen über diese Tools erhalten Sie auf der Website der CEFA-Toolbox, einer Werkzeugsammlung für Microsoft Excel, welche die genannten und weitere Add-Ins enthält.

To Top

Nachbearbeitung
Wenn Sie mit der Überarbeitung der Zellformatierungen fertig sind, sollten Sie die Arbeitsmappe ein letztes Mal speichern (aber nicht unter dem Original-Dateinamen; siehe Abschnitt "Grundsätzliches Vorgehen und Vorbereitung") und anschliessend die Dateigrösse im Windows Explorer oder in Microsoft Excel im Dialogfenster "Eigenschaften" kontrollieren.

Profi-Tipp 1: Anzahl Zellformate regelmässig kontrollieren
Die Anzahl Zellformate kann jederzeit kontrolliert und reduziert werden. Das ist eigentlich klar, sollte dennoch wieder einmal explizit erwähnt werden, weil man oft viel zu spät mit der Überarbeitung der Zellformatierungen beginnt. Also wenn die Fehlermeldung "Zu viele verschiedene Zellformate" bereits aufgetreten ist. Das Problem ist nämlich, dass bei Erreichen der Limitation kein Raum mehr zum Arbeiten vorhanden ist:
- Wenn man eine Formatierungskombination erwischt, die zu einem neuen Zellformat führt, so erscheint sofort die Fehlermeldung.
- Man kann Zellbereiche nicht vorübergehend "umformatieren", weil kein weiteres Format erstellt werden kann (Die Fehlermeldung würde erscheinen).
- Das Anlegen einer neuen Formatvorlage ist nicht möglich, da dies zu einem weiteren Zellformat führt, was wiederum die Fehlermeldung erscheinen lässt.

Die aktuelle Anzahl verschiedener Zellformate in einer Arbeitsmappe können Sie schnell und einfach mit dem Microsoft Excel Add-In Cell Format Counter abfragen. Dadurch erfahren Sie rechtzeitig, wie weit die Zellformate-Limitation noch entfernt ist (weitere Informationen siehe hier).

Profi-Tipp 2: Oft benötigte Funktionen auf eine benutzerdefinierte Symbolleiste legen
Während der Überarbeitung der Zellformatierungen werden Sie immer wieder die gleichen Excel-Funktionen und -Dialogfenster benutzen, beispielsweise den Dialog "Zellen" (bzw. "Zellformat"), die Funktion "Format übertragen", die Funktion "Formatierung löschen" und weitere. Da diese Fenster und Funktionen in Microsoft Excel über verschiedene Menüs und Symbolleisten aufgerufen werden, ist es von Vorteil, wenn Sie sich eine eigene, benutzerdefinierte Symbolleiste mit Schaltflächen für die oft benötigten Befehle anlegen. Die nachfolgende Abbildung zeigt eine solche Symbolleiste:

Benutzerdefinierte Symbolleiste mit Formatierungsfunktionen
Abbildung: Symbolleiste "Zellformate überarbeiten"

Die obige Symbolleiste hat sich in der Praxis gut bewährt, da alle wichtigen Funktionen über eine einzige Symbolleiste aufgerufen werden können - was letztendlich die Arbeit erleichtert und auch Zeit spart. Folgende Schaltflächen/Funktionen stehen zur Verfügung:

Schaltfläche Zweck/Beschreibung
Zellformatierung Öffnet das Dialogfenster "Zellen" (Abbildung)
Schriftfarbe Stellt die Schriftfarbe der selektierten Zelle ein
Füllfarbe Stellt die Füllfarbe der selektierten Zelle ein
Füllmuster Stellt die Muster der selektierten Zelle ein
Format übertragen Überträgt das Format der selektierten Zelle auf eine andere Zelle
Formatierung einfügen Fügt die Formatierungen aus der Zwischenablage ein (entspricht Menübefehl Bearbeiten/Inhalte einfügen/Formate)
Formatvorlage Öffnet das Dialogfenster "Formatvorlage" (Abbildung)
Optimale Zeilenhöhe Stellt die optimale Zeilenhöhe ein
Alles löschen Löscht alle Merkmale der selektierten Zelle (Inhalt, Format, Kommentar, bedingte Formatierung und Gültigkeitsprüfung)
Formatierung löschen Löscht die Formatierung der selektierten Zelle (Formatierung und bedingte Formatierung)
Rahmen entfernen Entfernt alle Rahmen der selektierten Zelle
Zellen löschen Löscht die selektierte Zelle
Zeilen löschen Löscht die selektierte Zeile
Spalten löschen Löscht die selektierte Spalte
Datei-Eigenschaften Öffnet das Dialogfenster "Eigenschaften von <Datei.xls>" (Abbildung)
Datei schliessen Schliesst die aktive Arbeitsmappe (Rückfrage "Änderungen speichern?" erscheint)

Anmerkung des Autors:
Damit Sie die Symbolleiste nicht selbst zusammenstellen müssen, habe ich sie an eine leere Arbeitsmappe angebunden und als Exceldatei gespeichert. Sie können diese Datei in komprimierter Form (zip-Datei) hier herunterladen:

Datei: Zellformate-Symbolleiste.zip
Inhalt: - Exceldatei (xls-Datei) mit einer angebundenen Symbolleiste.
- Die xls-Datei enthält keine Makros.

- Die Symbolleiste besitzt 16 Schaltflächen zum einfacheren Überarbeiten von Zellformatierungen (vgl. Abbildung weiter oben).
Grösse: 6 KB (entkomprimierte xls-Datei: 17 KB)
Datum: 24.09.2004
Hinweis: Speichern Sie die zip-Datei in einem beliebigen Ordner und entpacken Sie die Datei. Führen Sie dann einen Doppelklick auf die xls-Datei aus. Dadurch wird die Datei geöffnet und die enthaltene Symbolleiste in Microsoft Excel integriert. Nach Schliessen der Datei bzw. Beenden von Excel wird die xls-Datei nicht mehr benötigt und kann gelöscht werden (die zip-Datei ebenfalls).

To Top

Wichtige Hinweise
Beachten Sie bitte, dass die Anzahl Zellformate erst durch das Speichern und Schliessen (!) der Arbeitsmappe reduziert wird. Das Speichern der Mappe alleine nützt nichts, da sich die Zellformat-Informationen im Excel-Arbeitsspeicher befinden und dieser Speicherbereich erst beim Schliessen der Mappe geleert wird. Es gibt jedoch eine Ausnahme: Wenn eine unbenutzte Formatvorlage gelöscht wird, also eine Formatvorlage, die von keiner einzigen Zelle der Arbeitsmappe verwendet wird, so reduziert Excel die Anzahl Zellformate sofort um den Wert 1. Die Mappe muss in diesem Fall weder gespeichert, geschlossen noch neu geöffnet werden.

Anmerkung des Autors:
Leider gibt es in Excel keine Möglichkeit herauszufinden, ob die zu löschende Formatvorlage von Zellen verwendet wird. Sie wissen folglich gar nicht, ob die Anzahl reduziert wird, und müssen daher gezwungenermassen die Mappe speichern, schliessen und neu öffnen. Ob eine Formatvorlage benutzt wird, können Sie nur mit einem geeigneten Tool herausfinden. Beispielsweise mit dem Style Expert, einem Tool aus der CEFA-Toolbox. Der Style Expert ist ein Add-In für Microsoft Excel ab Version 97.

Es kann ohne weiteres vorkommen, dass auch nach dem Speichern, Schliessen und erneuten Öffnen der Arbeitsmappe noch immer keine neuen Zellformate verwendet werden können, d.h. obwohl die Formate (scheinbar) reduziert wurden, beispielsweise durch vollständiges Löschen (Inhalte und Formate) eines grösseren Zellbereiches. In diesem Fall existiert sehr wahrscheinlich irgend wo in der Mappe noch eine Zelle, die das gleiche Zellformat wie die gelöschten Zellen besitzt,

Die Arbeitsmappendatei kann durch das Löschen von Zellbereichen, ganzen Spalten oder Zeilen unerwartet gross werden. Eine doppelt so grosse Datei ist nicht ungewöhnlich. Warum das passieren kann, wird auf der Seite Dateigrösse von Arbeitsmappen ausführlich erklärt.

To Top

Massnahmen zum Vermeiden von zu vielen Zellformaten
Diese Beschreibung folgt in Kürze...

To Top

Nützliche Tools zum Analysieren und Optimieren der Zellformate
Es existiert in Microsoft Excel selbst keine Möglichkeit, die aktuelle Anzahl der in einer Mappe verwendeten Zellformate herauszufinden. Aus diesem Grund habe ich ein Analysetool namens Cell Format Analyzer entwickelt. Der Cell Format Analyzer ist das einzige mir bekannte Tool, mit dem die Zellformate einer Arbeitsmappe ausgelesen, analysiert und optimiert werden können.

Der Cell Format Analyzer kann auf der Download-Seite heruntergeladen werden:

Gehe zur Download-Seite  Hier gehts zur Download-Seite

Tipp: Bedingte Formatierung einsetzen
Wenn Ihre Arbeitsmappe auch nach Durchführen einer bestmöglichen Optimierung immer noch zu viele verschiedene Zellformate enthält, Sie aber unbedingt - quasi um jeden Preis -  weitere Zellen formatieren müssen, dann können Sie diesen Trick anwenden: Setzen Sie die bedingte Formatierung ein. Die bedingte Formatierung besitzt keinen Einfluss auf die Zellformate in einer Mappe. Das bedeutet, dass Sie Zellen formatieren können, ohne dass dabei die Anzahl verschiedener Zellformate grösser wird. Anhand einer geeigneten Bedingung (Formel) können Sie auf diese Weise weitere Zellen formatieren, auch wenn die Limitation bereits erreicht ist. Sie können auch die Anzahl Zellformate reduzieren, indem Sie bestehende Formatierungen von Zellen entfernen und durch die bedingte Formatierung ersetzen. Hier ein Beispiel:

Beispiel: Gefüllte Zelle(n) formatieren
1. Markieren Sie zuerst die Zelle oder den Zellbereich, der formatiert werden soll.
2. Öffnen Sie das Dialogfenster "Bedingte Formatierung" (Menübefehl Format/Bedingte Formatierung).
3. Definieren Sie für Bedingung 1: 'Zellwert ist' 'ungleich' '=""' (vgl. nächste Abbildung).
4. Klicken Sie auf Schaltfläche "Format" und stellen Sie die gewünschte Zellformatierung ein.
5. Verlassen Sie das Dialogfenster mit OK.

Dialogfenster "Bedingte Formatierung"
Abbildung: Dialogfenster "Bedingte Formatierung"

Wenn Sie beispielsweise für das zu verwendende Format Fettschrift, hellgrauer Hintergrund und Rahmenlinie unten einstellen (wie in der obigen Abbildung zu sehen), so können Sie auf diese Weise Spaltenüberschriften von Listen formatieren. Die folgende Abbildung zeigt einen Ausschnitt eines Tabellenblattes. Die Zellen der vierten Zeile wurden anhand der bedingten Formatierungen mit den oben erwähnten Formatierungseinstellungen belegt. Dadurch werden die Zellen mit einer Formatierung angezeigt, welche bei Spaltentiteln Sinn macht.

Mit der bedingten Formatierung formatierte Spaltentitel
Abbildung: Mit der bedingten Formatierung formatierte Spaltentitelzellen

Beachten Sie, dass in der oben abgebildeten Tabelle mit den Spaltenüberschriften lediglich ein einziges Zellformat verwendet wurde.

Hier erhalten Sie Informationen von Microsoft über die Limitation "Maximale Anzahl verschiedene Zellformate" und das Dialogfeld "Zellen formatieren":
  XL (alle Versionen): Fehlermeldung "Zu viele verschiedene Zellformate" (Excel für Windows und Mac)

  http://support.microsoft.com/?scid=kb;de;213904
  XL (all Versions): Error Message "Too Many Different Cell Formats" (Excel for Windows and Mac)
  http://support.microsoft.com/?scid=kb;en-us;213904
  XL (alle Versionen): Verwenden und Verstehen des Dialogfelds "Zellen formatieren" (Excel für Windows)
  http://support.microsoft.com/?scid=kb;de;264372
  XL (all Versions): Controlling and Understanding Settings in the Format Cells Dialog Box (Excel for Windows)
  http://support.microsoft.com/?scid=kb;en-us;264372
  MacXL (all Versions): Controlling and Understanding Settings in the Format Cells Dialog Box (Excel for Mac)
  http://support.microsoft.com/?scid=kb;en-us;298368

 

  Verwandte Limitation: Maximale Anzahl verschiedene Schriften
  Verwandte Limitation: Maximale Anzahl Zeilen mit bedingter Formatierung

To Top


Haben Sie Fragen, Anregungen oder einen Fehler entdeckt?
Kontakt zum Autor

Zuletzt aktualisiert am 25.01.2006 / 21:00 Uhr
Zuletzt kontrolliert am 19.12.2008 / 17:00 Uhr
© 2002-2009 by Philipp von Wartburg, Schweiz
Alle Rechte vorbehalten