Excel–Bedingte Formatierung

Immer wieder werde ich gefragt, wie man eine ganze Zeile einfärben kann, wenn in eine Zelle ein Wert steht. Ich möchte in diesem Beitrag das Prinzip erklären und Schritt für Schritt durchgehen.

Der Weg, den ich im folgenden zeige, ist nicht der schnellste, eleganteste, hübscheste Weg – das weiß ich und das ist auch nicht mein Ziel.

Mit diesem Weg lernen Sie das Prinzip kennen und hoffentlich verstehen. Darum nehme ich einen kleinen Umweg erst einmal in Kauf.

Wenn Sie das Prinzip verinnerlicht haben, können Sie den schnelleren, abgekürzten Weg lernen. Darüber werde ich auch in Kürze noch weitere Artikel veröffentlichen

Vorab noch eine Bemerkung:

Das wichtigste bei diesem Thema ist die Form des Bezuges: relativ, absolut oder gemischt.
Die Schreibweisen sind A1 bei relativ, $A$1 bei absolut und $A1 bzw. A$1 bei einem gemischten Bezug.
Wenn Sie sich darin nicht ganz sicher fühlen, können Sie hier mehr darüber lesen.

Also zuerst einmal das Prinzip an einem ganz einfachen Beispiel.

In diesem einfachen Beispiel sollen die Zellen in den beiden Spalten A und B eingefärbt werden, wenn in Spalte B die Ziffer 2 steht. Das ist nur für Zeile 4 der Fall – hier will ich also A4 und B4 einfärben.

Schritt 1: Bedingte Formatierung für eine Zelle einrichten

Starten Sie mit der ersten Zelle Ihres Bereiches – klicken Sie in diesem Beispiel auf A2.

• Rufen Sie “Start | Bedingte Formatierung | Neue Regel | Formel zur Ermittlung der zu formatierenden Zahlen verwenden” auf.
• Mit der Maus in das Feld unter “Wert formatieren, für die diese Formel wahr ist”.
• Anschließend die Zelle anklicken, die kontrolliert werden soll – das ist bei mir B2.
  Excel schreibt den Bezug als absoluten Bezug =$B$2
• Dahinter tippen Sie =2
  In der Zeile steht jetzt =$B$2=2
• Anschließend klicken Sie auf “Formatieren” und suchen sich eine Füllfarbe aus.
• Alles mit OK bestätigen und kontrollieren, ob A2 eingefärbt wird. Dürfte in meinem Beispiel nicht, weil in B2 eine 1 steht. Sie können den Wert probeweise zu “2” ändern, um zu sehen, ob Ihre Regel wirklich greift. Passt? Dann geht es weiter.

Schritt 2: Den Bereich auf die gesamte Tabelle ausdehnen

Rufen Sie jetzt mit “Start | Bedingte Formatierung | Regeln verwalten” den Manager für die Regeln auf. Sie sehen von rechts Ihre Regel mit der Bedingung (=$B$2=2), in der Mitte das  Format (Füllfarbe gelb) und rechts den Bereich, für den die Regel momentan gilt.

Bevor wir den Bereich auf A2 bis B6 ausweiten können, müssen wir die Bedingung anpassen.

Der Bezug ist momentan absolut $B$2 – es wird also immer B2 kontrolliert. Das darf aber nicht sein. Die Regel soll B2, B3, B4, B5 und B6 kontrollieren. Gemeinsam ist diesen Bezügen die Spalte B – nur sie darf also fixiert werden.

• Klicken Sie auf “Regel bearbeiten”
• Stellen Sie den Cursor in den Bezug $B$2
• Drücken Sie zweimal F4, bis Sie den Bezug auf $B2 geändert haben
• Bestätigen Sie die Änderung mit OK

Jetzt sind Sie wieder zurück im Manager für die Regeln und können den Bezug anpassen.

• Klicken Sie in die Zeile “Wird angewendet auf”
• Drücken Sie unbedingt F2 – das ist absolut wichtig, damit eine Bearbeitung der Bezüge möglich ist.
  Wenn Sie das vergessen, springen weitere Bezüge wild in die Zeile und Sie haben sehr kuriose Angaben wie z. B.
  =$A$4$A$4:$A$4
• Nachdem Sie F2 gedrückt haben, ergänzen Sie den Bereich
  =$A$2:$B$6
  Sie können auch alles nach dem =-Zeichen löschen und mit der Maus den gesamten Bereich markieren.
• Klicken Sie auf “Übernehmen” und kontrollieren Sie die Formatierung. Stimmt alles? Dann können Sie mit OK bestätigen.

Damit eine andere Zelle eingefärbt wird, verwendet man “Formel zur Ermittlung” und verweist auf die Zelle, die kontrolliert werden soll.

Damit viele Zellen richtig eingefärbt werden, darf die kontrollierte Zelle keinen absoluten Bezug haben.
Stellen Sie sich die Frage, welche Eigenschaft immer verwendet wird – immer die gleiche Spalte oder immer die gleiche Zeile? Diese Eigenschaft wird absolut gesetzt – die gleiche Spalte $B4.

Einen Schritt weiter und nochmal das gleiche Vorgehen.

Die gesamte Zeile von Spalte A bis Spalte G soll eingefärbt werden, wenn der Wert in Spalte G kleiner als 3 ist. Also für Anna und Sepp.

Klicken Sie wieder auf die erste Zelle des Bereiches – also A2. Erstellen Sie eine neue Regel und verwenden Sie “Formel zur Ermittlung der zu formatierenden Zahlen”. Die Formel lautet:

=$G$2<3

Formatieren Sie die Zelle mit einer Füllfarbe und bestätigen Sie alles mit OK. Kontrollieren Sie, indem Sie bei Max zwei x löschen – passt es? Dann erweitern Sie wieder den Bereich.

Rufen Sie den Regelmanager auf – was muss fixiert bleiben, was muss flexibel werden? Immer wird Spalte G kontrolliert, aber die Zeile ist variabel. Also wird geändert zu:
=$G2<3

Anschließend wird der Bereich im Regelmanager erweitert (F2 nicht vergessen!), so dass er jetzt lautet:
$A$2:$G$7

Kontrollieren Sie mit “Übernehmen”, dass alle Zeilen hinterlegt, die in Spalte G eine 2 stehen haben – passt? Dann mit OK bestätigen.

So – das war das Prinzip. Und dann können wir später den Abkürzungen widmen.