14. Türchen: Bedingte Formatierung mit Formeln

In den ersten beiden Beiträgen unseres Adventskalenders haben wir gesehen, wie man mit der bedingten Formatierung arbeitet. Da diese Excel-Funktion noch viele praktische Anwendungsfälle für uns bereit hält, möchten wir heute noch so einen Fall vorstellen.

Oft haben wir bei großen Datenmengen das Problem, schnell ein richtiges Gefühl für einen Wert zu erhalten. Dies ist bspw. beim Verhältnis von Einkaufs- zu Verkaufspreis der Fall. Um sich hier schnell und unaufdringlich eine visuelle Hilfe zu schaffen, ist eine bedingte Formatierung die erste Wahl.

Es wäre doch praktisch, wenn uns die Preisspalte rot angezeigt wird, wenn die Marge unter bspw. 20% liegt oder grün, wenn sie 60% übersteigt.

Um das zu erreichen, wechseln wir in die Preisspalte. Danach klicken wir im Ribbon Bedingte Formatierung > Neue Regel .

In dem Dialog wählen wir als Regeltyp Formel zur Ermittlung der zu formatierenden Zelle verwenden . Die Formel die wir aufbauen muß immer ein Ergebnis liefern das WAHR oder FALSCH ist, wie so eine Formel gebildet wird, haben wir in dem Beitrag über die WENN-Formel kennen gelernt.
In unserem Beispiel ermitteln wir zuerst die Marge, die Formel sieht in der Theorie so aus (Verkaufspreis-Einkaufspreis)/Verkaufspreis . Übertragen auf unsere Felder ist das ( (L3-M3)/L3 ) . Um jetzt nur ein Ergebnis vom Typ WAHR oder FALSCH zu erhalten verwenden wir den Operator > und setzen die 60% in die Formel ein. ( (L3-M3)/L3 > 60% ) .

Wichtig, wenn mit der Maus die Zellen ausgewählt werden, muß die absolute Formatierung ( $ – Zeichen ) entfernt werden, um die Formel später auf andere Zellen übertragen zu können . Danach nur noch über den Knopf Formatieren … eine passende Formatierung auswählen, hier ein grüner Hintergrund.

Um auch die zu geringen Margen hervor zu heben, gehen wir die beschriebenen Schritte nochmals durch. Für die Formel verwenden wir aber den Operator < und setzen 20% in die Formel ein ( (L3-M3)/L3 < 20% ) . Als Formatierung wählen wir einen roten Hintergrund mit weißer Schrift.

Im letzten Schritt wenden wir die Formatierung noch auf die ganze Spalte an und sehen auf einen Blick, welche Produkte über- oder unterdurchschnittliche Margen haben.