Die eingebauten Regeln der bedingten Formatierung sehen nur die Zelle an, die formatiert wird. Um eine Zelle auf Basis einer anderen Zelle einzufärben, nutzt du die Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden". Der ganze Trick steckt darin, wie du die Bezüge mit dem Dollarzeichen sperrst.
Die Schritte
- Markiere die Zellen, die du formatieren willst (die, deren Farbe sich ändern soll).
- Registerkarte Start → Bedingte Formatierung → Neue Regel.
- Wähle Formel zur Ermittlung der zu formatierenden Zellen verwenden.
- Gib eine Formel ein, die für die zu formatierenden Zellen WAHR ergibt.
- Klicke auf Formatieren, wähle eine Füllung oder Schriftfarbe und dann OK.
Eine Zelle auf Basis einer anderen Zelle formatieren
Angenommen, du markierst B2 und willst, dass es grün wird, wenn in C2 "Bezahlt" steht:
=$C2="Bezahlt"
Das $ vor dem C sperrt die Spalte, sodass jede Zelle in Spalte B immer die Spalte C in ihrer eigenen Zeile prüft. Weil die Zeilennummer offen bleibt (2, nicht $2), wandert die Regel die Zeilen hinab.
Eine ganze Zeile hervorheben
Das ist die häufigste Anwendung. Markiere den ganzen Datenbereich (zum Beispiel A2:D100) und nutze dann eine Formel, die mit gesperrter Spalte auf eine Spalte zeigt:
=$D2="Überfällig"
Jede Zelle der Zeile prüft die Spalte D ihrer eigenen Zeile, sodass die gesamte Zeile hervorgehoben wird, wenn in Spalte D "Überfällig" steht. Die Regel zum Sperren ist einfach: Spalte sperren ($D), Zeile offen lassen (2), wenn du eine Spalte über eine ganze Zeile hinweg lesen willst.
Zwei Zellen vergleichen
Hebe eine Zelle hervor, wenn sie größer ist als der Zielwert in einer anderen Zelle:
=B2>$E$1
Hier ist $E$1 vollständig gesperrt (Spalte und Zeile), weil jede Zelle gegen dieselbe einzelne Zielzelle vergleicht.
Spickzettel zum Sperren von Bezügen
- $C2 - immer Spalte C, Zeile wandert. Nutze es, um eine Spalte über viele Zeilen zu prüfen.
- C$2 - immer Zeile 2, Spalte wandert. Nutze es, um eine Zeile über viele Spalten zu prüfen.
- $C$2 - immer genau die Zelle C2. Nutze es für ein einzelnes festes Ziel.
- C2 - vollständig relativ, wandert in beide Richtungen. Selten das, was du in einer zellenübergreifenden Regel willst.
Häufige Probleme
- Nur die erste Zelle wird formatiert. Du hast versehentlich die Zeile gesperrt ($C$2). Entsperre die Zeile, damit die Regel nach unten wandert: $C2.
- Die falschen Zeilen werden hervorgehoben. Deine Markierung hat nicht in der Zeile begonnen, die die Formel annimmt. Sagt die Formel Zeile 2, muss die Markierung in Zeile 2 beginnen.
- Nichts passiert. Die Formel ergibt kein WAHR/FALSCH oder zeigt auf die falsche Spalte. Teste sie zuerst in einer freien Zelle; sie sollte WAHR oder FALSCH anzeigen.
- Textvergleich schlägt fehl. Textvergleiche in der bedingten Formatierung achten nicht auf Groß- und Kleinschreibung, aber überzählige Leerzeichen stören. Umschließe den Bezug mit GLÄTTEN, falls die Daten am Ende Leerzeichen haben können.
Häufig gestellte Fragen
Wie hebe ich eine Zeile auf Basis des Werts einer Zelle hervor?
Markiere den ganzen Zeilenbereich, erstelle eine Formelregel und sperre nur die Spalte der Zelle, die du prüfst: =$D2="Überfällig". Die gesperrte Spalte und die offene Zeile sorgen dafür, dass jede Zelle der Zeile die Spalte D ihrer eigenen Zeile liest.
Was macht das Dollarzeichen in einer Formel der bedingten Formatierung?
Das Dollarzeichen sperrt einen Teil eines Bezugs, sodass er sich nicht verschiebt, während die Regel über deine Markierung angewendet wird. $D2 hält die Spalte fest auf D, lässt aber die Zeile pro Zeile wechseln - genau das brauchst du, um eine Spalte über viele Zeilen zu lesen.
Warum wird nur die oberste Zelle formatiert?
Mit ziemlicher Sicherheit hast du die Zeile gesperrt ($D$2). Mit gesperrter Zeile prüft jede Zelle dieselbe einzelne Zelle statt ihrer eigenen Zeile. Ändere es in $D2, damit der Zeilenbezug wandert.
Kann ich auf Basis einer Zelle in einem anderen Blatt formatieren?
Nicht direkt in der Regel, denn Formeln der bedingten Formatierung können nicht auf ein anderes Blatt verweisen. Hol den Wert des anderen Blatts zuerst in eine Hilfszelle auf dem aktuellen Blatt und richte die Regel dann auf diese Hilfszelle.