Excel im Controlling: Klare Auswertungen – so geht’s

Excel im Controlling: Pivot-Tabellen, Kennzahlen und Dashboards sorgen für klare Auswertungen und präzise Entscheidungsgrundlagen im Unternehmen.

Excel ist im Controlling das zentrale Werkzeug für klare Auswertungen, weil Pivot-Tabellen, Power Query und Power Pivot große Datenmengen strukturiert, nachvollziehbar und automatisiert aufbereiten.

Mit den richtigen Funktionen erstellen Sie Soll-Ist-Vergleiche, KPI-Reportings und Dashboards, die Entscheidungen unmittelbar unterstützen. Dieser Artikel zeigt konkret, wie Sie dabei vorgehen.

Was bedeutet „Excel im Controlling“ eigentlich?

Im Controlling geht es darum, Zahlen aus unterschiedlichen Quellen zu sammeln, zu strukturieren und in aussagekräftige Kennzahlen zu übersetzen. Excel eignet sich dafür besonders gut, weil es sowohl einfache Berechnungen als auch komplexe Datenanalysen ermöglicht.

Zudem lassen sich mit Excel nahezu alle klassischen Controlling-Aufgaben abbilden:

• Budgetplanung und Forecasts
• Soll-Ist-Vergleiche und Abweichungsanalysen
• Umsatz-, Kosten- und Rentabilitätsauswertungen
• KPI-Reportings und Management-Dashboards
• Konsolidierung von Daten aus mehreren Abteilungen oder Systemen

Damit Auswertungen wirklich klar bleiben, kommt es jedoch nicht nur auf einzelne Formeln an. Entscheidend ist vielmehr, welche Werkzeuge Sie kombinieren und wie strukturiert Sie dabei vorgehen.

Die wichtigsten Excel-Werkzeuge für klare Controlling-Auswertungen

Excel bietet mehrere Ebenen der Datenanalyse. Je nach Datenmenge und Zielsetzung eignen sich unterschiedliche Werkzeuge.

Pivot-Tabellen als Basis jeder Auswertung

Pivot-Tabellen sind das Herzstück jeder klaren Auswertung im Controlling. Sie fassen große Datenmengen in wenigen Klicks zusammen, ohne dass Sie eine einzige Formel schreiben müssen. Außerdem lassen sich Pivot-Tabellen jederzeit umgruppieren, filtern und erweitern.

Typische Einsatzgebiete sind:

• Umsatzanalysen nach Region, Produkt oder Zeitraum
• Kostenauswertungen nach Kostenstelle
• Budgetüberwachung über mehrere Perioden

Power Query für automatisierte Datenaufbereitung

Während Pivot-Tabellen bereits vorhandene Daten auswerten, kümmert sich Power Query um den Schritt davor: den Import und die Bereinigung der Rohdaten. Dadurch sparen Sie sich manuelle Copy-Paste-Arbeit, die in vielen Controlling-Abteilungen nach wie vor üblich ist.

Power Query eignet sich besonders, wenn Sie regelmäßig Daten aus CSV-Dateien, SAP-Exporten, Textdateien oder Datenbanken zusammenführen müssen. Zudem lassen sich einmal erstellte Abfragen wiederverwenden, sodass sich künftige Reportings mit einem Klick aktualisieren lassen.

Power Pivot für komplexe Datenmodelle

Sobald mehrere Tabellen miteinander verknüpft werden müssen, stößt die klassische Pivot-Tabelle an ihre Grenzen. Power Pivot schafft hier Abhilfe, indem es ein internes Datenmodell mit Beziehungen zwischen Tabellen aufbaut.

Dadurch lassen sich beispielsweise Ist-Daten aus der Buchhaltung mit Plandaten aus der Budgetplanung verknüpfen, ohne dass Sie beide Tabellen manuell zusammenführen müssen. Zusätzlich ermöglichen sogenannte Measures, berechnet mit der Formelsprache DAX, komplexe Kennzahlen wie kumulierte Werte oder Vorjahresvergleiche.

Klassische Formeln als Ergänzung

Auch wenn Pivot-Tabellen und Power Query viele Aufgaben übernehmen, bleiben klassische Formeln unverzichtbar. Dazu zählen insbesondere:

• SVERWEIS und XVERWEIS zum Abgleich von Datensätzen
• SUMMEWENNS und MITTELWERTWENNS für bedingte Berechnungen
• INDEX und VERGLEICH als flexible Alternative zum SVERWEIS
• WENN-Funktionen, teilweise verschachtelt oder mit UND/ODER kombiniert

Schritt-für-Schritt: Pivot-Tabelle für Controlling-Auswertungen erstellen

