Die bedingte Formatierung ist eines meiner lieblings Werkzeuge in Excel! Man kann die bedingte Formatierung praktisch überall einsetzen: individuelle Zellen, ganze Bereiche, Tabellen, Pivot Tabellen… nur an einer Stelle, dort wo man eigentlich vermuten würde dass man es am ehesten finden würde, dort gibt es die bedingte Formatierung NICHT. Diagramme. Diagramme sind das Daten Visualisierungs-Tool schlechthin (und die bedingte Formatierung macht ja im Grunde genommen auch nichts anderes!) ABER genau DORT kennt Excel KEINE bedingte Formatierung! Trotzdem kann man eine bedingte Formatierung für Diagramme erstellen die sich auch dynamisch an die Werte anpasst! In diesem Artikel erkläre ich wie man das macht.

Speiseöl Umsätze im Vergleich

Die Erstellung einer dynamischen Formatierung für ein Diagramm lässt sich am einfachsten anhand eines Beispiels erklären. Wer das Diagramm und die Daten gleich in Excel anschauen will kann die Datei hier downloaden:

Hier unsere fiktiven Umsatzwerte:

Schritt 1: Erstellen eines normalen Säulendiagrammes

Anhand der obigen Werte erstellen wir ein ganz normales Säulendiagramm. Wir können bereits die gewünschte farblichen Formatierungen vornehmen und , falls in den Quelldaten nicht bereits gemacht, die Umsätze auf- oder absteigend sortieren damit die Verhältnisse schnell ersichtlich werden.

Schritt 2: Prozentuale Werte auf die zweite Achse verschieben

Als nächster Schritt berechnen wir die Prozentualen Wachstumswerte welche wir mit einer bedingten Formatierung darstellen wollen.   Damit wir eine bedingte Formatierung in Excel im Diagramm vortäuschen können müssen wir die Prozent Werte in zwei Datenreihen erfassen. Eine für die positiven Wachstumswerte welche wir grün machen in der Formatierung, und eine zweite, für die negativen werte die rot formatiert wird. Beide Datenreihen werden auf die zweite Achse verschoben. Damit jede Datenreihe nur die entsprechenden positiven oder negativen Werte anzeigt, benutzen wir eine WENN Formel die 0 zurück gibt. Würden wird as nicht tun würde einfach eine Datenreihe die andere überlappen und wir würden nur rot oder grün sehen… je nachdem welche Datenreihe weiter oben ist in den von Excel erstellen “Ebenen”. Die Formel in Zelle D4 im Beispiel sieht so aus: =WENN(C4/B4-1<0;0;C4/B4-1) Im Diagramm ergibt sich nun folgendes Bild: Nicht schlecht… aber die Nullwerte stören!

Schritt 3: Nullwerte für Prozente im Diagramm unterdrücken

Jeder Wert in der Datenreihe eines Diagrammes wird von Excel wiedergegeben. Selbst wenn der Wert kein Sinn macht! Ich könnte also einen Buchstaben anstelle einer Zahl in das Feld schreiben und Excel würde diesen dann im Diagramm einfach als 0 anzeigen! Was können wir also tun damit ein Wert in Excel unterdrückt / nicht angezeigt wird? Wir brauchen einen Wert der von Excel nicht wiedergegeben werden kann. Es gibt jedoch einen Wert welcher von Excel ignoriert wird: Der Fehlertyp #NV. Der Fehler #NV tritt auf, wenn bei einer Funktion oder einer Formel kein Wert verfügbar ist, also genau was wir suchen! Ein #NV Fehler wird im Diagramm nicht angezeigt! Diese Eigenschaft machen wir uns nun zu nutzen und setzen die Formel NV() an Stelle des 0 Wertes! Diese Formel gibt immer den Fehlerwert #NV zurück! Die Formel in Zelle D4 ist jetzt: =WENN(C4/B4-1<0;NV();C4/B4-1) Im Diagramm sieht man nun nur noch die positiven oder negativen Werte!

Schritt 4: Referenzlinie für die Prozentwerte erstellen

Damit die Prozentwerte nicht frei auf dem Blatt herumschweben wollen wir Ihnen noch eine Referenzlinie geben: die X-Achse für die Prozente. Diese fügen wir als gestrichelt formatierte Linie im Diagramm ein in dem wir eine weitere Datenreihe erfassen mit nur 0 Werten. Diese Datenreihe muss ebenfalls auf die zweite Achse verschoben werden! In der Beispieldatei habe ich diese Datenreihe “ZeroLine” genannt und die Werte direkt in der Datenreihe eingegeben ohne diese auf dem Tabellenblatt zu erfassen: =DATENREIHE(“ZeroLine”;Diagramm!$A$3:$A$9;{0\0\0\0\0\0\0};5)

Fertig!

Unser fertig erstelltes Diagramm in dem wir durch einen kleine Trick eine bedingte Formatierung simulieren!