Excel bedingte Formatierung - Aufbau
Workshops > Excel
Stand 26.10.2020
Die bedingte Formatierung mit Farben, Symbolen und Datenbalken ist nichts Neues mehr für Sie. Sie wollen mehr Einfluss auf die Bedingungsdefinitionen nehmen. Dies ist der Sinn des zweiten Teils dieses Workshops zum Thema Bedingte Formatierung. Ich zeige Ihnen, wie sich Ihnen mit Formeln fast grenzenlose Möglichkeiten auftun und wie Sie mehr Komfort in die Nutzung bringen. In einem dritten Teil habe ich zur Nachvollziehbarkeit und als Anregung einige Praxisbeispiele zum Nachbauen aufgeführt.
Sollten Ihnen die oben erwähnten Kenntnbisse fehlen, empfehle ich Ihnen, den ersten Teil des Workshops vorab durchzuarbeiten.
Formeln zur Bedingten Formatierung einsetzen
Die Beispiele des Basisteils basierten jeweils darauf, dass sich die Bedingung auf den Inhalt der zu formatierenden Zelle bezieht. Soll eine andere Zelle geprüft bzw. eine Bedingung berechnet werden, so verwenden Sie den Regeltyp Formel.
- Rufen Sie die nach dem Markieren der zu formatierenden Daten die Liste zur Bedingten Formatierung im Register Start auf.
- Wählen Sie den Eintrag Neue Regel...
- Im sich öffnenden Dialog klicken Sie auf den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden.
- Geben Sie in das Eingabefeld zur Regelbeschreibung die Formel ein ...
- ... und Formatieren die Zellen wie gewohnt.
- Schließen Sie mit OK ab.
In diesem Beispiel wird der Wochentag der Daten in den markierten Zellen per Funktion (wochentag) ermittelt und anschließend die Wochenenden (Wochentag 6 und 7) farblich hervorgehoben.
Beachten Sie, dass die Eingabe der Formel ohne jegliche Hilfe erfolgen muss. Es werden demnach keine Formelhilfen wie Funktionsinfos eingeblendet, der Funktionsassistent ist nicht verfügbar und auf Autovervollständigen warten Sie vergebens. Mei Tipp für komplizierte Formeln: Schreiben Sie diese zuerst in eine Zelle rechts von den Daten in die erste Zeile, übrprüfen sie und kopieren sie anschließend in das Eingabefeld. Das erspart ggf. eine Menge Sucharbeit bei Fehlern.
Per Mausklick eingefügte Zelladressen werden standardmäßig als Absolutadressen eingefügt. Ändern Sie dies bei Bedarf.
Die Tabelle für obiges Beispiel sieht anschließend wie folgt aus:
Mehrere Bedingungen festlegen
Die bisherigen Beispiele enthielten nur eine Bedingung pro Zelle. In Excel sind seit Version 2007 jedoch bis zu 64 Bedingungen je Zelle möglich - mehr als ausreichend. Dabei können auf eine Zelle mehrere verschiedene Regeltypen angewendet werden. So lassen sich beispielsweise Umsätze mit einer Farbskala und zusätzlich Symbolen formatieren.
Der Grund für den Einsatz mehrerer Bedingungen kann beispielsweise eine Staffelung sein. So sollen in einer Umsatzliste die Umsätze derart hervorgehoben werden, dass alle negativen Zahlen rot dargestellt werden, Zahlen ab 1.000 in Grün und der Rest in Gelb (0-999).
- Markieren Sie die zu formatierenden Zellen (hier A1:A8), rufen den Dialog über Regeln zum Hervorheben von Zellen auf und definieren die beiden Bedingungen (zwei Aufrufe). Alternativ über den Eintrag Bedingte Formatierung | Regel verwalten... und danach Neue Regel....
- Die beiden Bedingungen stehen anschließend im Verwaltungsdialog untereinader und werden von oben nach unten abgearbeitet. Die Reihenfolge der Regeln ist in diesem Beispiel nicht von Bedeutung.
In der ersten Bedingung werden die Zellwerte, die größer oder gleich 1.000 sind grün formatiert. - Mit der zweiten Bedingung werden Zahlen kleiner als 0 rot formatiert.
- In den Feldern Wird angewendet auf sehen Sie die Angabe zum Zellbereich, der formatiert werden soll (als Absolutbezug definiert).
- Bestätigen Sie mit OK.
Woher kommt aber die Formatierung bei den gelben Zellen? Dabei handelt es sich um die manuell vorgenommene Zellformatierung, die auif alle Zellen angewandt wird, bei denen keine der Bedingungen zutrifft. Die bedingte Formatierung überlagert quasi die Standardformate.
Übrigens: Mit der Bedingten Formatierung lassen sich nur solche Formate verändern, die sich nur auf die zu formatierenden Zellen auswirken. Das heißt, dass zum Beispiel die Schriftgröße und die Schriftart nicht veränderbar sind. Ebenso sind die sonst in Excel verfügbaren Rahmenarten (Dicke, doppelt etc.) nicht wählbar. Durch diese Formate würden Spaltenbreiten bzw. Zeilenhöhen verändert und dadurch auch nicht zu formatierende Zellen betroffen.
Konkurrierende Regeln
Manchmal ergeben sich Fälle, in denen die Regeln gegenseitig konkurrieren.
Im folgenden Bild sind die Regeln angegeben: >=500 und >=800. Im ersten Fall wird die Aussage auf >=500 überprüft. Trifft diese zu, wird blau formatiert. Anschließend erfolgt die Prüfung der Werte auf die Aussage >=800. Da die erste Aussage die zweite impliziert (da >=500 ALLE Werte ab 500 beinhaltet), bleibt die zweite Bedingung gegenstandslos. Eine Formatierung in Orange findet nicht statt.
Was also tun? Wir drehen die Aussagen um, prüfen zuerst auf Werte ab 800, danach auf Werte ab 500. Da hier bei der zweiten Prüfung nur noch die Werte bis maximal 799 geprüft werden, erfolgt die Formatierung korrekt. Die Veränderung der Reihenfolge erfolgt mit Hilfe der kleinen nach oben/unten weisenden Pfeile.
Ergeben also mehrere Bedingungen ein WAHR, werden jeweils die Formatmerkmale übernommen, die nicht bereits in einer vorangegangenen Bedingung verändert wurden. Im Beispiel oben wurden jeweils die Zellfarben verändert. Daher konkurrieren sie. Würde im Beispiel bei der einen Aussage die Zellfarbe geändert, bei der anderen aber die Schriftfarbe, würden beide Aussagen zu einer Formatierung führen, unabhängig von deren Reihenfolge. Wenn also Ihre Logik nicht zu funktionieren scheint, überpüfen Sie, ob ggf. eine Konkurrenzsituation vorliegt.
Falls Sie dieses Verhalten der weitergehenden Prüfungen nicht möchten, Excel also nach dem ersten WAHR einer Aussage nicht weiterprüfen soll, aktivieren Sie das Optionskästchen Anhalten. Ansonsten hat diese Option keine Bedeutung mehr und ist aus alten Versionen übriggeblieben. Wir werden sie allerdings später als eine Art Ein-/Ausschalter für Formatierungen nutzen.
Weitere Formatierungsmöglichkeiten
Neben den bereits gesehenen Formaten lässt Excel im Rahmen der Bedingten Formatierung auch Änderungen an den Zahlenformaten zu. Im Register Zahlen des Formatdialogs der Bedingten Formatierung können Sie analog dem sonst verwendeten Formatdialog die Formate individuell einstellen - auch benutzerdefinierte Zahlenformate.
Beispielhaft sollen die Zahlen eines Bereichs über einer Milliarde mit Mrd abgekürzt ausgegeben werden, über einer Million mit Mio und über 1.000 mit Tsd. Mit benutzerdefinierten Zahlenformaten alleine wäre dies nicht möglich, da maximal zwei Varianten eines Zahlenformats definierbar sind. Also verwenden wir stattdessen die Bedingte Formatierung, bei der es diese engen Grenzen nicht gibt.
Definieren Sie drei Bedingungen: Zellwert >= 1000, >=1000000, >= 1000000000. Legen Sie dazu als benutzerdefiniertes Format fest (die Punkte im Format repräsentieren dabei jeweils ein Vielfaches von Tausend, also drei Stellen):
Auf diese Weise ließen sich zum Beispiel auch Währungsformate in Abhängigkeit von Werten in anderen Zellen festlegen, oder, oder, oder.
Suchen, Auswählen, Kopieren, Löschen
Zum Ändern oder Löschen einer Bedingten Formatierung müssen Sie die Daten nicht komplett markieren. Ist eine Zelle innerhalb des formatierten Bereichs markiert, wenn Sie den Regelmanager aufrufen, ist dort unter Formatierungsregeln anzeigen für bereits der Eintrag Aktuelle Auswahl gewählt.
Wollen Sie einen anderen Bereich bearbeiten, wählen Sie aus der Liste den passenden Eintrag aus. Neben Aktuelle Auswahl sind Dieses Arbeitsblatt, alle anderen Tabellenblätter sowie alle Als Tabelle formatierte Bereiche sowie PivotTable aufgelistet. Ein Klick in das Eingabefeld Wird angewendet auf markiert den Bereich mit einem Laufrahmen. Hier lässt sich dieser problemlos durch erneutes Markieren anpassen.
Eine alternative Suchmethode bietet die Schaltfläche Suchen und Auswählen im Start-Register. Wählen Sie dort Inhalte auswählen und aktivieren die Option Bedingte Formate. Über die beiden Optionen Alle bzw. Gleiche steuern Sie den Umfang der Auswahl. Letztere entspricht dabei der zuvor angesprochenen Aktuelle Auswahl.
Beabsichtigen Sie, ein bedingtes Format auf andere Zellen zu kopieren stehen Ihnen verschiedene Wege offen. Erstens lässt sich dieses Vorhaben per Kopieren und Einfügen (Strg+C und Strg+V) bzw. mit dem Formatpinsel erledigen. Dabei wird jedoch eine neue Bedingte Formatierung hinzugefügt. Die bestehende Bedingung wird folglich nicht erweitert!. Gehören beide Bereiche (Original und Kopie) zusammen, zieht dies bei der späteren Pflege unnötigen Mehraufwand nach sich, da Sie immer beide Bedingungen anpassen müssen. 
Ein weiterer Nachteil dieser Methode ist, dass sowohl durch Kopieren als auch Format übertragen ALLE Formate übernommen werden, auch Zahlenformate, Währungen, benutzerdefinierte Formate etc.
Soll der Bereich lediglich erweitert werden, aber als EINE Bedingung gelten, ergänzen Sie im Manager im Eingabefeld Wird angewendet auf nach einem Semikolon den neuen Zellbereich. Sie können das wie in Excel beim Erweitern einer Zellmarkierung gewohnt per Strg-Taste und Markieren mit der Maus erledigen.
Möchten Sie eine definierte Bedingte Formatierung löschen, rufen Sie die Befehlsliste auf, wählen Regeln Löschen und dann den passenden Eintrag.
Alternativ rufen Sie den Dialog Regeln verwalten... auf und löschen dort die Regel nach dem MArkieren durch Klicken auf die Schaltfläche Regel löschen. Die durch die Regeln bedingten Formate werden entfernt und die ursprünglichen Formate werden wieder sichtbar.
Bedingte Formate in Pivot-Tabellen
Die Bedingte Formatierung ist auch auf Pivot-Tabellen anwendbar. Einige Unterschiede sind hierbei jedoch zu beachten, die nachstehend erläutert werden.
- Ändern Sie das Layout des PivotTable-Berichts, indem Sie Filter anwenden, Ebenen ausblenden, Ebenen reduzieren oder erweitern oder ein Feld verschieben, bleibt die bedingte Formatierung bestehen - allerdings nur, wenn die Felder in den zugrunde liegenden Daten nicht entfernt werden.
- Verwenden Sie gruppierte Werte oder eine Hierarchie, wird die bedingte Formatierung der Daten der übergeordneten Ebene nicht automatisch an die untergeordnete Ebene weitergeben und umgekehrt.
Das Ergebnis der Formatierung ist maßgeblich davon abhängig, auf welchen Bereich die Formatierungsregel angewendet werden soll: für die markierten Zellen, für ein entsprechendes Feld oder für ein Wertefeld.
- Markieren Sie die zu formatierenden Zellen wie gewohnt bzw. setzen Sie den Zellcursor in eine der Datenzellen.
- Fügen Sie die gewünschte Regel und Formatierung durch.
- Nachdem Sie die Formatierung bestätigt haben erscheint neben den Werten ein SmartTag, in dem Sie nach einem Klick darauf aus drei verschiedenen Varianten wählen können:
- Markierte Zellen
Betrifft nur die zuvor markierten Zeilen - Alle Zellen mit "Summe von x" Werten
Betrifft Einzelsummen und Gesamtsumme - Alle Zellen mit "Summe von x" Werten für "y"
Betrifft nur die Einzelwerte ohne die Summen
Sind Sie im Änderungsmodus einer Regel, dann finden Sie im Dialog ganz oben ebenfalls diese Auswahlmöglichkeiten, wo Sie nachträgliche Änderungen vornehmen können.
In diesem Workshopteil haben Sie erfahren, wie Sie die Bedingte Formatierung im Detail beeinflussen können. Im dritten und (vorläufig) letzten Teil des Workshops zeige ich Ihnen einige Beispiele aus der Praxis. Schauen Sie also nochmal rein.