Damit Ihre Auswertung sowohl korrekt als auch übersichtlich wird, empfiehlt sich folgende Vorgehensweise:

1. Daten als Tabelle formatieren. Markieren Sie Ihren Datenbereich und wandeln Sie ihn über „Einfügen“ → „Tabelle“ in eine strukturierte Excel-Tabelle um. Dadurch erweitert sich die Pivot-Tabelle automatisch, sobald neue Zeilen hinzukommen.
2. Pivot-Tabelle einfügen. Klicken Sie auf „Einfügen“ → „PivotTable“ und wählen Sie den Datenbereich oder das Datenmodell aus.
3. Zeilen- und Spaltenfelder festlegen. Ziehen Sie beispielsweise „Kostenstelle“ in die Zeilen und „Monat“ in die Spalten.
4. Werte hinzufügen. Ziehen Sie das Feld „Betrag“ in den Bereich „Werte“ und stellen Sie sicher, dass die Aggregation auf „Summe“ steht.
5. Filter und Datenschnitte ergänzen. Über „Einfügen“ → „Datenschnitt“ fügen Sie interaktive Filterschaltflächen hinzu, etwa für einzelne Abteilungen.
6. Zeitachse einbinden. Bei Datumsfeldern lässt sich zusätzlich eine „Zeitachse“ einfügen, um Zeiträume komfortabel einzugrenzen.
7. Bedingte Formatierung anwenden. Heben Sie kritische Abweichungen farblich hervor, damit Auffälligkeiten sofort sichtbar werden.

Auf diese Weise entsteht eine Auswertung, die sich jederzeit aktualisieren lässt, sobald neue Daten hinzukommen.

Daten automatisiert aufbereiten mit Power Query

Bevor eine Pivot-Tabelle überhaupt aussagekräftig sein kann, müssen die zugrunde liegenden Daten sauber sein. Genau hier setzt Power Query an.

So gehen Sie vor:

1. Daten importieren. Wählen Sie „Daten“ → „Daten abrufen“ und wählen Sie die passende Quelle, etwa eine CSV-Datei, eine SAP-Tabelle oder eine Datenbank.
2. Power-Query-Editor öffnen. Der Editor zeigt Ihnen eine Vorschau der importierten Daten sowie alle Transformationsschritte.
3. Fehlerhafte Zeilen entfernen. Filtern Sie Leerzeilen oder ungültige Werte heraus.
4. Spalten teilen oder zusammenführen. Nutzen Sie „Spalte teilen“, um beispielsweise ein Datum in Monat und Jahr aufzuteilen.
5. Datentypen prüfen. Stellen Sie sicher, dass Zahlen tatsächlich als Zahl und Datumswerte als Datum erkannt werden, da sonst spätere Berechnungen fehlschlagen.
6. Mehrere Dateien zusammenführen. Über „Dateien aus Ordner kombinieren“ lassen sich beispielsweise zwölf Monatsdateien automatisiert zu einer Jahresauswertung verbinden.
7. Abfrage laden. Klicken Sie abschließend auf „Schließen & Laden“, um die bereinigten Daten in Excel oder direkt in das Datenmodell zu übernehmen.

Da sich diese Schritte einmal einrichten und danach per Klick aktualisieren lassen, sparen Sie bei wiederkehrenden Monatsauswertungen erheblich Zeit.

Wichtige Controlling-Formeln für klare Auswertungen

Neben Pivot-Tabellen und Power Query gehören bestimmte Formeln zum Handwerkszeug jedes Controllers.

SVERWEIS und XVERWEIS

Der SVERWEIS sucht einen Wert in der ersten Spalte einer Tabelle und gibt einen zugehörigen Wert aus einer anderen Spalte zurück. Die Syntax lautet:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; 0)

Der modernere XVERWEIS löst dabei viele Schwächen des SVERWEIS, etwa die feste Suchrichtung. Deshalb empfiehlt sich der Einsatz von XVERWEIS, sofern Sie mit Excel 365 oder Excel 2021 arbeiten:

=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix)

SUMMEWENNS für bedingte Summen

Möchten Sie beispielsweise die Kosten einer bestimmten Kostenstelle in einem bestimmten Monat summieren, eignet sich SUMMEWENNS:

=SUMMEWENNS(Summenbereich; Kriterienbereich1; Kriterium1; Kriterienbereich2; Kriterium2)

INDEX und VERGLEICH als flexible Kombination

Wenn Sie Werte sowohl links als auch rechts vom Suchkriterium finden müssen, bietet die Kombination aus INDEX und VERGLEICH mehr Flexibilität als der klassische SVERWEIS:

=INDEX(Rückgabebereich; VERGLEICH(Suchkriterium; Suchbereich; 0))

Soll-Ist-Vergleich und Abweichungsanalyse in Excel erstellen

Der Soll-Ist-Vergleich gehört zu den zentralen Aufgaben im Controlling. Damit die Auswertung übersichtlich bleibt, empfiehlt sich folgender Aufbau:

1. Plan- und Ist-Werte in separaten Spalten hinterlegen, idealerweise als strukturierte Tabelle.
2. Absolute Abweichung berechnen mit einer einfachen Subtraktion: =Ist-Wert - Plan-Wert.
3. Prozentuale Abweichung ermitteln über: =(Ist-Wert-Plan-Wert)/Plan-Wert.
4. Bedingte Formatierung nutzen, um kritische Abweichungen ab einem definierten Schwellenwert farblich hervorzuheben, etwa rot ab einer Abweichung von mehr als zehn Prozent.
5. Pivot-Tabelle darüberlegen, um Abweichungen nach Kostenstelle, Produkt oder Zeitraum zu aggregieren.

Dadurch erkennen Sie auf einen Blick, in welchen Bereichen Handlungsbedarf besteht, ohne sich durch endlose Zahlenkolonnen arbeiten zu müssen.

Dashboards und Diagramme für aussagekräftige Reportings

Eine reine Zahlentabelle überzeugt selten auf Führungsebene. Deshalb lohnt sich der zusätzliche Aufwand für ein Dashboard.

Für ein wirkungsvolles Controlling-Dashboard sollten Sie Folgendes beachten:

• Wenige, aber relevante Kennzahlen darstellen, statt jede verfügbare Zahl unterzubringen
• Pivot-Charts direkt aus der Pivot-Tabelle ableiten, damit sich Diagramme automatisch mit den Daten aktualisieren
• Einheitliche Farbcodierung verwenden, etwa Grün für positive und Rot für negative Abweichungen
• Interaktive Elemente wie Datenschnitte einbinden, damit Nutzer selbst filtern können
• Klare Beschriftungen statt technischer Spaltennamen verwenden

Zusätzlich empfiehlt sich ein separates Dashboard-Tabellenblatt, auf dem ausschließlich Diagramme und Kennzahlen sichtbar sind, während die Rohdaten und Berechnungen auf anderen Blättern verborgen bleiben.

Dynamische Tabellen und bedingte Formatierung sinnvoll einsetzen

Damit Auswertungen auch bei wachsenden Datenmengen zuverlässig funktionieren, sollten Sie auf dynamische Tabellen setzen. Wandeln Sie dazu Ihre Datenbereiche über „Einfügen“ → „Tabelle“ in strukturierte Tabellen um. Neue Zeilen werden dadurch automatisch in Formeln, Pivot-Tabellen und Diagramme übernommen.

Ebenso hilfreich ist die bedingte Formatierung. Damit lassen sich beispielsweise:

• Werte über oder unter einem Schwellenwert farblich markieren
• Datenbalken zur schnellen visuellen Einschätzung einfügen
• Symbolsätze wie Ampeln für Statusanzeigen nutzen

Wichtig ist dabei, die Formatierung sparsam einzusetzen, da eine Tabelle mit zu vielen Farben schnell unübersichtlich wirkt.

Häufige Fehler bei Excel-Auswertungen im Controlling vermeiden

Damit Ihre Auswertungen wirklich belastbar bleiben, sollten Sie folgende Fehlerquellen kennen:

• Feste statt dynamische Bezüge: Wer Zellbereiche fest verdrahtet, muss Formeln bei jeder Datenaktualisierung manuell anpassen.
• Falsche Datentypen: Als Text formatierte Zahlen werden bei Summenformeln nicht berücksichtigt.
• Fehlende Datumsdimension: Ohne eine saubere Datumstabelle lassen sich Zeitvergleiche in Power Pivot nur schwer umsetzen.
• Zu komplexe Formeln: Stark verschachtelte WENN-Funktionen sind fehleranfällig und schwer nachvollziehbar. Hier lohnt sich häufig der Umstieg auf SUMMEWENNS oder Power Query.
• Fehlende Dokumentation: Ohne Kommentare oder ein separates Erläuterungsblatt verstehen selbst erfahrene Kollegen die Logik einer Auswertung oft nicht mehr.

Automatisierung mit Makros für wiederkehrende Auswertungen

