M365Con 2027 – die kostenlose Online-Konferenz · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Duplikate in Excel finden: markieren, zählen und auflisten

Von · · Aktualisiert

Doppelte Einträge in Excel finden mit bedingter Formatierung, COUNTIF-Hilfsspalten, EINDEUTIG, Power Query und Prüfungen über mehrere Blätter. Außerdem erfahren Sie, wie Sie ähnliche Einträge erkennen, bevor Sie etwas löschen.

Automatisch mit KI aus dem Originalbeitrag übersetzt. Englisches Original lesen

Um doppelte Einträge in Excel zu finden, ohne etwas zu löschen, markieren Sie Ihre Daten und wählen Sie Start > Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Doppelte Werte. Excel färbt jeden Wert ein, der mehrfach vorkommt. Wenn Sie statt Farben Zahlen möchten, ergänzen Sie eine Hilfsspalte mit einer COUNTIF-Formel. Dieser Artikel richtet sich an Excel-Anwender, die unübersichtliche Listen übernehmen und das Problem sichtbar machen müssen, bevor sie es beheben. Sie erhalten die Markierungsmethode, Zähl- und Kennzeichnungsformeln, eine Möglichkeit, nur die mehrfach vorkommenden Werte aufzulisten, eine Power-Query-Option, Prüfungen über mehrere Blätter und ein Vorgehen für ähnliche Einträge.

Das Wichtigste in Kürze

  • Der schnellste Weg, Duplikate in Excel zu finden, führt über Start, Bedingte Formatierung, Regeln zum Hervorheben von Zellen, Doppelte Werte. Die Funktion färbt wiederholte Werte ein und löscht nichts.
  • Eine COUNTIF-Hilfsspalte zeigt, wie oft jeder Wert vorkommt. Mit ZÄHLENWENNS können Sie außerdem mehrere Spalten gemeinsam prüfen.
  • COUNTIF ignoriert Groß- und Kleinschreibung und behandelt ? und * als Platzhalter. Dadurch kann das Ergebnis von Ihren Erwartungen abweichen.
  • Um zwei Blätter zu vergleichen, zählen Sie jeden Wert gegen die andere Liste mit COUNTIF. Wenn die zweite Liste in einer anderen Datei liegt, öffnen Sie beide Arbeitsmappen.
  • Ähnliche Einträge wie Jon und John werden nicht als Duplikate erkannt. Bereinigen Sie zuerst Leerzeichen und Groß- und Kleinschreibung, und prüfen Sie den Rest dann manuell oder mit dem Add-In Fuzzy Lookup.

Zuerst finden, dann entfernen. Wenn Sie bereits wissen, welche Zeilen Sie löschen möchten, behandelt unser begleitender Leitfaden zum Entfernen von Duplikaten in Excel diesen Schritt. Hier geht es darum, zuerst in Excel zu prüfen, damit Sie festlegen, was als Duplikat gilt und was Sie behalten.

Doppelte Werte mit bedingter Formatierung hervorheben

Dies ist die schnellste Prüfung, und Microsoft empfiehlt sie als Überprüfungsschritt, bevor Sie etwas entfernen. Sie folgt dem Microsoft-Artikel zum Filtern nach eindeutigen Werten oder zum Entfernen doppelter Werte.

  1. Markieren Sie die Zellen, die Spalte oder den Bereich, den Sie prüfen möchten. Meist reicht eine Spalte aus.
  2. Öffnen Sie auf der Registerkarte Start die Option Bedingte Formatierung.
  3. Wählen Sie Regeln zum Hervorheben von Zellen und danach Doppelte Werte.
  4. Wählen Sie ein Format, etwa eine helle rote Füllung, und klicken Sie auf OK.

Jede Zelle, deren Wert mehrfach vorkommt, ist nun eingefärbt. Scrollen Sie durch das Blatt oder filtern Sie die Spalte nach Farbe, um nur die markierten Zellen zu sehen. Um die Markierung rückgängig zu machen, öffnen Sie erneut die bedingte Formatierung und entfernen Sie die Regeln aus dem Bereich.

