Wie man doppelte Zellen und Zeilen in Excel hervorhebt

In diesem Tutorial lernen Sie, wie man Duplikate in Excel anzeigen kann. Wir sehen uns verschiedene Methoden an, um doppelte Zellen, ganze Zeilen oder aufeinanderfolgende Duplikate mit Hilfe von bedingter Formatierung zu schattieren. Außerdem werden Sie sehen, wie Sie Duplikate mit einem speziellen Tool farblich hervorheben können.

In der letzten Woche haben wir uns mit verschiedenen Möglichkeiten beschäftigt, Duplikate in Excel zu erkennen. Zweifellos sind die Formeln für doppelte Einträge sehr nützlich, aber das Hervorheben von doppelten Einträgen mit einer bestimmten Farbe könnte die Datenanalyse noch einfacher machen.

Die schnellste Methode zum Auffinden und Hervorheben von Duplikaten in Excel ist die Verwendung der bedingten Formatierung. Der größte Vorteil dieser Methode ist, dass sie Ihnen nicht nur die vorhandenen Duplikate anzeigt, sondern auch neue Duplikate erkennt und einfärbt, wenn Sie Ihre Daten eingeben, bearbeiten oder überschreiben.

Im weiteren Verlauf dieses Tutorials finden Sie eine Reihe von Möglichkeiten, um doppelte Datensätze je nach Ihrer spezifischen Aufgabe hervorzuheben. Diese Techniken funktionieren in allen Versionen von Excel 2016, Excel 2013, Excel 2010 und niedriger.

• Hervorheben von Duplikaten in Excel mit 1. Vorkommen (eingebaute-Auftreten (eingebaute Regel)
• Hervorheben von Duplikaten in Excel mit Ausnahme des 1. Auftretens
• Hervorheben von Duplikaten in einem Bereich (mehrere Spalten)
• Hervorheben von N-ten und nachfolgenden Duplikaten Datensätze
• Gesamte Zeilen basierend auf doppelten Werten in einer Spalte schattieren
• Wie man doppelte Zeilen in Excel hervorhebt
• Wie man aufeinanderfolgende doppelte Zellen anzeigt
• Duplicate Remover – der schnellste Weg, um Duplikate in Excel zu markieren

Wie man Duplikate in Excel mit der eingebauten Regel (mit 1. Vorkommen)

Für den Anfang, In allen Excel-Versionen gibt es eine vordefinierte Regel zum Hervorheben von doppelten Zellen. Um diese Regel in Ihren Arbeitsblättern zu verwenden, führen Sie die folgenden Schritte aus:

1. Wählen Sie die Daten aus, die Sie auf Duplikate prüfen möchten. Dies kann eine Spalte, eine Zeile oder ein Bereich von Zellen sein.
2. Klicken Sie auf der Registerkarte Start in der Gruppe Formatvorlagen auf Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Doppelte Werte…
   
3. Das Dialogfenster „Doppelte Werte“ öffnet sich mit dem standardmäßig ausgewählten Format „Hellrote Füllung“ und „Dunkelroter Text“. Um das Standardformat anzuwenden, klicken Sie einfach auf OK.

Neben der roten Füllung und der Textformatierung stehen in der Dropdown-Liste eine Handvoll weiterer vordefinierter Formate zur Verfügung. Um Duplikate mit einer anderen Farbe zu markieren, klicken Sie auf Benutzerdefiniertes Format… (das letzte Element in der Dropdown-Liste) und wählen Sie die gewünschte Füll- und/oder Schriftfarbe aus.

Mit der eingebauten Regel können Sie Duplikate in einer Spalte oder in mehreren Spalten hervorheben, wie im folgenden Screenshot gezeigt:

Wenn Sie die Excel-eigene Regel zum Hervorheben doppelter Werte verwenden, beachten Sie bitte die folgenden beiden Dinge:

• Sie funktioniert nur für einzelne Zellen. Um doppelte Zeilen zu markieren, müssen Sie eigene Regeln erstellen, die entweder auf Werten in einer bestimmten Spalte basieren oder Werte in mehreren Spalten vergleichen.
• Sie schattet doppelte Zellen einschließlich ihres ersten Auftretens ab. Um alle Duplikate mit Ausnahme des ersten Vorkommens hervorzuheben, erstellen Sie eine bedingte Formatierungsregel auf der Grundlage dieser Formel.

So heben Sie Duplikate ohne das erste Auftreten hervor

Um das zweite und alle folgenden Duplikate hervorzuheben, markieren Sie die Zellen, die Sie einfärben möchten, und erstellen Sie eine formelbasierte Regel auf diese Weise:

1. Klicken Sie auf der Registerkarte Start in der Gruppe Formatvorlagen auf Bedingte Formatierung > Neue Regel > Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.
2. Geben Sie in das Feld Werte formatieren, auf die diese Formel zutrifft eine Formel ähnlich der folgenden ein:
   =COUNTIF($A:$A2,$A2)>1

   wobei A2 die oberste Zelle des ausgewählten Bereichs ist.

3. Klicken Sie auf die Schaltfläche Formatieren… und wählen Sie die gewünschte Füll- und/oder Schriftfarbe.
4. Klicken Sie abschließend auf OK, um die Regel zu speichern und anzuwenden.

Wenn Sie noch nicht viel Erfahrung mit der bedingten Formatierung in Excel haben, finden Sie die detaillierten Schritte zur Erstellung einer formelbasierten Regel im folgenden Tutorial: Excel bedingte Formatierung basierend auf einem anderen Zellwert.

Als Ergebnis werden die doppelten Zellen mit Ausnahme der ersten Instanzen mit der von Ihnen gewählten Farbe hervorgehoben:

So zeigen Sie den dritten, vierten und alle folgenden doppelten Datensätze an

Um Duplikate ab dem N-ten Vorkommen anzuzeigen, erstellen Sie eine bedingte Formatierungsregel auf der Grundlage der Formel wie im vorherigen Beispiel, mit dem einzigen Unterschied, dass Sie >1 am Ende der Formel durch die gewünschte Zahl ersetzen. Beispiel:

Um die dritte und alle folgenden doppelten Instanzen hervorzuheben, erstellen Sie eine bedingte Formatierungsregel, die auf dieser Formel basiert:

=COUNTIF($A:$A2,$A2)>=3

Um die vierte und alle folgenden doppelten Datensätze hervorzuheben, verwenden Sie diese Formel:

=COUNTIF($A:$A2,$A2)>=4

Um nur bestimmte Vorkommen hervorzuheben, verwenden Sie den Gleichheitsoperator (=). Um z. B. nur die 2. Instanz hervorzuheben, würden Sie diese Formel verwenden:

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

Wie man Duplikate in einem Bereich (mehrere Spalten) hervorhebt)

