Bedingte Formatierung - Grundlagen

Die bedingte Formatierung bietet ab Excel 2007 die Möglichkeit, Zellformate (Zahlenformat / Schriftschnitt / Rahmen / Muster / Farbskalen / Datenbalken / Symbole) in Abhängigkeit von Bedingungen zu setzen, die wir für bestimmte Zellen oder Bereiche definieren können. Formatierungsregeln setzen sich zusammen aus dem Regeltyp, der Regelbeschreibung und der Formatfestlegung. Die bedingte Formatierung dominiert über die "normale" Formatierung. Insofern keine der Bedingungen zutrifft, ist die "normale" Formatierung sichtbar. Die Vielfältigkeit von max. 64 bedingten Formatierungen beherbergt aber auch die Gefahr, dass niemand mehr die Tabelle versteht, weil sie zu bunt wird. Es ist deshalb sinnvoll, sich selbst zu beschränken.


Auf folgende Themen wird nachfolgend detailliert eingegangen:

• Wesentliche Unterschiede zur bedingten Formatierung in Vorgängerversionen
• Die zur Verfügung stehenden Symbolsätze
• Hier können Sie die Beispieldatei herunterladen, in der Sie zu allen u.g. Formatvorlagen und Regeltypen Erklärungen und Beispiele finden
• Neue Regel: Formatvorlagen nutzen
• Neue Regel alternativ: Regeln und Formate frei festlegen
• Regeln verwalten: Bestehende Regeln nachbearbeiten
• Erklärung zum Kopierverhalten:
  Neuer Bereich mit gleichen Regeln - Bestehenden Bereich vergrößern/verkleinern
• Zellbereiche mit bedingter Formatierung suchen
• Regeln löschen; verschiedene Löschoptionen
• Rangfolge der Regeln beachten und ändern
  
• Möglichkeiten des Kontrollkästchens Anhalten

Zum Blattanfang

Wesentliche Unterschiede zur bedingten Formatierung in Vorgängerversionen

• Bis Excel 2003 kann man max. 3 Regeln für bedingte Formatierung festlegen. Sobald eine Bedingung WAHR ist, werden deren Regeln angewandt. Alle weiteren Regeln werden ignoriert.
• Ab Excel 2007 stehen uns bis 64 Regeln pro Zelle/Bereich zur Verfügung. Alle Regeln werden angewandt, außer wenn es zu einem Konflikt kommt, weil dadurch mehrere Formatierungen gleichzeitig durchgeführt werden müssten, die sich widersprechen. Dann wird die ranghöhere Regel ausgeführt.
  Auch Zahlenformate können ab Excel 2007 bedingt vergeben werden. Es wird nicht mehr nur geprüft, ob eine Bedingung WAHR ist, sondern auch der Formatstil wird überprüft. Dadurch lassen sich Bedingte Formate auch kombinierend überlagern. Siehe in diesem Zusammenhang auch unter Rangfolge und Kontrollkästchen Anhalten.
  Neu sind die Formatstile Farbskala, Datenbalken und Symbolsatz.
  Das Kopierverhalten der Bereiche mit bedingter Formatierung hat sich sehr verändert. Da muss man Vorsicht walten lassen, um korrekte Ergebnisse zu erzielen.

Zum Blattanfang

Die zur Verfügung stehenden Symbolsätze

Je nach Symbolart ergeben sich 3-5 Schwellenwerte, je nach Symbolanzahl. Sie können innerhalb einer Regel die Symbolsätze auch mischen, also z.B. grüne Ampel / gelbe Fahne / roter Pfeil, dies erreichen Sie über Auswahl im Dropdown neben den Symbolen im Dialogfenster Formatierungsregel bearbeiten. Auch ist es möglich Kein Zellensymbol dort auszuwählen, wenn Sie dies z.B. bei den 3 farbigen Pfeilen beim gelben Pfeil machen, werden nur noch die grünen und roten Pfeile angezeigt. Dort können Sie auch durch Klick auf die Schaltfläche Symbolreihenfolge umkehren diese umkehren und durch anhaken eines Kontrollkästchens entscheiden, ob die Symbole mit oder ohne Wert angezeigt werden. Eigene Symbolsätze können nicht erstellt werden.

Zum Blattanfang

Formatvorlagen

• Voraussetzung zum Anlegen der Bedingten Formatierung ist, dass Sie zuerst einen/mehrere Zellbereiche markiert haben, für die die Bedingung gelten soll
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen. Dort können Sie direkt zwischen vorgegebenen Formatvorlagen wählen.
• Durch Anklicken der Auswahl bzw. Drücken der Schaltfläche OK wird die entsprechende bedingte Formatierung ausgewählt und damit auf den markierten Bereich übertragen. Eine neue Regel wird immer über eventuell schon bestehenden Regeln eingefügt und als Ranghöchste behandelt. Ändern Sie gegebenfalls die Rangfolge!

Bei den Vorgaben steht folgende Auswahl zur Verfügung:

Formatvorlage		Bemerkung
Regeln zum Hervorheben von Zellen	Größer als…	Vergleichswert (das kann auch eine absolutierte Zelladresse sein, z.B. $A$5) kann in Dialog eingegeben werden und Formatierung im Dropdown ausgesucht werden
	Kleiner als…
	Zwischen…
	Gleich…
	Textinhalt…
	Datum…
	Doppelte Werte…	Auswahl, ob doppelte oder eindeutige Werte angezeigt werden sollen. Formatierung kann im Dropdown ausgesucht werden
Obere/Untere Regeln	Obere 10 Elemente…	Die Stückzahl bzw. der Prozentsatz kann im Dialogfeld geändert werden und die Formatierung kann im Dropdown ausgesucht werden
	Obere 10%...
	Untere 10 Elemente…
	Untere 10%...
	Über dem Durchschnitt…	Die Formatierung kann im Dropdown ausgesucht werden
	Unter dem Durchschnitt…
Datenbalken	mit Farbverlauf	Einen farbigen Datenbalken in der Zelle anzeigen. Die Länge des Datenbalkens stellt den Wert in der Zelle dar. Ein längerer Balken stellt einen höheren Wert dar. Gibt es im Bereich negative Zahlen, werden deren Balken in roter Farbe von einer automatisch gesetzten Achse nach links angezeigt.
	oder mit einfarbiger Füllung
Farbskalen		Einen zwei- oder dreifarbigen Farbverlauf in einem Bereich von Zellen anzeigen. Die Farbschattierung stellt den Wert der Zelle dar
Symbolsätze		Eine Auswahl von Symbolsätzen wird angezeigt zwischen denen wir uns entscheiden können. Automatisch findet dort immer eine Aufteilung in Prozent statt. Möglich ist eine Auswahl des Typs aber zwischen Zahl, Prozent, Formel oder Quantil. Je mehr Symbole ein Symbolsatz enthält, desto feiner ist die prozentuale Abstufung. Es kann ausgewählt werden, ob nur die Symbole oder auch die Werte angezeigt werden sollen.

Zum Blattanfang

Regeln und Formate frei festlegen

• Voraussetzung zum Anlegen der Bedingten Formatierung ist, dass Sie zuerst einen/mehrere Zellbereiche markiert haben, für die die Bedingung gelten soll
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen

Dort klicken Sie an: Neue Regel.... Im Dialogfenster Neue Formatierungsregel steht folgende Auswahl zur Verfügung:

Regeltyp	Formatstil / Auswahl	Bemerkung
Alle Zellen basierend auf ihren Werten formatieren	2-Farben-Skala	Farben auswählen
	3-Farben-Skala
	Datenbalken	- Im Kontrollkästechen "nur Balken anzeigen" anhaken, ob nur Balken ohne Werte angezeigt werden sollen. Die Länge des Datenbalkens stellt den Wert in der Zelle dar. Ein längerer Balken stellt einen höheren Wert dar. Gibt es im Bereich negative Zahlen, werden deren Balken in roter Farbe von einer automatisch gesetzten Achse nach links angezeigt. Bei der Balkendarstellung hat man viele Auswahlmöglichkeiten über Dropdown: - Ausfüllen: Einfarbige Füllung oder Farbverlauf - Farbe: Durch Anklicken im Dropdown auswählen - Rahmen: Kein Rahmen oder Durchgezogener Rahmen (empfohlen) aussuchen - Rahmenfarbe: Durch Anklicken im Dropdown auswählen
	Symbolsätze	Symbolart im Dropdown auswählen, dabei ergeben sich 3-5 Schwellenwerte, je nach Symbolanzahl. Im Kontrollkästchen "nur Symbol anzeigen" anhaken, wenn nur Symbole ohne Werte angezeigt werden sollen.
Nur Zellen formatieren, die enthalten	Zellwert	Zahlenformat, Hintergrundfarbe, Rahmen und Schriftschnitt sowie Schriftfarbe können festgelegt werden
	bestimmter Text
	Datum
	Leerzeichen
	Kein Leerzeichen
	Fehler
	Kein Fehler
Nur obere oder untere Werte formatieren	obere	Die Stückzahl bzw. der Prozentsatz kann im Dialogfeld geändert werden, ebenso kann zwischen % und Wert gewählt werden. Zahlenformat, Hintergrundfarbe, Rahmen und Schriftschnitt sowie Schriftfarbe können festgelegt werden.
	untere
Nur Werte über oder unter dem Durchschnitt formatieren	über	Zahlenformat, Hintergrundfarbe, Rahmen und Schriftschnitt sowie Schriftfarbe können festgelegt werden.
	unter
	gleich oder über
	gleich oder unter
	1 Std Abw Über
	1 Std Abw Unter
	2 Std Abw Über
	2 Std Abw Unter
	3 Std Abw Über
	3 Std Abw Unter
Nur eindeutige oder doppelte Werte formatieren	doppelte	Zahlenformat, Hintergrundfarbe, Rahmen und Schriftschnitt sowie Schriftfarbe können festgelegt werden
	eindeutige
Formel zur Ermittlung der zu formatierenden Zellen verwenden	Formel eingeben, die für die zu formatierenden Zellen WAHR/FALSCH ist. Dabei muss die Formel nur für die aktive Zelle eingegeben werden, sie überträgt sich relativ auf den markierten Bereich.	Zahlenformat, Hintergrundfarbe, Rahmen und Schriftschnitt sowie Schriftfarbe können festgelegt werden. Die Formellösung bietet sich immer an, wenn Formatierungen außerhalb der vordefinierten Regeltypen vorgenommen werden sollen bzw. wenn Bezug auf Zellen außerhalb des Regelbereichs genommen werden soll. Wichtig ist der korrekte Umgang mit relativen und absoluten Bezügen.

Durch Drücken der Schaltfläche OK wird die entsprechende bedingte Formatierung ausgewählt und damit auf den markierten Bereich übertragen. Eine neue Regel wird immer über eventuell schon bestehenden Regeln eingefügt und als Ranghöchste behandelt. Ändern Sie gegebenfalls die Rangfolge!

Zum Blattanfang

Bestehende Regeln nachbearbeiten

