Bedingte Formatierung in Excel mit einer Formel

Zur bedingten Formatierung in Excel konntest Du im Blog bereits mehrere Artikel lesen. Beispielhaft waren das die Beiträge „Mit 17 Symbolsätzen in Excel bedingt formatieren„, „Bedingte Formatierung: Alle Zellen basierend auf ihren Werten formatieren“ und „Bedingte Formatierung: Nur obere oder untere Werte formatieren„.

In diesem Beitrag geht es nun um die bedingte Formatierung nach der Regel „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

Durch die Bestimmung einer Regel mit einer Formel hast Du faktisch unbegrenzte Möglichkeiten, Zellen hervorzuheben, denn Formeln kannst Du in unbegrenzter Anzahl erstellen. Ein paar Beispiele sollen Dir verdeutlichen, was alles mit dieser Regel umgesetzt werden kann.

1. Einen zu formatierenden Datenbereich erstellen

Um die Wirkung dieser Regel zu demonstrieren, bedarf es einer Tabelle mit Werten.

Dargestellt sind die 12 Monate eines Jahres, die Plandaten, die Istdaten und die Abweichungen Ist ./. Plan.

2. Negative Abweichungen hervorheben

Um die negativen Abweichungen hervorzuheben, markierst Du den Bereich E3:E14.

Gehe im Menü Start auf Formatvorlagen / Bedingte Formatierung und wähle „Neue Regel“.

Wähle nun „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ und schreibe in die Regelbeschreibung die Formel

=$E3<0

Klicke dann auf „Formatieren“. Entscheide Dich für eine fette rote Schrift oder fülle die Zelle farbig.

Nachdem Du mit OK abgeschlossen hast, sind die negativen Abweichungen hervorgehoben.

Positive Abweichungen hebst Du mit der Formel

=$E3>0

hervor.

3. Hervorheben, wenn Istwerte kleiner Planwerte sind

Markiere hierzu den Bereich D3:D14 und gehe wie unter 2. vor.

Als Formel schreibst Du diesmal

=$D3<$C3

Die betreffenden Zellen sollen mit fetter roter Schrift hervorgehoben werden.

Die Tabelle sieht nun so aus:

Sollen die Istwerte hervorgehoben werden, die größer als die Planwerte sind, verwendest Du die Formel

=$D3>$C3

Wähle hier am besten eine andere Schriftfarbe.

4. Hervorheben, wenn Istwerte kleiner und größer Planwerte sind

Markiere wieder den Bereich D3:D14.

Für die Abfrage musst Du zwei Regeln festlegen.

In der ersten Regel schreibst Du die Formel

=$D3<$C3

mit fetter roter Schrift, in der zweiten Regel schreibst Du

=$D3>$C3

mit z.B. fetter blauer Schrift.

Im Regelmanager siehst Du die eingestellten Regeln. Klicke auf OK.

Nun hast Du beide Abfragen sichtbar gemacht.

5. Hervorheben, wenn die Abweichung größer als Pi * 100 ist

Eine unsinnige Abfrage, wirst Du denken. Du hast recht, aber auch das ist machbar.

Markiere dazu den Bereich E3:E14 und schreibe als Formel

=$E3>PI()*100

Wähle eine Ausfüllfarbe aus und bestätige mit OK. Dies ist das Ergebnis:

Du kannst eine Abfrage ebenso auf eine andere Datei beziehen und z.B. wollen, dass die positiven Abweichungen mit gelber Ausfüllfarbe hervorgehoben werden, wenn z.B. die Summe der positiven Abweichungen im Vorjahr kleiner als die Summe der positiven Abweichungen im aktuellen Jahr. Vorjahreswert in Tabelle3, aktuelle Werte in Tabelle2.

Dies könnten die Vorjahreswerte sein:

Die Summen der positiven Abweichungen betragen

• im Vorjahr 25
• im aktuellen Jahr 1.758

Die Formel könnte etwa so aussehen:

=SUMMEWENN(Tabelle3!E3:E14;“>0″;Tabelle3!E3:E14)<SUMMEWENN(Tabelle2!E3:E14;“>0″;Tabelle2!E3:E14)

Fest steht, dass die Summe der positiven Abweichungen im Vorjahr kleiner als im aktuellen Jahr ist.

25 < 1758

Der Bereich E3:E14 in der aktuellen Jahrestabelle wird somit hervorgehoben, im Beispiel grün ausgefüllt.

Du siehst, mit der Formel-Regel in der bedingten Formatierung lässt sich viel machen.