Ganze Zeile einfärben (bedingte Formatierung)

Bedingte Formatierungen bieten die Möglichkeit, Zellen basierend auf einem Wert zu formatieren (beispielsweise farblich hervorheben). Im Normalfall wird dies auf eine einzelne Zelle angewandt, um darzustellen, dass ein bestimmter Parameter unter- oder überschritten wurde. Ein konkretes Beispiel zeigt der Beitrag "Einen eingefärbten Kalender erstellen". Symbolsätze bieten die Möglichkeit, Trends und Entwicklungen (Pfeile) oder Ampeln und Indikatoren für den - oder gar anstelle des - entsprechenden Wert anzuzeigen. Dies kann in Übersichten und Berichten spannend sein, um auf einen Blick relevante Gegebenheiten erkennen zu können. Dies sind die Symbolsätze, die man unter dem Menüpunkt "Start" → "Bedingte Formatierung" findet:

bedingteFormatierungSymbols

In diesem Blog zeige ich Dir einen Weg, wie Du nicht nur eine einzelne Zelle hervorheben kannst, sondern sämtliche dazugehörigen Informationen in derselben Zeile werden ebenfalls formatiert. Somit werden erfasste Einträge besser ersichtlich und Du kannst Deine Daten beispielsweise nach Farbe filtern (siehe unten). Für unser Beispiel haben wir einen übersichtlichen Datensatz mit Kundeninformationen:

bedingteFormatierungRohdaten

Wir beabsichtigen nun, sämtliche Kunden farblich hervorzuheben, die über eine Kundenkarte verfügen (Spalte G). Um ein erstes Resultat zu erzielen, klicke in die Zelle "G2" und gehe via "Start" → "Bedingte Formatierung" auf "Regeln zum Hervorheben von Zellen" und dort auf "Textinhalt...":

bedingteFormatierungTextinhalt

Damit wird der bestehende Inhalt ("Ja") standardmässig rot hervorgehoben. Mit "OK" lässt sich die Auswahl bestätigen. Man kann jedoch die Formatierung beliebig anpassen, indem man in der Auswahl auf der rechten Seite "benutzerdefiniertem Format..." auswählt. Die Schritte da sind gleich, wie wenn man eine normale Zelle formatiert. Wenn Du die Auswahl bestätigt hast, gehe erneut auf "bedingte Formatierung" im Menüband und wähle dieses Mal zu unterst "Regeln verwalten...". Du siehst diese Option auch im Screenshot oben, wo wir "Regeln zum Hervorheben von Zellen" gefolgt von "Textinhalt..." angeklickt haben. Nun siehst Du die eine Regel, die Du erstellt hast. Gehe oberhalb des einzelnen Eintrags auf "Regel bearbeiten" und wähle anstelle von "Nur Zellen formatieren, die enthalten" die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kannst Du weiter unten eine Formel/Kondition eingeben, um bedingte Formatierung auszulösen. Verwende nachfolgende Formel:

=$G2="Ja"

(Das Dollarzeichen ist notwendig, um die Formatierung auf andere Spalten zu übertragen, aber dass die Spalte G und der Wert darin nach wie vor die Bedingung vorgibt.)

bedingteFormatierungFormatierungsregel

Bestätige die Formel und folglich die neue Formatierungsregel mit "OK". In der Übersicht passe nun noch den Bereich der Regel an -  dies ist die Spalte mit der Überschrift "Wird angewendet auf". Wie erweitern da den Bereich auf den gesamten Datenbereich (A2 bis G5). Dies führt dazu, dass alle Werte darin eingefärbt werden, sofern der jeweilige Wert in Spalte G "Ja" entspricht. Weitere Spalten rechts davon (z.B. H und fortfolgende) sind davon nicht betroffen. Damit der Bereich erweitert wird, gib folgende Formel an (die Dollarzeichen sind auch hier zwingend notwendig!):

=$A$2:$G$5

bedingteFormatierungRegelManager

Du solltest die Änderungen bereits aktiv sehen, wenn Du rechts unten auf "Übernehmen" klickst. Du kannst aber auch direkt auf "OK" drücken, um den Regel-Manager zu schliessen. Nun hast Du zwei Einträge komplett eingefärbt, weil der jeweilige Kunde über eine Kundenkarte verfügt:

bedingteFormatierungResultat

Resultate filtern (Farbfilter)