Wenn Sie auf Duplikate über mehrere Spalten hinweg prüfen möchten, nicht indem Sie die Spalten miteinander vergleichen, sondern alle Instanzen desselben Elements in allen Spalten finden möchten, verwenden Sie eine der folgenden Lösungen.

Duplikate in mehreren Spalten einschließlich des ersten Auftretens hervorheben

Wenn die erste Instanz eines Elements, das mehr als einmal im Datensatz vorkommt, als Duplikat gilt, ist es am einfachsten, die in Excel integrierte Regel für Duplikate zu verwenden.

Oder Sie erstellen eine bedingte Formatierungsregel mit dieser Formel:

Zum Beispiel, um Duplikate im Bereich A2:C8, lautet die Formel wie folgt:

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

Bitte beachten Sie die Verwendung von absoluten Zellreferenzen für den Bereich ($A$2:$C$8) und relativen Referenzen für die oberste Zelle (A2).

Duplikate in mehreren Spalten ohne 1. Auftreten hervorheben

Die Lösung für dieses Szenario ist viel kniffliger, kein Wunder, dass Excel keine eingebaute Regel dafür hat 🙂

Um doppelte Einträge in mehreren Spalten ohne das 1. Vorkommen zu markieren, müssen Sie 2 Regeln mit den folgenden Formeln erstellen:

Regel 1. Gilt für die erste Spalte

Hier verwenden Sie genau die gleiche Formel, die wir zum Hervorheben von Duplikaten ohne 1. Vorkommen in einer Spalte verwendet haben (die detaillierten Schritte finden Sie hier).

In diesem Beispiel erstellen wir eine Regel für A2:A8 mit dieser Formel:

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

Als Ergebnis werden die doppelten Elemente ohne 1. Vorkommen in der äußersten linken Spalte des Bereichs hervorgehoben (in unserem Fall gibt es nur ein solches Element):

Regel 2. Gilt für alle nachfolgenden Spalten

Um Duplikate in den verbleibenden Spalten (B2:C8) hervorzuheben, verwenden Sie diese Formel:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

