Bedingte Formatierung in Excel: Der ultimative Leitfaden mit Beispielen

Die bedingte Formatierung ist eine der einfachsten und dennoch leistungsstärksten Funktionen in Excel-Tabellen.

Wie der Name schon sagt, können Sie in Excel die bedingte Formatierung verwenden, wenn Sie Zellen hervorheben möchten, die eine bestimmte Bedingung erfüllen.

Es gibt Ihnen die Möglichkeit, Ihrem Datensatz schnell einen visuellen Analyse-Layer hinzuzufügen. Mithilfe der bedingten Formatierung in Excel können Sie Heatmaps erstellen, zunehmende/abnehmende Symbole, Harvey-Blasen und vieles mehr anzeigen.

Verwenden der bedingten Formatierung in Excel (Beispiele)

In diesem Tutorial zeige ich Ihnen sieben erstaunliche Beispiele für die Verwendung der bedingten Formatierung in Excel:

• Identifizieren Sie schnell Duplikate mithilfe der bedingten Formatierung in Excel.
• Markieren Sie Zellen mit einem Wert größer/kleiner als eine Zahl in einem Dataset.
• Hervorheben der Top/Bottom 10 (oder 10 %) Werte in einem Dataset.
• Hervorheben von Fehlern/Leerzeichen mithilfe der bedingten Formatierung in Excel.
• Erstellen von Heatmaps mit bedingter Formatierung in Excel.
• Markieren Sie jede N-te Zeile/Spalte mithilfe der bedingten Formatierung.
• Suchen und hervorheben mit bedingter Formatierung in Excel.

1. Duplikate schnell identifizieren

Bedingte Formatierung in Excel kann verwendet werden, um Duplikate in einem Datensatz zu identifizieren.

So können Sie dies tun:

• Wählen Sie das Dataset aus, in dem Sie Duplikate hervorheben möchten.
• Gehen Sie zu Home -> Bedingte Formatierung -> Hervorheben von Zellenregeln -> Doppelte Werte.
• Stellen Sie im Dialogfeld Werte duplizieren sicher, dass im linken Dropdown-Menü die Option Duplizieren ausgewählt ist. Sie können das anzuwendende Format angeben, indem Sie das rechte Dropdown-Menü verwenden. Es gibt einige vorhandene Formate, die Sie verwenden oder Ihr eigenes Format mit der Option Benutzerdefiniertes Format angeben können.
• OK klicken.

Dies würde sofort alle Zellen hervorheben, die ein Duplikat im ausgewählten Datensatz haben. Ihr Dataset kann sich in einer einzelnen Spalte, in mehreren Spalten oder in einem nicht zusammenhängenden Zellbereich befinden.

Siehe auch: Der ultimative Leitfaden zum Suchen und Entfernen von Duplikaten in Excel.

2. Markieren Sie Zellen mit einem Wert größer/kleiner als eine Zahl

Sie können die bedingte Formatierung in Excel verwenden, um schnell Zellen hervorzuheben, die Werte enthalten, die größer/kleiner als ein bestimmter Wert sind. Zum Beispiel das Hervorheben aller Zellen mit einem Verkaufswert von weniger als 100 Millionen oder das Hervorheben von Zellen mit Markierungen, die kleiner als der Schwellenwert für das Bestehen sind.

Hier sind die Schritte, um dies zu tun:

• Wählen Sie den gesamten Datensatz aus.
• Gehen Sie zu Home -> Bedingte Formatierung -> Hervorheben von Zellenregeln -> Größer als… / Kleiner als…
• Je nachdem, welche Option Sie auswählen (größer oder kleiner als), wird ein Dialogfeld geöffnet. Nehmen wir an, Sie wählen die Option "Größer als". Geben Sie im Dialogfeld die Nummer in das Feld links ein. Die Absicht besteht darin, Zellen hervorzuheben, deren Zahl größer als diese angegebene Zahl ist.

• Geben Sie das Format an, das auf die Zellen angewendet werden soll, die die Bedingung erfüllen, indem Sie das Dropdown-Menü auf der rechten Seite verwenden. Es gibt einige vorhandene Formate, die Sie verwenden oder Ihr eigenes Format mit der Option Benutzerdefiniertes Format angeben können.
• OK klicken.

