Wie vergleicht man zwei Spalten und hebt dabei Werte hervor, die in der anderen Spalte vorkommen?
Beim Arbeiten mit Daten in Excel stoßen Sie häufig auf Situationen, in denen Sie Werte identifizieren müssen, die in zwei verschiedenen Listen vorkommen. Angenommen, Sie verwalten ein Arbeitsblatt mit Liste A und Liste B und möchten jene Einträge aus Liste A visuell hervorheben, die auch in Liste B enthalten sind. So erkennen Sie schnell Überschneidungen, Duplikate oder Elemente, die einer weiteren Bearbeitung bedürfen. Solche Vergleiche sind in zahlreichen Szenarien wertvoll – etwa zur Inventarvalidierung, zum Aufspüren doppelter Einträge oder zum Abgleich von Datensätzen. Dieser Artikel stellt mehrere praktische Methoden vor, um zwei Spalten zu vergleichen und gemeinsame Werte in einer anderen Spalte in Excel hervorzuheben.
➤ Heben Sie Zellen hervor, wenn Duplikate in einer anderen Spalte mit Kutools für Excel vorhanden sind
➤ VBA-Code – Verwenden Sie VBA, um Spalten programmgesteuert zu durchlaufen und übereinstimmende Werte hervorzuheben
➤ Excel-Formel – Verwenden Sie die COUNTIF-Formel, um eine Hilfsspalte zur Kennzeichnung von Duplikaten zu erstellen
Markieren Sie Zellen, wenn Duplikate in einer anderen Spalte mit Bedingte Formatierung verwenden vorhanden sind
In Excel bietet die Verwendung der bedingten Formatierung eine schnelle und flexible Möglichkeit, Werte hervorzuheben, die in beiden Spalten vorkommen. Das ist besonders nützlich, wenn Sie Übereinstimmungen auf einen Blick visuell erkennen möchten – ohne Ihre zugrunde liegenden Daten zu verändern. Gehen Sie dazu wie folgt vor:
1. Wählen Sie die Zellen in Spalte A aus, deren Einträge auch in Spalte C enthalten sind und hervorgehoben werden sollen. Achten Sie dabei darauf, genau den richtigen Datenbereich – ohne zusätzliche leere Zellen – auszuwählen, da ansonsten Leerzellen ebenfalls markiert werden könnten, falls sie in beiden Spalten vorkommen.
2. Wechseln Sie zur Start-Registerkarte, klicken Sie auf Bedingte Formatierung verwenden und wählen Sie Neue Regel. Wählen Sie im Dialogfeld Neue Formatierungsregel die Option Formel zur Bestimmung der zu formatierenden Zellen verwenden. Geben Sie im Feld mit der Beschriftung Formatwerte, für die diese Formel zutrifft Folgendes ein: =VERGLEICH(A2;$C$2:$C$12;0). Diese Formel prüft, ob jeder Wert in Spalte A innerhalb des begrenzten Bereichs in Spalte C gefunden werden kann. Siehe Screenshot:
3. Klicken Sie auf Format, um das Dialogfeld Zellenformat festlegen zu öffnen. Wählen Sie auf der Registerkarte Füllung eine Hervorhebungsfarbe aus. Bestätigen Sie mit OK und schließen Sie das Dialogfeld.
4. Klicken Sie auf OK, um die Regel anzuwenden. Jeder Wert in Liste A, der auch in Liste B enthalten ist, wird hervorgehoben. Ergebnisbeispiel:
Die bedingte Formatierung eignet sich hervorragend für eine schnelle visuelle Analyse. Bei sehr großen Datensätzen können jedoch mehrere regelbasierte Formeln die Berechnung und das Scrollen verlangsamen; beachten Sie, dass diese Methode Übereinstimmungen lediglich hervorhebt, sie aber weder extrahiert noch filtert.
Vergleichen Sie zwei Spalten und markieren Sie Zellen, wenn Duplikate in einer anderen Spalte vorhanden sind
Wenn Sie zwei Spalten vergleichen und schnell Werte hervorheben möchten, die in beiden vorkommen, ist die Kutools für Excel-Funktion Gleiche/Unterschiedliche Zellen auswählen eine praktische Wahl. Sie eignet sich besonders für Anwender, die eine intuitive Benutzeroberfläche bevorzugen und komplexe Formeln oder manuelle Einstellungen vermeiden möchten. Mit Kutools heben Sie Duplikate bereits mit wenigen Klicks hervor – ideal bei langen Listen oder wiederkehrenden Aufgaben.Jetzt Kutools für Excel herunterladen und kostenlos testen!
Markieren Sie Zellen, wenn Duplikate in einer anderen Spalte mit Kutools für Excel vorhanden sind
Wenn Sie keine Formeln schreiben oder Regeln für die bedingte Formatierung konfigurieren möchten, nutzen Sie einfach das benutzerfreundliche Kutools für Excel-Add-In. Mit dem Tool Gleiche/Unterschiedliche Zellen auswählen vergleichen Sie zwei Spalten mühelos und heben übereinstimmende Werte hervor – ideal für Anwender aller Erfahrungsstufen.
Nach der Installation von Kutools für Excelgehen Sie bitte wie folgt vor:
1. Wechseln Sie zur Kutools-Registerkarte und klicken Sie auf Auswählen > Gleiche/Unterschiedliche Zellen auswählen, um das Dialogfeld zu öffnen. Diese Option ist leicht auffindbar, und die Funktion lässt sich nutzen – ganz ohne Formeln schreiben oder anpassen zu müssen.
2. Führen Sie im Dialogfeld Gleiche/Unterschiedliche Zellen auswählen folgende Schritte aus:
(1.) Legen Sie Ihre Vergleichsbereiche fest, indem Sie die beiden zu vergleichenden Listen in die Felder Werte suchen inund Gemäßauswählen;
(2.) Wählen Sie unter Basierend aufJede Zeile, um sicherzustellen, dass jedes Element verglichen wird;
(3.) Wählen Sie im Abschnitt Suchendie Option Gleiche Werte, um sich auf gemeinsame Elemente zwischen den beiden Spalten zu konzentrieren;
(4.) Geben Sie mithilfe der Option Verarbeitung der ausgewählten Ergebnisse Ihre bevorzugte Hervorhebungsfarbe für Hintergrund oder Schrift an. So erkennen Sie nach Abschluss des Vorgangs auf einen Blick, welche Werte übereinstimmen.
3. Sobald alles eingerichtet ist, klicken Sie auf die Schaltfläche Ok. Kutools verarbeitet Ihre Auswahl, und eine Meldung zeigt an, wie viele übereinstimmende Zellen gefunden und hervorgehoben wurden. Klicken Sie in der Meldung auf OK, um sie zu schließen. Doppelte Werte in Liste A sind nun klar markiert und hervorgehoben – für eine mühelose Überprüfung und weitere Analysen.
Hinweis: Möchten Sie stattdessen Duplikate in Liste B hervorheben, tauschen Sie einfach die Vergleichsbereiche im Dialogfeld. Diese Flexibilität ermöglicht es Ihnen, Übereinstimmungen je nach Bedarf in einer beliebigen Liste hervorzuheben.
Jetzt Kutools für Excel herunterladen und kostenlos testen!
Die Kutools-Lösung bietet entscheidende Vorteile für Anwender, die Wert auf Konsistenz und Effizienz legen. Beachten Sie jedoch, dass es sich um ein zusätzliches Excel-Add-In handelt, das unter Umständen nicht in allen Umgebungen zur Verfügung steht – etwa in streng regulierten Unternehmensumgebungen oder auf öffentlichen Computern.
VBA-Code – Verwenden Sie VBA, um Spalten durchlaufen und übereinstimmende Werte programmgesteuert hervorzuheben
Falls Sie regelmäßig Spalten vergleichen oder den Hervorhebungsprozess vollständig automatisieren möchten, ist VBA eine effektive und wiederholbare Lösung. Mit VBA können Sie den Vergleich exakt steuern, übereinstimmende Werte direkt an Ort und Stelle hervorheben und das Verhalten an jede konsistente Datenstruktur in Ihren Arbeitsblättern anpassen. Dieser Ansatz eignet sich für fortgeschrittene Anwender oder wenn solche Aufgaben regelmäßig ausgeführt werden müssen.
1. Öffnen Sie die Excel-Arbeitsmappe, und wechseln Sie anschließend zur Entwickler-Registerkarte. Klicken Sie auf Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie im VBA-Projektfenster auf Einfügen > Modul, um ein neues Modul zu erstellen. Kopieren Sie den folgenden Code in das Modul:
Sub HighlightMatchingValues()
Dim rngA As Range
Dim rngB As Range
Dim cellA As Range
Dim matchCell As Range
Dim xTitleId As String
Dim matchFound As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rngA = Application.InputBox("Select the range for List A", xTitleId, Type:=8)
Set rngB = Application.InputBox("Select the range for List B", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each cellA In rngA
matchFound = False
For Each matchCell In rngB
If cellA.Value = matchCell.Value And cellA.Value <> "" Then
matchFound = True
Exit For
End If
Next matchCell
If matchFound Then
cellA.Interior.Color = RGB(255, 255, 0) 'Highlight with yellow
End If
Next cellA
Application.ScreenUpdating = True
MsgBox "Matching values in List A have been highlighted.", vbInformation, "KutoolsforExcel"
End Sub2. Um den Code auszuführen, klicken Sie in das Modul und drücken Sie anschließend F5 oder klicken Sie auf die Schaltfläche Ausführen
. Wählen Sie bei der Aufforderung zunächst den Bereich für Liste A und anschließend den Bereich für Liste B aus. Das Makro hebt automatisch alle Werte in Liste A hervor, die auch in Liste B enthalten sind, und verwendet dafür eine gelbe Füllfarbe, um die Sichtbarkeit zu verbessern.
Tipps und Hinweise: Verwenden Sie diesen Ansatz, wenn Sie große Listen verarbeiten, eine wiederholte Automatisierung benötigen oder mehr Anpassungsmöglichkeiten wünschen. Speichern Sie Ihre Arbeit unbedingt vorab und achten Sie darauf, dass bereits mit Füllfarbe belegte Zellen berücksichtigt werden – denn das Makro überschreibt vorhandene Hintergrundfarben übereinstimmender Zellen. Möchten Sie den Vergleich umkehren oder die Markierungsfarbe ändern, passen Sie den Code entsprechend an. Die Makro-Eingabeaufforderungen führen Sie Schritt für Schritt durch die Auswahl und minimieren so das Risiko einer falschen Bereichsauswahl.
Falls Fehler auftreten, vergewissern Sie sich, dass der ausgewählte Bereich weder zusammengeführt ist noch Überschriften enthält, und dass die Werte vergleichbar sind (Zahlen oder Text ohne versteckte Sonderzeichen).
Excel-Formel – Verwenden Sie die COUNTIF-Formel, um eine Hilfsspalte zur Kennzeichnung von Duplikaten zu erstellen
Neben der direkten Hervorhebung können Sie auch Hilfsspalten mit Formeln nutzen, um Doppelte markieren, und diese anschließend manuell oder bedingt hervorzuheben. Diese Methode ist besonders einfach, wenn Sie Übereinstimmungen vor der Formatierung eindeutig kennzeichnen möchten. Sie eignet sich gut für Fälle, in denen Sie eine dauerhafte Indikatorspalte für nachfolgende Analysen oder automatisierte Arbeitsabläufe benötigen.
1.Geben Sie in einer leeren Spalte neben Ihrer Liste A (z. B. in Zelle)B2) die folgende Formel ein:
=IF(COUNTIF($C$2:$C$12,A2)>0,"Duplicate","")Diese Formel prüft, ob der Wert in A2 irgendwo in C2:C12 enthalten ist. Wenn ja, gibt sie „Duplikat“ zurück; andernfalls bleibt die Zelle leer. Passen Sie die Zellbezüge einfach an die tatsächliche Position und den Umfang Ihrer Datenliste an.
2. Drücken Sie die Eingabetaste und ziehen Sie die Formel mithilfe des Ausfüllkästchens nach unten, sodass sie alle Zeilen der Liste A abdeckt. Dadurch wird die Hilfsspalte automatisch ausgefüllt und zeigt an, welche Werte in beiden Spalten vorkommen.
3.(Optional) Für eine bessere Übersichtlichkeit können Sie nun basierend auf dieser Hilfsspalte Bedingte Formatierung verwenden verwenden:
- Wählen Sie den Bereich in Liste A aus, den Sie hervorheben möchten.
- Wechseln Sie zu Start > Bedingte Formatierung verwenden > Neue Regel.
- Wählen Sie Formel zur Bestimmung der zu formatierenden Zellen verwenden und geben Sie eine Formel wie =$B2=„Duplicate" ein (angenommen, Spalte B ist Ihre Hilfsspalte).
- Legen Sie das gewünschte Hervorhebungsformat fest und bestätigen Sie, um die Regel anzuwenden.
Dieser Ansatz sorgt für Transparenz beim Vergleich, bietet Flexibilität für weitere Verarbeitung oder Filterung und eignet sich ideal für Prüfpfade in Datenanalysen. Beachten Sie jedoch, dass Sie die Hilfsspalte für fortlaufende Vergleiche beibehalten müssen und zusätzliche Spalten die Komplexität des Arbeitsblatts geringfügig erhöhen können.
Verwandte Artikel:
Wie prüft oder findet man heraus, ob ein Wert in einer anderen Spalte vorhanden ist?
Wie vergleicht man zwei Spalten in Excel und listet die Unterschiede auf?
Wie hebt man Zellen hervor, wenn sie nicht in einer anderen Spalte enthalten sind (in Excel)?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins