Bedingte Formatierung – Excel (Microsoft Office)

Die Bedingte Formatierung in Excel kann super hilfreich sein aber leider auch super nervig, wenn man nicht weiß wie. So zeige ich heute ein kleines Beispiel dazu.

bedingte Formatierung

bedingte Formatierung

Mit der bedingten Formatierung können Zellen eingefärbt werden wenn bspw. ein bestimmter Wert erreicht wird, oder wenn ein konkreter Wert angenommen wird. Aber wie funktioniert die bedingte Formatierung?

» Siehe auch Teil 2: Bedingte Formatierung mit Symbolen (Ampelfarben, Häkchen und Kreuze) «

Video-Tutorial am Ende des Beitrags

Beispiel: Bedingte Formatierung

Ich möchte eine kleine Liste erstellen in der pro Zeile drei Informationen erfasst werden „Monat, Soll und Ist. Jeden Monat möchte ich eine bestimmte Anzahl an Stunden arbeiten diese werden in Soll eingetragen und in die Ist-Spalte trage ich dann ein wie viel Stunden ich schon gearbeitet habe.
Um eine bessere Übersicht zu bekommen sollen die Ist Zellen bedingt formatiert werden. Dazu sollen drei Regeln geschaffen werden (Wie im Bild oben):

  • 1. Wenn noch nichts gearbeitet wurde (Inhalt Null oder nichts) dann soll die Zelle rot markiert werden.
  • 2. Wenn schon etwas gearbeitet wurde aber noch nicht alles, dann soll die Zelle gelb markiert werden
  • 3. Wenn genug gearbeitet wurde (Soll Stunden wurden erreicht) soll die Zelle grün markiert werden
  • 4. Randbedingung ist natürlich, dass wenn in der Soll-Spalte nichts steht oder Null, auch keine Farbe eingesetzt wird
[ContentAd]

Bedingte Formatierung

Jetzt gibt es viele Wege die bedingte Formatierung einzurichten. Ich mag am liebsten den Weg, dass man seine Formeln selber schreibt. Aber zuerst markieren wir die betroffene Zelle (in meinem Beispiel C2) und öffnen das Menü für die Bedingte Formatierung:

bedingte Formatierung öffnen

bedingte Formatierung öffnen

Regeln Verwalten wählen

bedingte Formatierung Regeln verwalten

bedingte Formatierung Regeln verwalten

Anschließend wählen wir für jede der vier Regeln aus dem Beispiel eine neue Regel und wählen anschließend die unterste Option aus „Formel zur Ermittlung der formatierenden Zellen verwenden“. In der Formatierung wird dann einfach die Hintergrundfarbe mit angegeben.

Die Formeln:

Vorher erkläre ich das Prinzip. Jetzt haben wir die Möglichkeit Formeln zu beschreiben, welche unsere Regeln abbilden. Dazu nehme ich die UND und ODER Formel her. Im UND kann ich Bedingungen angeben die alle zutreffen müssen um erfüllt zu sein, im ODER braucht nur eine der angegebenen Bedingungen erfüllt werden. Lasst euch von den letzten Sätzen nicht verunsichern, das wird im folgendem klarer.

Grüne Markierung:

bedingte Formatierung grün

bedingte Formatierung grün

Die einfachste Bedingung ist die wenn alle Stunden abgeleistet wurden. Wir vergleichen einfach, ob die ist IST-Spalte größer gleich der SOLL-Spalte ist.

Gelbe Markierung:

bedingte Formatierung gelb

bedingte Formatierung gelb

Hier kommt die UND-Formel zum tragen. Denn beide Bedingungen müssen erfüllt sein. Wenn IST größer Null und wenn IST kleiner SOLL.

Weiße Markierung:

bedingte Formatierung weiß

bedingte Formatierung weiß

Hier kommt die ODER-Formel zum tragen. Diese wird erfüllt wenn eine der zwei Bedingungen erfüllt wird. In diesem Fall wenn in der SOLL-Spalte nichts steht oder Null, dann ist uns relativ egal was im IST steht, wir markieren immer mit Weiß.

Rote Markierung:

bedingte Formatierung rot

bedingte Formatierung rot

Hier wird es etwas schwieriger im Grunde machen wir einfach eine verkettete UND-Formel. Hier muss SOLL Beschrieben sein (B2<>““) und SOLL größer NULL sein und es muss eine der zwei Bedingungen IST = NULL oder IST leer sein. Dann wird rot markiert.

Fertig

bedingte Formatierungen Regeln

bedingte Formatierungen Regeln

Duplizieren

bedingte Formatierung duplizieren

bedingte Formatierung duplizieren

Anschließend kann die eine Formel über das kleine Viereck rechts unten an der Markierung über alle Zellen gezogen werden, und schon hat man die Formel auf alle Zellen duplizieren.

Beispiel Download

Anbei die fertige Excel-Datei zum Download bedingte Formatierung Stunden.xlsx

Video Tutorial

MrKnowing

Programmierer und Wissensnerd! Kontaktiere mich auf Google+ oder einfach per Mail danny@mrknowing.com

Das könnte dich auch interessieren …

2 Antworten

  1. Petra sagt:

    Hallo,
    ich habe ein Excel-Liste mit einer Spalte „Lieferdatum“. Mit Hilfe eines Ampelsystems möchte ich auf einen Blick sehen, wenn ich meine Liste öffne, welche Lieferungen noch nicht fällig sind (grün), welche schon kritisch (gelb) und welche schon überfällig sind (rot).
    Bitte um eine kurze Hilfestellung. Danke LG Petra

  1. 19. Februar 2014

    […] mit Symbolen (Ampel, Haken, Kreuze) statt Einfärbungen, funktioniert im Prinzip wie die Bedingte Formatierung aus Teil 1. Dazu werden lediglich drei Bereiche angelegt. Ein Bereich bspw. größer als 50 (grüner Haken), […]

Schreibe einen Kommentar

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