Excel-TippBedingte Formatierung in Diagrammen

Mit Hilfsspalten können Sie Säulen oder Balken in Ihren Excel-Diagrammen abhängig vom dargestellten Wert einfärben. So können Sie beispielsweise mit Ampelfarben arbeiten und Ihre Botschaft mit dem Diagramm besser hervorheben.

Die bedingte Formatierung ist ein sehr nützliches Feature in Excel, mit dem Sie Daten in Abhängigkeit von ihren Werten dynamisch hervorheben können. Leider können Sie die bedingte Formatierung nicht in Diagrammen einsetzen. Mit dem folgenden Trick können Sie Ihre Daten auch in Diagrammen ähnlich wie bei der bedingten Formatierung einfärben.

Beispiel: Diagrammfarbe abhängig von der Umsatzhöhe

Die folgenden Umsatzdaten sollen als Säulendiagramm dargestellt werden. Die Säulen sollen hierbei dynamisch wie folgt eingefärbt werden:

  • grün – Umsatz > 2.000
  • gelb – Umsatz > 1.000 und <= 2.000
  • rot – Umsatz <= 1.000
Ausgangsdaten für Diagramm mit bedingter Formatierung

Mit Hilfsspalten arbeiten

Um ein Säulendiagramm mit „bedingter Formatierung“ zu erstellen, müssen Sie für jede Farbe eine separate Datenreihe erstellen (Hilfsspalten). Es werden dann nur die Datenreihen im Diagramm dargestellt, für die der Wert zutrifft.

Erfassen Sie daher rechts neben der Ausgangstabelle in dem Bereich D2:F2 die folgenden Formeln:

  • Zelle D2 – Datenreihe für grüne Säule: =WENN(B2>2000;B2;NV())
    Die Formel besagt: Wenn der Umsatz in München (B2) größer als 2000 ist, dann wird der Umsatz als Wert ausgegeben. Sonst wird der Fehlerwert #NV als Ergebnis ausgegeben. Der Fehlerwert #NV bewirkt in Diagrammen, dass keine Darstellung erfolgt.
  • Zelle E2 – Datenreihe für gelbe Säule: =WENN(UND(B2>1000;B2<=2000);B2;NV())
    Hier gilt die Bedingung: Der Umsatz in München (B2) muss größer als 1000 und kleiner oder gleich 2000 sein; dann wird er als Wert ausgegeben. Sonst wird der Fehlerwert #NV als Ergebnis ausgegeben.
  • Zelle F2 – Datenreihe für rote Säule: =WENN(B2<=1000;B2;NV())
    Sollte der Umsatz in München kleiner oder gleich 1000 sein, dann wird der Umsatz – sonst der Fehlerwert #NV() dargestellt.

Sie erhalten somit den Umsatzwert immer in genau einer Spalte dargestellt. In den anderen beiden Spalten wird der Fehlerwert #NV ausgegeben.

Formel für Datenreihen erfassen

Als nächstes kopieren Sie die Formeln für die anderen Filialen entsprechend nach unten, damit Sie für jede Filiale einen Wert in den Hilfsspalten darstellen. Hinweis: Der Umsatzwert darf genau einmal in den Hilfsspalten pro Filiale erscheinen. In den anderen beiden Hilfsspalten muss der Fehlerwert #NV ausgegeben werden.

Formeln für alle Filialen unten kopieren

Das dynamische Diagramm erstellen

Markieren Sie jetzt den kompletten Datenbereich der Hilfsspalten (D1:F6) inklusive der ersten Zeile mit den Spaltenbeschriftungen (grün, gelb, rot) und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Einfügen > Befehlsgruppe Diagramme > Befehl Säulen- oder Balkendiagramme einfügen > Befehl Gestapelte Säulen.

Gestapelte Säulen einfügen