Dadurch würden sofort alle Zellen mit Werten größer als 5 in einem Dataset hervorgehoben.Hinweis: Wenn Sie Werte größer gleich 5 hervorheben möchten, sollten Sie erneut die bedingte Formatierung mit dem Kriterium „Gleich“ anwenden.

Der gleiche Vorgang kann zum Hervorheben von Zellen mit einem Wert unter einem angegebenen Wert durchgeführt werden.

3. Hervorhebung von Top/Bottom 10 (oder 10 %)

Die bedingte Formatierung in Excel kann die Top-10-Elemente oder die Top-10-% eines Datensatzes schnell identifizieren. Dies kann in Situationen hilfreich sein, in denen Sie die Top-Kandidaten schnell nach Punktzahlen oder Top-Deal-Werten in den Verkaufsdaten sehen möchten.

Ebenso können Sie die untersten 10 Elemente oder die untersten 10 % in einem Dataset schnell identifizieren.

Hier sind die Schritte, um dies zu tun:

• Wählen Sie den gesamten Datensatz aus.
• Gehen Sie zu Home -> Bedingte Formatierung -> Top/Bottom Rules -> Top 10 Items (oder %) / Bottom 10 Items (oder %).
• Basierend auf Ihrer Auswahl wird das Dialogfeld geöffnet. Angenommen, Sie haben die Top-10-Elemente ausgewählt, dann wird ein Dialogfeld wie unten gezeigt geöffnet:
• Geben Sie das Format an, das auf die Zellen angewendet werden soll, die die Bedingung erfüllen, indem Sie das Dropdown-Menü auf der rechten Seite verwenden. Es gibt einige vorhandene Formate, die Sie verwenden oder Ihr eigenes Format mit der Option Benutzerdefiniertes Format angeben können.
• OK klicken.

Dies würde sofort die Top-10-Elemente im ausgewählten Datensatz hervorheben. Beachten Sie, dass dies nur für Zellen funktioniert, die einen numerischen Wert enthalten.

Wenn das Dataset weniger als 10 Zellen enthält und Sie die Optionen zum Hervorheben der Top 10 Elemente/Untersten 10 Elemente auswählen, werden alle Zellen hervorgehoben.

Hier sind einige Beispiele dafür, wie die bedingte Formatierung funktionieren würde:

4. Hervorheben von Fehlern/Leerstellen

Wenn Sie in Excel mit vielen numerischen Daten und Berechnungen arbeiten, wissen Sie, wie wichtig es ist, Zellen mit Fehlern oder leeren Zellen zu identifizieren und zu behandeln. Werden diese Zellen in weiteren Berechnungen verwendet, kann dies zu fehlerhaften Ergebnissen führen.

Die bedingte Formatierung in Excel kann Ihnen helfen, Zellen mit Fehlern oder leeren Zellen schnell zu identifizieren und hervorzuheben.

Angenommen, wir haben einen Datensatz wie unten gezeigt:

Dieser Datensatz hat eine leere Zelle (A4) und Fehler (A5 und A6).

Hier sind die Schritte, um die Zellen hervorzuheben, die leer sind oder Fehler enthalten:

• Wählen Sie das Dataset aus, in dem Sie leere Zellen und Zellen mit Fehlern hervorheben möchten.
• Gehen Sie zu Home -> Bedingte Formatierung -> Neue Regel.
• Wählen Sie im Dialogfeld Neue Formatierungsregel die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
• Geben Sie die folgende Formel in das Feld im Abschnitt „Regelbeschreibung bearbeiten“ ein:
  =ODER(ISBLANK(A1),ISERROR(A1))
  • Die obige Formel überprüft alle Zellen auf zwei Bedingungen - ob sie leer ist oder nicht und ob ein Fehler vorliegt oder nicht. Wenn eine der Bedingungen WAHR ist, wird WAHR zurückgegeben.
• Legen Sie das Format fest, das Sie auf die Zellen anwenden möchten, die leer sind oder Fehler aufweisen. Klicken Sie dazu auf die Schaltfläche Formatieren. Es öffnet sich das Dialogfeld „Zellen formatieren“, in dem Sie das Format festlegen können.
• OK klicken.

Dies würde sofort alle Zellen hervorheben, die entweder leer sind oder Fehler enthalten.