Es gibt eine zweite Methode, die mehr Kontrolle bietet. Öffnen Sie Bedingte Formatierung > Regeln verwalten, erstellen Sie eine Neue Regel und wählen Sie Nur eindeutige oder doppelte Werte formatieren. In der Liste Alle formatieren können Sie eindeutig oder doppelt auswählen und das Zahlenformat, die Schriftart, den Rahmen oder die Füllung festlegen. Das ist praktisch, wenn Sie nur die einmalig vorkommenden Werte färben und die mehrfach vorkommenden unverändert lassen möchten.

Zwei Einschränkungen sollten Sie kennen. Laut Microsoft kann Excel doppelte Werte im Bereich „Werte“ einer PivotTable nicht hervorheben. Außerdem prüft die Regel bei nur einer ausgewählten Spalte jeweils nur diese Spalte. Eine Zeile, die sich nur wiederholt, wenn Sie mehrere Spalten gemeinsam betrachten, wird nicht markiert. Dafür brauchen Sie eine Formel.

Doppelte Einträge mit COUNTIF zählen und markieren

Eine Hilfsspalte liefert Ihnen eine Zahl, nach der Sie sortieren und filtern können. Sie ist außerdem das richtige Werkzeug, wenn zwei Zeilen nur dann Duplikate sind, wenn zwei oder drei Spalten übereinstimmen.

Jede Wiederholung markieren

Angenommen, Ihre Werte stehen in A2:A100. Geben Sie in B2 Folgendes ein:

=COUNTIF($A$2:$A$100,A2)

Kopieren Sie die Formel nach unten. Ein Ergebnis von 1 bedeutet, dass der Wert eindeutig ist. Eine 2 bedeutet, dass er zweimal vorkommt, und so weiter. Jede Kopie eines doppelten Werts erhält dieselbe Zahl, auch die erste. Um die Anzahl in WAHR oder FALSCH umzuwandeln, ergänzen Sie einen Vergleich: =COUNTIF($A$2:$A$100,A2)>1.

Nur die späteren Kopien markieren

Wenn Sie Zeilen löschen möchten, behalten Sie in der Regel die erste. Verwenden Sie dafür einen wachsenden Bereich:

=COUNTIF(A$2:A2,A2)>1

Der Bereich A$2:A2 wächst, wenn Sie die Formel nach unten kopieren. Das erste Auftreten eines Werts ergibt FALSCH. Jedes spätere Auftreten ergibt WAHR. Das entspricht dem Verhalten des Befehls „Duplikate entfernen“ in Excel, da dieser das erste Vorkommen beibehält.

Mehrere Spalten mit COUNTIFS prüfen

Bei einem Datensatz, der nur dann ein Duplikat ist, wenn beispielsweise Vorname und Nachname übereinstimmen, verwenden Sie COUNTIFS mit einem Paar aus Bereich und Kriterium je Spalte:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

Ein Ergebnis über 1 bedeutet, dass die Kombination mehrfach vorkommt. Für weitere Spalten fügen Sie weitere Paare hinzu. Versuchen Sie nicht, dieselbe Prüfung nachzubauen, indem Sie die Bereiche innerhalb von COUNTIF verknüpfen, etwa so: COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF erwartet als erstes Argument einen echten Bereich, daher tut diese Formel nicht, was sie zu tun scheint. COUNTIFS ist die saubere Lösung.

Was COUNTIF anders macht, als man erwartet

Die COUNTIF-Seite von Microsoft beschreibt einige Verhaltensweisen, die bei der Suche nach Duplikaten wichtig sind:

  • Der Textvergleich unterscheidet nicht zwischen Groß- und Kleinschreibung. „apples“ und „APPLES“ zählen als derselbe Wert. Excel behandelt sie hier als Duplikate.
  • Die Zeichen ? und * wirken als Platzhalter. Wenn Ihre Daten sie enthalten, zum Beispiel in Produktcodes, erfasst COUNTIF unter Umständen mehr, als Sie erwarten. Stellen Sie dem Zeichen eine Tilde voran, um es wörtlich zu suchen.
  • COUNTIF liefert bei Zeichenketten mit mehr als 255 Zeichen falsche Ergebnisse.
  • Führende oder nachgestellte Leerzeichen, gemischte gerade und typografische Anführungszeichen sowie nicht druckbare Zeichen können die Zählung verfälschen. Microsoft empfiehlt, die Daten zuerst mit den Funktionen TRIM und CLEAN zu bereinigen.

