6 Methoden um leere Zellen zu entfernen

Fehlerhafte Dateien, das ist Alltag in der Excel- & Datenwelt. Dazu gehören auch fehlende Werte.

Entweder man möchte diese Einträge bereinigen, ergänzen oder einfach nur wissen, welche Zeilen leere Zellen aufweisen. Welche manuellen und automatisierten Wege es hierfür gibt, zeige ich dir in diesem Beitrag!

Leere Zellen manuell entfernen 👷🏻‍♂️

Bedingte Formatierung

Markiere den Bereich, auf welchen die Formatierung angewendet werden soll.

Gehe in der Registerkarte Start im Bereich Formatvorlagen auf Bedingte Formatierung -> Neue Regel…

Wähle als Regeltyp die Option Formel zur Ermittlung der zu formatierenden Zellen verwenden aus.

Unter Regelbeschreibung tragen wir die Formel =ISTLEER(A2) ein.

Neben der Vorschau der Formatierung siehst du den Button Formatieren…. Hier stellst du die Formatierungen ein.

Damit wären alle leeren Zellen formatiert und du kannst weitere Schritte in Angriff nehmen.

Inhalte auswählen

Markiere (wie bei Methode 1) den gewünschten Bereich, aus dem du leere Zellen entfernen möchtest. Drücke anschließend STRG + G oder gehe in der Registerkarte Start im Bereich Bearbeiten auf Suchen und Auswählen -> Gehe zu.

Es öffnet sich ein Menü, klicke unten links auf den Button Inhalte….

Im nächsten Menü Inhalte auswählen wählst du die Option Leerzellen aus. Bestätige mit OK.

Et voilà! Alle leeren Zellen im gewünschten Bereich sind markiert. Diese kannst du nun befüllen (STRG + Enter), formatieren oder löschen (STRG + Minus-Taste).

Daten filtern

Aktiviere für deine Daten die Filter-Funktion. Hierfür gehst du in der Registerkarte Start unter Bearbeiten in das Menü Sortieren und Filtern, hier klickst du auf Filtern.

Klicke in der gewünschten Spalte den Pfeil neben der Überschrift, es öffnet sich das Menü zum Sortieren & Filtern.

Klicke auf Alles auswählen, um alle Einträge abzuwählen. Anschließend kannst du per Suchleiste oder indem du ganz runter scrollst den Eintrag Leere auswählen.

Nun siehst du alle Zeilen mit leeren Zellen der gewünschten Spalte. Diese kannst du nun befüllen (STRG + Enter), formatieren oder löschen (STRG + Minus-Taste).

Das Entfernen leerer Zellen automatisieren ♻

FILTER-Funktion

Die FILTER-Funktion gibt dir einen vorgegebenen Datenbereich (Matrix) mit festgelegten Kriterien gefiltert zurück.

Der Vorteil ist, dass FILTER eine Array-Funktion ist. Bedeutet, dass du die Formel in einer Zelle eingibst und dir damit ein kompletter Datensatz ausgegeben werden kann!

=FILTER(
verkäufe_filter_formel[#Alle];
verkäufe_filter_formel[[#Alle];[KundenID]] <> "")

Die Funktion besteht aus drei Teilen:
1. Matrix
Die Matrix ist der Bereich mit Daten, welchen du abbilden & filtern möchtest.

2. einschließen
Hier kommen die Filterkriterien rein. Das sieht in der Grundform so aus: [Bereich] = [Kriterium]

Bei mehreren Kriterien unterscheiden wir zwischen UND & ODER. UND wird mit einem Stern (*), ODER mit einem Plus (+) ausgewertet:
([Bereich_1] = [Kriterium_1]) * ([Bereich_2] = [Kriterium_2])

Für unseren Fall der leeren Zellen:
verkäufe_filter_formel[KundenID] <> ""

[KundenID] ist die Spalte, welche gefiltert wird, die doppelten Anführungszeichen stellen Leer dar.

3. wenn_leer
Wenn die Formel keine Zeile ausgeben kann (weil kein Kriterium zutrifft), dann kann hier der Output definiert werden.
Beispiel: “KEINE TREFFER”

Als Endergebnis erhältst du die Tabelle als dynamisches Array mit allen Zeilen, welche einen Inhalt in der Spalte KundenID haben, also keine leere Zelle sind.

PivotTable

Suche in der Registerkarte Einfügen unter Tabellen den PivotTable Button und klicke auf das Icon. Wähle nun den Datenbereich aus.

Alternativ kannst du in eine intelligente Tabelle klicken und in der Registerkarte Tabellenentwurf unter Tools die Funktion Mit PivotTable zusammenfassen nutzen. Diese wählt automatisch die intelligente Tabelle als Bereich aus.

Ziehe die Spalten, aus denen die leeren Einträge entfernt werden sollen, in die Zeilen des PivotTable.

Klicke in der gewünschten Spalte den Pfeil neben der Überschrift, es öffnet sich das Menü zum Sortieren & Filtern. Suche per Suchleiste, oder indem du ganz runter scrollst, den Eintrag Leer. Entferne den Haken.

Damit hast du leere Zellen erfolgreich entfernt! 🙂

Power Query

Mit Power Query kannst du dir ebenso eine Liste ohne Zeilen mit leeren Einträgen ausgeben lassen, auch im Tabellenformat. Lade hierfür die Daten in Power Query.

Anschließend wählst du die gewünschte Spalte aus, mit Rechtsklick auf andere Spalten entfernen bleibt die gewünschte Spalte übrig. Du kannst natürlich auch alle Spalten drin lassen und nur die gewünschte Spalte filtern, sofern du alle Daten behalten möchtest.

Klicke in der gewünschten Spalte den Pfeil neben der Überschrift, es öffnet sich das Menü zum Sortieren & Filtern. Wähle die Option Leere entfernen.

Diese Option erzeugt automatisch den erforderlichen Code, um sowohl null (Zelle komplett leer) als auch "" (leerer Text) zu entfernen. Zellen, die nur ein Leerzeichen enthalten, bereinigst du vorher mit Kürzen.

Abschließend kannst du die Werte zur besseren Übersichtlichkeit sortieren.

Nun kannst du die Abfrage schließen & in ein Blatt laden.

Wähle aus, in welcher Form du die Liste haben möchtest. Entweder als intelligente Tabelle oder als PivotTable. Meine Empfehlung wäre eine Tabelle, für ein PivotTable gibt es eine einfachere Lösung.

Power Query bietet den Vorteil, dass die Daten wieder als intelligente Tabelle geladen werden können, statt als PivotTable oder Formel. Zudem können weitaus komplexere Sachverhalte hiermit gelöst werden. Damit hast du eine Liste ohne leere Zellen erzeugt, welche du bei Änderung der Daten schnell aktualisieren kannst!

Fazit 💡

In diesem Beitrag hast du sechs Methoden gelernt, um leere Zellen / Zeilen aus deinen Daten zu entfernen.

Es gibt keine bessere oder schlechtere Möglichkeit, jede dieser Methoden hat ihre eigenen Anwendungsfälle. Daher lohnt es sich, jede Vorgehensweise zu üben und sich einzuprägen.

Viel Spaß beim Ausprobieren! 🙂

Beispieldatei zum Herunterladen