• Markieren Sie eine Zelle im Bereich, dessen Bedingte Formatierung Sie ändern wollen
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln verwalten...
• Das Dialogfenster Manager für Regeln zur bedingten Formatierung öffnet sich
• Es werden Ihnen alle Regeln angezeigt, die in der markierten Zelle gelten
• Markieren Sie die Regel, die Sie ändern möchten; dort wird Ihnen auch der Bereich angezeigt, für den die Regel gilt und den Sie hier auch ändern können
• Klicken Sie auf die Schaltfläche Regel bearbeiten...
• Das Dialogfenster Formatierungsregeln bearbeiten öffnet sich mit den Einstellungen, wie sie bisher festgelegt waren
• Sie können nun die gewünschten Änderungen hinsichtlich Regel und Formatierung vornehmen. Es stehen Ihnen alle Möglichkeiten offen wie oben beschrieben
• Schließen Sie die Fenster über die Schaltflächen OK und Übernehmen, um die Änderungen festzuhalten

Zum Blattanfang

Erklärung zum Kopierverhalten - Neuer Bereich mit gleichen Regeln - Bestehenden Bereich vergrößern/verkleinern

Grundlegende Frage ist, für welchen Bereich soll die Regel gelten? Davon hängt ab, wie wir bedingte Formatierung kopieren.

Neuen Bereich mit gleichen Regeln anlegen mit dem Formatpinsel:
Bisher waren wir gewohnt, bedingte Formate mit dem Formatpinsel zu übertragen. Wenn wir das in Excel ab Version 2007 machen, legt Excel einen neuen Bereich der bedingten Formatierung an. Dies kann zu ungewollten Auswirkungen führen. Also: Nur wenn wir ganz bewußt einen Bereich als neuen Bereich definieren wollen, können wir den Formatpinsel einsetzen. Die Regeln sind zwar die Gleichen wie im kopierten Bereich, werden aber jeweils innerhalb des alten und neuen Bereichs getrennt ausgewertet. Eine weitere Folge ist, wenn wir anschließend Regeln für einen der beiden Bereiche ändern, wirken sich diese Änderungen nicht auf den anderen Bereich aus.

Zeilen oder Spalten im bestehenden Bereich einfügen:
Wenn wir eine oder mehrere Zeilen bzw. Spalten innerhalb eines bestehenden Bereichs für eine Regel der bedingten Formatierung einfügen, werden diese in den Bereich aufgenommen.

Bestehenden Bereich der Regel vergrößern oder verkleinern:
Dazu müssen wir den Bereich schon bestehender Regeln im Dialogfenster Manager für Regeln zur bedingten Formatierung im Dialogfeld wird angewendet auf ändern. Im Einzelnen geht das so:

• Markieren Sie eine Zelle im Bereich, den Sie ändern (erweitern/verkleinern) wollen
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln verwalten...
• Das Dialogfenster Manager für Regeln zur bedingten Formatierung öffnet sich
• Es werden Ihnen alle Regeln angezeigt, die in der markierten Zelle gelten
• Markieren Sie die Regel, die Sie ändern möchten; dort wird Ihnen auch der Bereich angezeigt, für den die Regel gilt und den Sie hier auch ändern können
• So kann auch für auseinanderliegende Zeilen bzw. Spalten die gleiche Regel gelten, indem Sie den Bereich mit Semikolon eingeben z.B.: =$A$1:$C$4;$A$8:$C$8. Sie können die Bereiche aber auch mit der Maus markieren und sie werden automatisch ins Textfeld übernommen.
• Schließen Sie das Fenster über die Schaltflächen Übernehmen und OK, um die Änderungen festzuhalten

Verkleinert wird ein Bereich auch automatisch:

• Wenn wir Zeilen bzw. Spalten löschen oder
• Über das Untermenü Formate löschen bzw. Alle löschen in markierten Zellen löschen (das geht über das Löschen-Symbol im Register Start in der Gruppe Bearbeiten)

Zum Blattanfang

Zellbereiche mit bedingter Formatierung suchen

Um sich alle bedingt formatierten Zellen eines Arbeitsblatts anzeigen zu lassen, klicken Sie auf das Symbol Suchen und Auswählen im Register Start in der Gruppe Bearbeiten. Dort klicken Sie auf den Befehl Bedingte Formatierung. Dadurch werden alle bedingt Formatierten Zellen markiert.

