Hoe dubbele cellen en rijen te markeren in Excel

In deze zelfstudie leert u hoe u duplicaten in Excel kunt tonen. We gaan verschillende methoden onder de loep nemen om duplicaatcellen, hele rijen of opeenvolgende dupes te arceren met behulp van voorwaardelijke opmaak. Ook zult u zien hoe u duplicaten kunt markeren met verschillende kleuren met behulp van een gespecialiseerd gereedschap.

Vorige week hebben we verschillende manieren verkend om duplicaten in Excel te identificeren. Ongetwijfeld zijn de duplicaatformules erg handig, maar het markeren van dubbele vermeldingen met een gedefinieerde kleur zou de gegevensanalyse nog eenvoudiger kunnen maken.

De snelste manier om duplicaten in Excel te vinden en te markeren, is met behulp van voorwaardelijke opmaak. Het grootste voordeel van deze methode is dat het u niet alleen de bestaande dupes laat zien, maar ook nieuwe duplicaten detecteert en kleurt terwijl u uw gegevens invoert, bewerkt of overschrijft.

Verderop in deze zelfstudiegids vindt u een aantal manieren om dubbele records te markeren, afhankelijk van uw specifieke taak. Deze technieken werken in alle versies van Excel 2016, Excel 2013, Excel 2010 en lager.

• Duplicaten markeren in Excel met 1e occurrences (ingebouwdein regel)
• Hoe duplicaten markeren in Excel behalve 1e voorkomens
• Hoe duplicaten markeren in een bereik (meerdere kolommen)
• Hoe N-de en volgende duplicaat records
• Hoe hele rijen te markeren op basis van dubbele waarden in een kolom
• Hoe dubbele rijen te markeren in Excel
• Hoe opeenvolgende dubbele cellen te bekijken
• Duplicate Remover – de snelste manier om duplicaten te markeren in Excel

Hoe duplicaten te markeren in Excel met behulp van de ingebouwde regel (met 1e occurrences)

Voor starters, in alle Excel-versies, is er een vooraf gedefinieerde regel voor het markeren van dubbele cellen. Om deze regel in uw werkbladen te gebruiken, voert u de volgende stappen uit:

1. Selecteer de gegevens die u wilt controleren op duplicaten. Dit kan een kolom, een rij of een reeks cellen zijn.
2. Op het tabblad Home klikt u in de groep Stijlen op Voorwaardelijke opmaak > Cellen markeren Regels > Duplicaatwaarden…
   
3. Het dialoogvenster Duplicaatwaarden wordt geopend met de standaard geselecteerde opmaak Lichtrode vulling en Donkerrode tekst. Om de standaardopmaak toe te passen, klikt u eenvoudig op OK.

Naast de rode vulling en tekstopmaak zijn er nog een handvol andere voorgedefinieerde opmaken beschikbaar in de vervolgkeuzelijst. Om duplicaten met een andere kleur te markeren, klikt u op Aangepaste opmaak… (het laatste item in de vervolgkeuzelijst) en selecteert u de gewenste kleur voor de vulling en/of het lettertype.

Met behulp van de ingebouwde regel kunt u duplicaten in één kolom of in meerdere kolommen markeren, zoals te zien is in de volgende schermafbeelding:

Wanneer u de ingebouwde regel van Excel gebruikt voor het markeren van dubbele waarden, moet u de volgende twee dingen in gedachten houden:

• Het werkt alleen voor afzonderlijke cellen. Om dubbele rijen te markeren, zou u uw eigen regels moeten maken, hetzij op basis van waarden in een specifieke kolom of door waarden in verschillende kolommen te vergelijken.
• Het markeert dubbele cellen inclusief hun eerste voorkomen. Om alle duplicaten te markeren, behalve de eerste voorkomens, maakt u een voorwaardelijke opmaakregel op basis van deze formule.

Hoe duplicaten te markeren zonder eerste voorkomens

Om de tweede en alle volgende duplicaatvoorkomens te markeren, selecteert u de cellen die u wilt kleuren, en maakt u op deze manier een regel op basis van een formule:

1. Op het tabblad Begin, in de groep Stijlen, klikt u op Voorwaardelijke opmaak > Nieuwe regel >Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
2. In het vak Waarden opmaken waar deze formule waar is, voert u een formule in die lijkt op deze:
   =COUNTIF($A:$A2,$A2)>1

   Waar A2 de bovenste cel van het geselecteerde bereik is.

3. Klik op de knop Opmaak… en selecteer de gewenste opvul- en/of lettertypekleur.
4. Klik ten slotte op OK om de regel op te slaan en toe te passen.

Als u niet veel ervaring hebt met Excel voorwaardelijke opmaak, vindt u de gedetailleerde stappen voor het maken van een regel op basis van formules in de volgende zelfstudiegids: Excel voorwaardelijke opmaak op basis van een andere celwaarde.

