Wie man eine bedingte Formatierung in einem Diagramm erstellt

image   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: Diagramm-mit-bedingter-Formatierung Hier unsere fiktiven Umsatzwerte: image

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. image

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. image   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: image 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) image 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!

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

Comments

  • September 15, 2014

    […] Säulen die sich überlappen. Zu dieser Technik habe ich einen Beitrag veröffentlicht den Ihr hier lesen könnt der genauer auf das Thema […]

  • Hallo,

    die hier unter Schritt 3 beschriebene Vorgehensweise funktioniert bei mir nicht. Bei mir werden die #NV im Diagramm trotzdem angezeigt. Kann es an den Excel-Einstellungen unter Optionen liegen? Ist hier ggf. eine Änderung vorzunehmen?

    Danke und Gruß
    Daniel

    AnonymousJanuary 19, 2015
  • Hi Daniel,
    Die Excel Einstellungen sollten keinen Einfluss haben auf das ganze. Hast Du schon mal probiert ein Diagramm zu erstellen in dem Du nur die #NV Formel testest? Gruss, Lukas

    Lukas RohrJanuary 19, 2015
  • Guten Tag
    Was muss eingestellt werden, wenn anstelle der Werte Buchstaben für die X-Achse vorliegen, zB. für ein Rating A – E?

    Giovanni SenaFebruary 18, 2015
    • Da gibt es nur eine “Work around” Lösung. Buchstaben können in einer Zahlenskala nicht ausgewiesen werden. Deshalb müsste man den Buchstaben über eine Hilfsspalte einen Wert zuordnen und dann diesen ausweisen im Diagramm.

      Lukas RohrFebruary 20, 2015
  • Super Tipp, vielen Dank!! Genau das was ich gesucht habe.

    AnonymousApril 28, 2016

Leave a Reply