Genau dieser letzte Punkt steckt hinter den meisten Beschwerden à la „Excel findet meine Duplikate nicht“. Zwei Zellen, die identisch aussehen, können sich durch ein nachgestelltes Leerzeichen unterscheiden. Erst bereinigen, dann zählen.

Filtern und prüfen

Filtern Sie nach dem Anlegen der Hilfsspalte nach WAHR oder nach Werten größer 1 und sehen Sie sich die Zeilen an. Sie können nach der Hilfsspalte sortieren, um alle wiederholten Zeilen zusammenzufassen. Prüfen Sie eine Stichprobe, bevor Sie etwas löschen. Oft stellt sich ein vermeintliches „Duplikat“ als zwei echte Datensätze heraus, die denselben Namen tragen.

Nur die doppelten Werte auflisten

Manchmal möchten Sie eine kurze Liste dessen, was sich wiederholt, statt einer Markierung neben jeder Zeile.

In Excel für Microsoft 365, 2024 und 2021 übernimmt die Funktion UNIQUE diese Aufgabe. Laut Microsoft-Seite zu UNIQUE gibt =UNIQUE(array,[by_col],[exactly_once]) die eindeutigen Werte eines Bereichs zurück. Wenn Sie exactly_once auf TRUE setzen, werden nur Werte zurückgegeben, die genau einmal vorkommen. So liefert =UNIQUE(A2:A100) jeden Wert einmal, und =UNIQUE(A2:A100,,TRUE) liefert die Werte, die überhaupt nicht doppelt vorkommen. Für ausschließlich die doppelten Werte kombinieren Sie UNIQUE mit FILTER und COUNTIF:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

Diese Formel behält die Zeilen, deren Anzahl größer als 1 ist, und zeigt jeden davon einmal an. Dafür ist eine Excel-Version mit dynamischen Arrays erforderlich. Testen Sie die Formel an wenigen Zeilen, bevor Sie ihr bei einer großen Liste vertrauen.

In älteren Versionen verwenden Sie eine Hilfsspalte. Geben Sie =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") ein und kopieren Sie die Formel nach unten. Die Spalte zeigt nun jeden wiederholten Wert und lässt die übrigen Zellen leer. Blenden Sie die leeren Zellen per Filter aus. Sie sehen jeden wiederholten Wert so oft, wie er vorkommt, was für eine Prüfung völlig ausreicht.

Mit Erweitert können Sie nur die eindeutigen Datensätze an eine neue Stelle kopieren. Wechseln Sie zu Daten > Erweitert, wählen Sie In einen anderen Speicherort kopieren, geben Sie im Feld Kopieren nach eine Zelle ein und aktivieren Sie Nur eindeutige Datensätze. Der Vergleich dieser Liste mit dem Original zeigt, was sich wiederholt hat.

Mit Power Query nur die Duplikate behalten

Wenn Ihre Daten umfangreich sind oder jede Woche neu eintreffen, lassen Sie Power Query die Duplikate finden. Die Dokumentation von Microsoft zu Power Query beschreibt einen Befehl Duplikate behalten, das Gegenstück zu Duplikate entfernen. Er filtert die Tabelle auf die Zeilen, deren Werte in den ausgewählten Spalten mehrfach vorkommen. Anders als bei Duplikate entfernen gibt er alle passenden Zeilen zurück und nicht nur eine pro Wert.

