Duplikate finden und entfernen: vier Methoden im Vergleich

Doppelte Einträge fallen meist erst auf, wenn eine Summe nicht stimmt: Der Verein hat angeblich mehr Mitglieder, als Beiträge eingehen, oder ein Kunde bekommt denselben Brief zweimal. Excel bietet mehrere Wege, Duplikate zu finden und zu entfernen. Sie unterscheiden sich weniger in der Bedienung als in einer Frage, die vor jedem Klick steht: Was gilt in Ihrer Liste überhaupt als „gleich“?

Dieser Artikel vergleicht vier Methoden – bedingte Formatierung, den Befehl Duplikate entfernen, die Funktion EINDEUTIG und Power Query – und zeigt, wie Sie das Ergebnis mit einer ZÄHLENWENN-Hilfsspalte kontrollieren. Alle Zahlen beziehen sich auf eine kleine Beispielliste, damit Sie jeden Schritt nachvollziehen können.

Die Beispielliste: zwölf Zeilen, aber wie viele Mitglieder?

Beispieldatei: die Mitgliederliste eines fiktiven Sportvereins. Die Liste wurde aus zwei Quellen zusammenkopiert – einem alten Anmeldeformular und einer Online-Anmeldung. Sie hat zwölf Zeilen, die Jahresbeiträge ergeben zusammen 600 Euro.

Das Zeichen ␣ in Zeile 11 steht für ein Leerzeichen am Ende der Adresse, das in Excel unsichtbar bleibt. Je nachdem, wie streng Sie vergleichen, enthält die Liste unterschiedlich viele Duplikate:

Der Verein hat acht Mitglieder, die zusammen 390 Euro zahlen. Zu diesem Ergebnis führt aber nur die letzte Regel. Bei Felix Braun unterscheiden sich die E-Mail-Adressen tatsächlich – hier hilft keine Funktion, sondern nur die Entscheidung, welche Adresse aktuell ist. Behält ein Werkzeug automatisch die erste Zeile, landet womöglich die veraltete Adresse in der bereinigten Liste.

Methode 1: Duplikate markieren mit bedingter Formatierung

Die bedingte Formatierung löscht nichts, sie färbt nur ein. Damit ist sie der sichere erste Schritt. Markieren Sie die Spalte mit den Mitgliedsnummern und wählen Sie Start > Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Doppelte Werte. In der Beispielliste werden acht Zellen eingefärbt: jeweils beide Vorkommen der Nummern 1001, 1003, 1005 und 1006.

Wichtig ist, was die Regel nicht kann: Sie bewertet einzelne Zellen, keine ganzen Zeilen. Markieren Sie mehrere Spalten, wird jeder Wert mit allen anderen Zellen im markierten Bereich verglichen – zwei gleiche Beiträge von 60 Euro gelten dann ebenfalls als „doppelt“. Für zeilenweise Prüfungen brauchen Sie eine Hilfsspalte (siehe unten). Laut Microsoft lässt sich die Regel außerdem nicht auf Felder im Wertebereich einer PivotTable anwenden.

Methode 2: Daten > Duplikate entfernen

Der Befehl steht unter Daten > Datentools > Duplikate entfernen. Im Dialog wählen Sie die Spalten aus, die zusammen den Vergleichsschlüssel bilden. Die Microsoft-Dokumentation beschreibt dabei vier Punkte, die Sie kennen sollten:

• Es zählen nur die ausgewählten Spalten als Schlüssel, gelöscht wird aber die gesamte Zeile – auch die Inhalte der nicht ausgewählten Spalten.
• Das erste Vorkommen bleibt erhalten, alle weiteren werden entfernt.
• Verglichen wird der angezeigte Wert, nicht der gespeicherte. Ein Datum, das einmal als „08.03.2006“ und einmal als „8. März 2006“ formatiert ist, gilt als zwei verschiedene Werte.
• Bei gegliederten Daten oder Listen mit Teilergebnissen funktioniert der Befehl nicht; Gliederung und Teilergebnisse müssen vorher entfernt werden.

Nach dem Klick auf OK meldet Excel, wie viele doppelte Werte entfernt wurden. Die Änderung lässt sich mit Strg+Z rückgängig machen, solange Sie die Datei nicht geschlossen haben. Microsoft empfiehlt trotzdem, die Originaldaten vorher in ein anderes Blatt zu kopieren. Wählen Sie im Beispiel nur die Spalte Nr. aus, bleiben acht Zeilen übrig – mit der alten Adresse von Felix Braun, weil Zeile 8 vor Zeile 13 steht.

