Excel Archive | chartisan

Excel – Zahlenformate richtig nutzen

Viele Wege führen zum Ergebnis. Im Alltag kann es zwecks Fehler und Frustvermeidung hilfreich sein, eine Abkürzung zu kennen. Die detailliertere Kenntnis der unterschiedlichen Formatierungsoptionen in Excel ist eine erhebliche Vereinfachung.

Viele Aufgaben können durchaus mit der angebotenen Standardformatierung bewältigt werden. Doch gerade im Reporting ist es häufiger notwendig, lange Zahlenformate zu kürzen, eigene Einheiten oder Farben zu verwenden. Hier helfen Ihnen die „benutzerdefinierten Zahlenformate“.

Die Formatierung der Zahlenformate erreichen Sie entweder über das Menü, das Kontextmenü der jeweiligen Zelle oder mit dem Shortcut STRG+1. (Anmerkung: Shortcuts in Excel sind auch prima Abkürzungen 😊). In der Kategorienliste finden Sie als letzten Punkt „benutzerdefiniert“.

Diese Punkte helfen Ihnen schnell und zielgerichtet benutzerdefinierte Zahlenformate zu erstellen:

Die Formatierungszeile ist immer nach demselben Schema aufgebaut: positive Werte, negative Werte, Null, Text – die einzelnen Werte werden durch ein Semikolon getrennt

Farbbezeichnungen müssen jeweils in eckigen Klammern vorangestellt werden, z.B. [rot]

Standardmäßig können folgende Farben angegeben werden: schwarz, grün, weiß, blau, magenta, gelb, zyan, rot

Texte (z.B. Einheiten) sind immer in Anführungszeichen zu setzen, z.B. “km“

Das Rautensymbol # dient als Platzhalter für eine Zahl, wobei Nullen nicht angezeigt werden

Sollen ganze Zahlen angezeigt werden, geben Sie “0“ ein, z.B. “0,0“ zeigt Zahlen mit einer Nachkommastelle an; nicht angezeigte Nachkommastellen werden automatisch gerundet

Mit dem Sternsymbol * wird das nachfolgende Zeichen solange wiederholt bis die komplette Zelle gefüllt ist

Zu guter Letzt: Sie können Bedingungen auch in eckige Klammern setzen, z.B. [>10] hier wird das nachfolgend definierte Zahlenformat nur für Werte >10 angewendet

Mit der Anwendung dieser Basisfakten gelingt es Ihnen mühelos Ihr Datenmaterial bedarfsgenau zu formatieren und Alltagsfrust durch „ewiges Geklicke“ zu sparen.

Wer Näheres wissen möchten, dem empfehle ich auch gern den Artikel „1×1 der Zahlenformatierung in Excel“ von Robert Lochner, Linearis GmbH.

1×1 der Zahlenformatierung in Excel

Auf jeden Fall wünsche ich Ihnen Happy Reporting!

Ihre Silja Wolff

von Silja Wolff

Portfolio Diagramme “reloaded”

Knowledge

Portfolio-Darstellungen für viele Anwendungsfälle können in Excel mit dem Diagrammtyp “Blase” erstellt werden. Das Besondere an diesem Diagrammtyp ist, dass insgesamt drei Datendimensionen visualisiert werden: X-Position (horizontal), Y-Position (vertikal) sowie die Blasengröße (Fläche).

Portfolios kommen vor allem dann zum Einsatz, wenn eine Datenmenge segmentiert werden soll. Bei einem Investitionsportfolio können beispielsweise auf den Achsen Kennzahlen wie Rentabilität und Risikoindex aufgetragen werden, während die Blasengröße das Investitionsvolumen repräsentiert. Außerdem gehören Produkt-Markt-Betrachtungen, Mitarbeiterperformance und strategisches Projektmanagement zu typischen Einsatzgebieten für Portfolios. In allen diesen Fällen ermöglicht die Visualisierung eine leicht erkennbare Untergliederung der Daten (Produkte, Projekte, etc.) in bestimmte Gruppen, verbunden mit einer Bewertung in gut/schlecht bzw. erwünscht/unerwünscht. So können beispielsweise Projekte mit hohem Risiko und geringer Rendite auf einen Blick identifiziert werden.

Wie alle Diagramme sollen auch Portfolios prägnant und aussagekräftig sein. Leserfreundliche Beschriftungen und gezielte Hervorhebungen sind dafür besonders wichtig. Leider sind die Standardfunktionen der Excel-Blasendiagramme in dieser Hinsicht recht eingeschränkt. Daher bedienen wir uns wieder einiger guter Ideen um mit wenig Aufwand eine professionelle und aussagekräftige Abbildung zu erhalten. Die folgenden Arbeitsschritte zeigen wie es geht.

