Excel Diagramm mit Abweichungen: So erstellst du ein Varianzdiagramm

Excel Diagramm mit Abweichungen

Egal ob im privaten Leben oder auf der Arbeit, wir vergleichen den IST-Zustand immer mit einem Planwert. Dies lässt sich in Excel natürlich ganz easy auch in Tabellenform darstellen. Wer eher der visuelle Typ ist (bzw. sein Boss), der sollte auf ein Excel Diagramm zugreifen, dass Abweichungen zum Planwert leicht verträglich darstellt. Wir verwenden hierbei ein Säulendiagramm mit Varianz. In diesem Tutorial zeige ich euch, wie ihr am Ende zu diesem Resultat kommt:

Excel Diagramm mit Abweichungen

Das Säulendiagramm beinhaltet 3 Informationen für uns:

  • IST-Wert
  • Plan-Wert
  • Abweichung IST zu Plan

Wie so oft bei Excel Diagrammen gibt es hierfür keine fertige Einstellung. Das heißt es muss wieder ein wenig getrickst werden.

In der Vorlage findest du die fertige Lösung. Wenn du diese nicht herunterladen willst - kein Problem. Ich erkläre im Tutorial Schritt für Schritt, wie du zum fertigen Abweichungsdiagramm kommst. 

Ich empfehle dir die Vorlage vorab herunterzuladen, damit du mir genau folgen kannst.

Downloade die Vorlage bevor es losgeht

Download Vorlage Excel Diagramm mit Vorlage

Wenn du keine Lust zu lesen hast, schau dir doch das Video Tutorial an

Schritt 1: Aufbereitung der Daten

Bevor es ans Angemachte geht, müssen wir erst noch die bisher vorhandenen Daten (IST, Plan und Abweichungen) in ein Format bekommen, mit dem wir unser Diagramm füttern können. 

Hierzu benötigen wir 5 Hilfszeilen, die später einfach ausgeblendet werden können:

  • Abweichung pos.: weist nur positive Abweichungen aus
  • Abweichung neg.: weist negative Abweichungen aus
  • Transparent: Bildet im Diagramm die Säule, auf dem die Abweichung aufsetzt. Gemäß Ihrem Namen wir es im späteren Verlauf ausgeblendet
  • Datenbeschriftung: Weist die Abweichung im Diagramm aus
  • Position Datenbeschriftung: Bildet die Basis für die exakte Positionierung der Datenbeschriftung
Daten aufbereiten für Excel Diagramm Abweichungen darstellen

Die genaue Verformelung der 5 Hilfspalten sieht ihr hier:

Schritt 2: Datenbeschriftung formatieren

Damit wir die Datenbeschriftung auch mit den Pfeilen im Diagramm ausweisen können, müssen wir die Zellen formatieren.

Datenbeschriftung formatieren für Abweichungsdiagramm Excel

Über Rechtsklick -- Zellen formatieren gelangt ihr zu den Formatierungsoptionen. Wählt hier "Benutzerdefiniert" und tragt folgendes Format ein: ▲0;▼0;"-"

▲= ALT + 3 + 0 (gleichzeitig gedrückt)

▼= ALT + 3 + 1 (gleichzeitig gedrückt)

Schritt 3: Stapeldiagramm einfügen

Nachdem die Daten nun vorbereitet sind, können wir endlich mit der eigentlichen Aufgabe beginnen: Das Diagramm mit Abweichungen erstellen. Wir beginnen damit ein gestapeltes Säulendiagramm einzufügen und dabei gleich alle unnötigen Bestandteile wie Überschrift, Legende und Gitternetzlinien zu entfernen. Das Diagramm soll so clean wie möglich aussehen.

Gestapeltes Säulendiagramm einfügen

Schritt 4: Daten für das Diagramm auswählen

Wir ordnen dem Diagramm nun die Datenreihen zu. Lasst euch nicht von dem Durcheinander, das hierdurch entsteht, irritieren. In den nächsten Schritten werden wir das alles glattbügeln. Wir benötigen 5 Datenreihen:

  • IST
  • Transparent
  • Positiv
  • Negativ
  • Datenbeschriftung
Datenreihen für Diagramm mit Abweichungen in Excel anlegen

Schritt 5: Diagrammtyp anpassen

Bisher sehen wir nur ein normales Stapeldiagramm, das wir nun in ein Kombinationsdiagramm umwandeln und somit näher an unser Varianzdiagramm kommen. Hierbei arbeiten wir mit der Sekundärachse und einer Kombination aus Säulen -und Liniendiagrammen.

Diagrammtyp ändern

Schritt 6: Abstandsbreite anpassen

In diesem Schritt passen wir die Breite der Datenreihen an, die an der Sekundärachse ausgerichtet sind.

Schritt 7: Farben formatieren

So langsam kommen wir unseren Abweichungsdiagramm immer näher. Noch sehen jedoch die Farben noch ziemlich strange aus. Ich greife hier zum klassischen 3er Mix: Grau, Rot, Grün. 

Die Transparenz Datenreihe verschwindet hier nun, da sie mit der gleichen Farbe hinterlegt wird, wie die IST Datenreihe.

Farben formatieren im Excel Varianzdiagramm

Letzter Schritt: Datenbeschriftung einblenden und Liniendiagramm ausblenden

Im letzten Schritt müssen wir nur noch die Datenbeschriftungen einblenden und diese ein wenig formatieren. Zusätzlich blenden wir auch das Liniendiagramm aus, das für uns ja nur den Zweck der Datenbeschriftung erfüllt. Hierbei verwenden wir die Werte aus der Hilfszeile Datenbeschriftung für die Datenbeschriftung der Abweichungen.

Du hast es geschafft! War doch gar nicht so schwer. Speicher dir am besten das Diagramm als Vorlage ab, damit du in Zukunft nicht immer wieder alle Schritte durchgehen musst.

Falls du es noch nicht gemacht hast, hier kannst du die Excel Vorlage herunterladen:

Weitere Artikel

Kommentar schreiben

Kommentare: 3
  • #1

    Jens (Dienstag, 20 August 2019 14:53)

    Die Vorlage ist super, funktioniert aber nur mit Einschränkungen.
    Wenn in einer Periode die Plan-Werte positiv und die Ist-Werte negativ sind, ist die Darstellung fehlerhaft.

  • #2

    Nicole (Mittwoch, 21 August 2019 11:22)

    Idealerweise könnte man noch statt nur ein Produkt zu hinterlegt über eine Werteliste andere wählen, wobei dann die Ist/Plan Werte übernommen werden von anderer Stelle

  • #3

    Artur (Mittwoch, 21 August 2019 12:17)

    @Jens: Gebe ich dir Recht, sobald dieses Szenario eintrifft, wird das Diagramm schlecht nutzbar. Ich überlege mir mal, wie das besser gelöst werden kann

    @Nicole: Guter Vorschlag! Das wäre dann eine Verschmelzung zwischen dieser Vorlage und der Vorlage zu interaktiven Diagrammen. Dort zeige ich auf, wie man genau dies umsetzen kann.

    Beste Grüße