In der obigen Formel zählt die erste COUNTIF-Funktion die Vorkommen eines bestimmten Elements in der ersten Spalte, und die zweite COUNTIF tut dasselbe für alle nachfolgenden Spalten. Dann addiert man diese Zahlen und prüft, ob die Summe größer als 1 ist.

Als Ergebnis werden alle doppelten Elemente mit Ausnahme ihres ersten Auftretens gefunden und hervorgehoben:

Duplikate in allen Spalten mit einer einzigen Regel hervorheben

Eine andere mögliche Lösung besteht darin, eine leere Spalte auf der linken Seite Ihres Datensatzes hinzuzufügen und die obigen Formeln zu einer einzigen Formel wie dieser zu kombinieren:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Wobei B2 die oberste Zelle mit Daten in der zweiten Spalte des Zielbereichs ist.

Zum besseren Verständnis der Formel zerlegen wir sie in zwei Hauptteile:

• Für die erste Spalte (B) ist die WENN-Bedingung nie erfüllt, so dass nur die zweite COUNTIF-Funktion angewendet wird, die die reguläre Formel ist, die wir bereits zum Auffinden von Duplikaten ohne 1.
• Für alle folgenden Spalten (C2:D8) ist der Schlüsselpunkt die geschickte Verwendung von absoluten und relativen Referenzen in den beiden COUNTIF-Funktionen. Zum besseren Verständnis habe ich die Formel in die Spalte G kopiert, damit Sie sehen können, wie sie sich verändert, wenn sie auf andere Zellen angewendet wird:
  

Da die WENN-Bedingung für alle Spalten außer der ersten immer WAHR ist (Anzahl der Spalten ist größer als 1), geht die Formel folgendermaßen vor:

• Zählt die Anzahl der Vorkommen eines bestimmten Elements (D5 im Screenshot oben) in allen Spalten links von der angegebenen Spalte: COUNTIF(B:$C,D5)
• Zählt die Anzahl der Vorkommen eines bestimmten Elements in der Spalte des Elements bis zur Zelle des Elements: COUNTIF(D:D5,D5)
• Schließlich addiert die Formel die Ergebnisse der beiden COUNTIF-Funktionen. Wenn die Gesamtzahl größer als 1 ist, d.h. wenn das Element mehr als einmal vorkommt, wird die Regel angewendet und das Element hervorgehoben.

Ganze Zeilen auf der Grundlage doppelter Werte in einer Spalte hervorheben

Wenn Ihre Tabelle mehrere Spalten enthält, möchten Sie vielleicht ganze Zeilen auf der Grundlage doppelter Datensätze in einer bestimmten Spalte hervorheben.

Wie Sie bereits wissen, funktioniert die in Excel integrierte Regel für Duplikate nur auf Zellebene. Aber eine benutzerdefinierte formelbasierte Regel hat kein Problem mit der Schattierung von Zeilen. Der springende Punkt ist, die gesamten Zeilen zu markieren und dann eine Regel mit einer der folgenden Formeln zu erstellen:

• Um doppelte Zeilen mit Ausnahme des ersten Auftretens hervorzuheben:
  =COUNTIF($A:$A2, $A2)>1
• Um doppelte Zeilen mit dem ersten Auftreten hervorzuheben:
  =COUNTIF($A:$A, $A2)>1

Wobei A2 die erste Zelle und A15 die zuletzt verwendete Zelle in der Spalte ist, die Sie auf Duplikate überprüfen möchten. Wie Sie sehen, macht die geschickte Verwendung von absoluten und relativen Zellbezügen den Unterschied aus.
Der folgende Screenshot demonstriert beide Regeln in Aktion:

Wie man doppelte Zeilen in Excel hervorhebt

Das vorherige Beispiel hat gezeigt, wie man ganze Zeilen auf der Grundlage doppelter Werte in einer bestimmten Spalte einfärbt. Was aber, wenn Sie Zeilen anzeigen möchten, die in mehreren Spalten identische Werte aufweisen? Oder wie heben Sie absolute doppelte Zeilen hervor, also solche, die in allen Spalten völlig gleiche Werte aufweisen?

Verwenden Sie dazu die Funktion COUNTIFS, die den Vergleich von Zellen anhand mehrerer Kriterien ermöglicht. Um beispielsweise doppelte Zeilen hervorzuheben, die in den Spalten A und B identische Werte haben, verwenden Sie eine der folgenden Formeln:

• Um doppelte Zeilen ohne 1. Vorkommen hervorzuheben:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Um doppelte Zeilen mit 1. Vorkommen hervorzuheben:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

Der folgende Screenshot zeigt das Ergebnis:

Wie Sie verstehen, dient das obige Beispiel nur zu Demonstrationszwecken. Beim Hervorheben von doppelten Zeilen in Ihren realen Blättern sind Sie natürlich nicht auf den Vergleich von Werten in nur 2 Spalten beschränkt, die Funktion COUNTIFS kann bis zu 127 Bereich/Kriterien-Paare verarbeiten.

Hervorheben von aufeinanderfolgenden doppelten Zellen in Excel

Manchmal müssen Sie vielleicht nicht alle doppelten Zellen in einer Spalte hervorheben, sondern nur die aufeinanderfolgenden doppelten Zellen, d.h. die, die nebeneinander liegen. Markieren Sie dazu die Zellen mit Daten (ohne die Spaltenüberschrift) und erstellen Sie eine bedingte Formatierungsregel mit einer der folgenden Formeln:

• Um aufeinanderfolgende Duplikate ohne 1. Vorkommen hervorzuheben
  =$A1=$A2
• Um aufeinanderfolgende Duplikate mit 1. Vorkommen hervorzuheben:
  =OR($A1=$A2, $A2=$A3)

Der folgende Screenshot zeigt die Hervorhebung von aufeinanderfolgenden doppelten Texten, aber diese Regeln funktionieren auch für aufeinanderfolgende doppelte Zahlen und Daten:

Wenn Ihr Excel-Blatt leere Zeilen hat und Sie nicht möchten, dass die aufeinanderfolgenden leeren Zellen hervorgehoben werden, nehmen Sie die folgenden Verbesserungen an den Formeln vor:

• Um aufeinanderfolgende doppelte Zellen ohne 1. Vorkommen hervorzuheben und leere Zellen zu ignorieren:
  =AND($A2<>"", $A1=$A2)
• Um aufeinanderfolgende doppelte Zellen mit 1. Vorkommen hervorzuheben und leere Zellen zu ignorieren:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Wie Sie sehen, ist es keine große Sache, doppelte Zellen in Excel mithilfe der bedingten Formatierung hervorzuheben. Es gibt jedoch noch einen schnelleren und einfacheren Weg. Lesen Sie dazu den nächsten Abschnitt dieses Tutorials.

Mit dem Duplicate Remover Duplikate in Excel markieren

Das Add-In Duplicate Remover ist die Komplettlösung für den Umgang mit doppelten Datensätzen in Excel. Es kann doppelte Zellen oder ganze doppelte Zeilen finden, markieren, auswählen, kopieren oder verschieben.

Trotz seines Namens kann das Add-In Duplikate schnell in verschiedenen Farben markieren, ohne sie zu löschen.

Der Duplikat-Entferner fügt Ihrem Excel Ribbon 3 neue Funktionen hinzu:

• Dedupe Table – zum sofortigen Finden und Markieren von Duplikaten in einer Tabelle.
• Duplikatentferner – Schritt-für-Schritt-Assistent mit erweiterten Optionen zum Identifizieren und Hervorheben von Duplikaten oder eindeutigen Werten in einer Tabelle.
• 2 Tabellen vergleichen – Finden und Hervorheben von Duplikaten durch den Vergleich von zwei Spalten oder zwei separaten Tabellen.

Nach der Installation von Ultimate Suite for Excel finden Sie diese Tools auf der Registerkarte Ablebits Data in der Gruppe Dedupe:

Duplikate in Excel mit ein paar Klicks hervorheben

Für dieses Beispiel habe ich die folgende Tabelle mit ein paar hundert Zeilen erstellt. Und unser Ziel ist es, doppelte Zeilen hervorzuheben, die in allen drei Spalten gleiche Werte haben:

Glauben Sie es oder nicht, Sie können das gewünschte Ergebnis mit nur 2 Mausklicks erzielen 🙂

1. Klicken Sie bei einer beliebigen Zelle in Ihrer Tabelle auf die Schaltfläche Tabelle entwerten, und das clevere Add-In wird die gesamte Tabelle erfassen.
2. Das Dialogfenster Tabelle entwerten öffnet sich, wobei alle Spalten automatisch ausgewählt werden und die Option Duplikate einfärben standardmäßig ausgewählt ist. Sie brauchen also nur noch auf OK zu klicken 🙂 Fertig!