Blasendiagramm mit einer einzelnen Datenreihe erstellen (Ländernamen dürfen nicht mit markiert werden)
Achsenlänge und Gitternetzlinien nach Bedarf einstellen
Datenreihe grau einfärben (neutral, ohne Hervorhebung)

Formeln für automatisches Ein-/Ausblenden unterschiedlich gefärbter Datenreihen (grau, rot, grün) entwickeln, Ausblenden erfolgt mittels #NV

Datenquelle der ersten Datenreihen ändern, so dass die Blasengröße aus dem zuvor berechneten Formelbereich verwendet wird
zwei zusätzliche Datenreihen mit den entsprechenden Quellbereichen für rot und grün einfügen und formatieren, in jeder dieser Datenreihen sind nur einige Blasen sichtbar

Tipp: Blasen mit einer halbtransparenten Füllung und weißer Rahmenlinie sind auch dann gut erkennbar, wenn sich mehrere Datenpunkte überschneiden.

eine zusätzliche Datenreihe auf Basis des ursprünglichen Quellbereiches einfügen und unsichtbar formatieren (keine Füllung, keine Linie), alle Blasen dieser Datenreihe sind sichtbar
Datenbeschriftung für diese Datenreihe einblenden und mit den Zellen verknüpfen in denen die Ländernamen stehen

Tipp: Das Verknüpfen funktioniert wieder mit der Methode aus dem Artikel “Clevere Datenbeschriftungen”. Speziell beim Blasendiagramm ist es am besten, zuerst eine der Datenbeschriftungen anzuklicken und dann die Cursortaste (Pfeil) rechts zu drücken. Dadurch wird genau das erste Beschriftungsfeld markiert, das sich dann mittels “=” und Mausklick mit dem ersten Ländernamen verknüpfen lässt. Dann geht es mit Cursortaste rechts zum zweiten Beschriftungsfeld und so weiter. Da eine “Rubrikenbeschriftung” im Blasendiagramm nicht vorgesehen ist, sind die selbst erstellten, verknüpften Beschriftungen die einzige Möglichkeit. Seit Excel-Version 2013 gibt es die Beschriftungsoption “Wert aus Zellen”, mit der sich dasselbe Ergebnis schneller erreichen lässt.

Fertig 🙂

Excel Template downloaden

von Der Chartisan

Clevere Datenbeschriftungen

Knowledge

Datenpunkte in Diagrammen direkt zu beschriften ist in der Regel leserfreundlicher als eine seitliche Skala. Die Skala erscheint in Excel automatisch und ist daher häufig in Berichten und Präsentationen anzutreffen. Sie zwingt den Leser der Grafik jedoch zu anstrengenden Augenbewegungen zwischen Datenpunkten und Skala.

Bei direkter Beschriftung verweilt das Auge dagegen ruhig auf den Datenpunkten. Die Leser Ihrer Grafiken können sich besser auf die Inhalte(!) konzentrieren. Leider sind die Standardbeschriftungen in Excel teilweise zu unflexibel und liefern dadurch unschöne Resultate. Bei Liniendiagrammen kommt es beispielsweise oft zu Überschneidungen zwischen Beschriftung und Linie, wodurch die Zahlen im schlechtesten Fall nicht mehr ablesbar sind.

Excel bietet zwar verschiedene Beschriftungspositionen an (oben, unten, rechts, links, zentriert). Diese werden aber immer für alle Datenpunkte gleichzeitig angewendet, obwohl eine individuelle Ausrichtung pro Datenpunkt sinnvoller wäre. Selbstverständlich lassen sich die Beschriftungen manuell verschieben. Dies kann aber ebenfalls Probleme verursachen, weil die manuelle Position nach dem Aktualisieren der Daten meist nicht mehr passt.

Abhilfe schafft ein kleiner Trick: Mit einer unsichtbaren Hilfsdatenreihe wird eine Datenbeschriftung erzeugt, die sich automatisch richtig positioniert. Im Falle des Liniendiagramms kann für jeden Punkt errechnet werden, ob es sich um einen “Knick” nach oben oder nach unten handelt. Dazu vergleicht eine Formel jeden Datenpunkt mit dem Mittelwert seiner beiden Nachbarn. Bei anderen Diagrammtypen können an dieser Stelle natürlich ganz andere Formeln stehen.

Mit folgenden Arbeitsschritten erzeugen Sie eine clevere Beschriftung für Liniendiagramme:

Formeln zum Verschieben der Beschriftung nach oben (+1) oder unten (-1) anlegen
Hilfsdatenreihe “Label” für Datenbeschriftung berechnen (Höhe der Punkte)

