Daten lückenlos auflisten

In diesem Beitrag erfahren Sie, wie Sie Daten in Excel lückenlos auflisten. Sie erhalten eine präzise Schritt-für-Schritt-Anleitung mit einer bewährten Matrixformel, praxisnahe Hinweise zur Anpassung, typische Fehlerquellen sowie moderne Alternativen mit dynamischen Arrays und ein kompaktes VBA-Makro für die Automatisierung.

Ausgangssituation und Ziel

Im Bereich B5 bis B25 liegen Einträge vor, die durch leere Zellen unterbrochen sind. Ziel ist es, diese Daten ab Zelle D5 ohne Leerzeilen lückenlos aufzulisten. Dadurch entsteht eine saubere, kontinuierliche Liste, die sich für Auswertungen, Diagramme und Weiterverarbeitung eignet.

Die folgende Abbildung zeigt die Ausgangsdaten:

 
Abb 1: Ausgangsdaten inkl. Datenlücken

Ab Zelle D5 sollen die Daten nun ohne Leerzeilen ausgelesen werden. Es sollen also alle Einträge aus dem Bereich B5:B25 ohne Leerzeilen untereinander aufgelistet werden.

Gehen Sie dazu wie folgt vor:

1. Erfassen Sie folgende Formel in Zelle D5
   {=WENN(ZEILE(B1)> ANZAHL2($B$5:$B$25); "";INDEX($B:$B; KKLEINSTE(WENN(B$5:B$25<>"";
   ZEILE($5:$25)); ZEILE(B1))))}
   
   
2. Da es sich um eine Matrixfunktion handelt, dürfen die geschweiften Klammern nicht manuell eingegeben werden. Wenn Sie die Eingabe mit der Tastenkombination [Strg] + [Umschalt] + [Enter] abschließen, werden die geschweiften Klammern automatisch hinzugefügt und Excel interpretiert die Formel korrekt als Matrixfunktion.
   
   
3. Kopieren Sie im letzten Schritt diese Formeln nun noch bis zur Zelle D25 nach unten. Damit werden alle Daten aus dem Ausgangsbereich ausgelesen.

Wie Sie sehen, werden im Ergebnis wie gewünscht alle Daten ohne Leerzellen ausgegeben, siehe Abbildung 2.

Abb. 2: Lückenlose Anzeige der Daten

So funktioniert die Formel im Überblick. ANZAHL2 zählt die belegten Zellen im Quellbereich und begrenzt damit die Ausgabeliste. WENN steuert, dass unterhalb des letzten Eintrags eine leere Zeichenkette ausgegeben wird. WENN in Kombination mit ZEILE erzeugt ein Array der Zeilennummern für alle nichtleeren Zellen. KKLEINSTE liefert diese Zeilennummern der Reihe nach, und INDEX gibt den zugehörigen Zellinhalt zurück. ZEILE(B1) dient als Zähler für die Position in der Ergebnisliste und startet bei 1.

Praxisbeispiel und Anpassungen

Passen Sie die Bereiche an Ihre Daten an. Wenn Ihre Quelle zum Beispiel in C10 bis C100 liegt und die Ergebnisliste in E10 beginnen soll, ersetzen Sie die Bezugsbereiche entsprechend. Achten Sie darauf, absolute Bezüge mit Dollarzeichen zu verwenden, wenn der Quellbereich beim Herunterkopieren unverändert bleiben soll.

Wenn die Ergebnisliste länger als die Anzahl gefundener Einträge ist, erscheinen nach dem letzten Wert leere Zellen. Dies ist gewollt und vermeidet Fehlermeldungen.

Typische Fehlerquellen und Lösungen

Vergessen der Tastenkombination Strg plus Umschalt plus Enter in älteren Excel-Versionen. Lösung, geben Sie die Formel ein, drücken Sie Strg plus Umschalt plus Enter und prüfen Sie, ob Excel automatisch geschweifte Klammern anzeigt.

Falsche Bereichsbezüge. Lösung, kontrollieren Sie alle Zellenadressen, insbesondere die Bereiche in WENN, ZEILE und INDEX. Absolute Bezüge sollten mit Dollarzeichen gesetzt werden.

Mischung unterschiedlicher Trennzeichen. Lösung, verwenden Sie in deutschsprachigen Excel-Versionen das Semikolon als Funktionsargumenttrenner. Bei anderssprachigen Excel-Installationen weichen Funktionsnamen und Trennzeichen ab.

Leere Zellen mit Formeln, die einen leeren String liefern. Lösung, ANZAHL2 zählt auch leere Strings. Prüfen Sie gegebenenfalls, ob der Quellbereich echte Leerzellen enthält oder ob alternative Kriterien benötigt werden.

Moderne Alternative mit FILTER (Microsoft 365)

Wenn Sie Excel mit dynamischen Arrays verwenden, erreichen Sie das Ziel noch einfacher. Geben Sie in D5 folgende Formel ein. Die Ergebnisliste wird automatisch nach unten ausgegeben und passt sich bei Änderungen im Quellbereich dynamisch an.

=FILTER(B5:B25;B5:B25<>"")

Diese Lösung benötigt keine spezielle Tastenkombination. Sie ist besonders performant und wartungsfreundlich, wenn sich die Datenmenge häufig ändert.

VBA-Makro für lückenlose Liste

Für wiederkehrende Aufgaben oder sehr große Datenmengen bietet sich ein kurzes VBA-Makro an. Es liest alle nichtleeren Zellen aus Spalte B ab Zeile 5 ein und schreibt sie ab Zelle D5 lückenlos untereinander. Vor der Ausgabe wird die Zielspalte ab D5 bereinigt.

Sub ListeOhneLeerzeilen()
    Dim ws As Worksheet
    Dim src As Range, cell As Range
    Dim dest As Range
    Dim lastRow As Long, outRow As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Set src = ws.Range("B5:B" & lastRow)
    Set dest = ws.Range("D5")
    
    ws.Range(dest, ws.Cells(ws.Rows.Count, dest.Column)).ClearContents
    outRow = dest.Row
    
    For Each cell In src
        If Len(cell.Value) > 0 Then
            ws.Cells(outRow, dest.Column).Value = cell.Value
            outRow = outRow + 1
        End If
    Next cell
End Sub

Tipp für große Datenbereiche. Statt jede Zelle einzeln zu prüfen, können Sie mit SpecialCells nur belegte Zellen ansprechen. Dies reduziert die Laufzeit erheblich, wenn der Quellbereich sehr viele Leerzellen enthält.

Weitere Optionen für Profis

Power Query eignet sich, wenn Daten aus externen Quellen stammen und regelmäßig bereinigt werden müssen. Importieren Sie den Bereich als Tabelle, filtern Sie leere Zeilen heraus und laden Sie das Ergebnis als neue Tabelle. Diese Lösung ist robust, wiederholbar und ohne Formeln wartbar.

Fazit

Für das Ziel Daten lückenlos auflisten bietet die Matrixformel eine zuverlässige Lösung in klassischen Excel-Versionen. Mit FILTER erhalten Sie in Microsoft 365 eine besonders einfache und dynamische Variante. Für automatisierte Workflows ergänzt ein kurzes VBA-Makro den Werkzeugkasten, sodass Sie Listen ohne Leerzeilen schnell und fehlerfrei erzeugen.