Excel bedingte Formatierung - Basis
Workshops > Excel
Stand 26.10.2020
Sie kennen in Excel die Möglichkeit, Zelleigenschaften und Werte nach vielerlei Arten zu formatieren. Nach der Markierung formatieren Sie die Zell- oder Zeichenfarbe oder versehen Zellen mit Rändern. Sie ändern die Schriftart und -größe, geben benutzerdefinierte Formate an oder nutzen Zellformatvorlagen, um nur einige zu nennen. Diese Art der Formatierung ist statisch, d.h., die Formatierung bleibt solange bestehen, bis sie erneut manuell geändert wird. Das mag für Überschriften und ähnliches sinnvoll sein, da sich in diesen Fällen die Inhalte in der Regel nicht oder nur selten ändern.
Diesen Workshop habe ich aufgrund seiner Länge in zwei Teile aufgetrennt: den Basispart (dieser erste Teil) sowie den zweiten, erweiterten Teil, in dem zusätzlich Formeln in's Spiel kommen. In einem dritten Teil zeige ich abschließend einige Praxisbeispiele zum Nachbauen.
Der Überblick
Eine andere Art der Zellgestaltung stellt die Bedingte Formatierung dar. Mit dieser lassen sich Zelleigenschaften in Abhängigkeit vom positiven Ergebnis einer definierten Bedingung bzw. eines Vergleichs beeinflussen. Bei dieser Bedingung kann es sich um einfache Größenvergleiche oder aber um eine komplexe Formel handeln. Dazwischen ist alles möglich. Die Bedingung bleibt auch nach einer Änderung des Zellinhalts erhalten und wirkt sofort nach Abschluss der Eingabe. Dadurch eröffnen sich viele spannende Möglichkeiten, mehr Dynamik in Ihre Excel-Tabellen zu bringen.
Für diese Art der Formatierung bieten sich viele Einsatzzwecke an. So lassen sich beispielsweise alle Umsatzzahlen über 10.000 € in einer Liste automatisch farblich hervorheben. Umsatzspitzen sind somit leichter zu erkennen. Die Hervorhebung von Wochenenden in einem Kalender ist mit Farben leicht möglich. Probleme lassen sich schneller erkennen, Trends und Muster besser identifizieren und vieles mehr. Im Verlauf des workshops zeige ich Ihnen einige Anwendungsfälle.
Das folgende Bild zeigt einige kleine Beispiele für …
- Hervorhebung der vorherigen und der künftigen drei Tage (per 26.10.2020)
- Anzeige von Datenbalken
- Zeigen der besten und der schlechtesten 20% einer Umsatzliste (nur Symbol)
- Umsätze über dem Durchschnitt (orange) sowie die obersten drei (grün)
- farblich hervorgehobene Dubletten in einer Liste
Die Zuweisung
Ihnen liegt eine Tabelle mit Umsatzzahlen vor. Die Zellfarbe der markierten Werte in der Betragsspalte soll angepasst werden, wenn der Betrag größer 1.000 ist.
- Markieren Sie den zu formatierenden Datenbereich.
- Öffnen Sie im Register Start, Abschnitt Formatvorlagen die Dropdownliste bei Bedingte Formatierung.
- Wählen Sie den Eintrag Regeln zum Hervorheben von Zellen und anschließend Größer als...
- Geben Sie den Wert 1000 in das Eingabefeld ein und formatieren mit einer Farbe Ihrer Wahl. Über Benutzerdefiniertes Format öffnet sich der aus Excel bekannte Formatdialog, in dem Sie die Füllfarbe festlegen können (auch Farbverläufe und Musterfüllungen).
- Bestätigen Sie mit OK.
Die Zellen mit Werten über 1.000 werden entsprechend Ihrer Auswahl gefärbt. Der Unterschied zu einer statischen Färbung über den Befehl Füllfarbe im Register Start besteht darin, dass die Formatierung per Bedingte Formatierung dynamisch auf die Zellinhalte reagiert. Ändert sich der Zellinhalt wird jeweils geprüft, ob die festgelegte Bedingung noch zutrifft und im Ja-Fall die Formatierung vorgenommen. Trifft die Bedingung nicht (mehr) zu, erfolgt keine Änderung bzw. die zuvor vorgenommene Formatierung wird wiederhergestellt.
Obwohl die Formatierung korrekt anhand der Bedingung erfolgt, empfiehlt es sich, den Grenzwert (in diesem Beispiel den Wert 1.000) nicht statisch im Dialog einzugeben, sondern auf eine Zelle zu verweisen, in der sich der Wert befindet. Diese Zelle kann sich auch in einem anderen Tabellenblatt derselben Mappe befinden. Bezüge auf andere Mappen sind nicht direkt möglich. Zum Ändern des Grenzwerts (zum Beispiel auf Beträge ab 2.000 Euro) genügt es dann, den Wert in der Zelle anzupassen. Den Dialog zur Bedingten Formatierung müssen Sie nicht erneut aufrufen. Außerdem ist im Tabellenblatt erkennbar, welcher Grenzwert der Bedingung zugrunde liegt.
Um dies zu erreichen klicken Sie im Dialog auf die betreffende Zelle (hier D15), wenn sich der Cursor im Eingabefeld befindet, anstatt den Wert einzutragen. Der Zellbezug wird automatisch als Absolutwert ($D$15) eingetragen, was in diesem Fall korrekt ist.
Aus den eben genannten Gründen empfehle ich, generell auf Werte in Zellen zu verweisen und keine statischen Werte im Eingabefeld einzutragen. Das steigert die Übersichtlichkeit und macht Ihre Tabellen wesentlich pflegeleichter. Das obige Bild zeigt das angepasste Beispiel.
Wollen Sie Anpassungen vornehmen, rufen Sie den Eintrag Regeln verwalten… über das Symbol Bedingte Formatierung auf. Achten Sie darauf, dass sich die markierte Zelle innerhalb des bedingt formatierten Bereichs befindet.
- Die Dropdown-Liste (1) oben gibt an, auf welchen Bereich sich die aktuelle Anzeige bezieht. Im Beispiel "Diese Tabelle", da es sich um einen als Tabelle formatierter Datenbereich handelt.
- Im Eingabefeld (2) ist der dazugehörige Zellberzug ersichtlich. Dieser lässt sich nach einem Klick in dieses Feld anpassen.
- Per Doppelklick auf den Eintrag zur Regel bzw. Klick auf den Eintrag und anschließendem Klick auf Regel bearbeiten (3) wechseln Sie in den Bearbeitungsmodus.
- Mit Hilfe der Schaltfläche Regel löschen (4) kann die Regel gelöscht werden.
Eine weitere Möglichkeit der Zuweisung der Bedingten Formatierung ist in Excel ab Version 2013 möglich. In dieser Version wurde die sogenannte Schnellanalyse eingeführt. Haben Sie einen Zellbereich markiert, untersucht Excel die markierten Zellen und bietet nach einem Klick auf das rechts unterhalb der Markierung angezeigte Symbol diverse Formatierungsmöglichkeiten an - so auch die Bedingte Formatierung (hier nur mit Formatierung beschriftet).
Das folgende Bild zeigt eine derartige Auswahl der angebotenen Möglichkeiten. Der letzte (grau hinterlegte) Eintrag dient dem Löschen der für den markierten Bereich bereits vorhandenen Regeln.
Sollte das Symbol zur Schnellanalyse nach erfolgter Markierung nicht angezeigt werden, aktivieren Sie diese in den Optionen:
- Klicken Sie auf das Register Datei zum Wechsel in den Backstage-Bereich.
- Wählen Sie den Befehl Optionen.
- Im Abschnitt Allgemein blättern Sie zu den Benutzeroberflächenoptionen.
- Aktivieren Sie Optionen für Schnellanalyse anzeigen.
- Bestätigen Sie mit OK.
Inhalte prüfen und vergleichen
Wenn Sie die Liste der Regeln zum Hervorheben von Zellen nach einem Klick auf den Button Bedingte Formatierung genauer betrachten, stellen Sie fest, dass die Auswahl beschränkt ist. So fehlen beispielsweise die Einträge Größer oder gleich bzw. Kleiner oder gleich. Bei jeder der Haupt-Listeneinträge befindet sich am Ende der aufklappenden Liste der Eintrag Weitere Regeln… Ein Klick darauf zeigt einen Dialog, in dem Sie alle Möglichkeiten in einem zusätzlichen Listenfeld vorfinden:
- Regeln zum Hervorheben von Zellen
- Zellwert
- größer | kleiner als
- größer | kleiner oder gleich
- zwischen | nicht zwischen
- gleich | ungleich
- Bestimmter Text (Textinhalt)
- mit | ohne Inhalt
- beginnend | endend mit
- Datum
- Gestern | Heute | Morgen
- In den letzten 7 Tagen
- Letzte | Diese | Nächste Woche
- Letzter | Dieser | Nächster Monat
- Doppelte | Eindeutige Werte
- Leerzeichen | Keine Leerzeichen
- Fehler | Keine Fehler
- Obere/ Untere Regeln
- Obere | Untere x Elemente | %
- Über | Unter (gleich) | 1 | 2 | 3 Std Abw unter | über dem Durchschnitt
Beachten Sie, dass die Wochenselektionen beim Typ Datum dem amerikanischen System entsprechen. D.h., die Woche beginnt am Sonntag und endet am Samstag (in Europa üblicherweise Mo-So). Zur Anzeige der europäischen Version verwenden Sie eine Formel, wie später beschrieben.
Die Leerzeichen-Regel berücksichtigt Zellen mit Leerzeichen, leere Zellen sowie Formelzellen, die als Ergebnis eine leere Zeichenkette zurückgeben ("").
Eine dritte Möglichkeit, die Bedingte Formatierung für markierte Zellen zu starten ist der direkte Aufruf des Dialogs über die Auswahl Neue Regel…
Angezeigt werden die Optionen des Eintrags Alle Zellen basierend auf ihren Werten formatieren. Dabei handelt es sich um die 2- bzw. 3-Farb-Skalen, die Datenbalken sowie die Symbolsätze.
Die nächsten vier Einträge entsprechen den zuvor beschriebenen Regeln zum Hervorheben von Zellen bzw. den oberen und unteren Regeln.
Bei Änderung der Auswahl im oberen Bereich des Dialogs ändert sich der untere Teil ebenfalls.
Wird der letzte Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden ausgewählt zeigt sich lediglich ein Eingabefeld, in das die Formel einzutragen ist. Dieses Thema wird weiter hinten detailliert behandelt.
Beachten Sie, dass bei den Regeln Obere | Untere X Elemente | % keine Zellbezüge möglich sind. Die Anzahl bzw. Prozentangabe muss hier im Eingabefeld selbst eingegeben werden. Sollen die Werte aus Zellen übernommen werden, muss dies über eine Formel erfolgen. Ein Beispiel dazu finden Sie weiter hinten.
Vergleichstexte dürfen bis zu 255 Zeichen lang sein und Platzhalterzeichen (* oder ?) enthalten.
Vergleichstexte dürfen bis zu 255 Zeichen lang sein und Platzhalterzeichen (* oder ?) enthalten.
Farbskalen
Mittels Farbskalen geben Sie Anhaltspunkte zur leichteren Erkennung der Verteilung von Daten. Excel bietet Skalen mit zwei bzw. drei Farben an. Bei der 2-Farben-Skala dienen zwei Farben mit Schattierungen dem einfachen Vergleichen von Werten und deren Abstufungen (Minimum und Maximum). Bei der 3-farben-Skala kommt die Definition des Mittelpunkts hinzu.
Enthält eine Zelle des zu formatierenden Zellbereichs einen Fehlerwert, wird die bedingte Formatierung für diese Zelle nicht durchgeführt. Sie können dies verhindern, indem Sie den Fehler mit einer geeigneten Formel/Funktion abfangen und die Zelle mit einem sinnvollen Wert füllen.
Beachten Sie folgendes:
- Die Definition des Minimums, Maximums bzw. Mittelpunkts (bei der 3-Farben-Skala) erfolgt per Eingabe als Niedrigster | Höchster Wert, Zahl, Prozent (0-100), Formel bzw. Quantil (0-100).
- Bei Auswahl von Niedrigster | Höchster ist das Wertefeld gesperrt. Bei allen anderen Auswahlen müssen Sie einen Wert eingeben, per Formel berechnen bzw. auf eine Zelle mit der Zahl verweisen.
- Beim eingegebenen/berechneten Wert muss es sich um einen Zahlen-, einen Datums- oder einen Zeitwert handeln.
- Geben Sie Prozentsätze zwischen 0 und 100 (ohne das Prozentzeichen) ein, wenn Sie alle Werte in proportionalem Verhältnis verdeutlichen möchten.
- Die Eingabe eines Quantil zwischen 0 und 100 für Minimum und Maximum bei einer 2-Farben-Skala bietet sich an, wenn Sie eine Gruppe hoher Werte (z.B. das obere 20. Quantil) mit einer anderen Farbschattierung als niedrige Werte (z.B. das untere 20. Quantil) kennzeichnen möchten. Bei der 3-Farben-Skala ist der Wert 50 für den Mittelpunkt in der Regel am sinnvollsten.
- Der als Minimum definierte Wert muss kleiner als der für den Mittelpunkt (nur bei der 3-Farben-Skala) und kleiner als der für Maximum definierte Wert sein.
- Die für Minimum, Mittelpunkt und Maximum gewählten Typen können verschieden sein.
- Fülleffekte bzw. Musterfüllungen sind bei den Farbzuweisungen der Skalen nicht möglich.
Datenbalken
Eine weitere Möglichkeit der Bedingten Formatierung stellen Datenbalken dar. Diese helfen bei der Betrachtung der einzelnen Zellen im Verhältnis zueinander. Die Länge eines Balkens entspricht dabei dem Wert in der Zelle, wobei der höchste Wert den längsten Balken erzeugt, die anderen Werte entsprechend abgestuft.
In einer Umsatztabelle liegen Werte vor - positiv und negativ. Das Verhältnis dieser Werte wollen Sie mithilfe von Datenbalken darstellen.
Nach der Markierung der Daten, Zuweisung der bedingten Formatierung Datenbalken und Aufruf des Verwaltungsdialogs sieht der Änderungsdialog wie folgt aus:
- Die Liste zeigt alle Regeltypen an. Der Wechsel von einem zu einem anderen Typ ist hier problemlos möglich. Je nach gewähltem Typ variieren die angezeigten Elemente im unteren Teil des Dialogs.
- Das Listenfeld Formatstil enthält die vier Stile 2-Farben-Skala, 3-Farben-Skala, Datenbalken und Symbolsätze.
- Bei aktivierter Option Nur Balken anzeigen sind in den Zellen nur noch die Balken zu sehen. Die Werte werden ausgeblendet. Dies erhöht die Lesbarkeit der Balken. Bei Bedarf kopieren Sie die Werte per Formel in die Nachbarzelle, sodass Sie die Balken und die Werte gleichzeitig darstellen können.
- Die beiden Listenfelder Minimum und Maximum legen fest, wie die Balken generiert werden. Neben Automatisch gibt es auch noch Niedrigster | Höchster Wert, Zahl, Prozent, Formel und Quantil. Außer bei Automatisch und Niedrigster | Höchster Wert wird bei allen anderen das Eingabefeld darunter aktiviert. Hierin geben Sie den gewünschten Referenzwert vor,
- In den Farbwählern zu den Listen für Ausfüllen (Farbverlauf, Einfarbige Füllung) und Rahmen (Kein Rahmen, Durchgezogener Rahmen) legen Sie die gewünschten Farben fest.
- Das Listenfeld Balkenrichtung (Kontext, „Von links nach rechts“ und „Von rechts nach links“) ist nur bei Sprachen relevant, die eine Schreibrichtung von rechts nach links nutzen.
- Ein Klick auf den Button Negativer Wert und Achse… öffnet einen weiteren Dialog.
- In diesem legen Sie fest, welche Füllfarbe die Balken negativer Zahlen erhalten sollen ...
- ... und welche Farbe der Balkenrahmen.
- In den Achseneinstellungen definieren Sie, ob und wo ein Trenner für negative Zahlen erscheinen soll: variabel, je nach Zellwert, im Zellmittelpunkt bzw. ohne Achse.
Symbolsätze
Die letzte Gruppe - die Symbolsätze - helfen Ihnen, Ihre Daten in drei bis fünf durch Schwellenwerte getrennte Kategorien zu klassifizieren. Jedes Symbol steht dabei für einen Wertebereich, so zum Beispiel die Pfeile oder die Ampelsymbole. Für jedes Symbol legen Sie fest, ob es angezeigt werden soll, oder nicht. Innerhalb einer Bedingung ist es möglich, verschiedene Symbole zu verwenden. Im Bild sehen Sie die Definition eines Symbolsatzes mit einem grünen Pfeil nach oben für die oberen zehn und einem roten X für die unteren 20 Prozent.
Nachdem Sie einen Symbolsatz gewählt oder aber über Neue Regel einen erstellt haben, können Sie diese Regel im Regelmanager Ihren Wünschen entsprechend anpassen.
- Das Optionsfeld Symbolreihenfolge umkehren dreht nach Aktivierung die Symbolfarben um.
- Über das Listenfeld Symbolart lässt sich die Symbolgruppe nachträglich ändern.
- Das Optionsfeld Nur Symbol anzeigen steuert, ob die Werte in den Zellen zusätzlich zum Symbol angezeigt werden sollen oder nur das Symbol.
- Über die Dropdown-Listen (im Bild bei einer 3-Symbol-Bedingung) steuern Sie, welche Symbole angezeigt und welche ausgeblendet werden sollen.
- Daneben finden Sie das Listenfeld der Vergleichsoperatoren (>= und >).
- Das Eingabefeld dient der Eingabe der Vergleichswerte.
- Im Listenfeld Typ geben Sie an, ob es sich bei den eingegeben Werten um eine Zahl, einen Prozentwert (ohne Prozentzeichen), eine Formel oder ein Quantil handelt.
Haben Sie die Option Nur Symbol anzeigen aktiviert, lassen sich die Symbole mit den Zellformaten zur horizontalen bzw. vertikalen Ausrichtung formatieren. Werden die Werte ebenfalls angezeigt, wirkt die horizontale Zell-Ausrichtung auf die Werte und nicht auf die Symbole.
Beispiele verschiedener Formatierungen zeigt das folgende Bild.
Weitere Möglichkeiten eröffnen sich Ihnen durch den Einsatz von Formeln. Diese erfahren Sie in einem zweiten Teil zur Bedingten Formatierung. Dabei können Sie Ihrer Kreativität freien Lauf lassen. Es wird spannend. Versprochen.