Werte in Excel drehen, ohne Daten mühsam neu aufzubauen
Beim Arbeiten mit Excel entstehen Daten selten genau in der Struktur, die für Auswertung, Bericht oder Weiterverarbeitung gebraucht wird. Eine Kundenliste steht untereinander, ein Monatsreport soll aber nebeneinander aufgebaut sein. Eine Matrix kommt aus einem Export, muss für ein Dashboard jedoch in eine Zeile oder Spalte umgewandelt werden. Genau dafür ist das Transponieren in Excel gedacht: Zeilen werden zu Spalten, Spalten zu Zeilen, Listen werden neu angeordnet.
In diesem Ratgeber lernst du, wann die Einfügeoption Transponieren genügt, wann die Funktion MTRANS die bessere Wahl ist und wie du mit ZUZEILE und ZUSPALTE aus mehrdimensionalen Bereichen flexible dynamische Arrays erzeugst. Die Beispiele helfen bei typischen Aufgaben aus Controlling, Vertrieb, Personal, Assistenz, Projektmanagement und Datenaufbereitung. Wenn du Excel-Formeln systematisch vertiefen möchtest, findest du passende Trainings im Excel Competence Center und in unseren Microsoft Excel Schulungen.
Was bedeutet Transponieren in Excel?
Transponieren bedeutet, die Ausrichtung eines Zellbereichs zu drehen. Aus einer vertikalen Liste in einer Spalte wird eine horizontale Liste in einer Zeile. Aus einer Tabelle mit mehreren Zeilen und Spalten entsteht eine gedrehte Matrix. Die Inhalte bleiben erhalten, nur ihre Position im Tabellenblatt ändert sich.
Typische Anwendungsfälle:
- Eine Liste mit Namen, Artikeln oder Kostenstellen von einer Spalte in eine Zeile drehen
- Monatswerte aus einer Zeile in eine Spalte bringen, damit Pivot-Tabellen oder Diagramme besser funktionieren
- Exportdaten aus Systemen wie CRM, ERP oder Zeiterfassung für Auswertungen vorbereiten
- Matrixdaten für Berichte, Dashboards oder Formeln neu strukturieren
- Leere Werte oder Fehler beim Umformen mit ZUZEILE und ZUSPALTE gezielt ausblenden
Variante 1: Transponieren per Kopieren und Einfügen
Die schnellste Variante ist die Einfügeoption Transponieren. Sie eignet sich, wenn die gedrehte Tabelle einmalig erzeugt werden soll und keine automatische Aktualisierung erforderlich ist.
- Den gewünschten Zellbereich markieren.
- Mit Strg + C kopieren.
- Die Zielzelle auswählen.
- Über die Einfügeoptionen Transponieren wählen.
Wichtig für die Praxis:
Beim Kopieren und Einfügen entsteht ein neuer Datenbereich. Änderungen an den Originaldaten werden nicht automatisch in den transponierten Bereich übernommen. Für dynamische Berichte, Vorlagen oder wiederkehrende Datenimporte sind Formeln daher meistens sinnvoller.
Variante 2: MTRANS für dynamisch gedrehte Zellbereiche
Die Funktion MTRANS gibt die transponierte Version einer Matrix zurück. In Microsoft 365 und aktuellen Excel-Versionen reicht es, die Formel in die linke obere Zielzelle einzutragen. Excel füllt den benötigten Ergebnisbereich automatisch als dynamisches Array. In älteren Excel-Versionen musste MTRANS als klassische Matrixformel bestätigt werden.
Syntax:
=MTRANS(Matrix)Soll der Bereich A2:A8 von einer Spalte in eine Zeile gedreht werden, lautet die Formel:
=MTRANS(A2:A8)Das Ergebnis läuft nach rechts über mehrere Zellen. Dieser Überlaufbereich ist durch einen Rahmen erkennbar. Die Formel selbst steht nur in der ersten Ergebniszelle. Wird im Originalbereich ein Wert geändert, passt Excel das Ergebnis automatisch an.
MTRANS ist ideal, wenn:
- Zeilen und Spalten exakt vertauscht werden sollen
- das Ergebnis mit den Quelldaten verbunden bleiben soll
- eine vollständige Matrix gedreht wird
- keine Werte beim Umformen ignoriert werden müssen
Grenzen von MTRANS:
- Leere Zellen bleiben im Ergebnis erhalten
- Fehlerwerte werden mit übertragen
- Die Scan-Reihenfolge lässt sich nicht flexibel steuern
- Für reine Listenumformung sind ZUZEILE und ZUSPALTE oft passender
Variante 3: ZUZEILE macht aus einer Matrix eine Zeile
Die Funktion ZUZEILE gibt einen Bereich als einzelne Zeile aus. Im Unterschied zu MTRANS wird nicht nur die Ausrichtung gedreht. ZUZEILE kann auch eine ganze Matrix zeilenweise oder spaltenweise auslesen und in einer einzigen Ergebniszeile zusammenführen. Zusätzlich lassen sich leere Zellen und Fehlerwerte ignorieren.
Syntax:
=ZUZEILE(Array; [ignorieren]; [nach_Spalte_scannen])| Formel | Ergebnis |
|---|---|
=ZUZEILE(A2:D4) | Gibt alle Werte aus A2:D4 in einer Zeile aus |
=ZUZEILE(A2:D4;1) | Ignoriert leere Zellen |
=ZUZEILE(A2:D4;2) | Ignoriert Fehlerwerte |
=ZUZEILE(A2:D4;3;WAHR) | Ignoriert leere Zellen und Fehler, liest spaltenweise |
Der zweite Parameter ist in der Praxis besonders wertvoll: Mit 1 werden Leerzellen ausgelassen, mit 2 Fehlerwerte, mit 3 beides. Der dritte Parameter steuert, ob Excel zeilenweise oder spaltenweise durch die Matrix läuft. So lässt sich die Reihenfolge des Ergebnisses gezielt bestimmen.
Variante 4: ZUSPALTE macht aus einer Matrix eine Spalte
Die Funktion ZUSPALTE arbeitet nach demselben Prinzip wie ZUZEILE, liefert das Ergebnis aber untereinander in einer Spalte. Das ist besonders hilfreich, wenn breit angelegte Datentabellen für Listen, Pivot-Tabellen, Datenmodelle oder Power Query vorbereitet werden sollen.
Syntax:
=ZUSPALTE(Array; [ignorieren]; [nach_Spalte_scannen])| Formel | Ergebnis |
|---|---|
=ZUSPALTE(A2:D4) | Gibt alle Werte aus A2:D4 in einer Spalte aus |
=ZUSPALTE(A2:D4;1) | Lässt leere Zellen aus |
=ZUSPALTE(A2:D4;3) | Lässt leere Zellen und Fehlerwerte aus |
=ZUSPALTE(A2:D4;1;WAHR) | Liest die Matrix spaltenweise aus |
Praxisbeispiel:
Ein Export liefert Artikelnummern über mehrere Spalten verteilt. Für eine Prüfung, einen Abgleich oder eine weitere Formel wird aber eine saubere Liste untereinander gebraucht. Mit =ZUSPALTE(A2:F50;1) entsteht aus dem gesamten Bereich eine Liste ohne Leerzellen.
MTRANS, ZUZEILE oder ZUSPALTE: Welche Funktion passt?
| Aufgabe | Empfohlene Methode | Warum? |
|---|---|---|
| Bereich einmalig drehen | Kopieren und Transponieren | Schnell, ohne Formel, aber nicht dynamisch |
| Zeilen und Spalten exakt vertauschen | MTRANS | Matrix bleibt mit Quelldaten verbunden |
| Mehrere Zeilen in eine Ergebniszeile bringen | ZUZEILE | Matrix wird als einzelne Zeile ausgegeben |
| Mehrere Spalten in eine Liste umwandeln | ZUSPALTE | Matrix wird als einzelne Spalte ausgegeben |
| Leere Zellen oder Fehler beim Umformen auslassen | ZUZEILE oder ZUSPALTE | Parameter ignorieren steuert die Bereinigung |
Häufige Fehler beim Transponieren
- Überlaufbereich blockiert: Wenn rechts oder unterhalb der Formel bereits Werte stehen, kann Excel das dynamische Array nicht ausgeben.
- Falsche Methode gewählt: Kopieren und Einfügen ist statisch. Für aktualisierbare Berichte sind Formeln besser geeignet.
- Leerzellen werden als 0 angezeigt: Bei ZUZEILE und ZUSPALTE kann der Parameter
1helfen, leere Zellen zu ignorieren. - Reihenfolge stimmt nicht: Der Parameter
nach_Spalte_scannenentscheidet, ob Excel zeilenweise oder spaltenweise liest. - Excel-Version unterstützt die Funktion nicht: ZUZEILE und ZUSPALTE stehen in Microsoft 365 und Excel 2024 zur Verfügung. In älteren Versionen ist MTRANS häufiger die passende Alternative.
Weiterlernen mit Excel Trainings von cmt
Transponieren ist nur ein Baustein für saubere Datenaufbereitung in Excel. Wer regelmäßig mit Formeln, Listen, Reports und Importdaten arbeitet, profitiert von einem strukturierten Training mit echten Praxisdateien. In der Excel Aufbaukurs Schulung werden fortgeschrittene Funktionen, Listen und Auswertungen trainiert. Für Datenimporte, Transformationen und wiederkehrende Aufbereitungsschritte lohnt sich zusätzlich das Training Excel Power Query Einführung.
Wenn der Schwerpunkt auf Formeln und Berechnungen liegt, bietet der Lernpfad Excel für Berechnungen eine passende Orientierung vom Einstieg bis zu fortgeschrittenen Excel-Funktionen.