Kommentare - 0
Kommentare - 0
Logo DeclutterData - Icon

DeclutterData

Datenoptimierung & Controlling

^

Lesefortschritt: 0%

Start 9 Bedingte Formatierung 9 6 Methoden um leere Zellen zu entfernen

6 Methoden um leere Zellen zu entfernen

Phillip Seefeld
Tabelle mit Daten und Kreuzen
Fehler­haf­te Datei­en, das ist All­tag in der Excel- & Daten­welt. Dazu gehö­ren auch feh­len­de Wer­te.

Ent­we­der man möch­te die­se Ein­trä­ge berei­ni­gen, ergän­zen oder ein­fach nur wis­sen, wel­che Zei­len lee­re Zel­len auf­wei­sen. Wel­che manu­el­len und auto­ma­ti­sier­ten Wege es hier­für gibt, zei­ge ich dir in die­sem Beitrag!

Leere Zellen manuell entfernen 👷🏻‍♂️

Bedingte Formatierung

Mar­kie­re den Bereich, auf wel­chen die For­ma­tie­rung ange­wen­det wer­den soll.

Excel | Intelligente Tabelle | Aktive Markierung

Zel­len der Tabel­le mar­kiert

Gehe in der Regis­ter­kar­te Start im Bereich For­mat­vor­la­gen auf Beding­te For­ma­tie­rung -> Neue Regel.…

Excel | Benutzeroberfläche | Bedingte Formatierung | Neue Regel

Regel für beding­te For­ma­tie­rung erstellen

Wäh­le als Regel­typ die Opti­on For­mel zur Ermitt­lung der zu for­ma­tie­ren­den Zel­len ver­wen­den aus.
Excel | Benutzeroberfläche | Bedingte Formatierung | Neue Regel

Menü zur Erstel­lung einer neu­en Formatierungsregel

Unter Regel­be­schrei­bung tra­gen wir die For­mel =ISTLEER(A2) ein.

CHECK

Tau­sche A2 gegen die ers­te Zel­le aus, wel­che for­ma­tiert wer­den soll. Die beding­te For­ma­tie­rung wen­det die­se Regel dann auf alle ande­ren Zel­len eben­so an.

Neben der Vor­schau der For­ma­tie­rung siehst du den But­ton For­ma­tie­ren….Hier stellst du die For­ma­tie­run­gen ein.
Excel | Bedingte Formatierung | Neue Regel | Formatierung

Vor­schau & But­ton für For­ma­tie­rung

Damit wären alle lee­ren Zel­len for­ma­tiert und du kannst wei­te­re Schrit­te in Angriff nehmen.
Excel | Bedingte Formatierung | Neue Formatierungsregel ausgefüllt

Fer­ti­ge For­ma­tie­rungs­re­gel mit For­melFor­ma­tie­rung

Excel | Intelligente Tabelle | Leere, rot markierte Zellen

Lee­re Zel­len mit ange­wand­ter For­ma­tie­rungs­re­gel in rot

Inhalte auswählen

Mar­kie­re (wie bei Metho­de 1) den gewünsch­ten Bereich, aus dem du lee­re Zel­len ent­fer­nen möch­test. Drü­cke anschlie­ßend STRG + G oder gehe in der Regis­ter­kar­te Start im Bereich Bear­bei­ten auf Suchen und Aus­wäh­len -> Gehe zu.

Excel | Benutzeroberfläche | Gehe zu...

Opti­on Gehe zu…

Es öff­net sich ein Menü, kli­cke unten links auf den But­ton Inhal­te….
Excel | Benutzoberfläche | Gehe zu...

Menü der Opti­on Gehe zu…

Im nächs­ten Menü Inhal­te aus­wäh­len wählst du die Opti­on Leer­zel­len aus. Bestä­ti­ge mit OK.

Excel | Inhalte auswählen | Leerzellen

Menü zur Aus­wahl aller Leer­zel­len

Et voi­là! Alle lee­ren Zel­len im gewünsch­ten Bereich sind mar­kiert. Die­se kannst du nun befül­len (STRG + Enter), for­ma­tie­ren oder löschen (STRG + Minus-Taste).

ACHTUNG

Beim Löschen der Zel­len kön­nen Daten ver­lo­ren gehen, da oft­mals die gan­ze Zei­le der Leer­zel­le gelöscht wird!

Excel | Intelligente Tabelle | Leere, rot markierte Zellen
Leer­zel­len zur wei­te­ren Bear­bei­tung markiert

Daten filtern

Akti­vie­re für dei­ne Daten die Fil­ter-Funk­ti­on. Hier­für gehst du in der Regis­ter­kar­te Start unter Bear­bei­ten in das Menü Suchen und Aus­wäh­len, hier klickst du auf Fil­tern.

Excel | Benutzeroberfläche | Sortieren und Filtern | Filtern

Ein­fü­gen eines Fil­ters für dei­ne Daten

