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.

Lerne noch mehr spannende Datenvisualisierungen kennen und werde Dashboard Master!

Spare dir unnötige Copy&Paste Aufgaben mit automatisierten Excel Dashboard, die deinen Chef begeistern. Setze ausdrucksstarke Datenvisualisierungen ein und aktualisiere diese mit wenigen Mausklicks! 

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: 10
  • #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

  • #4

    Alex H. (Dienstag, 17 September 2019 14:30)

    Hallo Artur,
    vorweg: SUPER Vorlage!!! Habe ich gleich in mein interaktives Diagramm (mehrere Produkte) eingebaut :-)
    Habe eine Frage zur Ermittlung der "Position der Datenbeschriftung":
    In deinem Bsp funktioniert das mit "+20", da sich die Werte alle auf einem ähnlichen Niveau bewegen.
    In meiner Anwendung variiert das. Mal ähnliches Niveau, mal sehr unterschiedlich. Da sich das Diagramm dann ensprechend von der Skallierung her anpasst, sieht es nicht immer gut aus. Ich habe es jetzt für mich mit einem Faktor (+10%) gelöst, aber zufrieden bin ich damit nicht sonderlich (sieht nicht einheitlich aus, da je nach Wertepaar 10% mal mehr und mal weniger ausmachen).
    Gibt es hierzu andere Ideen, wie man das lösen kann?
    LG Alex

  • #5

    Alex (Mittwoch, 15 April 2020 11:31)

    Hallo,

    die Vorlage ist perfekt für mein Vorhaben.

    Allerdings habe ich ein Problem. Ich habe Excel 2016. Bei dem Schritt die Werte der Positionsbeschriftung durch die Werte mit den Pfeilen zu ersetzen bietet mir Excel die Option "Werte aus Zellen" gar nicht erst an. Weiß jemand woran das liegen kann?

  • #6

    Dario (Montag, 21 Februar 2022 10:20)

    Gibt es schon eine Lösung wenn der Soll-Wert positiv ist aber der Ist-Wert negativ ist?
    Bei mir wird dann die Abweichung nur im Positiven angezeigt und überschreitet den eigentlichen Soll Wert.
    Danke :)

  • #7

    Daniel (Freitag, 18 März 2022 16:36)

    Tolles Tutorial, Danke!!!

  • #8

    Susanne (Samstag, 26 März 2022 22:37)

    Super Grafik ❣️
    Ich würde gerne einen Datenschnitt einfügen. Hast du eine Idee wie es funktionieren könnte?

  • #9

    Fiete (Mittwoch, 11 Mai 2022 11:49)

    Hey ich versuch das gerade nach zu bauen, allerdings fehlt mir der Schritt " Diagrammtyp ändern". Ich habe nur eine Auswahl bis Verbund. Kombi existiert bei mir leider nicht, deswegen komme ich nicht weiter.

    Hast du eine Idee?

  • #10

    Philip (Donnerstag, 23 November 2023 08:14)

    Tolle Darstellung ! Ist es möglich, die Darstellung der positive und negativen Abweichung zu tauschen ? Also dass die negative Abweichung nach unten und die positive nach oben über die Balken hinaus geht ?