Der Microsoft-Artikel zum Behalten oder Entfernen doppelter Zeilen beschreibt die Schritte für das Entfernen. Der Anfang ist für beide Varianten gleich:

  1. Wählen Sie eine Zelle in Ihren Daten aus und wechseln Sie zu Abfrage > Bearbeiten, oder laden Sie die Daten über Daten > Aus Tabelle/Bereich.
  2. Klicken Sie auf eine Spaltenüberschrift, um die Spalte auszuwählen. Halten Sie die Umschalttaste oder Strg gedrückt, um weitere Spalten auszuwählen.
  3. Wenden Sie den Befehl an. Die Schritte von Microsoft zum Entfernen verwenden Start > Zeilen entfernen > Duplikate entfernen. Suchen Sie auf der Registerkarte Start bei den anderen Zeilenbefehlen nach dem passenden Befehl Duplikate behalten und prüfen Sie die Vorschau.

Microsoft weist außerdem darauf hin, dass Power Query zwischen Groß- und Kleinschreibung unterscheidet. „Apple“ und „apple“ sind dort also verschiedene Werte, anders als bei COUNTIF. Das ist ein echter Unterschied zwischen beiden Methoden. Wenn Sie eine wiederholbare wöchentliche Prüfung wollen, sollten Sie sich für ein Verhalten entscheiden und dabei bleiben, zum Beispiel indem Sie die Spalte vorher in Großbuchstaben umwandeln.

Duplikate zwischen zwei Tabellenblättern oder Listen finden

Eine typische Aufgabe ist die Prüfung, ob eine Liste auf einem Blatt auch auf einem anderen vorkommt: eine neue Kundenliste gegen die alte oder die Bestellungen dieses Monats gegen die des Vormonats.

  1. Legen Sie die beiden Listen auf getrennte Blätter, zum Beispiel Tabelle1 und Tabelle2, wobei die Werte in Spalte A stehen.
  2. Geben Sie auf Tabelle1 in B2 die Formel =COUNTIF(Tabelle2!A:A;A2)>0 ein.
  3. Kopieren Sie die Formel nach unten. WAHR bedeutet, dass der Wert auch auf Tabelle2 vorkommt.
  4. Filtern Sie nach WAHR, um die Überschneidungen zu sehen, oder nach FALSCH, um die neuen Einträge zu sehen.

Für zwei Spalten, die gemeinsam übereinstimmen müssen, verwenden Sie COUNTIFS mit einem Paar für jede Spalte und den Bereichen des anderen Blatts.

Wenn die zweite Liste in einer anderen Arbeitsmappe liegt, warnt Microsoft, dass eine Formel, die auf eine geschlossene Arbeitsmappe verweist, den Fehler #WERT! zurückgeben kann. Halten Sie beide Arbeitsmappen während der Arbeit geöffnet oder binden Sie die zweite Liste mit Power Query in die erste ein. Denken Sie nach der Prüfung daran, dass das Ergebnis vom Datenstand zu diesem Zeitpunkt abhängt. Ändert sich eine der beiden Listen, ändern sich auch die Markierungen.

Ähnliche Treffer: Jon Smith und John Smith

Alles oben Genannte findet nur exakte Übereinstimmungen, abgesehen von der Groß- und Kleinschreibung. Echte Listen enthalten aber auch Tippfehler und unterschiedliche Schreibweisen. Excel hat keine integrierte Funktion, die die Ähnlichkeit zweier Texte bewertet. Deshalb gehen Sie in zwei Schritten vor.

Schritt eins: die falschen Unterschiede entfernen. Verwenden Sie GLÄTTEN, um zusätzliche Leerzeichen zu entfernen, und SÄUBERN, um nicht druckbare Zeichen zu entfernen, und bauen Sie die Prüfung auf den bereinigten Werten auf. In einer Hilfsspalte zeigt =GLÄTTEN(A2) den bereinigten Text. Zählen Sie die Duplikate in dieser Spalte und nicht im Original. Da COUNTIF die Groß- und Kleinschreibung ignoriert, müssen Sie dort die Schreibweise nicht ändern. In Power Query, wo die Groß- und Kleinschreibung eine Rolle spielt, wandeln Sie die Spalte in Groß- oder Kleinbuchstaben um.