Als resultaat worden de duplicaatcellen met uitzondering van de eerste gevallen gemarkeerd met de kleur van uw keuze:

Hoe derde, vierde en alle volgende duplicaatrecords weer te geven

Om duplicaten weer te geven die beginnen met het N-de voorkomen, maakt u een voorwaardelijke opmaakregel op basis van de formule zoals in het vorige voorbeeld, met als enige verschil dat u >1 aan het eind van de formule vervangt door het vereiste getal. Bijvoorbeeld:

Om de 3e en alle volgende duplicaatrecords te markeren, maakt u een voorwaardelijke opmaakregel op basis van deze formule:

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

Om de 4e en alle volgende duplicaatrecords te markeren, gebruikt u deze formule:

Om alleen specifieke occurrences te markeren, gebruikt u de operator gelijk aan (=). Om bijvoorbeeld alleen 2e gevallen te markeren, gebruikt u deze formule:

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

Hoe markeer ik duplicaten in een bereik (meerdere kolommen)

Wanneer u wilt controleren op duplicaten in meerdere kolommen, niet door de kolommen met elkaar te vergelijken, maar alle gevallen van hetzelfde item in alle kolommen wilt vinden, gebruikt u een van de volgende oplossingen.

Duplicaten in meerdere kolommen markeren, inclusief 1e voorkomen

Als de eerste instantie van een item dat meer dan eens in de gegevensreeks voorkomt, als een duplicaat wordt beschouwd, is de eenvoudigste manier om te gaan het gebruik van Excel’s ingebouwde regel voor duplicaten.

Of maak een voorwaardelijke opmaakregel met deze formule:

Of maak bijvoorbeeld een voorwaardelijke opmaakregel met deze formule:

Om dubbelen in het bereik A2 te markeren:C8, gaat de formule als volgt:

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

Let op het gebruik van absolute celverwijzingen voor het bereik ($A$2:$C$8), en relatieve verwijzingen voor de bovenste cel (A2).

Duplicaten in meerdere kolommen zonder 1e occurrences markeren

De oplossing voor dit scenario is een stuk lastiger, geen wonder dat Excel hier geen ingebouwde regel voor heeft 🙂

Om dubbele vermeldingen in meerdere kolommen te markeren en daarbij de 1e occurrences te negeren, moet u 2 regels maken met de volgende formules:

Regel 1. Geldt voor de eerste kolom

Hier gebruikt u precies dezelfde formule als die we gebruikten om duplicaten zonder 1e occurrences in één kolom te markeren (de gedetailleerde stappen vindt u hier).

In dit voorbeeld maken we een regel voor A2:A8 met deze formule:

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

Als resultaat worden de dubbele items zonder 1e occurrences gemarkeerd in de meest linkse kolom van het bereik (er is slechts één zo’n item in ons geval):

Regel 2. Geldt voor alle volgende kolommen

Om doublures in de resterende kolommen (B2:C8) te markeren, gebruik je deze formule:

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

In de bovenstaande formule telt de eerste COUNTIF-functie de keren dat een bepaald item voorkomt in de eerste kolom, en de tweede COUNTIF doet hetzelfde voor alle volgende kolommen. En dan tel je die getallen bij elkaar op en controleer je of de som groter is dan 1.

Het resultaat is dat alle dubbele items, behalve hun eerste voorkomen, worden gevonden en gemarkeerd:

Duplicaten in alle kolommen met één enkele regel markeren

Een andere mogelijke oplossing is om een lege kolom toe te voegen aan de linkerkant van uw gegevensreeks, en de bovenstaande formules te combineren tot één enkele formule, zoals deze:

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

Waarbij B2 de bovenste cel is met gegevens in de 2e kolom van het doelbereik.

Om de formule beter te begrijpen, splitsen we deze op in 2 hoofdonderdelen:

• Voor de eerste kolom (B) wordt nooit aan de IF-conditie voldaan, dus wordt alleen de tweede COUNTIF-functie toegepast, die de gewone formule is die we al gebruikten om duplicaten zonder 1e occurrences in één kolom te vinden.
• Voor alle volgende kolommen (C2:D8), is het belangrijkste punt het slimme gebruik van absolute en relatieve referenties in de twee COUNTIF functies. Om het begrijpelijker te maken, heb ik de formule naar kolom G gekopieerd, zodat u kunt zien hoe de formule verandert wanneer deze op andere cellen wordt toegepast:
  

Omdat de IF voorwaarde altijd WAAR is voor alle andere kolommen dan de eerste (aantal kolommen is groter dan 1), gaat de formule op deze manier verder:

