Negative Abweichungen und Prozentwerte automatisch farblich kennzeichnen
Um negative Prozentzahlen z.B. in einem Plan-Ist-Vergleich schnell und leichter zu erkennen, können Sie die relevanten Zellen entweder mit einem speziellen Format versehen oder alternativ auch eine Regel für die Bedingte Formatierung erstellen. In diesem Tipp lesen Sie zunächst, wie Sie das spezielle Format anlegen, damit Ihre Tabelle nicht so aussieht:
Bild 1: Negative Abweichungen werden nicht farblich hervorgehoben
Es empfiehlt sich die folgende Variante, damit die Augen die Abweichungen schneller erfassen können und der Blick auf das Wesentliche fällt:
Bild 2: Negative Abweichungen werden rot dargestellt
Um negative Abweichungen für absolute Zahlen farbig darzustellen, markieren Sie die gewünschten Zellen oder den Zellbereich und wählen (z.B. im Kontextmenü der rechten Maustaste) den Menüpunkt „Zellen formatieren …“.
In dem sich öffnenden Fenster wählen Sie als Kategorie in der Liste den letzten Punkt „Benutzerdefiniert“ und als Typ geben Sie das folgende Format ein:
#.###. "T€";[Rot]-#.###. "T€"
Bild 3: Benutzerdefiniertes Format für absolute Zahlen im T€-Format
Um negative Abweichungen für Prozentwerte farbig darzustellen, markieren Sie die gewünschten Zellen und wählen „Zellen formatieren …“.
Als Kategorie wählen Sie „Benutzerdefiniert“ und als Typ geben Sie folgendes ein:
#.##0,0 %;[Rot]-#.##0,0 %
Bild 4: Benutzerdefiniertes Format für Prozentabweichungen
Somit werden jetzt alle negativen Abweichungen in den beiden Spalten rot dargestellt:
Bild 5: Negative Abweichungen werden rot dargestellt
Diese neuen benutzerdefinierten Formate werden direkt in dieser Arbeitsmappe gespeichert. D.h., wenn Sie das Format erneut an einer anderen Stelle in einem Tabellenblatt dieser Arbeitsmappe zuweisen möchten, taucht es in der Liste bereits als Typ auf. In anderen Arbeitsmappen können Sie sich diese benutzerdefinierten Formate nur verfügbar machen, wenn Sie mit Vorlagen arbeiten (.xltx).
Möglichkeit 2: Bedingte Formatierung
Eine weitere und noch viel umfassendere und weiterreichende Möglichkeit, die negativen Werte darzustellen ist die Verwendung einer Bedingten Formatierung. Mit einer entsprechenden Formatierungsregel können Sie die negativen Werte dann z.B. auch noch zusätzlich in fetter Schrift darstellen oder gleich die ganze Zelle farblich einfärben.
Markieren Sie hierzu zunächst wieder die gewünschten Zellen oder den Zellbereich mit den Abweichungen. Für unser Beispiel markieren wir zunächst alle Zellen mit den absoluten Abweichungen und wählen dann im Register Start in der Gruppe Formatvorlagen im Kontextmenü für die Bedingte Formatierung den Menüpunkt Neue Regel ….
Bild 6: Neue Regel für die Bedingte Formatierung erstellen
In dem sich öffnenden Fenster Neue Formatierungsregel wählen Sie den Regeltyp Nur Zellen formatieren, die enthalten aus. Die notwendige Regelbeschreibung lautet:
Nur Zellen formatieren mit Zellwert kleiner als Null.
Bild 7: Zellen formatieren mit Zellwert kleiner als Null
Ein Klick auf Formatieren öffnet das bekannte Fenster Zellen formatieren, wo jetzt im Register Schrift die Farbe Rot ausgewählt werden kann. Nach Bestätigung mit OK schließen Sie das Formatierungsfenster und mit einer weiteren Bestätigung mit OK schließen Sie die Bedingte Formatierung ab. Die negativen absoluten Abweichungen werden jetzt rot dargestellt:
Bild 8: Negative absolute Abweichungen sind rot formatiert
Um auch die prozentualen Abweichungen entsprechend bedingt zu formatieren, rufen Sie die neu erstellte Regel wieder auf: Hierzu klicken Sie im Menü Bedingte Formatierung auf Regeln verwalten...:
Bild 9: Aufruf einer bestehenden Regel
Es folgt der Manager für die bestehenden Regeln zur bedingten Formatierung. In dem Fenster werden immer alle bestehenden Regeln für die aktuelle Zellauswahl dargestellt, in unserem Fall nur die eben erstellte. Hier können Sie ansonsten auch andere Regeln für andere Zellbereiche und auch andere Arbeitsblätter der geöffneten Arbeitsmappe bearbeiten.
Bild 10: Manager für die Regeln zur bedingten Formatierung
Für unsere bestehende Regel wird angezeigt, auf welche Zellen bzw. Zellbereiche sie angewendet wird, also bisher auf H7 bis H26. Da wir nun zusätzlich auch die prozentualen Abweichungen formatieren möchten, erweitern wir in der bestehenden Regel einfach den Bereich um die entsprechenden Zellen J7 bis J26. Mit dem Semikolon ergänzen wir in der Formel den zweiten Bereich und tragen dahinter diesen neuen Bereich mit absoluten Bezügen ein:
Bild 11: Erweiterung der bestehenden Regel
Nach Bestätigung der Eingaben werden jetzt auch die Prozentwerte rot dargestellt, sofern sie negativ sind:
Bild 11: Alle negativen Abweichungen sind rot formatiert
Im Gegensatz zu den oben zuerst gezeigten speziellen Formaten sind die Möglichkeiten mit der Bedingten Formatierung vielfältiger. Sie können nicht nur das Zahlenformat bearbeiten, sondern die gesamte Zelle in Ihrer Darstellung verändern. Also z.B. auch die Schriften und Schriftgrößen ändern, Werte fett darstellen oder Zellen farbig ausfüllen.
Das hier angewandte Excelbeispiel Plan-Ist-Vergleich finden Sie bei unseren Berichtsvorlagen.
In Kooperation mit ...
Seminartipp:
weitere Seminartipps ...
Startseite > Excel-Tipps und -Tricks > Exceltipp 170