Kli­cke in der gewünsch­ten Spal­te den Pfeil neben der Über­schrift, es öff­net sich das Menü zum Sor­tie­ren & Fil­tern.
Excel | Intelligente Tabelle | Kopfzeile
Kopf­zei­le einer intel­li­gen­ten Tabel­le mit akti­vier­tem Filter
Kli­cke auf Alles aus­wäh­len, um alle Ein­trä­ge abzu­wäh­len. Anschlie­ßend kannst du per Such­leis­te oder indem du ganz run­ter scrollst den Ein­trag Lee­re aus­wäh­len.
Excel | Filtermenü | Leere ausgewählt

Fil­ter­me­nü mit akti­ver Aus­wahl von lee­ren Einträgen

Nun siehst du alle Zei­len mit lee­ren Zel­len der gewünsch­ten Spal­te. Die­se kannst du nun befül­len (STRG + Enter), for­ma­tie­ren oder löschen (STRG + Minus-Taste).

ACHTUNG

Beim Löschen der Zel­len kön­nen Daten ver­lo­ren gehen, da oft­mals die gan­ze Zei­le der Leer­zel­le gelöscht wird!

Excel | Intelligente Tabelle | Gefilterte Spalte (leere Zellen)

Auf lee­re Zel­len gefil­ter­te Tabelle

Das Entfernen von Duplikaten automatisieren

FILTER-Funktion

Die FILTER-Funk­ti­on gibt dir einen vor­ge­ge­be­nen Daten­be­reich (Matrix) mit fest­ge­leg­ten Kri­te­ri­en gefil­tert zurück.

INFO

Die­se Metho­de funk­tio­niert erst mit Excel 2021365 oder früher!

Der Vor­teil ist, dass FILTER eine Array-Funktion ist. Bedeu­tet, dass du die For­mel in einer Zel­le ein­gibst und dir damit ein kom­plet­ter Daten­satz aus­ge­ge­ben wer­den kann!
Excel | FILTER | Argumente

Argu­men­te der FILTER-Funk­ti­on
(optio­na­le Argu­men­te weggelassen)

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

INFO