Excel erstellt automatisch ein gestapeltes Säulendiagramm als separates Objekt. Das Diagramm schaut auf den ersten Blick schon sehr gut aus, da grundsätzlich immer nur eine Datenreihe dargestellt wird. Die anderen beiden Datenreihen, die nicht sichtbar sind, haben als Wert den Fehlerwert #NV als Quelle. Sie können dies an den unterschiedlichen Farben erkennen. Allerdings stimmen die Farben nicht mit den gewünschten Ampelfarben überein.

Säulen werden je nach Wert unterschiedlich eingefärbt

Die gewünschten Diagrammfarben einstellen

Im nächsten Schritt müssen Sie die Farben der Säulen anpassen. Klicken Sie einmal mit der linken Maustaste auf die erste Säule im Diagramm. Hierdurch markieren Sie die Datenreihe für die graue Säule. Sie können dies an den Markierungspunkten im Diagramm erkennen, die nur die grauen Säulen im Diagramm umranden.

Datenreihe für graue Säule markieren

Drücken Sie jetzt als nächstes die Tastenkombination Strg + 1. Durch diese Tastenkombination wird am rechten Rand der Aufgabenbereich Datenreihen formatieren eingeblendet. Wählen Sie hier oben das Symbol für Füllung und Linie aus und aktivieren Sie anschließend das Optionsfeld Einfarbige Füllung. Unter Füllfarbe wählen Sie die Farbe Rot aus, da die ausgewählte Datenreihe dieser Ampelfarbe (<=1000) entspricht. Welche Säule welche Farbe erhalten soll, sehen Sie auch mithilfe der Legende (grau = rot).

Füllfarbe rot auswählen

Aufgrund dieser Einstellung wird die markierte Datenreihe (Säulen) automatisch rot eingefärbt.

Datenreihe mit der Farbe Rot formatieren

Wiederholen Sie diese Schritte jetzt für die anderen beiden Hilfsspalten (Säulen), indem Sie diese grün und gelb einfärben.

Säulen mit Ampelfarben versehen

Wenn Sie jetzt die Umsatzwerte in der Spalte B verändern, dann passen sich die Säulen und deren Farben entsprechend dynamisch an.

Beschriftung richtig einstellen

Die horizontale Achsenbeschriftung im Diagramm stimmt allerdings noch nicht, da hier nicht die Namen der Städte angezeigt werden. Diese Beschriftung können Sie mit wenigen Klicks anpassen. Vergewissern Sie sich, dass das Diagramm im Tabellenblatt ausgewählt ist, und aktivieren Sie im Menüband die Befehlsfolge Kontextbezogene Registerkarte Diagrammentwurf > Befehlsgruppe Daten > Befehl Daten auswählen.

Befehl Daten auswählen aktivieren

Es öffnet sich das Dialogfeld Datenquelle auswählen. Klicken Sie hier auf die Schaltfläche Bearbeiten.

Schaltfläche Bearbeiten auswählen für horizontale Achsenbeschriftung

Es wird das Dialogfeld Achsenbeschriftungen geöffnet. Erfassen Sie hier unter Achsenbeschriftungsbereich den Bereich, in dem die Stadtnamen stehen. Dies ist im Beispiel der Bereich A2:A6.

Bereich für Achsenbeschriftung erfassen

Schließen Sie alle offenen Dialogfelder durch Klick auf Ok. Sie erhalten jetzt die Namen der Städte als horizontale Achsenbeschriftung angezeigt. Ändern Sie den Stadtname in der Spalte A, dann wird der Name im Diagramm ebenfalls geändert.

Umsatzaufstellung mit Ampelfarben

Im letzten Schritt vergeben Sie nun noch einen aussagekräftigen Diagrammtitel. Klicken Sie mit der linken Maustaste auf dem Diagrammtitel, so dass dieser ausgewählt ist. Durch einen erneuten Klick mit der linken Maustaste auf dem Diagrammtitel wird der Bearbeitungsmodus aktiviert. Sie können jetzt mit der Tastatur einfach einen Titel erfassen.

Und wenn Sie die Legende (unten) für überflüssig halten, klicken Sie diese an und entfernen (Entf) Sie die Legende.

Dazu im Management-Handbuch

Weiterlesen

Vorlagen nutzen

Excel-Tipps