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

79 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

      • Hallo ihr lieben,
        kann mir evtl. jemand weiterhelfen?
        Ich habe im Excel eine Tabelle, in der Spalte A ist fortlaufend das Datum (formatiert mit „Datum lang“ dh zB Freitag, 02. Dezember 2022) angeführt. Wenn nun in der Spalte A ein Sonntag vorkommt, müsste die jeweilige Zeile (B4-F404) zB rot befüllt werden. Die Datumsspalte soll allerdings unberührt bleiben. Für diese Spalte besteht bereits eine Regel, dass der Text rot und durchgestrichen ist. Wie kann ich also eine Regel erstellen, dass die jeweilige Zeile ab der Spalte B bis F formatiert wird, wenn in der Zeile ein Sonntag vorkommt? :-/

        Ich hoffe jemand kann mir helfen! Danke und GLG Nina

  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

        • Hallo Melanie

          Ein absoluter Anfänger bist Du wohl nicht, wenn Du Dich bereits mit diversen Bedingungen herumschlägst! 🙂

          Ich konnte Deinen Fall mit drei unterschiedlichen Regeln (bedingte Formatierungen) lösen. Du findest unter diesem Link eine Beispieldatei zum Download:
          https://excelblog.ch/wp-content/uploads/BedingteFormatierung_Belade-und-Entladedatum.xlsx

          Dort kannst Du einfach auf "Bedingte Formatierung" -> "Regeln verwalten..." und im Auswahlfenster nicht "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" auswählen. So siehst Du die hinterlegten Regeln (und auch die Reihenfolge, dass die gelbe Hervorhebung höhergestellt ist - sie kann mit den Pfeilen oberhalb auch umsortiert werden).

          Die Regeln erstelle ich jeweils via Menüpunkt "Start" -> "Bedingte Formatierung" -> "Neue Regel...". Für die Spalte C (Beladedatum) habe ich folgende Regel hinterlegt:
          =UND(UND($C2="";$O2<>"");HEUTE()-7>$O2)
          Die Regel wende ich auf den Bereich =$C$2:$C$1000 an.

          Für das Entladedatum (Spalte O) setze ich diese Regel:
          =UND(UND($C2<>"";$O2="");HEUTE()-7>$C2)
          Die Regel wende ich auf den Bereich =$O$2:$O$1000 an.

          Um leere Fälle hervorzuheben, habe ich diese Regel eingesetzt:
          =UND($C2="";$O2="")
          Sie wird angewandt auf den Bereich =$C$2:$C$1000;$O$2:$O$1000

          Ich hoffe, das klappt so bei Dir mit der Implementierung! Viel Erfolg!

          Beste Grüsse
          Roman

          • Hallo Roman,
            bitte entschuldige das späte Feedback.
            Ich danke dir wirklich sehr für deine Hilfe - es funktioniert alles Bestens und erleichtert mir die Arbeit enorm!
            Wie gut, dass es Experten gibt, die ihr Wissen teilen!

            Nette Grüße
            Melanie

  9. Hallo Roman!

    Vielen herzlichen Dank für diese sehr hilfreiche Anleitung!

    An sich kenne ich mich mit der "Bedingten Formatierung" einigermaßen aus und verwende sie auch oft, weil sie super praktisch ist.

    Leider komme ich aber nicht drauf, warum die Bedingte Formatierung in einer Tabelle, die aus einem ERP-System ins Excel heruntergeladen wird, überhaupt nicht funktioniert.

    Excel-Download aus ERP ist bereits mit Teilergebnis-Zeilen versehen, die keine Formel enthalten, sondern nur Werte und Texte. Auf dieses Download wird eine Tabellen-Formatvorlage übertragen, die im Wesentlichen nur Schrift und Rahmen für die Gesamttabelle und die Überschriftenzeile enthält, sowie einige Spalten gruppiert und ausblendet. Nachher entferne ich noch im Tabellenbereich alle Leerzeilen mit F5 "Gehe zu" und möchte anschließend nur noch die Ergebniszeilen farblich hervorheben.

    Dein Beispiel läßt sich dafür 1:1 in meiner Tabelle umsetzen. In der Spalte A:A beginnt die Ergebniszeile mit dem Text "Ergebnis". Der Bereich der ersten Ergebniszeile ist A2:AG2.

    Sobald ich jedoch im Regelmanager die Formel: =$A2="Ergebnis", Formate und den Bereich für die ganze Ergebniszeile $A1$:$AG1$ definiert habe und auf OK gehe, schließt sich natürlich der Regelmanager, aber es tut sich nichts. Ein nochmaliger Blick auf den Regelmanager offenbart dann, dass gar keine Regel erstellt wurde! Es ist wie verhext und ich bin ziemlich ratlos. Auch andere Tricks wie z.B. mit STRG+H/Erweiterte Optionen/Formate anhand einer Zeilenvorlage suchen/ und auf andere Formate ändern funktionieren in dieser Tabelle nicht, woanders schon.

    Die Suche im Internet nach einer Lösung hat mich zu deinem Blog geführt.

    Ich bin für jeden Tipp dankbar!

    Freundliche Grüße
    Snježana

    • Hallo Snježana

      Ich denke, Du bist sehr nahe an der Lösung dran. Zwei Dinge fallen mir auf:
      Du schreibst "[...] die ganze Ergebniszeile $A1$:$AG1$ definiert [...]" - hast Du das von Deinem Excel so reinkopiert oder bei Deinem Kommentar hier selbst eingegeben? Die Dollarzeichen müssten jeweils zwingend vor die Zeilen-Zahl, d.h. $A$1:$AG$1
      Wenn Du das wie von Dir eingibst, wird der Bezug womöglich nicht angenommen und zurückgesetzt. Ich denke jedoch nicht, dass es daran liegt. Damit die Regel jedoch auch auf weiteren Zeilen greift, solltest Du z.B. $AG$1000 hinterlegen - sonst ist die Regel nur für die erste Zeile gültig (und dort siehst Du wohl keine Farbformatierung aufgrund der Spalten-Überschriften Deiner Tabelle - es scheint dann, wie Du sagst, "wie verhext" zu sein).

      Ein weiterer Punkt, der mit auffällt, ist die Regel in Kombination mit dem angewandten Bereich. Wenn Du =$A2="Ergebnis" setzt, vergewissere Dich bitte einmal, ob die Bezüge / Zeilen nicht verschoben sind, da Du nur die Spalte absolut gesetzt hast (was auch korrekt ist). Wenn Du danach auf "Regel verwalten..." gehst und oben nicht nur die "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" gewählt hast, scheint dann noch alles zu passen? Wenn Zeilen nicht korrekt fixiert sind oder die Regeln zu Beginn nicht auf der entsprechenden Zeile angewandt werden, kann es zu Verschiebungen kommen. Insbesondere, da Du gemäss Deinem Kommentar =$A2="Ergebnis" mit $A$1:$AG$1 verbindest, versuche es einmal mit:

      =$A2="Ergebnis" und $A$2:$AG$1000 (um die Formatierung auf den Zeilen 2 bis 1000 anzuwenden); oder mit:
      =$A1="Ergebnis" und $A$1:$AG$1000
      So gibst Du auf jeden Fall vor, dass in der gleichen Zeile nach "Ergebnis" gesucht wird.

      Beste Grüsse
      Roman

  10. Hallo Roman,

    super Anleitung selbst für einen Excel Klaus wie mich 🙂
    Wie mache ich es jetzt aber, dass ich diese Formel auf mehrere Blätter in einem excel sheet anwende?

    Da fehlt es mir leider wieder.

    VG Maria

    • Hallo Maria

      Vielen Dank für Deinen Kommentar!

      Wenn Du mehrere ähnliche Tabellenblätter hast, die Du einfärben möchtest, aber die Bedingung immer innerhalb des jeweiligen Blatts geprüft werden soll, dann musst Du einfach den obigen Weg auf jedem Blatt anwenden. Das heisst, so wie Du das für Dein erstes Tabellenblatt hingekriegt hast, müsstest Du auf das zweite Tabellenblatt und die Regel entsprechend dort einpflegen.

      Möchtest Du hingegen auf mehreren Tabellenblätter die Zeilen einfärben, wenn auf einer konstanten Tabelle eine Bedingung zutrifft, dann müsstest Du einfach Deine Formel anpassen. Im Beitrag oben ist eine Formel =$G2="Ja" aufgeführt - diese müsstest Du anpassen zu =Tabellenblattname!$G2="Ja". Wie im ersten Szenario beschrieben, müsstest Du die Schritte (neue Regel erstellen) pro Tabellenblatt wiederholen, um die bedingte Formatierung dort auch anzuzeigen.

      Liebe Grüsse
      Roman

  11. Hallo Roman,

    geht das auch mit Datumsangaben? Ich kann Zellen mit hinterlegter Datumsformel formatieren, wenn ich Sie in Relation zu =Heute() stelle.

    Formel Spalte H < Heute
    =DATUM(JAHR(H197);MONAT(H197);TAG(H197)-180)

    Wenn ich das auf die Zeile ausweiten will, scheitere ich, weil es mir das ganze Blatt einfärbt

    Danke Dawn

    • Hi Dawn

      Du bist meiner Meinung nach auf dem richtigen Weg. Versuch es einmal, indem Du für H197 die Spalte fixierst (absolut setzt) - setze dort einmal ein Dollarzeichen vor das "H" ($H197).

      Bitte stelle zudem sicher:
      - Stimmt die Zeilenangabe 197 denn tatsächlich, d.h. befindest Du Dich auch in der Zeile 197? Nicht, dass etwas verschoben ist.
      - Was ist der Anwendungsbereich Deiner bedingten Formatierung? Gehe dazu auf "Bedingte Formatierung" -> "Regeln verwalten..." und wähle oberhalb nicht "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" aus. Welche Angabe hast Du nun bei "Wird angewendet auf"? Entspricht das dem Bereich, den Du auch möchtest?

      Etwas anderes könnte sein, dass auch leere Zeilen markiert werden, da die Kondition "H... < HEUTE()" ja eigentlich zutrifft. Versuch es vielleicht mit der Formel "=UND(H...< HEUTE();H...<>"")" - es wird also auch vorausgesetzt, dass überhaupt ein Wert in der Zelle steht!
      (Allenfalls eben auch hier $H... verwenden, wie oben erwähnt)

      Liebe Grüsse
      Roman

  12. Servus Roman

    ich habe ein Dokument in dem ich mehrere Arbeitsblatter mit einer erstellten Datenbank verlinkt habe . Nun möchte ich von Seite "A" eine Berufliche Funktion auswählen - werde über den Link direkt zur Datenbank gebracht und ein entsprechende Name wird markiert. Ist es möglich, mit der Auswahl durch den Link diese Zelle bzw eine ganze Zeile mit bedingter Formatierung dann farblich hervorzuheben? Wie muss die Formel lauten? Bei Auswahl? gibt es bereits eine vorgegebene?
    Danke für deine tollen Erklärungen dir mir schon geholfen haben - aber zu diesem Thema hab ich leider nichts gefunden.

    • Hallo Günther

      Das hört sich für mich nach einem Makro an, das Dir die entsprechende Lösung bieten kann. Hast Du damit Erfahrungen?

      Ausgelöst wird Dein Makro durch eine Änderung auf Seite "A" (eine berufliche Funktion wird ausgewählt). In einem Beitrag "Live-Suche in der Excel Suchmaschine" habe ich ein solches Makro erläutert. Dies wäre der Beitrag dazu:
      https://excelblog.ch/live-suche-in-der-excel-suchmaschine/

      Man steuert das Auslösen des Makros durch Angabe der Zelle (target.Row = 1 And target.Column = 2 für "B1"). Daraufhin müsstest Du den Wechsel auf die Datenbank per Makro angeben und die Zelle entsprechend selektieren (respektive die bedingte Formatierung auf dieser Zelle via Makro eintragen lassen).

      Beachte: Da das "Auslöse-Makro" in dem Tabellenblatt "A" hinterlegt ist, kannst Du nicht per se einen Wechsel auf ein anderes Tabellenblatt durchführen (Sheets("Datenbank").Select würde nicht funktionieren). Setze deshalb globale Variablen, um z.B. die "berufliche Funktion" weiterreichen zu können. Rufe mittels "Call MeinMakro" ein anderes Makro auf, das in z.B. in Modul1 hinterlegt ist - so kannst Du den Wechsel auf das Sheet "Datenbank" und alle dort notwendigen Schritte (bedingte Formatierung setzen) vornehmen.

      Das ist allerdings sicherlich nicht ganz trivial, wenn Du noch nie mit Makros gearbeitet hast - verstehe ich absolut.

      Liebe Grüsse
      Roman

  13. Danke für die ausführliche Instruktion. Makros sind mir grundsäzlich ein Begriff, habe aber nur wenig Erfahrung. Werde mich aber anhand deiner Anleitung durchtüfteln und gebe dann Bescheid über das Ergebnis.
    Gruss aus Salzburg

    • Hallo Günther

      Danke für Deine Rückmeldung. Es gibt noch eine andere Möglichkeit, die Du gerne ausprobieren könntest.

      Ausgehend von der im Beitrag oben aufgezeigten Formel:
      "=$G2="Ja"

      Der erste Teil davon muss zwingend gleichbleiben und bezieht sich ja auf das Tabellenblatt, wo Du entsprechend die Zeile einfärben möchtest (dort steht in Spalte G würde Deine berufliche Funktion stehen). Nun passen wir jedoch den Teil mit '=Ja"' an. In einem zweiten Tabellenblatt (Auswahlliste) habe ich in Zelle "A2" die Drop-Down-Liste hinterlegt, wo man die berufliche Funktion wählen kann. Die Formel für die bedingte Formatierung müsste demnach wie folgt angepasst werden:
      =$G2=Auswahlliste!$A$2

      Wenn der Wert auf der Auswahlliste (Zelle A2) sich verändert, wird automatisch auf dem anderen Tabellenblatt die bedingte Formatierung auf die neue Kondition (z.B. berufliche Funktion) angepasst.

      Vielleicht hilft Dir dieser (simplere) Weg und führt zum gewünschten Erfolg?

      Liebe Grüsse nach Salzburg
      Roman

  14. Hallo Roman, danke für die super Anleitung.
    Ich habe jedoch eine Frage. Für den Fall, den Du oben skizzierst, würde sich doch nur die gesamte Tabelle grün färbe, weil in G2 ein "Ja" steht. Man möchte doch aber, dass für jede Zelle in Spalte G die Prüfung vorgenommen wird, ob ein "ja" hinterlegt ist und die entsprechende Zeile soll sich einfärben. Wie mache ich das? Ich habe eine Spalte mit Datumsangaben. Sobald ein Datum eingeben ist, soll sich die gesamte Zeile grün färben. Bei dir bspw. =ISTLEER($G$2)=FALSCH. Das soll aber für die gesamte Tabelle passieren und ich möchte es nicht für jede Spalte einzeln pflegen. Wie geht das? Danke und viele Grüße

    • Hi Marly

      Bitte beachte: Es wird sich nicht die gesamte Tabelle einfärben, nur weil in G2 ein "Ja" steht, denn wir haben die Prüfung explizit mit den Dollarzeichen auf $G2 angesetzt (nicht $G$2). Es ist zwar die Spalte fixiert worden (also werden auch links und rechts davon Werte eingefärbt, nicht jedoch die Zeile. Das bedeutet, dass jeweils der Wert in Spalte G innerhalb der jeweiligen Zeile geprüft wird. Durch die Angabe des Bereichs in "Wird angewendet auf" steuern wir, wie viele Zellen wir tatsächlich einfärben möchten, respektive wo die Prüfung stattfindet.

      Du bist mit Deiner Formel (=ISTLEER($G$2)=FALSCH) gut unterwegs und hast den richtigen Ansatz. Setze dies einmal auf $G2 - eben ohne die Zeile Nr. 2 absolut zu setzen (kein Dollarzeichen davor). Du könntest alternativ auch die Formel "=ISTZAHL($G2)" verwenden, so wird ebenfalls ein Datumswert eingefärbt (denn hinter jedem Datum steht auch eine Zahl).

      Beste Grüsse
      Roman

  15. Hallo Roman,
    ich habe einen Urlaubsplan in einer Excelmappe erstellt.
    Alle Mitarbeiter in den Zeilen und die Tage über die Spalten. Über alles liegt ein Listenformat, damit man Abteilungen explizit anschauen kann.
    Jetzt zu meiner Frage:
    Es sollen mehrere Personen gleichzeitig eintragen können.
    Andere sollen nur Lesezugriff bekommen um filtern zu können.
    Über Onedrive wäre das ja grundsätzlich möglich, allerdings wird hier die schöne Filtermöglichkeit (anklicken der gewünschten Filter) nicht übernommen (nur über Text möglich). Siehst du eine andere, bessere Lösungsmöglichkeit?

    Herzlichen Dank für deine Hilfe
    lg, Birgit

  16. Hallo, Roman, ich habe folgendes Problem, das ich mir über Excel einfacher machen möchte:

    In einer Tabelle, in der analytische Messwerte eingetragen werden, kommt es vor, dass für zwei Proben mit der gleichen Bezeichnung Werte eingetragen werden. Die Bezeichnung besteht aus Buchstaben und Zahlen.

    Mein Wunsch wäre, dass Excel bei gleicher Probenbezeichnung den Messwert einer bestimmten Zelle vergleicht, und die jeweilige Zeile, die den niedrigeren Wert aufweist, in grüner Schriftart formatiert.

    Also, vereinfacht ausgedrückt:

    in Spalte A steht Text 1, Text 2, Text 2, Text 3 usw.
    In den beiden Zeilen, wo Text 2 drinsteht (gleiche Bezeichnung), soll die jeweilige Zelle in Spalte B, in der Zahlenwerte stehen, miteinander verglichen werden, welcher Wert der kleinere ist. Und diese Zeile soll grün geschrieben werden.

    Ich hoffe, Du kannst mir da weiter helfen.

    • Hallo Manuela

      Hier habe ich mit Hilfsspalten gearbeitet, um eine Lösung auf Deinen Fall zu erarbeiten. Du findest unter diesem Link eine Beispieltabelle:
      Duplikate - niedrige Werte einfärben.xlsx

      Als erstes habe ich neben den bisherigen Daten eine Spalte "Zähler" eingefügt. Diese ermittelt, ob ein Duplikat vorhanden ist (d.h. ein Text mehrmals vorkommt - mit der Formel ZÄHLENWENN).

      Daraufhin wird in Spalte D ermittelt, was denn im Falle eines Duplikats (Zähler grösser als 1) der Minimalwert ist - mit der Formel MINWENNS. Diese lässt uns Konditionen hinterlegen, um nur das Minimum des aktuellen Textes zu ermitteln.

      Die letzte Hilfsspalte enthält dann eine WENN-Formel, die prüft, ob es sich bei der aktuellen Zeile sowohl um ein Duplikat wie auch um den hier gültigen Minimalwert handelt. In dem Falle wird "Niedrigster Wert!" ausgegeben.

      Die bedingte Formatierung prüft dann wie in diesem Blog-Post beschrieben, ob die Kondition in der aktuellen Zelle (Spalte E = "Niedrigster Wert!") enthalten ist. Falls ja, wird die Zelle grün eingefärbt. Du kannst gerne auch eine grüne Schrift hinterlegen unter "Bedingte Formatierung" -> "Regeln anzeigen".

      Die Regel ist auf die gesamte Spalte B hinterlegt. Du könntest unterhalb also einen Text 3 einfügen mit einem bestimmten Wert. Wenn rechts davon in den Hilfsspalten die Formeln entsprechend vorhanden sind, dann wird Dir der niedrigste Wert für Text 3 unmittelbar eingefärbt.

      Liebe Grüsse
      Roman

      • Hallo, Roman,
        Danke für Deine Hilfe, jedoch bin ich in der Umsetzung auf ein Problem gestoßen. Meine Version von Excel scheint zu alt zu sein.
        Die Formel für das Berechnen des jeweils kleinsten Wertes klappt nicht, da die Formel nicht erkannt wird.

        Ich bekomme in Deiner Beispieltabelle für die Formel in der Spalte D bei mir angezeigt:

        =WENN(C2>1;_xlfn.MINIFS(B:B;A:A;A2))

        Hier liegt das Problem. Mein Excel kennt die Funktion MINWENNS offenbar nicht. Scheinbar haben wir eine ältere Version.
        Gibt es für mich eine Alternative, um diese Berechnung trotzdem auszuführen?
        Das mit der Zählerspalte hat einwandfrei funktioniert, und auch die Formel in der Spalte E ist korrekt. Nur sich das Minimum aus beiden Duplikaten zu suchen, daran bin ich ja bisher kläglich gescheitert.

        Liebe Grüße,
        Manuela

        • Hallo Manuela

          Das ist tatsächlich so: Der Fehler erscheint, weil diese Formel in Deiner Version noch nicht verfügbar ist.

          Es gibt eine andere Möglichkeit, wie Du dasselbe Ergebnis erlangst, indem wir "ältere" Formeln verwenden. In der Zelle D2 wäre ja ansonsten diese Formel:
          =WENN(C2>1;MINWENNS(B:B;A:A;A2))

          Setze dafür einfach die nachfolgende Formel ein:
          =WENN(C2>1;MIN(WENN(A:A=A2;B:B)))

          Das sollte Dir ein Resultat liefern.

          Liebe Grüsse
          Roman

  17. Hallo Roman,
    zuerst ein großes Lob für deinen Blog, er ist sehr hilfreich und gut erklärt. Mach weiter so!
    Nun zu meiner Frage:
    Wie muss ich die Formatierung machen, wenn ich folgendes Ergebnis haben möchte:
    Ich habe eine Tabelle mit 8 Spalten, in der dritten (D2) steht der Name, z.B. von einem Lieferanten. Die Farbe soll sich nach diesem Namen richten, z.B. Meier = Gelb, Müller= Blau. Nun möchte ich das alle Zellen in der Zeile mit einem Wert >0 die Farbe des Lieferanten bekommt und alle Zellen 0 hat soll die gesamte Zeile grün werden.
    Kannst du mir hier helfen?
    Vielen Dank im Voraus
    Grüße Jürgen

  18. Hallo Roman,
    vielen Dank für die guten Erklärungen. Ich bin absoluter Leihe und bringe mir excell selbstständig bei. Eine Aufgabe steht und schauen was die Profis dazu schreiben. Vlt. können Sie mir helfen:
    Ich habe eine Tabelle angelegt und möchte bei Eintragung eines Datums, dass sich die Zeile einfärbt. Dies soll unabhängig vom aktuelen Datum geschehen. Das bedeutet, trägt man ein Datum ein, dann soll die Zeile farbliche dargestellt werden. Die Tabelle befindet sich zwischen den Spalten B bis H. Habe es schon mit der bedingten Formatierung versucht. Habe dazu die von Ihne o.g. Vorgehensweise genommen. Konnte es aber nur mit dem aktuellen Datum realisieren. Bei älteren funktionierte es nicht.
    Für Ihre Bemühungen bedanke ich mich im voraus. MfG Sven

    • Hallo Sven

      Vielen Dank für Deinen Kommentar und die spannende Frage.

      Hinter jedem Datum steht jeweils eine Zahl (so ist der 27. September 2021 zum Beispiel die Zahl 44466; denn der Tag 01.01.1900 entspricht der Zahl 1). Man könnte also prüfen, ob die Zelle eine Zahl enthält (Formel =ISTZAHL), das kann aber zu unterwünschten Ergebnissen führen, da auch reguläre Zahlenwerte erkannt würden.

      Es gibt in Excel eine exaktere Möglichkeit, um zu prüfen, ob die Zelle als Datum formatiert ist. Füge hierzu einmal ein Datum in eine Zelle ein und setze nebenan die untenstehende Formel:
      =ZELLE("format";A1)
      Falls in "A1" ein Datum steht, erhältst Du "D1" als Formel-Resultat; im Falle einer Zahl lautet das Ergebnis "S".

      Dies können wir nun verwenden, um eine eigene Formel für die bedingte Formatierung zu verwenden. Starte dabei möglichst in Zelle "B1" und setze als Formel (wie im Beitrag oben):
      =ZELLE("format";B1)="D1"

      Wenn Du das jetzt auf den Bereich "$B:$H" abwendest, wird jeweils die Zelle geprüft und gemäss Deiner Formatierung hervorgehoben.

      Liebe Grüsse
      Roman

  19. Moin Roman,

    habe mir hier jede Frage durchgelesen, in der Hoffnung eine Antwort auf meine zu finden...
    Leider Fehlalarm 🙂
    Ich habe alles Schritt für Schritt befolgt nur für eine deutliche größere Tabelle (ca. 3500 Zellen). Ich habe in der Spalte, die verglichen werden soll 2 Worte drinnen ("Kein Mitglied"). Nun habe ich das Gefühl, dass das System durcheinander kommt, weil es mir auch zwischendurch Zellen markiert, wo nicht "Kein Mitglied" steht.

    Vielleicht siehst du ja eine offensichtliche Hürde... Bei Interesse kann ich es auch nochmal ausführlich beschreiben, aber ich denke kurz und knackig reicht eventuell schon aus.

    MfG Tom

    • Hallo Tom

      Mit der Grösse der Tabelle sollte es eigentlich keinen Zusammenhang haben (auch 3'500 Zeilen bringt Excel nicht durcheinander). Den Fall habe ich nachgestellt und es funktioniert bei mir.

      Mein Begriff "Kein Mitglied" - wie auch andere Angaben - stehen jeweils in Spalte G. Somit lautet meine Regel/Formel:
      =$G2="Kein Mitglied"

      Anwenden tue ich die Regel auf diesen Bereich:
      =$A$2:$G$3500

      Kannst Du das so nochmals ausprobieren?

      Beste Grüsse
      Roman

  20. Guten Tag,
    habe ich es richtig verstanden, dass man wirklich jede Formatierung einzeln einpflegen muss?

    Einfaches Beispiel:
    Arbeitszeittabelle, jede Zeile Samstag/Sonntag soll sich grau färben und Zeilen wie z.B. Urlaub, krank sollen sich auch automatisch grün/blau färben.

    Da sind dann also knapp 140 verschiedene Eingaben (pro Monat) nötig?

    Vielen Dank und Viele Grüße

    Chris

    • Hallo Chris

      Danke für Deinen Kommentar. Nein, Du musst die Formatierung eigentlich nur einmal hinterlegen und kannst via "Bedingte Formatierungen" --> "Regeln verwalten" Deine eingepflegte Regel einsehen und bei "Wird angewendet auf" den Bereich auf ganze Spalten ("$A:$B") oder einen spezifischen Bereich wie "$A$1:$C$100" festlegen. So greift eine Regel direkt auf all diesen Zellen, wenn der Inhalt "Samstag/Sonntag" ist.

      Im Blogbeitrag wird die Regel einmalig hinterlegt und auch via "Wird angewendet auf" auf mehrere Zeilen angewandt.

      Liebe Grüsse
      Roman

  21. Hallo Roman,

    deine Erklärung hat mir sehr weiter geholfen!
    Allerdings habe ich noch ein etwas komplizierteres Anliegen.
    Ich habe die Spalte mit der Bedingung als Dropdownliste angelegt, weil sich der Status in der jeweiligen Spalte immer mal tagesaktuell ändern kann. Nun soll es so sein, dass sich natürlich dann die gesamte Zeile in die neue Farbe der neuen Bedingung umfärbt. Wie kann ich das erreichen? Viele Grüße Sophie

    • Hallo Sophie

      Welche Ausprägungen hat Deine Dropdownliste? Nehmen wir an, Du hast vier Auswahlmöglichkeiten:
      Nord, Ost, Süd, West

      Die Auswahlmöglichkeit ist nichts anderes, als das "Ja"/"Nein", das ich im Blog-Beitrag aufgeführt habe. Du könntest jetzt also insgesamt viermal eine Regel hinterlegen, jeweils für den jeden Deiner Dropdown-Fälle. Man kann nämlich mehrere Regeln für den gleichen Bereich definieren, es greift dann die erste Regel, die auch zutrifft (die Reihenfolge könntest Du im Regelmanager einstellen, falls notwendig).

      Passt das so für Dich? Viel Erfolg!

      Liebe Grüsse
      Roman

      • Hallo Roman,

        ich weiß leider nicht, was ich falsch mache, aber das funktioniert bei mir irgendwie nicht. Ich habe die Regeln erstmal so angelegt, dass nur die eine Spalte gefärbt wird, wenn ich einen aus den insgesamt 8 Dropdownpunkten ausgewählt habe. Danach habe ich alles so gemacht wie in deiner Anleitung beschrieben, aber es werden dann auf einmal ganz andere, teils willkürliche Zeilen/Spalten eingefärbt, die nichts mit den Punkten zutun haben. Ganze Zeilen werden gar nicht eingefärbt. Irgendwas scheine ich falsch zu machen.
        LG Sophie

  22. Hallo Roman,

    vielen Dank für die Erläuterung! Ich dachte bisher, sowas wäre nur mit Makros möglich.
    Ich habe mich daran versucht, die Formel umzuschreiben: und zwar soll die gesamte Zeile markiert werden, wenn irgendwo in dieser Zeile ein Schlagwort (z. B. "Baum") auftaucht (zum besseren anschließenden Filtern). Also nicht auf eine bestimmte Vergleichsspalte bezogen.
    Das hat auch ganz gut geklappt, bis auf 2 Zeilen, die es nicht markiert hat. Wo könnte der Fehler liegen?
    Die Formel ist:
    =FINDEN("Baum";$DT2:$RH150)
    und der Bereich =$A$2:$RH$150

    Wenn ich bei Finden schon A2 eingebe, nimmt er einige Markierungen wieder raus, obwohl in den hinteren Spalten der Suchbegriff auftaucht. Woran könnte das liegen?

    LG Luisa

    • Hallo Luisa

      Deinen Fall habe ich nachgestellt und die richtige Lösung zuerst mit der "FINDEN"-Formel auch nicht gefunden. So habe ich von Grund auf eine Variante aufgebaut, wie ich auch sonst nach Werten suche (z.B. in einer "Suchmaschine", die auch hier auf dem Blog gezeigt wird).

      Die Regel, die ich für die Zeile 2 eingebaut habe, lautet wie folgt:
      =LÄNGE(WENNFEHLER(WVERWEIS("Baum";2:2;1;0);""))>0

      Die Regel wird angewandt auf den Bereich:
      =$A$2:$RH$150

      Mit dieser Formel prüfe ich, ob der Begriff "Baum" in der jeweiligen Zeile vorkommt (z.B. 2:2 - was nach unten springt, bis zu 150, gemäss dem gewählten Bereich). Sollte die WVERWEIS-Formel einen Fehler geben, lasse ich den Wert LEER ausgeben ("") - ansonsten erhalten wir "Baum" zurück, dessen Länge mehr als 0 ist.

      Anstelle von "2:2" könntest Du auch "$A2:$RH2" verwenden.

      Liebe Grüsse
      Roman

      • Danke für die schnelle Rückmeldung 🙂 das funktioniert aber nur, wenn "Baum" der einzige Begriff in einer Zelle ist, oder? Zumindest war das das Ergebnis meines Tests. Ich bräuchte jedoch etwas, das auch "anschlägt", wenn der Begriff nur einer von vielen innerhalb einer Zelle ist, weshalb ich es erst über FINDEN versucht habe. Gibt es da noch eine Möglichkeit?
        Als Beispiel: ich suche nach "Kind", dann sollen mir auch die Zeilen markiert werden, wenn irgendwo in einer Zelle "Kinder und Jugendliche", "Eltern-Kind" etc. steht.

        Vielen Dank und liebe Grüße
        Luisa

        • Hallo Luisa

          Das stimmt natürlich, es wird auf den expliziten Begriff geachtet. Wenn Du allerdings vorher und nachher ein Sternchen (*) einbaust, wird das Ganze flexibler. Versuche es deshalb mit:
          =LÄNGE(WENNFEHLER(WVERWEIS("*baum*";2:2;1;0);""))>0

          ... oder für den Begriff "Kind" mit:
          =LÄNGE(WENNFEHLER(WVERWEIS("*kind*";2:2;1;0);""))>0

          Du kannst auch "Baum" oder "Kind" schreiben - der WVERWEIS ignoriert Gross- und Kleinschreibung.

          Beste Grüsse
          Roman

  23. Hallo
    Ich habe ein Problem, welches ich denke hat viel mit den beschriebenen zu tun.
    Ich habe in Zelle A1 den Text Mo. Di. Mi. etc. nun möchte ich dass wenn Sa. oder So. steht, die nachstehenden Zellen je Zeile, also im Bsp. A2 und A3 eingefärbt werden. Leider finde ich nicht heraus, wie ich eine bestimmte Anzahl Zellen nach einer bestimmten formatieren kann. Zudem Ab Zelle A4 beginnt es wieder von vorne für den Februar. Daher müssen auch die nachstehenden zwei Zellen in der Formel erwähnt werden, da sonst die Formel nicht kopiert werden kann.
    Ich bin gespannt auf eure Antwort

    • Hi Mike

      Deine Lösung, um "Sa." und "So." einfärben zu können, sollte wie folgt ausschauen. Definiere zwei Regeln.

      Die Formel der ersten Regel lautet:
      "=ODER(A1="Sa.";A1="So.")"
      Wende diese an auf den Bereich (respektive für die Zeilen, die direkt unterhalb der Tagesangaben liegen, in meinem Fall Zeilen 2 und 5 für die Spalten A bis N und A bis I):
      =$A$2:$N$2;$A$5:$I$5

      Die Formel der zweiten Regel lautet identisch:
      =ODER(A1="Sa.";A1="So.")
      Wende diese dann jedoch auf einen Bereich weiter unten an (Zeilen 3 und 6 in meinem Falle, für die beispielhaften Spalten A bis N und A bis I):
      =$A$3:$N$3;$A$6:$N$6

      So werden Dir die Zellen unterhalb im Falle von "Sa." oder "So." eingefärbt/formatiert.

      Beste Grüsse
      Roman

  24. Hallo!

    Ich bin dabei ein Kalender-Excel-Sheet zu erstellen, welches für jedes Quartal ein Arbeitsblatt hat. Es soll für jedes Monat Samstage, Sonntage und Feiertage schwarz markiert werden, da an diesen Tagen kein Backup läuft (und somit kein Band für's Backup eingelegt werden muss).

    Mir fehlt noch die Markierung für die Feiertage. Diese hätte ich gerne in ein eigenes Arbeitsblatt eingetragen, als einfach Liste.
    Aber wie bekomme ich das jetzt in eine Formel?

    • Hi Simon

      Wir können die drei Szenarien in einer "WENN"-Formel mittels "ODER" kombinieren. Setzen wir zuerst die Voraussetzungen:
      Angenommen, Dein Datum steht in der Zelle A1 (z.B. 01.01.2022). Mittels =TEXT(A1;"TTTT") findest Du den Wochentag heraus - also ob es sich um einen Samstag oder Sonntag handelt.

      Führe in einer separaten Tabelle alle Feiertage auf, z.B. in Spalte A die Daten (01.01.2022) und in der Spalte B nebenan die Bezeichnung des Feiertags (z.B. "Neujahr"). Mit einem SVERWEIS kannst Du Dein ursprüngliches Datum aus Zelle A1 suchen. Die Formel wäre z.B.:
      =SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH)
      Das Ergebnis hier wäre ein Datum, respektive dessen zugrundeliegende Zahl (jeder Tag hat eine Zahl, der 1. Januar 2022 ist die Zahl 44562).

      Um nun zu ermitteln, ob mindestens einer dieser Fälle zutrifft, verwende eine einfache "WENN"-Formel:
      =WENN(ODER(ISTZAHL(SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH));TEXT(A1;"TTTT")="Samstag";TEXT(A1;"TTTT")="Sonntag");1;0)

      Du prüfst zuerst, ob der SVERWEIS eine Zahl zurückgibt - oder ob es sich um einen Samstag, oder um einen Sonntag handelt. Trifft einer dieser Fälle zu, erhältst Du eine "1", ansonsten eine "0".

      Du könntest dies in eine bedingte Formatierung einbauen, setze dazu einfach hinter die oben genannte Formel noch eine "[...]=1", dies wäre dann:
      =WENN(ODER(ISTZAHL(SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH));TEXT(A1;"TTTT")="Samstag";TEXT(A1;"TTTT")="Sonntag");1;0)=1

      So hast Du die Kondition gesetzt, um die Zelle(n) entsprechend einzufärben/zu formatieren.

      Bitte beachte: Du musst je nach Aufbau Deiner Tabelle und Absicht allenfalls noch die Bezüge absolut setzen (Dollarzeichen), z.B. falls Du einen grösseren Bereich unterhalb einfärben möchtest die Zeile (bspw. A$1) oder falls Du daneben einfärben möchtest die Spalte (bspw. $A1) usw.

      Beste Grüsse
      Roman

  25. Hallo, ich finde deine Anleitung hier wirklich super.

    Aber ich stehe auch total auf dem Schlauch bei einer Teiltext Suche. Vor meiner Kundennummer stehen noch drei Buchstaben "INC" oder "NIC". Ich möchte mir die Zeilen mit den "INC" markieren lassen. Egal wie ich es mache, (FINDEN oder LINKS) es werden immer irgendwelche Zeilen markiert, nur nicht die, in der die Buchstaben stehen.

    Für deine Hilfe wäre ich dir echt dankbar.

    • Hallo Ariane

      Vielen Dank!

      Deinen Fall konnte ich rekonstruieren und eine Lösung finden. In meinem Fall (wie in den Screenshots im Initialbeitrag hier) steht die Kundennummer in der Spalte A. Ich selektiere dafür die Zelle "A2" und gehe via "Bedingte Formatierung" --> "Neue Regel..." und füge wie oben illustriert eine "Formel zur Ermittlung der zu formatierenden Zellen" ein.

      Um diejenigen Zeilen zu markieren, die mit "INC" beginnen, setze ich diese Formel:
      =LINKS($A2;3)="INC"

      Danach setze ich den Anwendungsbereich entsprechend so, dass es auf die ganze Tabelle gültig ist. Das gewählte Format (z.B. farbige Markierung) greift dann sofort. Kundennummern, die mit "NIC" beginnen, werden nicht markiert; sie müssen aufgrund der LINKS-Formel effektiv mit "INC" beginnen.

      Hat es bei Dir so funktioniert?

      Beste Grüsse
      Roman

  26. Hallo Roman,
    ich bin wirklich sehr erstaunt wie gut du Excel beherrscht! Beneidenswert.
    Ich habe eine csv Tabelle in Excel umgewandelt. Ich möchte alle Zeilen, die den Teiltext "Miet" enthalten, ganz einfärben. Wenn ich aber den Tabulator nur auf eine Zelle setze, färbt er nur diese ein. Markiere ich den ganzen Text, färbt er auch die anderen Felder ein. Soweit so gut.
    Aber wenn ich die ganze Zeile einfärben will, komme ich nicht weiter. Welche Kondition gebe ich hier ein "Formel zur Ermittlung der zu formatierenden Zellen verwenden"?

    Viele Grüsse

    Dagmar

    • Hallo Dagmar

      Vielen Dank für Deinen Kommentar.

      Beginne beim Konfigurieren einer bedingten Formatierung am besten bei der obersten Zeile (z.B. Zeile 2, wenn Du noch Überschriften hast). Gehe dann wie im Initial-Beitrag aufgeführt via "Bedingte Formatierung" --> "Neue Regel..." und wähle die letzte Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus.

      Als Formel kannst Du zwei Varianten verwenden. Wie kürzlich in der Antwort an Ariane angegeben, falls Dein Text jeweils mit "Miet" beginnt (und der Text in Spalte A steht):
      =LINKS($A2;4)="Miet"
      Diese Formel nimmt die vier Zeichen von links.

      Als zweite Option, falls "Miet" irgendwo enthalten sein kann, verwende diese Formel:
      =FINDEN("Miet";$A2)
      Bitte beachte, dass diese "FINDEN"-Formel Klein- und Grossschreibung berücksichtigt.

      Beachte bitte zudem, dass das Dollarzeichen gesetzt ist und die Spalte somit angebunden wird (nur so können danebenliegende Zellen auch eingefärbt werden)!

      Setze nach Eingabe der Formel die gewünschte Formatierung und abschliessend noch den Anwendungsbereich Deiner Formel. Dies wäre z.B. =$A$2:$G$6 - abhängig von Deiner Tabelle. Berücksichtige bitte, dass ich bei "A2" beginne; das ist auch da, wo ich die Formel gesetzt habe. Ansonsten gibt es eine Verschiebung der Bezüge.

      Konntest Du so das gewünschte Resultat erzielen?

      Beste Grüsse
      Roman

  27. Auch, wenn es eine Weile her ist, hoffe ich, dass mir vielleicht noch jemand helfen kann. Wenn, dann eine(r) von euch!

    Wenn in meiner Zelle "Absage" steht, wird die Zeile rot gefärbt. Das funktioniert mit dem tollen Tutorial von Roman schon super.
    Wenn ich jedoch noch eine Begründung dazu schreiben will, also "Absage, weil ..." entfärbt es die Zeile wieder.
    Habe es schon mit Sternchen probiert, also (=$L2="*Absage*"), aber das funktioniert leider auch nicht.

    Hat da jemand eine Idee? Bin auf sonstigen Websites nicht weit gekommen.

    Beste Grüße
    Marten

    • Hallo Marten

      Du solltest dies mit der untenstehenden Formel lösen können:
      =FINDEN("Absage";$L2)

      Das markiert Dir sowohl diejenigen Zeilen, die in Spalte "L" nur das Wort "Absage" haben, allerdings auch jene, wo man hinterher noch eine Begründung anfügt ("Absage, weil ...").

      Hat das so funktioniert bei Dir?

      Liebe Grüsse
      Roman

  28. Hi Roman
    Hat jemand eine Idee warum es mir immer die nächste Zeile rot einfärbt anstatt der Zeile in der ich den Wert eintrage?
    Hier zum Hintergrund:
    Ich möchte ein Mitgliederverzeichnis anlegen und in der letzten Spalte ist vermerkt ob das Mitglied bereits ausgeschieden/ausgetreten ist. Eingetragen wird nur ein "x" somit lautet meine Formel: =$K2="x" und angewendet wird es auf die ganze Tabelle =$A$3:$K$35. Allerdings sobald ich das x setze färbt es die nächste Zeile rot aber nicht die betreffende. Ich finde einfach keinen Fehler.
    Danke und viele Grüße
    Andrea

    • Hallo Andrea

      Bitte prüfe exakt die Formel
      =$K2="x"
      sowie Deinen Anwendungsbereich. Gemäss Deiner Erläuterung beziehst Du Dich auf die Zelle K2, der Anwendungsbereich ist jedoch von Zeile 3 bis 35. Womöglich muss entweder der Bezug auf Zelle K3 gehen oder der Anwendungsbereich auf $A$2:$K$35 erweitert werden.

      Liebe Grüsse
      Roman

  29. Hallo Roman

    Ich nehme mir ebenfalls die Freiheit und stelle dir eine Frage bezüglich dieser Funktion, da ich glaube (aber nicht weiss), dass es mit der bedingten Formatierung machbar ist.

    Es ist so, ich führe eine Liste, welche ich mit dank deiner Anleitung hier erstellt habe (grosses Danke dafür) in welcher ich den Status meiner Anfragen farblich hervorhebe. Es färbt dann die ganze Zeile ein.
    - Angefragt: rot
    - Erhalten: gelb
    - Kontrolle: blau
    - Abgeschlossen: grün
    (Die Farben tun nichts zu meiner Frage, nur dass du dir's vorstellen kannst).
    Im Moment habe ich die Formatierung so, dass wen ich z.B. ein "X" in das Feld 'Angefragt' schreibe, sich die Zeile verfärbt. Wenn ich nun den Status ändern will und z.B. im Feld 'Erhalten' ein "X" eingebe, färbt sich die Zeile in die neue Farbe. So weit so gut, jedoch bleibt das erste "X" bestehen. Da ich dies mehrmals am Tag machen muss, wäre es gut, wenn sich das erste "X" selber löschen würde. So eine Art WENN Funktion. Wenn ein Feld ein "X" enthält, müssen die anderen leer sein. Gibts dafür eine Lösung welche ich vielleicht auch gleich in die Bestehende Formel einbinden kann?
    Die Formel fürs einfärben jetzt ist:
    =$H9="x"
    (das halt für die Spalten H-K welche den Status beinhalten) und färbt wie erwähnt die ganze Zeile.

    Vielen herzlichen Dank für deine Hilfe.

  30. Hallo Roman,

    ich brauch das täglich zum Bearbeiten riesiger Listen, bin schon daran verzweifelt, dass die so unübersichtlich sind und Du hast mir gerade den PoPo gerettet.
    Dank Dir die richtige Formal in die erstel Zelle oben links geschrieben und dann mit "Format übertragen" in die gesamte Tabelle übertragen.
    Hat prima geklappt. Vielen Danke!

  31. Vielen Dank für die aufschlussreiche Anleitung! Allerdings stolpere ich darüber die Regel für die bedingte Formatierung von einer Zeile in die nächste zu übertragen.

    Sagen wir ich habe eine Spalte Z in die ich die Werte "grün" oder "rot" eingeben möchte um dann die ganze Zeile zu färben. Also:
    =$Z1="grün" / und dann wird angewendet auf: "=$A$1:$Z$1"
    Klappt super!

    Wenn ich jetzt aber die Zelle kopieren will um die Regel für die bedingte Formatierung auf mehrere Zeilen anzuwenden, wird für "wird angewendet auf" immer der Wert für die Spalte übertragen, also zum Beispiel "=$Z$23" (statt =$A$23:$Z$23).

    Ich hab auch versucht mit STRG ALT V zu kopieren, aber ohne Erfolg.

    Mein Gefühl ist, ich übersehe etwas Offensichtliches... aber was??

    • Ja es war wirklich offensichtlich, wenn mal erst mal darauf gestoßen ist. (hat mich nur eine Stunde gekostet.. ohje)

      Natürlich muss ich den Anwendungsbereich einfach für meine gesamte Mappe definieren, also z.B. =$Z1="grün" / wird angewendet auf =$A$1:$Z$999 und dann brauche ich nur eine Formel für alles. Das steht ja auch in deinem Beispiel 🙂

      Ich lasse meine (doofe) Frage mal stehen und grüße alle, die meine Hassliebe für Excel teilen 🙂

  32. Hallo zusammen,

    ich möchte (über ein Aggregat) nur mit Werten belegte Zeilen mit Rahmen versehen, alle nachstehenden leeren Zeilen nicht.

    Ich habe keine anderen sich gegenseitig aushebelnden Regeln in der bedingten Formatierung definiert.

    In Zelle $A1 wird geprüft:
    =$A1>0
    für die Formatierung Rahmenlinien
    Bereich:
    =$A$1:$J$800
    Egal welche Zeile, egal welcher Wert in der erste Zelle in Spalte A, ob größer 0 oder gleich 0, wird mit Rahmenlinien versehen. Alles von Zeile 1 bis 800 ist gerahmt, der untere Teil ist aber komplett leer.

    Prüfung der Formel in der Bedingten Formatierung >> festgestellter Fehler:
    In jeder Zeile prüft Excel =$A1>0.

    Klar woran das liegt. Wie bekomme ich es automatisch hin, dass er fie Zeile für die Prüfung wechselt und jede Zeile für sich prüft?

    In Zeile 2 muss ja stehen =$A2>0 , in Zeile 3 muss geprüft werden =$A3>0, usw. Kann doch nicht ewa sein, dass ich jede einzelne Zeile bedingt formatieren muss.

    Formatierungspinsel hilft zwar, kommt auch durcheinander, wenn ich unterschiedliche Anzahl von Zeilen beim Markieren und Format übertragen auswähle, und macht dann wieder falsche Zeilenadressen in die Prüfung rein. 800 Zeile will ich in einem Aufwasch erledigen.

    Gibts einen Trick?

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.