[#Alle] wird durch die intel­li­gen­te Tabel­le auto­ma­tisch gene­riert, wenn man die Kopf­zei­le mit einbezieht.

Die Funk­ti­on besteht aus drei Tei­len:
1. Matrix
Die Matrix ist der Bereich mit Daten, wel­chen du abbil­den fil­tern möch­test.

2. ein­schlie­ßen
Hier kom­men die Fil­ter­kri­te­ri­en rein. Das sieht in der Grund­form so aus: [Bereich] = [Kri­te­ri­um]

Bei meh­re­ren Kri­te­ri­en unter­schei­den wir zwi­schen UND ODER. UND wird mit einem Stern (*), ODER mit einem Plus (+) aus­ge­wer­tet:
([Bereich_1] = [Kriterium_1]) * ([Bereich_2] = [Kriterium_2])

Für unse­ren Fall der lee­ren Zel­len:
verkäufe_filter_formel[KundenID] <> “”

[Kun­denID] ist die Spal­te, wel­che gefil­tert wird, die dop­pel­ten Anfüh­rungs­zei­chen stel­len Leer dar.

3. wenn_leer
Wenn die For­mel kei­ne Zei­le aus­ge­ben kann (weil kein Kri­te­ri­um zutrifft), dann kann hier der Out­put defi­niert wer­den.
Bei­spiel: “KEINE TREFFER”

Als End­ergeb­nis erhältst du die Tabel­le als dyna­mi­sches Array mit allen Zei­len, wel­che einen Inhalt in der Spal­te Kun­denID haben, also kei­ne lee­re Zel­le sind.

Excel | FILTER | Array

Aus­ge­ge­be­ne Lis­te (Array) durch FILTER-For­mel

PivotTable

Suche in der Regis­ter­kar­te Ein­fü­gen unter Tabel­len den Pivot­Ta­ble But­ton und kli­cke auf das Icon. Wäh­le nun den Daten­be­reich aus.

Excel | Menüpunkt PivotTable

Pivot­Ta­ble ein­fü­gen durch Benut­zer­ober­flä­che

Excel | PivotTable | Erstellen mit Spaltenbezug

Daten der Pivot­Ta­ble ohne Bezug auf intel­li­gen­te Tabelle

Alter­na­tiv kannst du in eine intel­li­gen­te Tabel­le kli­cken und in der Regis­ter­kar­te Tabel­len­ent­wurf unter Tools die Funk­ti­on Mit Pivot­Ta­ble zusam­men­fas­sen nut­zen. Die­se wählt auto­ma­tisch die intel­li­gen­te Tabel­le als Bereich aus.

Excel | Intelligente Tabelle | Mit PivotTable zusammenfassen

Pivot­Ta­ble ein­fü­gen mit Bezug auf intel­li­gen­te Tabelle

Excel | PivotTable | Erstellen mit intelligenter Tabelle

Daten der Pivot­Ta­ble mit Bezug auf intel­li­gen­te Tabelle

Zie­he die Spal­ten, aus denen die lee­ren Ein­trä­ge ent­fernt wer­den sol­len, in die Zei­len des Pivot­Ta­ble.

PivotTable mit Auswahl einer Spalte als Zeile

Spal­te für Lis­te ohne Leer­zel­len als Zei­len des PivotTable

Kli­cke in der gewünsch­ten Spal­te den Pfeil neben der Über­schrift, es öff­net sich das Menü zum Sor­tie­ren & Fil­tern. Suche per Such­leis­te, oder indem du ganz run­ter scrollst, den Ein­trag Leer. Ent­fer­ne den Haken.
Excel | PivotTable | Filtermenü | Leer ausgefiltert

Fil­ter­me­nü zum Ent­fer­nen von Leerzellen

Damit hast du lee­re Zel­len erfolg­reich ent­fernt! 🙂

Power Query

Mit Power Query kannst du dir eben­so eine Lis­te ohne Zei­len mit lee­ren Ein­trä­gen aus­ge­ben las­sen, auch im Tabel­len­for­mat. Lade hier­für die Daten in Power Query.

Power Query | Abfrage von Tabelle

Gela­de­ne Tabel­le in Power Query

Anschlie­ßend wählst du die gewünsch­te Spal­te aus, mit Rechts­klick auf ande­re Spal­ten ent­fer­nen bleibt die gewünsch­te Spal­te übrig. Du kannst natür­lich auch alle Spal­ten drin las­sen und nur die gewünsch­te Spal­te fil­tern, sofern du alle Daten behal­ten möchtest.

Excel | Power Query | Spalte beibehalten

Rele­van­te Spal­te durch Ent­fer­nen aller ande­ren Spal­ten auswählen

Power Query | Spalte mit KundenIDs & null

Spal­te mit null-Wert (leer)

Kli­cke in der gewünsch­ten Spal­te den Pfeil neben der Über­schrift, es öff­net sich das Menü zum Sor­tie­ren & Fil­tern. Wäh­le die Opti­on Lee­re ent­fer­nen.
Power Query | Filtermenü | Leere entfernen

Menü­punkt zum Ent­fer­nen lee­rer Zellen

Die­se Opti­on erzeugt auto­ma­tisch den erfor­der­li­chen Code, um sowohl null (Zel­le kom­plett leer) als auch “” (Leer­zei­chen) zu entfernen.

Power Query | Table.SelectRows | Leere / null entfernen

Durch Lee­re ent­fer­nen erzeug­ter Power Query Code

Abschlie­ßend kannst du die Wer­te zur bes­se­ren Über­sicht­lich­keit sor­tie­ren.

Excel | Power Query | Aufsteigend sortieren

Ein­deu­ti­ge Lis­te auf- oder abstei­gend sortieren

Nun kannst du die Abfra­ge schlie­ßen & in ein Blatt laden.

Power Query | Schließen & Laden

Laden der Abfra­ge in Arbeitsmappe

Wäh­le aus, in wel­cher Form du die Lis­te haben möch­test. Ent­we­der als intel­li­gen­te Tabel­le oder als Pivot­Ta­ble. Mei­ne Emp­feh­lung wäre eine Tabel­le, für ein Pivot­Ta­ble gibt es eine ein­fa­che­re Lösung.

Excel | Power Query | Daten importieren

Optio­nen zum Impor­tie­ren der Daten in die Arbeitsmappe

Power Query bie­tet den Vor­teil, dass die Daten wie­der als intel­li­gen­te Tabel­le gela­den wer­den kön­nen, statt als Pivot­Ta­ble oder For­mel. Zudem kön­nen weit­aus kom­ple­xe­re Sach­ver­hal­te hier­mit gelöst wer­den. Damit hast du eine Lis­te ohne lee­re Zel­len erzeugt, wel­che du bei Ände­rung der Daten schnell aktua­li­sie­ren kannst!
Excel | Intelligente Tabelle mit eindeutigen Kundennummern

Query Abfra­ge als intel­li­gen­te Tabelle

Fazit 💡

In die­sem Bei­trag hast du sechs Metho­den gelernt, um lee­re Zel­len / Zei­len aus dei­nen Daten zu entfernen.

Es gibt kei­ne bes­se­re oder schlech­te­re Mög­lich­keit, jede die­ser Metho­den hat ihre eige­nen Anwen­dungs­fäl­le. Daher lohnt es sich, jede Vor­ge­hens­wei­se zu üben und sich einzuprägen.

Viel Spaß beim Ausprobieren! 🙂

Phillip Seefeld

Ich möchte dir helfen deine Daten & Zahlen auf das nächste Level zu bringen! Hier im Blog schreibe ich über alles Mögliche zu Excel & Power BI. Mehr Austausch? Tritt DeclutterData auf Reddit bei!
Ich helfe dir!

Schwierigkeiten mit Excel & Power BI?

Das könnte dir auch gefallen 🙂

Share This

Bitte weitersagen 🙂

Beitrag teilen und Wissen verbreiten. Danke 💚