Sobald sich eine Auswertung regelmäßig wiederholt, lohnt sich der Einsatz von Makros. Über „Entwicklertools“ → „Makro aufzeichnen“ zeichnen Sie wiederkehrende Schritte, etwa das Aktualisieren einer Pivot-Tabelle oder das Formatieren eines Berichts, einmalig auf. Anschließend lässt sich das Makro per Knopfdruck erneut ausführen.

Für fortgeschrittene Automatisierungen bietet sich zudem VBA (Visual Basic for Applications) an, mit dem sich beispielsweise mehrere Arbeitsmappen automatisiert konsolidieren lassen. Wer regelmäßig SAP-Daten importiert, kann diesen Schritt außerdem direkt in Power Query automatisieren, sodass ein separates Makro oft gar nicht mehr nötig ist.

Häufige Fragen zu klaren Auswertungen mit Excel im Controlling

Welches Excel-Tool eignet sich am besten für Controlling-Auswertungen?

Für die meisten Auswertungen reicht eine klassische Pivot-Tabelle völlig aus. Sobald jedoch mehrere Datenquellen kombiniert werden müssen, lohnt sich zusätzlich Power Query für den Datenimport sowie Power Pivot für komplexe Datenmodelle mit mehreren verknüpften Tabellen.

Ab welcher Excel-Version stehen Power Query und Power Pivot zur Verfügung?

Beide Tools sind seit Excel 2016 fest integriert. In älteren Versionen wie Excel 2010 oder Excel 2013 müssen sie noch als separates Add-in aktiviert werden. Nutzer von Excel 365 oder Excel 2021 profitieren zudem von zusätzlichen Funktionen wie dem XVERWEIS.

Wie erstelle ich einen Soll-Ist-Vergleich in Excel?

Hinterlegen Sie Plan- und Ist-Werte in separaten Spalten und berechnen Sie die Abweichung über eine einfache Subtraktion. Anschließend visualisieren Sie kritische Abweichungen mit bedingter Formatierung und verdichten die Ergebnisse bei Bedarf zusätzlich über eine Pivot-Tabelle.

Wann sollte ich SVERWEIS durch XVERWEIS ersetzen?

Sobald Ihnen der XVERWEIS zur Verfügung steht, sollten Sie ihn dem SVERWEIS vorziehen, da er sowohl links als auch rechts suchen kann und weniger fehleranfällig gegenüber eingefügten Spalten ist.

Wie halte ich eine Pivot-Tabelle automatisch aktuell?

Wandeln Sie den zugrunde liegenden Datenbereich zunächst in eine strukturierte Tabelle um. Dadurch erweitert sich der Datenbereich automatisch, sobald neue Zeilen hinzukommen. Nach dem Einfügen neuer Daten genügt anschließend ein Klick auf „Aktualisieren“ in der Pivot-Tabelle.

Welche Formel eignet sich für bedingte Summen mit mehreren Kriterien?

Für Summen mit mehreren Bedingungen, etwa Kostenstelle und Monat, eignet sich SUMMEWENNS. Sie können beliebig viele Kriterienbereiche und zugehörige Kriterien ergänzen.

Wie baue ich ein übersichtliches Controlling-Dashboard in Excel?

Legen Sie ein eigenes Tabellenblatt ausschließlich für das Dashboard an und binden Sie dort Pivot-Charts, Kennzahlen sowie Datenschnitte ein. Rohdaten und Berechnungen bleiben auf separaten, ausgeblendeten Blättern, damit das Dashboard übersichtlich bleibt.

Lohnt sich Power Query auch bei kleinen Datenmengen?

Ja, denn selbst bei überschaubaren Datenmengen spart Power Query Zeit, sobald sich ein Import regelmäßig wiederholt. Zudem lässt sich eine einmal erstellte Abfrage beliebig oft wiederverwenden, wodurch manuelle Fehler beim Kopieren entfallen.

Wie vermeide ich Fehler in komplexen Controlling-Tabellen?

Nutzen Sie durchgängig dynamische Bezüge, prüfen Sie regelmäßig die Datentypen und dokumentieren Sie zentrale Formeln nachvollziehbar. Ebenso hilft eine klare Trennung zwischen Rohdaten, Berechnungen und Reporting-Ansicht dabei, Fehlerquellen frühzeitig zu erkennen.

Fazit

Excel liefert im Controlling mit Pivot-Tabellen, Power Query und Power Pivot alle Werkzeuge für klare, automatisierte Auswertungen. Entscheidend ist jedoch die richtige Kombination aus sauberer Datenaufbereitung, passenden Formeln und übersichtlicher Visualisierung.

Wer diese Elemente konsequent verbindet, spart nicht nur Zeit, sondern erhält zudem Reportings, die auch bei wachsenden Datenmengen zuverlässig und nachvollziehbar bleiben.