Datenreihe “Label” in das Diagramm einfügen, Punkte und Verbindungslinie unsichtbar formatieren und eine zentrierte Datenbeschriftung einblenden

erstes Beschriftungsfeld der neuen Datenreihe zweimal anklicken (nicht doppelklicken), so dass das einzelne Beschriftungsfeld markiert ist
“=” eintippen und auf die Tabellenzelle klicken in der der anzuzeigende Wert steht (z. B. Wert für Januar), dann mit Enter bestätigen (es entsteht eine sogenannte verknüpfte Beschriftung)
restliche Datenpunkte mit den entsprechenden Tabellenzellen verknüpfen

Fertig 🙂

Tipp: Seit Excel-Version 2013 gibt es die Beschriftungsoption “Wert aus Zellen”, mit der sich das gewünscht Ergebnis viel schneller erreichen lässt.

Excel Template downloaden

von Der Chartisan

Small Multiples in Excel – klein aber fein

Knowledge

“Small Multiples” sind Abbildungen, die eine Datenmenge in mehreren kleinen Diagrammen gleichen Typs darstellen. Dies ist oft übersichtlicher als dieselben Daten in einem einzigen, komplexeren Diagramm zu vermischen. Aussagekraft und Überblick ergeben sich in diesem Fall durch die Gesamtheit der Diagramme.

Ob ein Small Multiple günstiger ist als ein Einzeldiagramm mit mehreren Datenreihen hängt vom Anwendungsfall ab. Gestapelte Säulen- und Balkendiagramme heben visuell die Summe hervor. Small-Multiple-Diagramme setzen den visuellen Fokus auf die einzelnen Datenreihen.

Wird beispielsweise der Konzernumsatz als gestapeltes Säulendiagramm gezeigt (1 Diagramm mit 4 Datenreihen), so ist der Gesamtumsatz sehr gut ablesbar, weniger die genaue Entwicklung einzelner Produktgruppen.

Werden hingegen 4 Einzeldiagramme (mit je einer Datenreihe) für die Produktgruppen gezeigt, dann ist die Umsatzentwicklung je Produktgruppe ist sehr gut ablesbar. Auch zusätzliche Details wie Abweichungsanzeigen auf Ebene Produktgruppe sind problemlos möglich.

Ein guter Überblick ergibt sich allerdings nur dann, wenn alle Einzeldiagramme der Small-Multiple-Abbildung konsequent vereinheitlicht werden. Besonders wichtig ist dabei eine einheitliche Skalierung (einheitlicher Maßstab). Hervorhebungen verbessern die Aussagekraft, z. B. mit den Signalfarben grün und rot für positive (erwünschte) bzw. negative (unerwünschte) Abweichungen.

Excel besitzt leider keine besondere Funktionalität für solche Abbildungen. Dennoch lassen sich mit einigen Arbeitsschritten problemlos Small Multiples nach eigenen Anforderungen erstellen.

Diagramm erstellen

Diagrammtyp: gruppierte Balken
Datenreihen: Hauptdatenreihe und MinMax-Datenreihe für einheitliche Skalierung

Diagramm formatieren

Datenbeschriftung hinzufügen
Datenreihen formatieren… Reihenachsenüberlappung: 100% / Abstandsbreite: 50% / Farbe Balken: einfarbige Füllung = grün, invertieren falls negativ, zweite Farbe = rot / Farbe MinMax: keine Füllung

Achse formatieren… Achsenoptionen: Kategorien in umgekehrter Reihenfolge / Linie: einfarbige Linie, schwarz / Beschriftungsposition: niedrig
Diagrammbereich formatieren… Füllung: keine Füllung / Rahmen: keine Linie / Schriftgrad: 8 pt

Diagramm positionieren

Diagrammfläche (außen) und Zeichnungsfläche (innen) mit Alt-Taste an Zellen ausrichten

Diagramme kopieren

Datenquelle pro Diagramm anpassen

Fertig 🙂

Tipp: In einem der nächsten Artikel wird noch eine zweite Methode vorgestellt, mit der sich Small Multiples für einfache Anwendungsfälle noch schneller erzeugen lassen.

Excel Template downloaden

von Der Chartisan

Abweichungen darstellen

Knowledge

Bei der Präsentation von Daten für Entscheider geht es sehr häufig um den Vergleich von Abweichungen, z. B. von
Plan-, Ziel- oder Erfahrungswerten. Erst der Vergleich erlauben uns, die aktuellen Zahlen sinnvoll zu beurteilen, z. B. um zu entscheiden, ob eine Reaktion notwendig ist.

