Excel Diagramm mit Abweichungen: So erstellst du ein Varianzdiagramm

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:

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.

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! 

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

Die genaue Verformelung der 5 Hilfspalten sieht ihr hier:

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

Ü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)

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.

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

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.

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

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.

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:

• 5 Excel Formeln, die du kennen solltest
• 5 Excel Tricks für einen frühen Feierabend
• Der Kostenstellenbericht
• Interaktive Diagramme in Excel