Ob der Befehl Groß- und Kleinschreibung unterscheidet und wie er mit dem unsichtbaren Leerzeichen umgeht, beschreibt die Microsoft-Seite nicht ausdrücklich. Nach unserer Einschätzung sollten Sie sich darauf nicht verlassen und Textspalten vor dem Entfernen normalisieren (siehe Abschnitt zur Hilfsspalte).

Methode 3: Die Funktion EINDEUTIG

EINDEUTIG (englisch UNIQUE) liefert die eindeutigen Werte oder Zeilen eines Bereichs als dynamisches Array, das automatisch in die Nachbarzellen überläuft. Die Originaldaten bleiben unverändert. Die Syntax lautet:

=EINDEUTIG(Array;[nach_Spalte];[genau_einmal])

Mit nach_Spalte = FALSCH oder weggelassen vergleicht die Funktion Zeilen, mit WAHR Spalten. Ist genau_einmal WAHR, gibt sie nur Werte zurück, die genau einmal vorkommen. Für die Beispielliste (Nummern in B2:B13) heißt das:

=EINDEUTIG(B2:B13)                 → 8 Nummern
=ANZAHL2(EINDEUTIG(B2:B13))        → 8
=EINDEUTIG(B2:B13;;WAHR)           → 1002, 1004, 1007, 1008

Die dritte Formel ist nützlich für die Gegenprobe: Sie zeigt die vier Mitglieder, die nur einmal in der Liste stehen. Alle übrigen kommen mehrfach vor. Für ganze Zeilen geben Sie den kompletten Bereich an, etwa =EINDEUTIG(B2:E13).

EINDEUTIG ist laut Microsoft in Microsoft 365, Excel 2021 und Excel 2024 verfügbar, in Excel 2019 und älter nicht. Liegt die Quelle in einer formatierten Tabelle und verwenden Sie strukturierte Verweise wie Mitglieder[Nr.], passt sich das Ergebnis automatisch an, wenn Zeilen hinzukommen. Verweist die Formel auf eine andere Arbeitsmappe, muss diese geöffnet sein, sonst erscheint nach dem Aktualisieren #BEZUG!.

Methode 4: Power Query

Power Query eignet sich, wenn dieselbe Bereinigung regelmäßig mit neuen Daten nötig ist. Klicken Sie in die Liste und wählen Sie Daten > Aus Tabelle/Bereich. Im Power-Query-Editor markieren Sie die Schlüsselspalten und wählen Start > Zeilen verkleinern > Zeilen entfernen > Duplikate entfernen. Über Schließen & laden landet das Ergebnis in einem neuen Blatt; bei neuen Daten genügt Daten > Alle aktualisieren.

Zwei Unterschiede zu den Excel-Werkzeugen nennt die Microsoft-Dokumentation ausdrücklich. Erstens beachtet Power Query die Groß- und Kleinschreibung: „Lea.Schmid“ und „lea.schmid“ sind dort zwei verschiedene Werte. Auf alle Spalten angewendet, ergibt das in der Beispielliste elf Zeilen statt acht. Abhilfe schaffen die Transformationen Transformieren > Format > Kleinbuchstaben und Format > Kürzen vor dem Entfernen. Zweitens sichert Power Query laut Dokumentation nicht zu, dass jeweils die erste Instanz eines Duplikats erhalten bleibt. Wenn es darauf ankommt, welche Zeile übrig bleibt, sollten Sie die Reihenfolge vorher gezielt festlegen.

Kontrolle mit einer ZÄHLENWENN-Hilfsspalte

Bevor Sie etwas löschen, lohnt eine Hilfsspalte, die jede Zeile nach Ihrer eigenen Regel prüft. Zuerst bilden Sie in Spalte F einen Schlüssel aus Nummer und bereinigter Adresse:

=B2&"|"&KLEIN(GLÄTTEN(D2))

Dann zählen Sie in Spalte G, zum wievielten Mal dieser Schlüssel bis zur aktuellen Zeile auftritt:

=ZÄHLENWENN($F$2:F2;F2)