Auch bei der Visualisierung sollten daher Abweichungen eine große Rolle spielen. In bestimmten Fällen ist dies in Excel mit wenigen Klicks möglich, wie der heutige Trick zeigt:

Diagramm mit zwei Linien-Datenreihen erstellen

Abweichungen Schritt-für-Schritt erklärt

Diagramm markieren und im Menü Diagrammtools/Entwurf die Abweichungsbalken hinzufügen (siehe Screenshot; bei Excel 2007/2010 unter Diagrammtools/Layout)
Abweichungsbalken rot bzw. grün formatieren, die Balkenbreite lässt sich nach Rechtsklick auf eine der Liniendatenreihen einstellen (Parameter “Abstandbreite”)

ggf. Datenbeschriftung und Legende hinzufügen (siehe unser Trick “Beschriftungen”)

Fertig 🙂

Excel Template für diesen Trick jetzt downloaden

von Der Chartisan

Direkte Beschriftungen in Diagrammen

Knowledge

Datenbeschriftungen und Legenden sind wichtig, um den Inhalt von Diagrammen zu verstehen.

Leider sind die in Excel vorgesehenen Beschriftungsmöglichkeiten teilweise nicht wirklich leserfreundlich. Summen über gestapelten Säulen sind beispielsweise nicht vorgesehen. Legenden, die direkt neben der Datenreihe stehen und die Augen des Betrachters nicht zu anstrengenden Hin- und Her-Blicken zwingen, sucht man in den Standardfunktionen vergeblich.

Mit unserem heutigen Trick ist es möglich, leserfreundliche und gleichzeitig dynamische Beschriftungen in Diagramme einzubauen:

Teil 1: Summen über gestapelten Säulen

Summenwerte ausrechnen und als neue Datenreihe ins Diagramm einfügen
(Rechtsklick auf das Diagramm, dann “Daten auswählen”, dann Reihe hinzufügen)
Datenreihentyp der neuen Reihe auf “Punkt XY” ändern
(Rechtsklick auf die neuen Säulen, dann “Datenreihendiagrammtyp ändern”)
Punkte unsichtbar formatieren und Datenbeschriftung oberhalb einblenden

Teil 2: Legende neben gestapelten Säulen

Standardlegende ausblenden
Säulendatenreihen auf einen Bereich mit leeren Zellen verlängern, so dass neben der letzten Säule genug Platz für die Legende entsteht
X- und Y-Koordinaten für die Legendentexte errechnen, abhängig von den Daten der letzten Säule (siehe Beispieldatei)
Datenreihe vom Typ “Punkt XY” einfügen (wie bei Teil 1)

(die X-Werte, für die horizontale Ausrichtung der Punkte, lassen sich in der Datenquelle erst erfassen, nachdem der Typ “Punkt XY” ausgewählt wurde)
Punkte unsichtbar formatieren und Datenbeschriftung rechts einblenden
erste Datenbeschriftung der neuen Datenreihe zweimal anklicken (nicht doppelklicken), so dass ein einzelnes Beschriftungsfeld markiert ist
“=” eintippen und auf die Tabellenzelle klicken in der der gewünschte Legendentext steht, dann mit Enter bestätigen (es entsteht eine sogenannte verknüpfte Beschriftung)

restliche Legendentexte mit den entsprechenden Tabellenzellen verknüpfen

Fertig 🙂

Template Beschriftung direkt an der Säule

Excel Template für diesen Trick jetzt downloaden

von Der Chartisan

Mehrere Diagramme richtig skalieren

Knowledge

In vielen Fällen ist es wichtig, Daten auf mehrere Diagramme zu verteilen, weil die Darstellung dann übersichtlicher wird. Es entstehen sogenannte Small-Multiple Abbildungen.

Wenn aber jedes einzelne Diagramm eine andere Skala verwendet, geht schnell die Aussagekraft der gesamten Seite verloren. Im Sinne einer hochwertigen Visualisierung ist daher eine einheitliche Skalierung sehr wichtig. Gleichzeitig sollen die Diagramme natürlich pflegeleicht und dynamisch sein.

Negativbeispiel

Skalierung mehrerer Diagramme ohne Excel Trick

Diese Herausforderung lässt sich in Excel mit unserem heutigen kleinen Trick bewältigen:

Minimum und Maximum aller(!) Einzelwerte berechnen (Funktionen MIN und MAX)
Min- und Max-Werte als zusätzliche Datenreihe in jedes einzelne Diagramm einfügen
Min-Max-Datenreihe unsichtbar formatieren (keine Füllung, keine Rahmenlinie)
Diagramme mit gleicher Größe nebeneinander ausrichten

(Alt-Taste beim Ziehen mit der Maus gedrückt halten; dann rasten die Diagramme an den Zellen ein)