Tabellen bearbeiten in ExcelLange Listen mit SPALTENUMBRUCH auf mehrere Spalten verteilen

Aus einer Teilnehmerliste wird ein Aushang

Die Personalabteilung plant eine interne Schulung und führt die Teilnehmer in Excel als einfache Liste. Für den Aushang ist diese Liste zu lang. Besser lesbar wird sie, wenn höchstens acht Namen untereinander stehen und die Liste dann in der nächsten Spalte weiterläuft. Bei 23 Namen ergibt das drei Spalten.

Natürlich könnten Sie die Namen blockweise kopieren. Spätestens bei der ersten Nachmeldung stimmt die Aufteilung aber nicht mehr, und Sie fangen von vorn an.

Deshalb bleiben die Namen hier in einer einzigen Quellliste, und die Funktion SPALTENUMBRUCH() erzeugt daraus die Ansicht für den Aushang.

Syntax und was die Funktion SPALTENUMBRUCH() bewirkt

SPALTENUMBRUCH nimmt die Werte einer einzelnen Zeile oder Spalte und ordnet sie spaltenweise neu an: Die erste Spalte wird von oben nach unten gefüllt, danach geht es in der Spalte rechts daneben weiter.

Das Ergebnis belegt mehrere Zellen, obwohl die Formel nur in einer steht. Excel nennt dieses Verhalten „Überlaufen“.

SPALTENUMBRUCH braucht zwei Angaben: die Liste und die Anzahl der Einträge je Spalte. Ein drittes, optionales Argument legt fest, womit Excel freie Positionen am Ende auffüllt. Die Syntax lautet:

=SPALTENUMBRUCH(Liste; Anzahl_je_Spalte; [Auffüllwert])

Die Argumentnamen sind hier zur Erklärung frei gewählt. Eckige Klammern kennzeichnen ein optionales Argument und werden nicht eingegeben.

Die Bedeutung der Argumente in der Funktion ist:

• Liste: Der Zellbereich der Spalte, die Sie umbrechen wollen; zum Beispiel der Bereich A2:A24.
• Anzahl_je_Spalte: Die Anzahl der Einträge aus der Liste, die untereinander angeordnet werden sollen; zum Beispiel 8 Einträge pro Spalte.
• Auffüllwert: Eine Zeichenfolge, die angezeigt wird, wenn Zellen in der letzten Ausgabespalte nicht belegt sind. Setzen Sie die Zeichenfolge in Anführungsstriche ("").

Entscheidend ist das zweite Argument: Es bestimmt, wie viele Einträge untereinander stehen – nicht, wie viele Spalten entstehen. Die Spaltenzahl ergibt sich daraus von selbst.

Ist die Zahl größer oder gleich der Anzahl der Listeneinträge, bleibt es bei einer einzigen Spalte. Ohne drittes Argument füllt Excel freie Positionen mit #NV. 

Hinweis: SPALTENUMBRUCH gibt es in Excel für Microsoft 365 und in Excel 2024, jeweils für Windows und Mac. Auch in Excel für das Web können Sie die Funktion nutzen. In der englischen Excel-Version heißt die Funktion WRAPCOLS. 

Beispiel: Eine übersichtliche Liste der Teilnehmenden erstellen

Sie haben eine einfache Excel-Liste, in der Sie die Namen von 23 Teilnehmenden in Spalte A untereinander erfassen. Diese Liste wollen Sie in einer einfachen, übersichtlichen Form ausdrucken oder per PDF an andere Personen weiterleiten.

Dazu geben Sie in einem freien Bereich neben Ihrer Liste ein:

=SPALTENUMBRUCH(A2:A24; 8)

Sie schreiben die Formel nur in die erste Zelle, den restlichen Bereich füllt Excel selbst. In Zelle F9 bleibt eine Position übrig, die Excel mit #NV füllt.

Damit in Zelle F9 kein #NV mehr steht, ergänzen Sie in der Funktion in Zelle D2 das dritte Argument:

=SPALTENUMBRUCH(A2:A24; 8; "")

Die leere Zeichenfolge dient jetzt als Auffüllwert, Zelle F9 erscheint leer. Die Zelle gehört trotzdem weiter zum Ergebnisbereich.

Variante: Die Anzahl der Einträge über eine Zelle steuern

Vielleicht passt Ihr Aushang besser mit sechs Namen je Spalte. Damit Sie dafür nicht jedes Mal die Formel anfassen müssen, lagern Sie die Zahl in eine eigene Zelle aus.

Schreiben Sie in Zelle H1 den Text „Einträge je Spalte“ und in Zelle H2 die Zahl 8. Ersetzen Sie die Formel in D2 durch:

=SPALTENUMBRUCH(A2:A24; $H$2; "")

Ändern Sie den Wert in Zelle H2 auf 6. Die 23 Namen verteilen sich jetzt auf vier Spalten, das Ergebnis reicht von D2 bis G7. Die letzte Position bleibt leer.

Neue Teilnehmende automatisch berücksichtigen

Bisher verweist die Formel auf den festen Bereich A2:A24. Kommt ein Name hinzu, müssten Sie den Bezug von Hand erweitern. Das übernimmt eine dynamische Excel-Tabelle: Ihre strukturierten Verweise passen sich an, wenn Zeilen hinzukommen oder wegfallen.

Schritt 1: Aus der Quelle eine Excel-Tabelle machen

Markieren Sie den Bereich A1:A24 und wählen Sie Einfügen > Tabelle. Achten Sie im Dialog darauf, dass der Bereich stimmt und die Option für Überschriften aktiviert ist.

Klicken Sie anschließend in die Tabelle und tragen Sie im Register Tabellenentwurf im Feld Tabellenname tblTeilnehmer ein. Die Spaltenüberschrift Name bleibt unverändert.

Schritt 2: Den Tabellenbezug einsetzen

Die folgende Formel ersetzt die bisherige in D2.

=SPALTENUMBRUCH(tblTeilnehmer[Name]; $H$2; "")

tblTeilnehmer[Name] steht für alle Datenzeilen der Spalte Name. Am Ergebnis ändert sich zunächst nichts. Achten Sie nur darauf, dass die Tabelle auf Spalte A beschränkt bleibt und nicht bis in den Ergebnisbereich reicht.

Schritt 3: Hinzufügen und Entfernen prüfen

Tragen Sie jetzt in Zelle A25 (und darunter) weitere Namen ein, wird der Bereich mit den umgebrochenen Spalten automatisch erweitert.

Fehler erkennen und beheben

Bleibt das erwartete Ergebnis aus, prüfen Sie zuerst, ob der Ausgabebereich frei ist, und danach die Argumente. Ändern Sie immer nur eine Sache auf einmal, dann sehen Sie, was gewirkt hat.