Bedingte Formatierung mit Formeln in Excel
Standardmäßig hat Excel uns bereits viele Arten der bedingten Formatierung zur Verfügung gestellt, die für unsere Daten verfügbar sind. Wenn jedoch ein Benutzer möchte, dass die Formatierung nach bestimmten Kriterien oder Formeln erfolgt, können wir dies auf der Registerkarte "Startseite" des Abschnitts "Bedingte Formatierung" tun, wenn wir dies tun Klicken Sie auf die neue Regel. Wir finden eine Option für die bedingte Formatierung mit Formeln, mit der wir Formeln schreiben können, die die zu formatierenden Zellen definieren.
Die bedingte Formatierung mit Formula ist nichts anderes als das Ändern des Formats der Zellen basierend auf den vom Benutzer angegebenen Bedingungen oder Kriterien. Möglicherweise haben Sie die bedingte Formatierung verwendet, um den obersten Wert im Bereich hervorzuheben, und auch doppelte Werte.
Eine gute Sache ist, dass wir Formeln in bedingter Formatierung in Excel haben. Alle Formeln sollten logisch sein; Das Ergebnis ist entweder TRUE oder FALSE. Wenn der logische Test bestanden wird, erhalten wir eine bedingte Formatierung, und wenn der logische Excel-Test fehlschlägt, erhalten wir nichts.
Überblick
Lassen Sie mich Ihnen die bedingte Formatierung mit dem Formelfenster vorstellen.
Im folgenden Abschnitt des Artikels finden Sie eine Vielzahl von Beispielen für die Anwendung der bedingten Formatierung mit Formeln.
Wie verwende ich die bedingte Excel-Formatierung mit Formeln?
# 1 - Markieren Sie Zellen mit Werten unter 500
Angenommen, unten sind die Verkaufszahlen aufgeführt, mit denen Sie arbeiten, und unten ist der Verkaufspreis pro Einheit angegeben.
- Schritt 1: Gehen Sie zur bedingten Formatierung und klicken Sie auf Neue Regel.
- Schritt 2: Wählen Sie nun " Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen."
- Schritt 3: Wenden Sie nun unter „ Werte formatieren, bei denen eine Formel wahr ist“ die Formel als A2 <500 an und klicken Sie dann auf Formatieren , um die Excel-Formatierung anzuwenden.
- Schritt 4: Wählen Sie das Format nach Ihren Wünschen.
- Schritt 5: Jetzt können Sie die Vorschau des Formats im Vorschaufenster sehen.
- Schritt 6: Klicken Sie nun auf OK, um die Formatierung abzuschließen. Jetzt wurden alle Zellen mit einer Nummer <500 hervorgehoben.
# 2 - Markieren Sie eine Zelle basierend auf einer anderen Zelle
Wir können die eine Zelle basierend auf einem anderen Zellenwert hervorheben. Angenommen, wir haben Produktnamen- und Produktpreisdaten in den ersten beiden Spalten.
Aus der obigen Tabelle müssen wir die Produkte hervorheben, wenn der Verkaufspreis> 220 ist.
- Schritt 1: Wählen Sie die Produktpalette aus und gehen Sie dann zur bedingten Formatierung und klicken Sie auf Neue Regel.
- Schritt 2: Wenden Sie in der Formel die Formel als B2> 220 an.
- Schritt 3: Klicken Sie auf die Taste Format und wenden Sie das Format nach Ihren Wünschen an.
- Schritt 5: Klicken Sie auf OK. Wir haben die Formatierung fertig.
# 3 - Markieren Sie alle leeren Zellen im Bereich
Angenommen, unten sind die Daten, die Sie haben.
In den obigen Daten müssen Sie alle Zellen markieren, die leer oder leer sind. Wir müssen die ISBLANK-Formel in der bedingten Excel-Formatierung verwenden.
Wenden Sie die folgende Formel im Abschnitt Formeln an.
Wählen Sie die Formatierung nach Ihren Wünschen.
Klicken Sie auf OK, um alle leeren Zellen im ausgewählten Bereich hervorzuheben.
# 4 - Verwenden Sie die AND-Funktion, um Zellen hervorzuheben
Betrachten Sie die folgenden Daten für diese Formel.
Wenn die Region Zentral ist und der Verkaufswert> 500 ist, müssen wir die gesamte Zeile markieren.
- Schritt 1: Wählen Sie zuerst die gesamten Daten aus.
- Schritt 2: Hier müssen wir die UND-Excel-Funktion verwenden, da wir hier zwei Bedingungen testen müssen und beide Bedingungen WAHR sein sollten. Wenden Sie die folgende Formel an.
- Schritt 3: Klicken Sie auf Format.
- Schritt 4: Gehen Sie zu FILL und wählen Sie die gewünschte Farbe und die gewünschten Effekte aus.
- Schritt 5: Klicken Sie auf OK. Die Zeilen werden hervorgehoben, wenn beide Bedingungen WAHR sind.
# 5- Verwenden Sie die ODER-Funktion, um Zellen hervorzuheben
Betrachten Sie die folgenden Daten für das Beispiel.
Im obigen Datenbereich möchte ich die Städtenamen Bangalore und Mysore hervorheben.
- Schritt 1: Wählen Sie zuerst die Daten aus, gehen Sie zur bedingten Formatierung und klicken Sie dann auf Neue Regel.
- Schritt 2: Da wir eine der beiden Wertezellen hervorheben müssen, wenden Sie die OR-Excel-Funktion an.
- Schritt 3: Klicken Sie auf das Format und wählen Sie das gewünschte Format aus.
- Schritt 4: Klicken Sie nach dem Anwenden der Formatierung auf OK, um die Aufgabe abzuschließen. Eine Formel würde alle Zellen hervorheben, die Werte Bangalore & Mysore haben.
# 6 - Verwenden Sie die COUNTIF-Funktion, um Zellen hervorzuheben
Angenommen, Sie arbeiten mit der Kundendatenbank. Sie müssen alle Clients identifizieren, deren Namen mehr als fünf Mal in der Liste vorkommen. Unten sind die Daten.
Wählen Sie die Daten aus und wenden Sie die COUNTIF-Formel im Formelabschnitt an.
Klicken Sie auf das Format und übernehmen Sie die gewünschte Formatierung.
Jetzt hebt die Formel alle Namen hervor, wenn die Anzahl mehr als 5 beträgt.
Bernstein ist der einzige Name, der mehr als fünf Mal in der Liste vorkommt.
# 7 - Markieren Sie jede alternative Zeile
Wir können auch die Formel haben, um jede alternative Zeile in Excel der Daten hervorzuheben. Angenommen, unten sind die Daten aufgeführt, an denen Sie arbeiten.
Wählen Sie die Daten aus und wenden Sie die folgende Formel an.
= MOD (ROW (), 2)
Übernehmen Sie das Format nach Ihren Wünschen und klicken Sie auf OK. Wir würden jede alternative Zeile durch die Formel hervorheben.
# 8 - Markieren Sie jede alternative Spalte
So wie wir jede alternative Zeile ähnlich hervorgehoben haben, können wir jede alternative Spalte hervorheben. Betrachten Sie die gleichen Daten für das Beispiel.
Wenden Sie die folgende Formel an.
= MOD (COLUMN (), 2)
Kopieren Sie die obige Formel und wenden Sie sie im Formelabschnitt der bedingten Formatierung an.
Klicken Sie auf OK. Es wird die alternative Zeile hervorgehoben.
Wenn Sie die erste Spalte weglassen und in der zweiten Spalte hervorheben möchten, müssen Sie die folgende Formel verwenden.
Es würde die alternative Spalte ab der zweiten Spalte hervorheben.
Dinge, an die man sich erinnern sollte
- Die bedingte Formatierung akzeptiert nur logische Formeln, deren Ergebnisse nur entweder WAHR oder FALSCH sind.
- Eine Vorschau der bedingten Formatierung ist nur ein Hinweis darauf, wie die Formatierung aussieht.
- Verwenden Sie niemals einen absoluten Bezug wie in der Formel. Wenn Sie die Formel anwenden und die Zelle direkt auswählen, wird sie als absolute Referenz verwendet. Sie müssen sie als relative Referenz in Excel verwenden.