Tipp. Wenn Sie doppelte Zeilen anhand einer oder mehrerer Spalten erkennen möchten, deaktivieren Sie alle irrelevanten Spalten und lassen Sie nur die Schlüsselspalte(n) ausgewählt.

Und das Ergebnis sieht ungefähr so aus:

Wie Sie in der obigen Abbildung sehen, hat das Tool „Dupe Table“ doppelte Zeilen ohne erstes Auftreten hervorgehoben.

Wenn Sie Duplikate einschließlich des ersten Auftretens hervorheben möchten, oder wenn Sie eindeutige Datensätze statt Duplikate einfärben möchten, oder wenn Sie die rote Standardfarbe nicht mögen, dann verwenden Sie den Assistenten zum Entfernen von Duplikaten, der all diese Funktionen und noch viel mehr bietet.

Duplikate in Excel mit einem fortgeschrittenen Schritt-für-Schritt-Assistenten hervorheben

Im Vergleich zum schnellen Tool „Tabelle abziehen“ erfordert der Assistent zum Entfernen von Duplikaten ein paar mehr Klicks, macht dies aber durch eine Reihe zusätzlicher Optionen wieder wett. Ich zeige ihn Ihnen in Aktion:

1. Markieren Sie eine beliebige Zelle in Ihrer Tabelle, in der Sie Duplikate markieren möchten, und klicken Sie in der Multifunktionsleiste auf die Schaltfläche Duplikatentferner. Der Assistent wird ausgeführt und die gesamte Tabelle wird ausgewählt. Das Add-In schlägt außerdem vor, für den Fall der Fälle eine Sicherungskopie Ihrer Tabelle zu erstellen. Wenn Sie dies nicht benötigen, deaktivieren Sie das Kontrollkästchen.

   Überprüfen Sie, ob die Tabelle korrekt ausgewählt wurde, und klicken Sie auf Weiter.

   

2. Wählen Sie einen der folgenden Datentypen aus, die Sie finden möchten:
   • Duplikate außer 1. Vorkommen
   • Duplikate mit 1. Vorkommen
   • Einzigartige Werte
   • Einzigartige Werte und 1. doppelte Vorkommen

   Für dieses Beispiel suchen wir Duplikate + 1:
   

3. Wählen Sie nun die Spalten aus, die auf Duplikate geprüft werden sollen. Da wir komplette doppelte Zeilen markieren wollen, habe ich alle 3 Spalten ausgewählt.
   

   Zusätzlich können Sie mit dem Add-In angeben, ob Ihre Tabelle Überschriften hat und ob Sie leere Zellen überspringen wollen. Beide Optionen sind standardmäßig aktiviert.

4. Schließlich wählen Sie die Aktion aus, die bei Duplikaten durchgeführt werden soll. Sie haben eine Reihe von Optionen wie Markieren, Löschen, Kopieren, Verschieben von Duplikaten oder Hinzufügen einer Statusspalte, um die Duplikate zu identifizieren.

Da wir heute verschiedene Möglichkeiten zum Hervorheben von Duplikaten in Excel erkunden, liegt unsere Wahl auf der Hand 🙂 Wählen Sie also die Option Mit Farbe füllen und wählen Sie eine der Standardthemenfarben, oder klicken Sie auf Weitere Farben… und wählen Sie eine beliebige benutzerdefinierte RGB- oder HSL-Farbe.

Klicken Sie auf die Schaltfläche Fertigstellen und genießen Sie das Ergebnis 🙂

So markieren Sie Duplikate in Excel mit unserem Add-in Duplicate Remover. Wenn Sie neugierig sind, dieses Tool auf Ihren eigenen Arbeitsblättern auszuprobieren, können Sie gerne eine voll funktionsfähige Testversion der Ultimate Suite herunterladen, die alle unsere zeitsparenden Tools für Excel enthält. Und Ihr Feedback in den Kommentaren wird sehr geschätzt!

Wenn Sie mit der Leistung von Duplicate Remover zufrieden sind, freuen wir uns, Ihnen diese ganz besondere Gelegenheit zu bieten:

Das könnte Sie auch interessieren

• Wie man Duplikate in Excel identifiziert (in 1 Tabelle)
• Wie man 2 Spalten oder Tabellen auf Übereinstimmungen und Unterschiede vergleicht
• Wie man doppelte Zeilen in Excel entfernt