Schritt zwei: Prüfen Sie, was übrig bleibt. Sortieren Sie die bereinigte Liste alphabetisch und gehen Sie die benachbarten Einträge durch. Ähnliche Treffer wie „Jon Smith“ und „John Smith“ landen dadurch nebeneinander, sodass ein Mensch sie erkennen kann. Bei größeren Listen hat Microsoft Research das Fuzzy Lookup add-in for Excel veröffentlicht. Es kann unscharfe Duplikate innerhalb einer Tabelle finden oder ähnliche Zeilen über zwei Tabellen hinweg abgleichen, und es vergibt für jeden Treffer einen Ähnlichkeitswert. Prüfen Sie die Voraussetzungen, bevor Sie es einplanen: Die Downloadseite nennt Windows mit Excel 2007, 2010, 2013 und 2016 sowie einen Stand von 2020. Die Installation auf einer aktuellen Microsoft 365-Umgebung kann scheitern, und unter Mac steht das Add-in nicht zur Verfügung.

Egal, was Sie verwenden: Prüfen Sie unscharfe Ergebnisse manuell. Zwei Personen mit ähnlichen Namen sind nicht zwangsläufig dieselbe Person.

Häufige Fehler

  • Nur eine Spalte prüfen, obwohl das Duplikat über zwei Felder definiert wird. Ein doppelter Nachname allein ist noch kein doppelter Datensatz.
  • COUNTIF-Ergebnisse bei Daten mit überflüssigen Leerzeichen oder Sonderzeichen vertrauen. Bereinigen Sie die Daten zuerst mit TRIM und CLEAN.
  • Übersehen, dass COUNTIF die Groß- und Kleinschreibung ignoriert, Power Query sie aber berücksichtigt. Dadurch erhalten Sie mit beiden Methoden unterschiedliche Ergebnisse.
  • COUNTIF($A$2:$A$100,A2)>1 verwenden und jede WAHR-Zeile löschen. Dabei wird auch die erste Kopie markiert, sodass Sie alle Kopien löschen würden. Verwenden Sie stattdessen den wachsenden Bereich, um nur die späteren Einträge zu kennzeichnen.
  • Ganze Spalten nach Zeichenketten durchsuchen, die länger als 255 Zeichen sind. Dabei liefert COUNTIF falsche Ergebnisse.
  • Direkt nach dem Markieren löschen. Kopieren Sie das Tabellenblatt vorher, denn Microsoft weist darauf hin, dass entfernte Duplikate dauerhaft gelöscht werden.

Verwandte Themen zum Vertiefen

  • Entfernen Sie die gefundenen Duplikate. Der Befehl Duplikate entfernen auf der Registerkarte Daten behält den ersten Treffer und löscht den Rest. Sortieren Sie vorher, um zu steuern, welche Zeile erhalten bleibt.
  • Behalten Sie den besten Datensatz, nicht den ersten. Sortieren Sie nach Datum oder Wert, damit die gewünschte Zeile vorne steht, und markieren Sie dann die späteren Kopien mit dem wachsenden COUNTIF.
  • Listen über mehrere Dateien vergleichen. Mit Power Query können Sie mehrere Arbeitsmappen zu einer Tabelle zusammenführen, sodass Sie sie gemeinsam prüfen können.
  • Neue Duplikate verhindern. Datenüberprüfung mit einer benutzerdefinierten Formel kann einen Wert blockieren, der bereits vorhanden ist. Probieren Sie das zuerst auf einem Testblatt aus.
  • Eine Nachvollziehbarkeit sicherstellen. Lassen Sie hilfsweise die Hilfsspalte in einer Kopie des Tabellenblatts stehen, damit Sie später nachweisen können, was markiert wurde und warum.

Nächster Schritt

Wählen Sie eine Spalte in Ihrer aktuellen Datei aus, markieren Sie ihre Duplikate mit bedingter Formatierung und zählen Sie, wie viele Zellen hervorgehoben werden. Fügen Sie dann eine COUNTIF-Hilfsspalte hinzu und vergleichen Sie die Zahlen. Wenn sie voneinander abweichen, haben Sie gerade versteckte Leerzeichen oder Unterschiede bei der Groß- und Kleinschreibung gefunden, und Sie wissen nun, wie Sie Duplikate in Excel finden, bevor Sie etwas löschen.

Quellen

Entitäten in diesem Artikel

Microsoft Excel Power Query

Verwandte Artikel

RSS