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.

BedFormel1

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.

BedFormel2

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

BedFormel3

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.

BedFormel4

Die Tabelle sieht nun so aus:

BedFormel5

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.

BedFormel6

Nun hast Du beide Abfragen sichtbar gemacht.

BedFormel7

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

BedFormel8

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

BedFormel9

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:

BedFormel10

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)

BedFormel11

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.

BedFormel12

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

2 Kommentare zu „Bedingte Formatierung in Excel mit einer Formel“

  1. Hallo Gerhard,
    das Thema „bedingte Formatierung“ scheint derzeit wirklich überall aktuell zu sein (ob nun bei dir oder beim tabellenexperte mit Pivot). Als kleine Ergänzung mag ich zum Beispiel Formeln das von mir gerne genutzte Maximal- und Minimalwert eines Bereiches erwähnen.
    Hier ist es hilfreich per MIN() und MAX() die Daten entsprechend einzufärben. Aus leidvoller Erfahrung habe ich dann auch lernen dürfen, dass die Formel UND praktisch ist um zu überprüfen ob in der jeweiligen Zelle überhaupt ein Wert ist. Siehe https://www.andreas-unkelbach.de/blog/?go=show&id=757 :-). Da ich gerade einmal wieder an einer vergleichenden Tabelle sitze musste ich hier entsprechend schmunzeln und wünsche dir noch eine kreative Woche.
    Viele Grüße
    Andreas

  2. Hallo Andreas,
    danke für Deinen Kommentar.
    Na klar, man könnte ein solches Thema endlos weiterbearbeiten, denn Formeln und Fallbeispiele gibt es wohl genug.
    Deinen Tipp zu UND, Min und Max sehe ich deshalb als wertvolle Ergänzung zu meinem Beitrag.
    Dir auch eine erfolgreiche Woche mit vielen guten Ideen.
    Gerhard.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s