Einleitend habe ich erwähnt, dass das Resultat nun aufgrund der bedingten Formatierung deutlich besser ersichtlich ist. Das Ergebnis ist auch ansprechender, als wenn nur die Zelle in der Spalte G eingefärbt wäre. In jeder beliebigen Spalte kann nun nach der Farbe gefiltert werden, um nur die eingefärbten Zeilen anzuzeigen:

bedingteFormatierungFarbfilter

25 Gedanken zu “Ganze Zeile einfärben (bedingte Formatierung)

  1. Hoi Roman

    Danke für Deine immer sehr einfach erklärten Möglichkeiten. Ich würde gerne die bedingte Formatierung, ausgeweitet auf die gesamte Datensatzzeile, anwenden aber aufgrund einer Teiltext-Suche.

    Beispiel: statt ="June" ="*une". Beim ersten Schritt funktionierts, die Zelle wird markiert, wenn ich jedoch die Bedingung umwandle in Formel zur Ermittlung der zu formatierenden Zellen verwenden, ist wieder fertig.

    Hast Du einen Rat?

    Liebe Grüsse
    Monique

    • Hallo Monique

      Danke für Deinen Kommentar und die Frage.

      Wenn Du *une schreibst, gehe ich davon aus, dass es sich bei "une" um die drei letzten Zeichen von rechts handelt. Du könntest deshalb mit der Formel RECHTS() eine passende Lösung finden - auch innerhalb von bedingten Formatierungen.

      Ich habe die Tabelle aus dem Beitrag oben verwendet und die Spalte "F" in "Geburtstagsmonat" geändert. Wenn da nun "June" oder auch "Iune" (absichtlich falsch geschrieben) steht, wird bei mir die ganze Zeile farblich hervorgehoben. Die Regel der bedingten Formatierung wurde angepasst und sie lautet nun:
      ="une"=RECHTS($F2;3)
      (Diese Formel war zuvor =$G2="Ja" - wie im Beitrag oben dargestellt.)

      Hat das so bei Dir geklappt und das gewünschte Resultat hervorgebracht?

      Beste Grüsse
      Roman

  2. Hallo Roman,
    ich bin absoluter Anfänger was Excel angeht und habe anhand Deiner Formel versucht einen Bezug herzustellen. Ich erstelle gerade eine Ladeliste und möchte zur besseren Übersicht das die Textzeile der Artikelbeschreibung die Farbe ändert sobald ich in die Spalte "Stück" einen Wert eintrage. Ich habe es versucht mit: Wenn größer als 0, dann hellroter Text. Funktioniert super. Wenn ich aber jetzt den Bezug zu den restlichen Spalten herstellen möchte klappt das nicht. Ich habe es mit =$C14=">0" versucht und anschließend den Bereich erweitert auf =$A$14:$J$14. Ich vermute der Fehler liegt bei >0. Hast Du einen Tipp für mich?
    Viele Grüße Ken

    • Hallo Ken

      Danke für Deinen Kommentar und die Frage. Du hast es fast geschafft. Versuch es mal mit dieser Formel:
      =$C14>0

      Ich habe das soeben mit der Beispieldatei von oben ausprobiert; Kundennummer grösser als 30002. Dafür habe ich =$A2>30002 gesetzt und die entsprechenden Zeilen wurde eingefärbt.

      Beste Grüsse & viel Erfolg
      Roman

  3. Hallo Ken,
    Deine Ausführung ist ganz toll beschrieben und wenn ich ein" Ja" in meiner Zelle bräuchte würde alles klappen.
    Ich habe aber eine Umsatzliste und möchte einen Wert der bezahlt wurde in eine Zelle übertragen. Wenn dieser Wert in der Zelle steht, soll sich die ganze Zeile grün einfärben.
    Das würde doch Sinn machen in der bedingten Formatierung ">als" . Er färbt mir aber immer nur die eine Zelle ein.
    Kannst Du mir hier bitte nochmal einen Tip geben?
    Vielen Dank und
    ganz liebe Grüße
    Nicola

    • Hallo Nicola

      Danke für Deine Frage. Die Formel der bedingten Formatierung müsste wie folgt lauten:
      =$A2>1000

      Du musst also keine Anführungszeichen verwenden, um die Kondition "grösser als" abzubilden. Einfach so wie oben reinkopieren. Beachte: Meine Zahl steht aktuell in der Spalte A, deshalb habe ich die Spalte A auch mit Dollarzeichen zusätzlich fixiert (absolut gesetzt). Wo steht Dein Wert, aufgrund dessen die gesamte Zeile eingefärbt werden soll?

      Liebe Grüsse
      Roman

  4. Hallo Ken

    Deine Ausführung funktioniert super - bei mir leider nur für eine Zeile.

    Ausgangslage: wenn ich in der Spalte M einen Eintrag vornehme, egal in welcher Zeile, möchte ich eine grüne Einfärbung der ganzen Zeile programmieren. Wie kann ich die Formel eingeben, damit dies für das ganze Arbeitsblatt umgesetzt wird? Danke für deine Hilfe.

    • Hallo Yvonne
      Klicke bitte einmal eine Zelle an (z.B. in der Spalte M), wo deine Formel und die bedingte Formatierung funktioniert. Gehe danach in die bedingten Formatierung (Menüband Start -> Bedingte Formatierung) und dort auf "Regeln verwalten".

      Im Beitrag oben ist ein Screenshot angezeigt (Manager für Regeln zur bedingten Formatierung) und Du siehst da diese angelegte Regel:
      Formel: =$G2="Ja"

      Kannst Du das einmal mit Deiner implementierten Lösung vergleichen? Womöglich hast Du da sowohl die Spalte (Dollarzeichen vor dem Spalten-Buchstaben) wie auch die Zeile (Dollarzeichen vor der Zeilen-Zahl) fixiert? Achte darauf, dass nur die Spalte (in meinem Beispiel $G) fixiert ist.

      Eine Alternative ist, im gleichen Fenster zu prüfen: Rechts von der Formel, die ich gerade oben aufgeführt habe, prüfe bitte die Angaben in "Wird angewendet auf". Hast Du da den ganzen Bereich ausgewählt, sodass Deine Regel auch da gültig ist? (z.B. =$A$2:$G$5 - wie es im Screenshot im Beitrag gezeigt wird)

      Beste Grüsse
      Roman

      • Hallo Roman,

        ich hab das gleiche Problem wie Yvonne aktuell. Bei mir erscheint dank deiner Formelhilfe eine Zeile in grün aber ich bekomme es nicht auf die anderen Zeilen hin.

        Als Formel hab ich: =$B3="Closed" und bei "wird angewendet auf" hab ich: =$A$12:$AG$41

        • Hallo Miri

          Deine Formel sieht grundsätzlich gut aus und dass Du es auf eine Zeile angewandt bekommst, ist schon einmal gut.

          Kannst Du bitte prüfen:
          Hast Du allenfalls mehrere Regeln in den bedingten Formatierungen, sodass eine die andere übersteuert? Wenn Du auf bedingte Formatierungen -> "Regeln verwalten..." gehst, dann kannst Du oben die Regeln für die aktuelle Auswahl oder für das ganze Arbeitsblatt anzeigen lassen.

          Kommen da je nach Selektion andere Regeln? Kannst Du das mehrmals prüfen auch wenn Du nur einzelne Zellen selektierst und unter "Aktuelle Auswahl" nachschaust? Stimmt hier für jede Zeile die Formel?

          Grundsätzlich benötigst Du im gesamten Arbeitsblatt nur eine einzige Regel mit den Formeln oben.

          Beste Grüsse
          Roman

  5. Hallo Roman,

    ich versuche mich auch gerade an deiner bedingten Formatierung, leider bekomme ich das nicht so hin wie ich es gerne haben möchte.

    Mein Problem ist folgendes, ich ´möchte das wenn in einer Spalte die Zahl >=1 eingetragen wird, die folgende Zeile eingefärbt wird.

    Ich habe es mit der folgenden Formel probiert: =$G$5:$G$5000>=1 den Zellenbereich habe ich auch ausgewählt =$A$5:$X$5400. Habe ich dort einen Denkfehler drin?

    Lieben Gruß

    • Hallo Erik

      Beginne bei der Zeile 5, klicke dafür beispielsweise in die Zelle A5. Gehe auf "Bedingte Formatierungen" -> "Neue Regel...". Wähle dort wie oben beschrieben "Formel zur Ermittlung der zu formatierenden Zellen verwenden" und gib als Formel folgendes ein:
      =$G5>=1
      (Setze noch ein Format, wie Du die Zeile einfärben möchtest)

      Gehe dann erneut auf "Bedingte Formatierungen" und dieses Mal auf "Regeln verwalten...". Setze danach den Bereich "Wird angewendet auf" auf:
      =$A$5:$X$5400

      Bei einer Regel bezieht man sich auf eine einzelne Zelle. Durch die Dollar-Zeichen (absolut setzen) stellen wir sicher, dass die Regel immer auf die Spalte G angewandt wird. Durch den Bereich ("wird angewendet auf") wird das Format auf weitere Zeilen angewandt, da aber die Zeilen-Nummer 5 in der Formel nicht fixiert ist (kein Dollarzeichen), wird jeweils innerhalb der gleichen Zeile in Spalte G nach dem Wert >=1 nachgeschaut.

      Ich hoffe, die Erklärung ist verständlich und Du konntest den Fall lösen!

      Liebe Grüsse
      Roman

  6. Hallo Roman,
    vielen Dank für die klare Anleitung.
    An sich klappt es ganz gut, nur dass wenn ich den Inhalt in der Spalte der Bedingung ändere, die Farbe erst nach speichern oder (irgend)eine Eingabe in eine Zelle sich verändert.
    Weisst du zufällig woran das liegen könnte?

    Anhand deines Bsp. weiter oben passiert folgendes:
    - Spalt G = Kundenkarte -> ist die Bedingung =$G2="Ja" erfüllt, färben sich die Zellen grün
    - Wenn ich in der Spalte G Ja durch Nein überschreibe, bleiben die Zellen grün bis ich die Datei gespeichert habe oder in irgendeiner anderen Zelle etwas eingebe/überschreibe.

    Schöne Grüße, Myriam

    • Hallo Myriam

      Ich habe Deinen Fall nachgestellt und eine Vermutung von mir geprüft: Grundsätzlich kann man die Kondition ändern (also Ja durch Nein ersetzen) und die bedingte Formatierung reagiert unmittelbar darauf. Bitte prüfe einmal unter dem Reiter "Formeln" im Menüband, ob Du bei den Berechnungsoptionen nicht mehr "Automatisch" sondern "Manuell" eingestellt hast.

      In meiner Beispieldatei habe ich die Berechnung auf "Manuell" geändert - dann wird die bedingte Formatierung nicht angezeigt, bevor ich nicht auf "Neu berechnen" klicke.

      Beste Grüsse
      Roman

      • Genau das war es. Du bist mein Held des Tages!! 😉
        Vielen lieben Dank für die schnelle Antwort und die Unterstützung.

        Schöne Grüße,
        Myriam

  7. Hallo Roman,
    mein Problem ist ein bisschen komplexer. Ich möchte die Zelle A1 blau färben, wenn der Wert der Zelle C1 wie folgt aussieht C1>B1/2 und C1< B1. Beispiel: Wenn B1=10 ist, dann soll A1 blau sein, wenn C1=7

    Hoffe ich konnte mich verständlich ausdrücken

    Schöne Grüße, Birgit

    • Hallo Birgit

      Bitte entschuldige meine etwas späte Antwort. Du kannst dies wie folgt erreichen:

      Füge einmal in Zelle B1 die Zahl 10 ein sowie in Zelle C1 die 7 - wie Du es beschrieben hast. Klicke danach in die Zelle A1 und gehe auf "Bedingte Formatierung" -> "Neue Regel...". Wähle daraufhin wie im Beitrag oben ebenfalls die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden".

      Danach gibst Du die folgende Formel in das untere Feld ein:
      =UND($C$1>$B$1/2;$C$1< $B$1)

      Setze dann noch Deine Formatierung (z.B. blau einfärben), klicke auf OK und im nächsten Fenster (Manager für Regeln zur bedingten Formatierung) noch rechts unten auf "Übernehmen" - und Deine Zelle A1 ist entsprechend formatiert!

      Falls Du die Formatierung dann gerne auf weitere Zeilen anwenden möchtest, passe die Formel an zu:
      =UND($C1>$B1/2;$C1< $B1)

      Es sind dadurch nur die Spalten mit den Dollarzeichen fixiert (absolut gesetzt). Als letztes musst Du noch in der Übersicht aller Regeln den Bereich "Wird angewendet auf" zu beispielsweise =$A$1:$A$5 ändern. Wenn Du danach weitere Zahlen einfügst (beispielsweise 12 und 7) werden sie in den entsprechenden Zeilen auch eingefärbt.

      Du hast die Konditionen in Deinem Kommentar schön formuliert, ich konnte sie exakt so übernehmen. Der Schlüssel war die Formel "=UND()", die sowohl die erste (C1 > B1/2) wie auch die zweite Kondition (C1 < B1) als erfüllt verlangt. Die Formel "=ODER()" würde Dir bereits die Zelle einfärben, wenn mindestens eine der Konditionen erfüllt ist. Du kannst übrigens auch mehr als nur zwei Konditionen einpflegen - einfach mit Semikolon (";") trennen. Beste Grüsse Roman

  8. Hallo Roman,
    excellente Erklärung.
    Was kann ich eigentlich tun, wenn ich eine solche Liste habe, aber mit drei verschiedenen Möglichkeiten in der Spalte "G": ja, nein, unbekannt.
    Bei "ja": Grünfärbung der Zeile (das alleine hast du ja sehr gut erklärt und funktioniert bestens). Dann soll aber:
    bei "nein": Rotfärbung der entsprechenden Zeilen und
    bei "unbekannt": Gelbfärbung der entsprechenden Zeilen.
    Wie kann ich das machen? Ich kann doch nicht zwei bedingte Formatierungen in einer Spalte anwenden - oder doch?
    Schöne Grüße
    Stefan

    • Hallo Stefan

      Du kannst sehr wohl mehrere bedingte Formatierungen anwenden. Nachdem Du eine Regel hinterlegt hast, siehst Du sie ja via "Bedingte Formatierung" -> "Regeln verwalten".
      Füge dann einmal eine zweite Regel ein - gehe exakt gleich vor, wie bei Deiner ersten Regel (Deine zweite Kondition - beispielsweise "Nein").

      Wenn Du jetzt erneut in "Regeln verwalten" gehst, müsstest Du beide Regeln sehen; achte je nach Zellen-Selektion darauf, dass Du oben in dem Pop-Up Fenster die Auswahl entsprechend auf "Dieses Arbeitsblatt" setzt. Unter Umständen greifen mehrere Regeln auf eine Kondition (ist in Deinem Beispiel nicht der Fall). Du kannst oberhalb der Regeln mit den Pfeilen die Reihenfolge ändern - die oberste Regel übertrifft alle untenstehenden, falls mehrere gleichzeitig erfüllt wären.

      Liebe Grüsse
      Roman

      • Hallo Roman,

        auch ich bin ein absoluter Anfänger, was Excel-Formeln angeht. Ich bin über die Google-suche auf deinen Beitrag gestoßen und hoffe, dass du mir evtl. helfen kannst.
        Ich habe eine Tabelle in der u.a. ein Beladedatum (C2) und ein Entladedatum (O2) einzugeben ist. Um nun überfällige Belege schneller entdecken zu können (ca. 800 Zeilen), möchte ich gerne eine rote Färbung der leeren Zellen ( "C" oder "O"), falls in der jeweils anderen Zelle bereits ein Datum eingetragen wurde, welches älter als 7 Tage ist. Allerdings nur, wenn nicht bereits in beiden Zellen ein Wert steht oder beide Zellen leer sind.
        Ich hoffe, dies war nicht zu kompliziert erklärt.
        Hier mal ein konkretes Beispiel:
        In C2 steht ein (Belade)Datum, dass schon älter als 7 Tage ist, Zelle O2 (Entlade)Datum ist leer. Jetzt sollte O2 rot eingefärbt werden.
        Sobald der Wert in O2 eingetragen wird (also beide Zellen ausgefüllt sind), soll die Einfärbung wieder aufgehoben sein.
        Das Gleiche in umgekehrter Version:
        Wenn das Datum in O2 älter als 7 Tage ist und in C2 noch kein Eintrag erfolgt ist, soll C2 rot eingefärbt werden, bis der Wert nachgetragen wurde.
        Sollten C2 UND O2 leer sein, sollen die beiden Zellen gelb hinterlegt werden.

        Bisher habe ich mit Hilfe der bedingten Formatierung erreicht, dass mit alle Einträge mit einem Datum älter als 7 Tage rot eingefärbt werden und leere Zellen übersprungen werden. Aber die Einfärbung in Abhängigkeit mit der Überprüfung der anderen Zelle will mir leider nicht gelingen.
        =UND($C2"";$C2<HEUTE()-7)

        Freundliche Grüße

        Melanie

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.