• Telt het aantal voorkomens van een gegeven item (D5 in de schermafbeelding hierboven) in alle kolommen links van de gegeven kolom: COUNTIF(B:$C,D5)
• Telt het aantal keren dat een gegeven item voorkomt in de kolom van het item, tot aan de cel van het item: COUNTIF(D:D5,D5)
• Tot slot telt de formule de resultaten van beide COUNTIF-functies bij elkaar op. Als het totale aantal groter is dan 1, d.w.z. als het item meer dan één keer voorkomt, wordt de regel toegepast en wordt het item gemarkeerd.

Het markeren van hele rijen op basis van dubbele waarden in één kolom

Als uw tabel meerdere kolommen bevat, wilt u misschien hele rijen markeren op basis van dubbele records in een specifieke kolom.

Zoals u al weet, werkt Excel’s ingebouwde regel voor duplicaten alleen op celniveau. Maar een aangepaste regel op basis van formules heeft geen probleem met het arceren van rijen. Het belangrijkste is dat u de hele rijen selecteert en vervolgens een regel maakt met een van de volgende formules:

• Om dubbele rijen te markeren met uitzondering van 1e occurrences:
  =COUNTIF($A:$A2, $A2)>1
• Om dubbele rijen te markeren met inbegrip van 1e occurrences:
  =COUNTIF($A:$A, $A2)>1

Waarbij A2 de eerste cel is en A15 de laatst gebruikte cel in de kolom die u wilt controleren op duplicaten. Zoals u ziet, maakt het slimme gebruik van absolute en relatieve celverwijzingen het verschil.
De volgende schermafbeelding toont beide regels in actie:

Hoe markeert u dubbele rijen in Excel

Het vorige voorbeeld heeft laten zien hoe u volledige rijen kunt kleuren op basis van dubbele waarden in een specifieke kolom. Maar wat als u rijen wilt weergeven die identieke waarden in meerdere kolommen hebben? Of, hoe markeert u absoluut duplicaat rijen, degene die volledig gelijke waarden hebben in alle kolommen?

Werk hiervoor met de COUNTIFS functie die het mogelijk maakt cellen te vergelijken op meerdere criteria. Om bijvoorbeeld dubbele rijen te markeren die identieke waarden hebben in de kolommen A en B, gebruikt u een van de volgende formules:

• Om dubbele rijen te markeren met uitzondering van 1e voorkomen:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Om dubbele rijen te markeren met 1e voorkomen:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

De volgende schermafbeelding toont het resultaat:

Zoals u begrijpt, is het bovenstaande voorbeeld alleen bedoeld als demonstratie. Bij het markeren van dubbele rijen in uw echte werkbladen bent u natuurlijk niet beperkt tot het vergelijken van waarden in slechts 2 kolommen; de COUNTIFS-functie kan tot 127 bereik/criteriumparen verwerken.

Opeenvolgende dubbele cellen markeren in Excel

Soms is het nodig niet alle duplicaten in een kolom te markeren, maar alleen de opeenvolgende dubbele cellen weer te geven, d.w.z. de cellen die naast elkaar liggen. Om dit te doen, selecteert u de cellen met gegevens (de kolomkop niet meegerekend) en maakt u een voorwaardelijke opmaakregel met een van de volgende formules:

• Om opeenvolgende duplicaten zonder 1e occurrences te markeren:
  =$A1=$A2
• Om opeenvolgende duplicaten met 1e occurrences te markeren:
  =OR($A1=$A2, $A2=$A3)

De volgende schermafbeelding demonstreert het markeren van opeenvolgende duplicaatteksten, maar deze regels werken ook voor opeenvolgende duplicaatnummers en -datums:

Als uw Excel-blad mogelijk lege rijen bevat en u niet wilt dat de opeenvolgende lege cellen worden gemarkeerd, brengt u de volgende verbeteringen aan in de formules:

• Om opeenvolgende duplicaatcellen zonder 1e occurrences te markeren en blanco cellen te negeren:
  =AND($A2<>"", $A1=$A2)
• Om opeenvolgende duplicaatcellen met 1e occurrences te markeren en blanco cellen te negeren:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Zoals u ziet, is het niet veel moeite om duplicaten in Excel te markeren met behulp van voorwaardelijke opmaak. Er is echter nog een snellere en eenvoudigere manier. Om het uit te vinden, lees het volgende deel van deze zelfstudiegids.

Hoe duplicaten te markeren in Excel met Duplicate Remover

De Duplicate Remover add-in is de alles-in-één oplossing voor het omgaan met dubbele records in Excel. Het kan vinden, markeren, selecteren, kopiëren of verplaatsen van dubbele cellen of hele dubbele rijen.

Ondanks de naam, kan de add-in snel duplicaten markeren in verschillende kleuren zonder ze te verwijderen.

De Duplicaat Verwijderaar voegt 3 nieuwe functies toe aan uw Excel Lint:

