Mit der bedingten Formatierung können Sie wichtige Daten hervorheben und eine bestimmte Formatierung auf Zellen anwenden, die bestimmte Kriterien erfüllen.
Die bedingte Formatierung von Excel ist eine leistungsstarke Funktion, mit der Sie das Format von Zellen basierend auf einer Bedingung (oder Kriterien) ändern können. Bedingte Formatierung hilft dabei, in einer Excel-Tabelle gespeicherte Daten hervorzuheben, hervorzuheben oder zu unterscheiden.
Alle Regeln zur bedingten Formatierung basieren auf einfacher Logik: Wenn die Bedingungen wahr sind, wird eine bestimmte Formatierung angewendet; Wenn die Bedingungen falsch sind, wird die Formatierung nicht angewendet.
Die bedingte Formatierung hilft Ihnen, wichtige Daten in einem Dataset hervorzuheben, Anomalien hervorzuheben und Daten mithilfe von Datenbalken, Farben und Symbolsätzen zu visualisieren. In diesem Tutorial erfahren Sie, wie Sie bedingte Formatierung anwenden, um Zellen oder Zellbereiche basierend auf einer Bedingung hervorzuheben.
So wenden Sie die bedingte Formatierung von Excel an
Sie sind beispielsweise für die Verwaltung einer großen Inventarliste mit mehreren Artikeln und deren jeweiliger Lagermenge verantwortlich. Und wenn der Lagerbestand eines bestimmten Artikels unter, sagen wir 50, sinkt, ist es wichtig, dass Sie dies wissen, damit Sie ihn auffüllen können.
Wenn diese Inventarliste einen Datensatz mit Hunderten von Zeilen enthält, ist es nicht sehr effektiv, Zeile für Zeile zu durchsuchen, um zu sehen, ob in dieser Spalte Zahlen unter „50“ liegen. Hier kommt die Funktion der bedingten Formatierung zum Einsatz. Mit wenigen Mausklicks können Sie alle Werte in einer Spalte, die kleiner als „50“ ist, markieren.
Bedingte Formatierung mit Hervorhebungsregeln
In unserem Beispiel haben wir ein Arbeitsblatt mit Verkaufsaufzeichnungen einiger Produkte. Wir möchten den Betrag hervorheben, der weniger als 500 im Verkauf beträgt.
Wählen Sie dazu zunächst den Zellbereich aus, auf den Sie eine Regel (Bedingung) anwenden möchten. In unserem Beispiel möchten wir den Betrag, der kleiner als 500 ist, in der Spalte „Betrag“ hervorheben, also wählen Sie Spalte D aus. Sie können Werte in einem Zellbereich oder mehreren Zellbereichen oder sogar einem ganzen Blatt hervorheben.
Gehen Sie dann zur Registerkarte „Startseite“ und klicken Sie auf „Bedingte Formatierung“. Wählen Sie in der Dropdown-Liste "Regeln für Zellen hervorheben" aus und da wir weniger als 500 Werte finden möchten, klicken Sie auf die Option "Weniger als". Excel bietet sieben voreingestellte Hervorhebungsregeln. Sie können jeden von ihnen verwenden, um Daten hervorzuheben.
Als nächstes wird ein Dialogfeld "Weniger als" angezeigt. Geben Sie dazu „500“ in das Feld „Zellen formatieren, die kleiner sind als“ ein und wählen Sie im Dropdown-Menü daneben die Formatierung für die Hervorhebung aus.
Jetzt werden die Zellen, deren Werte weniger als 500 enthalten, in der gewählten Formatierung hervorgehoben.
Sehen wir uns an, wie Sie Werte hervorheben, die eine bestimmte Textzeichenfolge enthalten. Im folgenden Beispiel möchten wir alle Mitarbeiter aus New South Wales (NSW) hervorheben.
Gehen Sie dazu zur Registerkarte Start –> Bedingte Formatierung –> Regeln für Zellen hervorheben –> Text, der enthält.
Sie können andere Optionen im horizontalen Dropdown-Menü "Zellen hervorheben" verwenden, um doppelte Werte, Datumsangaben, Größer als, Gleich oder Zwischen Werte hervorzuheben.
Geben Sie im Dialogfeld Text, der enthält, „NSW“ in das Feld ein, wählen Sie die Formatierung und klicken Sie auf „OK“.
Das Ergebnis:
Bedingte Formatierung mit Top/Bottom-Regeln
Oben/Unten-Regeln sind weitere hilfreiche vordefinierte Regeln für die bedingte Formatierung, die in Excel verfügbar sind. Mit diesen Regeln können Sie auf die oberste (n) Anzahl von Elementen, die oberste (n) Anzahl von Prozent, die unterste (n) Anzahl von Elementen, die unterste (n) Anzahl von Prozent oder darüber liegende Zellenwerte aufmerksam machen durchschnittlich oder unterdurchschnittlich.
Angenommen, Sie haben den Notensatz eines Schülers in einer Tabelle und möchten die Top-10-Performer (Top-10-Ränge) aus dieser Liste herausfinden. Bei der bedingten Formatierung können Sie die Top-10-Markierungen oder Top-15-Markierungen oder eine beliebige (n) Anzahl von Top-Elementen hervorheben. Um die Top-10-Elemente in einem Zellbereich hervorzuheben, wählen Sie zunächst den Bereich (Gesamt) in der Tabelle aus.
Gehen Sie dann zu "Bedingte Formatierung", erweitern Sie "Obere/Untere Regeln" und wählen Sie die Option "Top 10 Elemente...".
Ändern Sie im Dialogfeld „Top 10 Elemente“ die Anzahl der Ränge mithilfe der kleinen Pfeile im linken Feld. Wenn Sie die Top 20 Ränge (Gesamtnote) in Ihrer Notenliste hervorheben möchten, dann setzen Sie die Zahl auf 20. Der Standardwert ist bereits 10, also behalten wir ihn bei. Wählen Sie die Zellenformatierung im rechten Feld und klicken Sie auf "OK".
Die ersten 10 Markierungen aus der Spalte „Gesamt“ werden wie unten gezeigt hervorgehoben.
Nehmen wir an, Sie möchten in der Spalte „Prüfung 1“ im selben Notenblatt unterdurchschnittliche Noten finden. Gehen Sie dazu zur Registerkarte Start –> Bedingte Formatierung –> Regeln oben/unten –> Unter dem Durchschnitt.
Sie können Ihre eigene bedingte Formatierung einrichten, anstatt eine der vordefinierten Bedingungen auszuwählen. Um Ihre eigene Formatierung zu erstellen, wählen Sie die Option „Benutzerdefiniertes Format“ im Fenster „Unter dem Durchschnitt“.
Es öffnet sich ein neues Fenster Zellen formatieren, in dem Sie Ihre Formatierungszellen anpassen können. Wir wählen eine orange Farbe, um die unterdurchschnittlichen Markierungen hervorzuheben. Sobald Sie fertig sind, klicken Sie zweimal auf "OK", um das Ergebnis zu sehen.
Das Ergebnis:
Datenbalken anwenden
Datenbalken sind einfach horizontale Balken in Ihren Zellen. Die Größe des Balkens ist relativ zum Wert der Zelle bzw. zum Wert anderer Zellen im ausgewählten Bereich. Das bedeutet, dass der Wert des kurzen Balkens im Vergleich zu anderen Zellenwerten niedrig ist und ein langer Balken bedeutet, dass der Wert im Vergleich zu anderen Zellenwerten hoch ist.
Wählen Sie zunächst den Zellbereich aus, der mit den Datenbalken visualisiert werden soll.
Gehen Sie als Nächstes zu "Bedingte Formatierung" auf der Registerkarte "Start", erweitern Sie dann "Datenleisten" im Dropdown-Menü und wählen Sie die gewünschte Datenleiste aus.
Der Unterschied zwischen dem Datenbalkenformat und anderen Formaten besteht darin, dass es in allen Zellen angezeigt wird, anstatt eine bestimmte Bedingung zu erfüllen.
Farbskalen anwenden
Farbskalen sind Datenbalken sehr ähnlich, da sie beide den Wert der einzelnen Zellen mit dem Wert anderer Zellen im ausgewählten Bereich korrelieren. Datenbalken visualisieren den Zellenwert jedoch durch die Länge eines Balkens, während Farbskalen dies mit Farbverläufen tun.
Jede Farbskalenoption verwendet zwei- oder dreifarbige Farbverlaufsmuster. Eine Farbe wird den höchsten Werten zugewiesen, eine andere Farbe wird den niedrigsten Werten zugewiesen und alle anderen Werte dazwischen erhalten eine Mischung dieser beiden Farben.
Dazu verwenden wir dasselbe Beispiel, das wir für Datenbalken verwendet haben. Wählen Sie den Zellbereich aus und gehen Sie zu Start –> Bedingte Formatierung –> Farbskalen. Wählen Sie dann einen Farbbereich aus dem horizontalen Dropdown-Menü Farbskalen aus.
Wenn wir den ersten Farbbereich ausgewählt haben, wird rote Farbe dem niedrigsten Wert zugewiesen, grüne Farbe wird dem höchsten Wert zugewiesen und alle Werte dazwischen sind Farben, die eine Mischung aus roten und grünen Farben sind (wie unten gezeigt). Da Gelb durch die Kombination von Rot und Grün mit gleicher Intensität entsteht, werden die Zellen mit Durchschnittswerten damit belegt.
Symbolsätze anwenden
Symbolsätze sind eine weitere Methode, um die Daten in jeder Zelle zu visualisieren und Zellen miteinander zu korrelieren.
Wählen Sie die Zellen aus und klicken Sie auf Start –> Bedingte Formatierung –> Symbolsätze. Sie können eines dieser Symbolsätze auswählen, die für Ihre Anforderungen besser geeignet sind. In unserem Beispiel wählen wir die erste Option unter Directional.
Rote, gelbe und grüne Pfeile, die jeweils auf niedrige, mittlere oder hochpreisige Artikel hinweisen.
Bedingte Formatierung entfernen
Um die bedingte Formatierung zu entfernen, gehen Sie zurück zur Option "Bedingte Formatierung" auf der Registerkarte "Startseite" und klicken Sie auf "Regeln löschen". Wählen Sie dann aus, welche Regeln Sie löschen möchten.
Wir wählen „Regeln aus dem gesamten Blatt löschen“, um alle bedingten Formatierungen aus dem Arbeitsblatt zu entfernen.
Jetzt haben Sie gelernt, wie Sie die bedingte Formatierung verwenden, um Zellen mit der voreingestellten Bedingung von Excel zu formatieren.