Notiz: Sie müssen bei der bedingten Formatierung nicht den gesamten Bereich A1:A7 in der Formel verwenden. Die oben genannte Formel verwendet nur A1. Wenn Sie diese Formel auf den gesamten Bereich anwenden, überprüft Excel jeweils eine Zelle und passt die Referenz an. Wenn es beispielsweise A1 überprüft, verwendet es die Formel =OR(ISBLANK(A1),ISERROR(A1)). Wenn es Zelle A2 überprüft, verwendet es die Formel =OR(ISBLANK(A2),ISERROR(A2)). Es passt die Referenz automatisch an (da es sich um relative Referenzen handelt), je nachdem, welche Zelle analysiert wird. Sie müssen also nicht für jede Zelle eine separate Formel schreiben. Excel ist schlau genug, um den Zellbezug von selbst zu ändern 🙂

Siehe auch: Verwenden von IFERROR und ISERROR zur Behandlung von Fehlern in Excel.

5. Erstellen von Heatmaps

Eine Heatmap ist eine visuelle Darstellung von Daten, wobei die Farbe den Wert in einer Zelle darstellt. Sie können beispielsweise eine Heatmap erstellen, bei der eine Zelle mit dem höchsten Wert grün gefärbt ist und sich mit abnehmendem Wert in Richtung Rot verschiebt.

Etwas wie unten gezeigt:

Der obige Datensatz hat Werte zwischen 1 und 100. Zellen werden basierend auf dem darin enthaltenen Wert hervorgehoben. 100 erhält die grüne Farbe, 1 erhält die rote Farbe.

Hier sind die Schritte zum Erstellen von Heatmaps mithilfe der bedingten Formatierung in Excel.

• Wählen Sie den Datensatz aus.
• Gehen Sie zu Home -> Bedingte Formatierung -> Farbskalen und wählen Sie eines der Farbschemas.

Sobald Sie auf das Heatmap-Symbol klicken, wird die Formatierung auf den Datensatz angewendet. Es gibt mehrere Farbverläufe, aus denen Sie wählen können. Wenn Sie mit den vorhandenen Farboptionen nicht zufrieden sind, können Sie weitere Regeln auswählen und die gewünschte Farbe angeben.

Hinweis: Auf ähnliche Weise können Sie auch Datenbarden- und Symbolsätze anwenden.

6. Markieren Sie jede andere Zeile/Spalte

Sie können alternative Zeilen hervorheben, um die Lesbarkeit der Daten zu erhöhen.

Diese werden als Zebrastreifen bezeichnet und können besonders hilfreich sein, wenn Sie die Daten drucken.

Jetzt gibt es zwei Möglichkeiten, diese Zebrastreifen zu erstellen. Am schnellsten konvertieren Sie Ihre Tabellendaten in eine Excel-Tabelle. Es hat automatisch eine Farbe auf abwechselnde Zeilen angewendet. Hier können Sie mehr darüber lesen.

Eine andere Möglichkeit ist die bedingte Formatierung.

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Hier sind die Schritte zum Hervorheben alternativer Zeilen mithilfe der bedingten Formatierung in Excel.

• Wählen Sie den Datensatz aus. Wählen Sie im obigen Beispiel A2:C13 (ohne die Kopfzeile). Wenn Sie auch die Kopfzeile einschließen möchten, wählen Sie den gesamten Datensatz aus.
• Öffnen Sie das Dialogfeld Bedingte Formatierung (Start-> Bedingte Formatierung-> Neue Regel) [Tastaturkürzel - Alt + O + D].
• Wählen Sie im Dialogfeld das Dialogfeld "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
• Geben Sie die folgende Formel in das Feld im Abschnitt „Regelbeschreibung bearbeiten“ ein:
  =ISODD(REIHE())

• Die obige Formel überprüft alle Zellen und wenn die ROW-Nummer einer Zelle ungerade ist, gibt sie TRUE zurück. Das angegebene bedingte Format würde auf alle Zellen angewendet, die WAHR zurückgeben.
• Legen Sie das Format fest, das Sie auf die Zellen anwenden möchten, die leer sind oder Fehler aufweisen. Klicken Sie dazu auf die Schaltfläche Formatieren. Es öffnet sich das Dialogfeld „Zellen formatieren“, in dem Sie das Format festlegen können.
• OK klicken.

Das ist es! Die alternativen Zeilen im Datensatz werden hervorgehoben.

Sie können in vielen Fällen dieselbe Technik verwenden. Sie müssen lediglich die entsprechende Formel in der bedingten Formatierung verwenden. Hier sind einige Beispiele:

• Markieren Sie alternative gerade Zeilen: =ISEVEN(ROW())
• Markieren Sie alternative Zeilen zum Hinzufügen: =ISODD(ROW())
• Markieren Sie jede 3. Zeile: =MOD(ROW(),3)=0

7. Suchen und Hervorheben von Daten mit bedingter Formatierung

Dies ist eine etwas fortgeschrittene Verwendung der bedingten Formatierung. Es würde Sie wie einen Excel-Rockstar aussehen lassen.

Angenommen, Sie haben ein Dataset wie unten gezeigt mit Produktname, Vertriebsmitarbeiter und Geografie. Die Idee ist, eine Zeichenfolge in Zelle C2 einzugeben, und wenn sie mit den Daten in einer oder mehreren Zellen übereinstimmt, sollte diese hervorgehoben werden. Etwas wie unten gezeigt:

Hier sind die Schritte zum Erstellen dieser Such- und Hervorhebungsfunktion:

• Wählen Sie den Datensatz aus.
• Gehe zu Home -> Bedingte Formatierung -> Neue Regel (Tastaturkürzel - Alt + O + D).
• Wählen Sie im Dialogfeld Neue Formatierungsregel die Option "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
• Geben Sie die folgende Formel in das Feld im Abschnitt „Regelbeschreibung bearbeiten“ ein:
  =UND($C$2””,$C$2=B5)
• Legen Sie das Format fest, das Sie auf die Zellen anwenden möchten, die leer sind oder Fehler aufweisen. Klicken Sie dazu auf die Schaltfläche Formatieren. Es öffnet sich das Dialogfeld „Zellen formatieren“, in dem Sie das Format festlegen können.
• OK klicken.

Das ist es! Wenn Sie nun etwas in Zelle C2 eingeben und die Eingabetaste drücken, werden alle übereinstimmenden Zellen hervorgehoben.

Wie funktioniert das?

Die bei der bedingten Formatierung verwendete Formel wertet alle Zellen im Dataset aus. Angenommen, Sie geben Japan in Zelle C2 ein. Jetzt würde Excel die Formel für jede Zelle auswerten.

Die Formel würde TRUE für eine Zelle zurückgeben, wenn zwei Bedingungen erfüllt sind:

• Zelle C2 ist nicht leer.
• Der Inhalt von Zelle C2 stimmt genau mit dem Inhalt der Zelle im Datensatz überein.

Daher werden alle Zellen, die den Text Japan enthalten, hervorgehoben.

Laden Sie die Beispieldatei herunter

Sie können dieselbe Logik verwenden, um Variationen zu erstellen, wie zum Beispiel:

• Markieren Sie die gesamte Zeile anstelle einer Zelle.
• Hervorheben auch bei teilweiser Übereinstimmung.
• Markieren Sie die Zellen/Zeilen während der Eingabe (dynamisch) [Du wirst diesen Trick lieben :)].

So entfernen Sie die bedingte Formatierung in Excel

Nach der Anwendung bleibt die bedingte Formatierung erhalten, es sei denn, Sie entfernen sie manuell. Als bewährte Methode sollten Sie die bedingte Formatierung nur auf die Zellen anwenden, in denen Sie sie benötigen.

Da es flüchtig ist, kann es zu einer langsamen Excel-Arbeitsmappe führen.

So entfernen Sie die bedingte Formatierung:

• Wählen Sie die Zellen aus, aus denen Sie die bedingte Formatierung entfernen möchten.
• Gehen Sie zu Home -> Bedingte Formatierung -> Clear Rules -> Clear Rules from Selected Cells.
  • Wenn Sie die bedingte Formatierung aus dem gesamten Arbeitsblatt entfernen möchten, wählen Sie Regeln aus dem gesamten Blatt löschen.

Wichtige Informationen zur bedingten Formatierung in Excel

• Bedingte Formatierung in flüchtig. Dies kann zu einer langsamen Arbeitsmappe führen. Verwenden Sie es nur bei Bedarf.
• Wenn Sie Zellen kopieren, die eine bedingte Formatierung enthalten, wird die bedingte Formatierung ebenfalls kopiert.
• Wenn Sie mehrere Regeln auf denselben Zellensatz anwenden, bleiben alle Regeln aktiv. Bei Überschneidungen wird die zuletzt angewendete Regel bevorzugt. Sie können die Reihenfolge jedoch ändern, indem Sie die Reihenfolge im Dialogfeld Regeln verwalten ändern.