• Dedupe Tabel – om onmiddellijk te vinden en te markeren duplicaten in een tabel.
• Duplicate Remover – stapsgewijze wizard met geavanceerde opties om duplicaten of unieke waarden in 1 tabel te identificeren en te markeren.
• Compare 2 Tables – vind en markeer duplicaten door twee kolommen of twee afzonderlijke tabellen te vergelijken.

Na installatie van Ultimate Suite for Excel, vindt u deze tools op het tabblad Ablebits Data in de Dedupe-groep:

Duplicaten markeren in Excel in een paar klikken

Voor dit voorbeeld heb ik de volgende tabel gemaakt met een paar honderd rijen. En ons doel is om dubbele rijen te markeren die in alle drie de kolommen gelijke waarden hebben:

Geloof het of niet, u kunt het gewenste resultaat krijgen met slechts 2 muisklikken 🙂

1. Met een willekeurige cel in uw tabel geselecteerd, klikt u op de knop Dedupe Table, en de slimme add-in zal de hele tabel ophalen.
2. Het dialoogvenster Dedupe Table wordt geopend met alle kolommen automatisch geselecteerd, en de optie Color duplicates standaard gekozen. Dus, alles wat je hoeft te doen is klikken op OK 🙂 Klaar!

Tip. Als u dubbele rijen wilt detecteren aan de hand van een of meer kolommen, schakelt u alle irrelevante kolommen uit en laat u alleen de belangrijkste kolom of kolommen geselecteerd.

En het resultaat zou er ongeveer zo uitzien:

Zoals u in de bovenstaande afbeelding ziet, heeft het gereedschap Dupe Table duplicate rijen zonder eerste occurrences gemarkeerd.

Wilt u duplicaten markeren inclusief eerste exemplaren, of wilt u unieke records kleuren in plaats van duplicaten, of houdt u niet van de standaard rode kleur, gebruik dan de wizard Duplicate Remover die al deze functies heeft en nog veel meer.

Duplicaten markeren in Excel met behulp van een geavanceerde stap-voor-stap wizard

Vergeleken met de snelle Dedupe Table tool, de Duplicate Remover wizard vereist een paar meer klikken, maar het maakt dit goed met een aantal extra opties. Ik zal het u in actie laten zien:

1. Selecteer een cel in uw tabel waar u duplicaten wilt markeren, en klik op de Duplicate Remover knop op het lint. De wizard wordt uitgevoerd en de hele tabel wordt geselecteerd. De invoegtoepassing zal ook voorstellen om een reservekopie van uw tabel te maken, voor het geval dat. Als u dat niet nodig hebt, verwijdert u het vinkje in dat vakje.

   Controleer of de tabel correct is geselecteerd en klik op Volgende.

   

2. Selecteer een van de volgende gegevenstypen die u wilt vinden:
   • Duplicaten behalve 1e occurrences
   • Duplicaten met 1e occurrences
   • Unieke waarden
   • Unieke waarden en 1e duplicaat occurrences

   Voor dit voorbeeld, laten we Duplicaten + 1e occurrences vinden:
   

3. Nu selecteren we de kolommen die we willen controleren op duplicaten. Omdat we volledige dubbele rijen willen markeren, heb ik alle 3 kolommen geselecteerd.
   

   Daarnaast kunt u met de invoegtoepassing opgeven of uw tabel koppen heeft en of u lege cellen wilt overslaan. Beide opties zijn standaard geselecteerd.

4. Tot slot kiest u de actie die u op duplicaten wilt uitvoeren. U hebt een aantal opties zoals selecteren, verwijderen, kopiëren, duplicaten verplaatsen of een statuskolom toevoegen om de dupes te identificeren.

Omdat we vandaag verschillende manieren verkennen om duplicaten in Excel te markeren, ligt onze keuze voor de hand 🙂 Selecteer dus Vul met kleur en kies een van de standaard themakleuren, of klik op Meer kleuren… en kies een aangepaste RGB- of HSL-kleur.

Klik op de knop Voltooien en geniet van het resultaat 🙂

Zo markeert u duplicaten in Excel met behulp van onze add-in Duplicaatverwijderaar. Als u nieuwsgierig bent om deze tool op uw eigen werkbladen uit te proberen, bent u van harte welkom om een volledig functionele proefversie van de Ultimate Suite te downloaden, die al onze tijdbesparende tools voor Excel bevat. En uw feedback in opmerkingen zal zeer op prijs worden gesteld!

Als u tevreden bent met de prestaties van Duplicate Remover, zijn we blij om u deze zeer speciale kans te bieden:

U bent misschien ook geïnteresseerd in

• Hoe duplicaten te identificeren in Excel (in 1 tabel)
• Hoe 2 kolommen of tabellen te vergelijken voor overeenkomsten en verschillen
• Hoe dubbele rijen te verwijderen in Excel