Wenn Sie sich alle Zellen eines Bereichs anzeigen lassen wollen, die die gleiche bedingte Formatierung wie eine bestimmte Zelle haben, dann markieren Sie diese bestimmte Zelle zunächst. Danach klicken Sie auf das Symbol Suchen und Auswählen im Register Start in der Gruppe Bearbeiten. Dort klicken Sie auf den Befehl Inhalte auswählen. Im Dialogfenster Inhalte auswählen wählen Sie die beiden Optionen Bedingte Formate und Gleiche aus. Verlassen Sie das Dialogfenster über die Schaltfläche OK. Dadurch werden alle Zellen markiert, die die gleiche Formatierung haben wie die ursprünglich von Ihnen markierte Zelle und im gleichen Bereich liegen.

Im Dialogfeld Manager für Regeln zur bedingten Formatierung können Sie sich darüber hinaus blattweise nach Dropdownauswahl neben Formatierungsregeln anzeigen für die Regeln anzeigen lassen. Das Dialogfeld erreichen Sie, indem Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen klicken. Dort klicken Sie das Menü: Regeln verwalten... an.

Zum Blattanfang

Regeln löschen

Abhängig davon, was Sie löschen wollen, müssen verschiedene Wege eingeschlagen werden:

Wenn eine oder mehrere Regeln eines Bereichs gelöscht werden sollen, andere Regeln für den Bereich aber bestehen bleiben sollen, dann:

• Markieren Sie eine Zelle im Bereich, in dem Sie Regeln löschen wollen
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln verwalten...
• Das Dialogfenster Manager für Regeln zur bedingten Formatierung öffnet sich
• Es werden Ihnen alle Regeln angezeigt, die in der markierten Zelle gelten
• Markieren Sie die Regel, die Sie löschen möchten; dort wird Ihnen auch der Bereich angezeigt, für den die Regel gilt
• Klicken Sie auf die Schaltfläche Regel löschen
• Schließen Sie die Fenster über die Schaltfläche OK

Wenn alle Regeln eines Bereichs gelöscht werden sollen, dann:

• Markieren Sie den Bereich, in dem Sie Regeln löschen wollen
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln löschen...
• Dann klicken Sie das Untermenü Regeln in ausgewählten Zellen löschen an
• Die Löschung geschieht umgehend

Wenn alle Regeln des aktiven Arbeitsblatts gelöscht werden sollen, dann:

• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln löschen...
• Dann klicken Sie das Untermenü Regeln im gesamten Blatt löschen an
• Die Löschung geschieht umgehend

Alle Regeln einer Tabelle löschen:
Dieser Unterbefehl steht nur zur Verfügung, wenn ein Tabellenbereich voher über das Register Einfügen, Gruppe Tabellen, Befehl Tabelle in eine Tabelle umgewandelt worden ist. Unter dieser Voraussetzung können Sie die bedingte Formatierung so löschen:

• Markieren Sie eine Zelle im Tabellenbereich
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln löschen...
• Dann klicken Sie das Untermenü Regeln in dieser Tabelle löschen an
• Die Löschung geschieht umgehend

Alle Regeln einer PivotTabelle löschen:
Dieser Unterbefehl steht nur zur Verfügung, wenn es sich um eine PivotTabelle handelt. Unter dieser Voraussetzung können Sie die bedingte Formatierung so löschen:

• Markieren Sie eine Zelle im PivotTabellenbereich
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln löschen...
• Dann klicken Sie das Untermenü Regeln in dieser PivotTabelle löschen an
• Die Löschung geschieht umgehend

Zum Blattanfang

Rangfolge der Regeln ändern

Ab Excel 2007 stehen uns bis 64 Regeln pro Zelle/Bereich zur Verfügung. Alle Regeln werden angewandt, außer wenn es zu einem Konflikt kommt, weil dadurch mehrere Formatierungen gleichzeitig durchgeführt werden müssten, die sich widersprechen. Dann wird die ranghöhere Regel ausgeführt.

