Die bedingte Formatierung in Excel ist zwar sehr flexibel, aber manchmal trifft sie nicht ganz ins Schwarze – insbesondere, wenn man ganze Zeilen basierend auf dem Wert einer einzelnen Zelle oder einem Kontrollkästchen hervorheben möchte. Wer schon einmal versucht hat, Bedingungen festzulegen, um Daten hervorzuheben oder übersichtlich zu gestalten, ohne manuell Farben verwenden zu müssen, weiß, wie hilfreich diese Funktion sein kann. Doch die perfekte Anwendung zu finden, kann knifflig sein. Hier ist eine praxiserprobte Lösung, die in verschiedenen Konfigurationen funktioniert hat und auch einige Besonderheiten im Verhalten von Formeln je nach Excel-Version oder -Layout berücksichtigt.
Teil 1: So heben Sie ganze Zeilen basierend auf dem Wert einer einzelnen Zelle hervor
Angenommen, Sie haben eine Tabelle – beispielsweise B2:G6 –, in der Mitarbeiterdaten erfasst sind, und in Spalte G stehen die Vertragslaufzeiten. Wenn ein Vertrag bereits abgelaufen ist, soll die gesamte Zeile rot markiert werden. Klingt einfach, aber die korrekte Umsetzung erforderte einige Versuche, insbesondere mit der Formel. Falls die Formatierung nicht angewendet wird, überprüfen Sie bitte, ob Ihre Zellbezüge korrekt verankert sind und Ihre Formel mit der ersten Datenzeile übereinstimmt.
Was Sie tun müssen:
- Beginnen Sie mit der Auswahl des gesamten Datenbereichs (ohne Überschriften), z. B.B2:G6. Je nach Datensatz müssen Sie möglicherweise mehr Zeilen auswählen oder den Bereich verschieben.
- Gehen Sie zur Registerkarte „Startseite “, klicken Sie auf „Bedingte Formatierung“ und wählen Sie dann „Neue Regel“.
- Wählen Sie „Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen“.
- Geben Sie die Formel ein:
=$G2<TODAY(). Falls sich Ihr Enddatum in Spalte G befindet, fixieren Sie diese Spalte mit dem Dollarzeichen, behalten Sie aber die relative Zeilennummer bei. Diese Formel vergleicht das Datum jeder Zeile mit dem heutigen Datum. Auf manchen Rechnern kann dies beim ersten Mal fehlschlagen oder unerwartetes Verhalten zeigen. Seien Sie daher darauf vorbereitet, die Formel gegebenenfalls zu deaktivieren oder Excel neu zu starten. - Klicken Sie auf „Format“ und wechseln Sie dann zum Reiter „Füllung “.Wählen Sie eine Farbe – Rot, hell genug, um sie gut zu erkennen. Klicken Sie auf „OK“.
- Klicken Sie im Hauptdialogfeld erneut auf OK. Ihre Zeilen mit vergangenen Daten sollten nun rot hervorgehoben sein. Falls nicht, überprüfen Sie die Formel, insbesondere die Zellbezüge und die Spaltensperre.
Für komplexere Berechnungen – wie das Hervorheben von Text anhand mehrerer Bedingungen – benötigen Sie möglicherweise UND- oder ODER- Funktionen in Ihrer Formel. Wenn Sie beispielsweise Zeilen hervorheben möchten, in denen der Vertrag endete und ein bestimmtes Kontrollkästchen aktiviert ist, lässt sich dies mit einer Kombination wie dieser realisieren:.Manchmal sind aber auch Formeln hilfreich =AND($G2<TODAY(), $H2=TRUE), die auf Text- oder Teilstringsuche basieren, wie beispielsweise.=SEARCH("string", $A2)>0
Sobald Ihre Regel festgelegt ist, können Sie unter „Bedingte Formatierung“ > „Regeln verwalten“ die Regelpriorität anpassen. Beachten Sie, dass die zuletzt zutreffende Regel gilt, wenn sich mehrere Bedingungen überschneiden. Bei einer Konfiguration funktionierte es auf Anhieb; bei einer anderen musste ich die Reihenfolge ändern oder Excel neu starten, damit die Änderungen übernommen wurden. Typisch Excel, dass es einem unnötig schwer gemacht wird.
Teil 2: Formatieren einer Zeile, wenn ein Kontrollkästchen aktiviert ist
Diese Methode erfordert etwas mehr Aufwand, eignet sich aber hervorragend, wenn Sie Kontrollkästchen anstelle von Datumsangaben oder Text bevorzugen. Kontrollkästchen fügen Sie über die Registerkarte „Entwicklertools “ hinzu. Falls diese nicht aktiviert ist, gehen Sie zu „Datei“ > „Optionen“ > „Menüband anpassen“ und aktivieren Sie „Entwicklertools“. Klicken Sie anschließend auf der Registerkarte „Entwicklertools“ auf „Einfügen“ > „Kontrollkästchen (Formularsteuerelement)“. Ziehen Sie das Kontrollkästchen in Ihre Tabelle und formatieren Sie es per Rechtsklick.
Folgendes ist zu beachten: Sie müssen jedes Kontrollkästchen mit einer Zelle verknüpfen – beispielsweise Spalte H –, die je nach Status des Kontrollkästchens den Wert WAHR oder FALSCH enthält. Klicken Sie dazu mit der rechten Maustaste auf das Kontrollkästchen, wählen Sie „ Steuerelement formatieren “ und legen Sie die Zellenverknüpfung beispielsweise auf „Zelle“ fest $H2. Wiederholen Sie diesen Vorgang für jedes Kontrollkästchen und achten Sie darauf, dass die Verknüpfung zur entsprechenden Zeile passt.
Erstellen Sie nun eine Regel für die bedingte Formatierung: Markieren Sie Ihre Tabelle (z. B.A2:H6) und gehen Sie dann zu „ Bedingte Formatierung“ > „Neue Regel“. Wählen Sie „Formel verwenden, um die zu formatierenden Zellen zu bestimmen“. Geben Sie die Formel ein =\$H2=TRUE. Wählen Sie für die Formatierung den gewünschten Stil, z. B.eine Hintergrundfarbe oder Fettdruck, und klicken Sie auf „OK“.Fertig. Immer wenn Sie nun das Kontrollkästchen aktivieren, wird die entsprechende Zeile farbig hervorgehoben.
Achtung: Manchmal hilft es, alte Regeln zu entfernen, da Formatierungskonflikte zu Problemen führen können. Falls sich Kontrollkästchen ungewöhnlich verhalten oder nicht sofort aktualisiert werden, schadet ein Neustart von Excel oder die Neuberechnung von Formeln meist nicht. Seltsamerweise werden Verknüpfungen oder Regeln auf manchen Rechnern nicht sofort angewendet; in diesem Fall ist etwas Geduld oder ein Neustart erforderlich.
Es erfordert etwas Geduld, all diese Elemente optimal aufeinander abzustimmen – insbesondere bei komplexen Tabellen oder vielen Regeln. Doch sobald der Dreh raus ist, wird die visuelle Datenverwaltung deutlich einfacher. Achten Sie nur darauf, dass Ihre Zellbezüge und Ankerpunkte korrekt sind; andernfalls gerät die Formatierung durcheinander. Denn Excel überrascht uns immer wieder gern.