Der Bereich wächst beim Herunterziehen mit, weil nur der Anfang mit Dollarzeichen fixiert ist. Eine 1 bedeutet: erstes Vorkommen. Werte ab 2 markieren Wiederholungen. Im Beispiel steht die 2 in den Zeilen 7, 9 und 11. Ein Filter auf G = 1 zeigt die neun Zeilen der dritten Regel; die Summe der sichtbaren Beiträge beträgt 450 Euro. Zeile 13 bleibt sichtbar, weil die Adresse abweicht – genau der Fall, den Sie von Hand klären müssen.

Laut Microsoft ignoriert ZÄHLENWENN die Groß- und Kleinschreibung; KLEIN ist für die Zählung also eigentlich nicht nötig, macht den Schlüssel aber auch für Power Query und andere Werkzeuge verwendbar. Zwei Grenzen nennt die Dokumentation: Bei Zeichenfolgen mit mehr als 255 Zeichen liefert ZÄHLENWENN falsche Ergebnisse. Und führende oder abschließende Leerzeichen sowie nicht druckbare Zeichen können das Ergebnis verfälschen – deshalb GLÄTTEN (englisch TRIM) im Schlüssel.

Die vier Methoden im Vergleich

Typische Fehler und ihre Ursache

Zu viele Zeilen gelöscht. Im Dialog Duplikate entfernen war nur eine Spalte wie „Name“ ausgewählt. Zwei verschiedene Personen mit gleichem Namen gelten dann als Duplikat, und die zweite Zeile verschwindet komplett. Lösung: einen echten Schlüssel wählen (Mitgliedsnummer, Kundennummer) oder mehrere Spalten kombinieren.

Offensichtliche Dubletten bleiben stehen. Ursache sind meist unsichtbare Unterschiede: abschließende Leerzeichen, geschützte Leerzeichen oder abweichende Schreibweisen. Die Hilfsspalte mit GLÄTTEN, WECHSELN und KLEIN macht sie vergleichbar.

Gleiche Datumswerte gelten als verschieden. Weil Duplikate entfernen den angezeigten Wert vergleicht, führen unterschiedliche Datumsformate zu getrennten Einträgen. Vereinheitlichen Sie das Zahlenformat der Spalte vorher. Mehr dazu im Artikel Datumswerte in Excel.

Die falsche Version eines Datensatzes bleibt übrig. Excel behält das erste Vorkommen, Power Query sichert keine bestimmte Zeile zu. Sortieren Sie in Excel vorher so, dass die gewünschte Zeile oben steht – etwa nach Änderungsdatum absteigend –, und prüfen Sie Fälle wie Felix Braun einzeln.

Summen ändern sich unbemerkt. Wer nach dem Bereinigen nicht nachrechnet, merkt nicht, wenn zu viel entfernt wurde. Notieren Sie Zeilenzahl und Summe vorher und vergleichen Sie danach mit der Erwartung aus der Hilfsspalte.

Welche Methode wann passt

Nach unserer Einschätzung ist eine feste Reihenfolge sinnvoll: zuerst sehen, dann zählen, dann löschen. Die bedingte Formatierung zeigt in Sekunden, ob es überhaupt doppelte Schlüssel gibt. Die ZÄHLENWENN-Hilfsspalte legt fest, was Sie als gleich betrachten, und liefert die erwartete Zeilenzahl. Erst dann entfernen Sie.

Für eine einmalige Bereinigung genügt Daten > Duplikate entfernen auf einer Kopie der Daten. Brauchen Sie die eindeutige Liste nur als Auswertung oder als Quelle für eine Dropdown-Liste (siehe Dropdowns und Datenüberprüfung), ist EINDEUTIG die schonendere Wahl, sofern Ihre Excel-Version die Funktion kennt. Kommen dieselben Daten jeden Monat neu, lohnt sich Power Query – dort sollten Sie Kleinschreibung und Kürzen als feste Schritte vor das Entfernen setzen. Wie Sie Monatsdateien in Power Query zusammenführen, beschreibt der Artikel Power Query: Monatsdateien aus einem Ordner zusammenführen.

Unabhängig von der Methode gilt: Prüfen Sie nach dem Bereinigen Zeilenzahl und Summe. Stimmen beide mit der Erwartung überein – im Beispiel acht Mitglieder und 390 Euro –, ist die Liste sauber.