Deshalb ist es wichtig, dass jede Regel an der richtigen Stelle in Bezug auf die anderen Regeln angeordnet ist. Je höher eine Regel in der Reihenfolge der Regeln steht, desto höher ist ihr Rang.

• Markieren Sie eine Zelle im Bereich, dessen Rangfolge der Regel Sie ändern wollen
• Klicken Sie auf das Symbol Bedingte Formatierung im Register Start in der Gruppe Formatvorlagen
• Dort klicken Sie an: Regeln verwalten...
• Das Dialogfenster Manager für Regeln zur bedingten Formatierung öffnet sich
• Es werden Ihnen alle Regeln angezeigt, die in der markierten Zelle gelten
• Markieren Sie die Regel, deren Rangfolge Sie ändern möchten; dort wird Ihnen auch der Bereich angezeigt, für den die Regel gilt
• Die Rangfolge der Regel ändern Sie nun, indem Sie auf eins der Dreieckssymbole klicken (entweder nach oben oder nach unten)
• Schließen Sie das Fenster über die Schaltflächen Übernehmen und OK, um die Änderungen festzuhalten

Zum Blattanfang

Möglichkeiten des Kontrollkästchens Anhalten

• Beim Einsatz des Kontrollkästchens Anhalten ist zunächst zu beachten, dass die Regeln in der gewünschten Rangfolge stehen.
• Das angehakte Kästchen Anhalten hinter einer Regel wirkt sich nur auf Zellen aus, deren Bedingung WAHR ist und zwar nur dann, wenn ein anderer Formatstil angesprochen wird. Dann wird der Formatstil der niederrangigeren Regel nicht berücksichtigt. Unter Formatstilen versteht man Zahlenformat / Schriftschnitt / Rahmen / Muster
• Beim gleichen Formatstil wird ja automatisch die ranghöhere Regel ausgeführt, somit hat das Kästchen Anhalten dort keine Auswirkung.
• Die Prüfung und Anwendung der Formatierungen der folgenden Regeln werden aber für Zellen fortgesetzt, die bis dahin FALSCH als Ergebnis hatten.
• Das Kontrollkästchen Anhalten kann nicht für die Formatstile Farbskala, Datenbalken oder Symbolsatz aktiviert werden.

Wichtig ist also das Wissen, dass Excel ab der Version 2007 zwei Prüfungen vornimmt:

• Zum Einen, ob die Bedingung der Regel WAHR/FALSCH ist und
• Zum Anderen wird auf den Formatstil der Regel geprüft

	A	B
1	Beispiel: Auswirkung des Kontrollkästchens Anhalten:
2
3	Kontrollkästchen Anhalten nicht angehakt hinter Regel 1:	6
4	Die erste Regel ist WAHR, deshalb wird die Hintergrundfarbe blau
5	Der Formatstil Hintergrundfarbe grün der 2. Regel wird deshalb nicht angezeigt
6	Aber der Formatstil Schriftschnitt Fett der 2. Regel wird angezeigt
7	Daher ist dies auch ein Beispiel für die Kombination aus zwei Regeln
8
9	Kontrollkästchen Anhalten angehakt hinter Regel 1:	6
10	Die erste Regel ist WAHR, deshalb wird die Hintergrundfarbe blau
11	Der Formatstil Hintergrundfarbe grün der 2. Regel wird deshalb nicht angezeigt
12	Durch das angehakte Kästchen Anhalten wird der Formatstil Schriftschnitt Fett der 2. Regel nicht angezeigt

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format B3 1. / Formel ist =B3>4 Abc B3 2. / Formel ist =B3>5 Abc B9 1. / Formel ist =B9>4 Abc B9 2. / Formel ist =B9>5 Abc