M365Con 2027 – gratis online conferentie · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Duplicaten verwijderen in Excel: opdracht, formules, Power Query

By · · Updated

Duplicaten verwijderen in Excel: gebruik Gegevens, Duplicaten verwijderen, of bewaar de oorspronkelijke gegevens met UNIQUE, formules en Power Query. Stappen, beperkingen en fouten.

Automatically translated with AI from the original post. Read the English original

Om duplicaten in Excel te verwijderen, selecteer je je gegevens, ga je naar Gegevens > Duplicaten verwijderen, vink je de kolommen aan die bepalen wat als duplicaat geldt en selecteer je OK. Excel verwijdert elke rij die dezelfde waarden in die kolommen herhaalt en houdt de eerste over. Dit artikel is bedoeld voor Excel-gebruikers die lijsten, exports en rapporten opschonen en de juiste methode willen kiezen. Je leest eerst de opdracht, daarna manieren om duplicaten te controleren voordat je ze verwijdert, vervolgens opties die je brongegevens intact laten, en tot slot een aantal automatiseringsroutes voor terugkerende taken.

Belangrijkste punten

  • Selecteer je gegevens, open Gegevens en klik op Duplicaten verwijderen. Vink de kolommen aan die een duplicaat bepalen en selecteer OK. Excel verwijdert de hele rij en houdt de eerste overeenkomst over.
  • Het verwijderen is definitief, dus kopieer de gegevens eerst naar een ander blad. Ctrl+Z werkt alleen zolang je niets anders hebt gedaan.
  • Excel vergelijkt wat in de cel wordt weergegeven, dus 3/8/2006 en 8 mrt 2006 tellen als verschillende waarden. Maak de opmaak eerst gelijk.
  • Wil je duplicaten bekijken voordat je ze verwijdert, markeer ze dan met voorwaardelijke opmaak of markeer ze met een hulpkolom met COUNTIF.
  • UNIQUE en Power Query geven je een schone lijst zonder de brongegevens aan te raken, en Power Query herhaalt de opschoning bij elke vernieuwing.

Excel heeft meer dan één hulpmiddel hiervoor, omdat "duplicaat" verschillende dingen kan betekenen. Soms is het een volledig identieke rij. Soms is het hetzelfde klant-ID met een ander telefoonnummer. Bepaal welke van de twee je bedoelt voordat je ergens op klikt.

Duplicaten verwijderen met de opdracht op het tabblad Gegevens

Dit is de snelste route en de meest gebruikte. Het volgt het artikel van Microsoft over filteren op unieke waarden of het verwijderen van dubbele waarden.

  1. Maak een back-up van de gegevens. Kopieer het bereik naar een ander blad of een andere werkmap. Microsoft is hierover duidelijk: de duplicaten worden definitief verwijderd.
  2. Selecteer het bereik. Klik op een willekeurige cel in je gegevens of in een Excel-tabel, of selecteer het bereik zelf. Alleen het geselecteerde bereik of de geselecteerde tabel wordt gewijzigd. Cellen buiten het bereik blijven staan waar ze staan.
  3. Open de opdracht. Ga naar Gegevens en selecteer Duplicaten verwijderen in de groep Gegevenshulpmiddelen.
  4. Kies de kolommen. Vink in het dialoogvenster de kolommen aan die Excel moet vergelijken. Je kunt Alles selecteren of Alles deselecteren gebruiken. Staan de kolomnamen in je eerste rij, laat dan Mijn gegevens hebben koppen aangevinkt.
  5. Selecteer OK. Excel toont een melding met het aantal verwijderde dubbele waarden en het aantal unieke waarden dat overblijft.

Let op stap 4, want daar gaan mensen vaak de mist in. Een rij is alleen een duplicaat als alle kolommen die je hebt aangevinkt overeenkomen. Neem een lijst met ID, Naam en Prijs. Als je ID en Naam aanvinkt maar Prijs niet, dan zijn twee rijen met dezelfde ID en naam duplicaten, ook als de prijzen verschillen, en Excel verwijdert de hele tweede rij, inclusief de prijs. Wil je elke rij bewaren waarin iets verschilt, vink dan alle kolommen aan. Als alleen de ID een record bepaalt, vink dan alleen de ID aan, en houd rekening met wat je weggooit.

Druk direct na de opdracht op Ctrl+Z als het resultaat niet klopt. Vertrouw er niet op dat je dit later nog kunt herstellen.

Welke rij blijft staan: eerst sorteren

Microsoft meldt dat de eerste voorkomst blijft staan en dat latere identieke rijen worden verwijderd. Dat betekent dat de volgorde van de rijen de uitkomst bepaalt. Heb je een contactenlijst met meerdere regels per persoon en wil je de meest recente, sorteer dan op datum, nieuwste eerst, en voer daarna Duplicaten verwijderen uit. Wil je de rij met een ingevuld e-mailadres, sorteer dan zo dat die rijen bovenaan staan. Sorteren hoort hier gewoon bij het opschonen, het is geen overbodige extra.

Wat als identiek geldt

Excel vergelijkt wat in de cel te zien is, niet wat er werkelijk is opgeslagen. Volgens het voorbeeld van Microsoft worden 3/8/2006 en 8 mrt 2006 als verschillende waarden behandeld, ook al is de datum hetzelfde. Hetzelfde geldt voor getallen met een andere opmaak of tekst met een spatie aan het eind. In het artikel over het zoeken en verwijderen van duplicaten staat bovendien dat de aantallen die Excel daarna toont, lege cellen of spaties kunnen bevatten.

Zorg dat de cellen er gelijk uitzien voordat je iets verwijdert. Pas één datumnotatie toe, verwijder losse spaties (de functie TRIM doet dat) en controleer of getallen niet als tekst zijn opgeslagen. Microsoft vermeldt op de pagina's niet of de opdracht hoofdletters en kleine letters negeert. Test dit dus op een klein bereik als hoofdletters voor jou belangrijk zijn.

Wanneer de opdracht niet werkt

Je kunt geen duplicaten verwijderen uit gegevens met overzichtsniveaus of subtotalen. Verwijder eerst het overzicht en de subtotalen en voer daarna de opdracht uit.

Bekijk de duplicaten voordat je ze verwijdert

Microsoft adviseert om eerst te filteren of markeren en daarna pas te verwijderen. Dat kost een minuut en voorkomt de meeste spijt.

Duplicaten markeren met voorwaardelijke opmaak

  1. Selecteer de cellen die je wilt controleren.
  2. Open op het tabblad Start de optie Voorwaardelijke opmaak, dan Regels voor het markeren van cellen en vervolgens Dubbele waarden.
  3. Kies een markeringsstijl en selecteer OK.

Elke herhaalde waarde is nu gekleurd. Dit kun je niet toepassen op het gebied Waarden van een draaitabel. Deze methode controleert één kolom tegelijk, dus een rij die alleen over meerdere kolommen heen herhaalt, wordt niet gemarkeerd. Daarvoor heb je een hulpkolom nodig.

Dubbele rijen markeren met een hulpkolom

Een hulpkolom zet naast elke rij een WAAR of ONWAAR. Zo kun je filteren, controleren en daarna verwijderen.

  1. Voeg een kolom in naast je gegevens en voeg een kop toe, zoals "Duplicaat?".
  2. Controleer je kolom A, vul dan in B2 in: =AANTAL.ALS(A$2:A2;A2)>1
  3. Kopieer de formule omlaag. De eerste keer dat een waarde voorkomt, krijg je ONWAAR. Elke latere keer krijg je WAAR.
  4. Filter de hulpkolom op WAAR. Controleer die rijen. Verwijder ze als het echt duplicaten zijn.

Omdat het bereik A$2:A2 groeit wanneer je de formule omlaag kopieert, worden alleen de latere voorkomens gemarkeerd en blijft de eerste met rust. Dat sluit aan bij wat Duplicaten verwijderen doet.

Wil je meerdere kolommen tegelijk controleren, maak dan een sleutelkolom die ze samenvoegt: =A2&"|"&B2&"|"&C2. Voer het AANTAL.ALS uit op die sleutel. Zet een scheidingsteken zoals de pijp tussen de velden. Zonder scheidingsteken worden "12" en "34" én "1" en "234" allebei "1234" en lijken ze dan hetzelfde record.

Filteren heeft ook een nadeel. Als je gefilterde rijen verwijdert door ze te selecteren, controleer dan of alleen de zichtbare, gemarkeerde rijen zijn geselecteerd, en doe dit op een kopie. Sorteer bij twijfel liever de hulpkolom en verwijder het blok met WAAR-rijen in één keer.

Een lijst zonder duplicaten maken en de bron bewaren

Soms wil je niets verwijderen. Je hebt alleen een schone lijst nodig voor een keuzelijst, een samenvatting of een import. Hier passen drie opties.

De functie UNIQUE

In Excel voor Microsoft 365, Excel 2024 en Excel 2021 geeft =UNIQUE(A2:A100) elke unieke waarde uit het bereik terug, en het resultaat loopt door in de cellen eronder. Volgens Microsoft is de functie ook beschikbaar in Excel voor het web en in de mobiele apps. De syntaxis is =UNIQUE(matrix,[by_col],[exactly_once]):

  • matrix is het bereik dat je wilt controleren. Bevat het meerdere kolommen, dan vergelijkt UNIQUE hele rijen.
  • by_col is TRUE als je kolommen in plaats van rijen wilt vergelijken. Laat je dit weg, dan vergelijkt de functie rijen, wat de gebruikelijke werkwijze is.
  • exactly_once is TRUE als je alleen de rijen wilt die precies één keer in de gegevens voorkomen. Laat je dit weg, dan krijg je elke unieke rij.

Het resultaat wordt bijgewerkt wanneer de bron verandert. Als de bron een Excel-tabel is en je gestructureerde verwijzingen gebruikt, past het spill-bereik zich aan wanneer je rijen toevoegt of verwijdert. Wil je een gesorteerde lijst, dan wikkel je de functie in: =SORT(UNIQUE(A2:A100)).

Het tweede argumentenpaar is belangrijker dan veel mensen denken. =UNIQUE(A2:A100,,TRUE) geeft de waarden die precies één keer voorkomen, en dat is een andere vraag dan "de extra exemplaren verwijderen". Bedenk vooraf welke van de twee je nodig hebt.

Eén beperking: oudere Excel-versies (2019 en eerder) kennen UNIQUE niet. Microsofts pagina noemt alleen de versies die hierboven zijn genoemd.

Geavanceerd filteren

Dit werkt in elke desktopversie en kopieert het resultaat naar een andere locatie.

  1. Selecteer je gegevens.
  2. Ga naar Gegevens > Geavanceerd in de groep Sorteren en filteren.
  3. Kies Kopiëren naar andere locatie.
  4. Voer een cel in het vak Kopiëren naar in en vink Alleen unieke records aan.
  5. Selecteer OK.

Je oorspronkelijke gegevens blijven staan waar ze staan, en de unieke rijen verschijnen op de nieuwe locatie.

Het oudere formulepatroon

Kun je UNIQUE niet gebruiken, voer dan in een nieuwe kolom =ALS(AANTAL.ALS($A$2:A2;A2)=1;A2;"") in en vul die naar beneden door. Elke waarde verschijnt dan één keer, en de herhalingen worden lege tekst. Kopieer de kolom en plak die als waarden, en filter daarna de lege cellen eruit. Het is omslachtig, maar het werkt in elke versie.

Gedeeltelijke overeenkomsten en verschillen in hoofdlettergebruik aanpakken

Echte gegevens zijn zelden een zuivere kopie. Drie situaties komen steeds weer terug.

Exacte duplicaten. Elke aangevinkte kolom komt overeen. De opdracht verwerkt dit prima.

Gedeeltelijke overeenkomsten. De sleutelkolom herhaalt zich, maar andere velden verschillen, bijvoorbeeld hetzelfde klant-ID met twee verschillende adressen. Hier moet je zelf bepalen welke versie klopt. Sorteer zodat de juiste eerst staat en verwijder daarna alleen op de sleutelkolom duplicaten. Of markeer de herhalingen met de hulpkolom en controleer ze handmatig. Geen enkele tool kan je vertellen welk adres correct is.

Verschillen in hoofdlettergebruik. Power Query is hoofdlettergevoelig, volgens de documentatie van Microsoft, dus "Apple" en "apple" zijn daar geen duplicaten. De voorgestelde oplossing is de kolom met een transformatie naar hoofdletters of kleine letters om te zetten voordat je duplicaten verwijdert. Dat wijzigt de opgeslagen waarden, dus bewaar de oorspronkelijke kolom als je de exacte schrijfwijze nog nodig hebt.

Automatiseren: Power Query en VBA

Voor een eenmalige opschoning is de opdracht voldoende. Voor een bestand dat je elke week opschoont, zet je iets op dat zich herhaalt.

Power Query

Komen je gegevens uit een CSV-bestand, een database of een andere werkmap, verwijder dan de duplicaten tijdens het importeren. Power Query legt de stap vast en voert die opnieuw uit bij elke vernieuwing.

  1. Laad de gegevens in de Power Query-editor. Staan ze al in een werkblad, selecteer dan een cel in de gegevens en kies Query > Bewerken, of gebruik Gegevens > Gegevens ophalen om een nieuwe bron te laden.
  2. Klik op de kolomkop om een kolom te selecteren. Houd Shift of Ctrl ingedrukt om er meer te selecteren. De kolommen hoeven niet naast elkaar te staan.
  3. Ga naar Start > Rijen verwijderen > Duplicaten verwijderen.
  4. Sluit af en laad het resultaat.

Microsoft's pagina over het behouden of verwijderen van dubbele rijen in Power Query beschrijft deze stappen. De richtlijnen over duplicaten zijn het waard om te kennen: de geselecteerde kolommen bepalen wat als duplicaat geldt, en Power Query garandeert niet dat de eerste rij van een reeks behouden blijft. Als het uitmaakt welke rij overblijft, vertrouw dan niet alleen op sorteren. Microsoft verwijst in het artikel over veelvoorkomende problemen met Power Query naar de richtlijnen over "Sortering behouden". Een verwante optie, Duplicaten behouden, doet het omgekeerde: die houdt alleen rijen over waarvan de waarden meer dan één keer voorkomen, wat een snelle manier is om een lijst te controleren.

Als je twee tabellen samenvoegt, bedenk dan waar de ontdubbeling hoort. Duplicaten vóór de samenvoeging uit elke bron verwijderen houdt de samenvoeging klein en voorspelbaar. Ze na de samenvoeging verwijderen ruimt op wat de samenvoeging heeft veroorzaakt. Kies op basis van waar je duplicaten vandaan komen, en controleer het aantal rijen in het voorbeeld voordat je laadt.

VBA-macro

Als je dezelfde opschoning herhaalt op werkmappen die je elke dag opent, is een macro een optie. Het Range-object heeft een methode RemoveDuplicates, gedocumenteerd in Microsoft's VBA-referentie. Die neemt de kolommen die vergeleken moeten worden en geeft aan of de eerste rij koppen bevat. Microsoft's eigen voorbeeld is:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Dat vergelijkt de eerste twee kolommen van het blok A1:C100 en behandelt de eerste rij als kop. Om het te gebruiken, druk je op Alt+F11, voeg je een module in, plak je de regel binnen een Sub en sla je het bestand op als werkmap met macro's (.xlsm). Voer het eerst uit op een kopie. Een macro verwijdert net zo definitief als de opdracht, en Ctrl+Z haalt de rijen niet terug. Je IT-team blokkeert macro's misschien ook, dus controleer dat voordat je iets bouwt wat je collega's niet kunnen uitvoeren. Wil je meer over de taal weten, dan verzamelt de pagina VBA verwant materiaal.

Excel voor het web en mobiel

Microsoft's artikelen over het verwijderen van duplicaten vermelden alleen de desktopversies van Excel (Microsoft 365, 2024, 2021, 2019 en 2016) en zeggen niets over het web of de mobiele apps. Microsoft beschrijft de opdracht Duplicaten verwijderen voor die versies niet, dus bouw er geen proces op. Wat Microsoft wel documenteert voor het web en mobiel is de functie UNIQUE. Gebruik die dus om op die apparaten een schone lijst te krijgen, en voer het definitief verwijderen uit op de desktop.

Veelgemaakte fouten

  • De opdracht uitvoeren zonder back-up. Sluit je het bestand, dan zijn de rijen definitief weg.
  • De verkeerde kolommen aanvinken. Een kolom weglaten verandert welke rijen als duplicaat gelden, en de hele rij wordt verwijderd, niet alleen de herhaalde cel.
  • Vergeten eerst te sorteren, en dan je afvragen waarom het oude record bleef staan in plaats van het nieuwe.
  • Cellen vergelijken die er hetzelfde uitzien maar anders zijn opgemaakt, zoals datums die op twee manieren zijn geschreven, of tekst met spaties aan het eind.
  • Proberen de opdracht uit te voeren op gegroepeerde of met subtotalen gegevens. Verwijder die eerst.
  • UNIQUE met exactly_once op WAAR zien als manier om de overtollige rijen te verwijderen. Die geeft alleen waarden terug die precies één keer voorkomen, dus herhaalde waarden verdwijnen volledig uit het resultaat.

Verwante onderwerpen om te verkennen

  • Markeer duplicaten met voorwaardelijke opmaak en signaleer herhaalde rijen met een hulpkolom met COUNTIF voordat je iets verwijdert. Beide staan hierboven beschreven.
  • Maak de data schoon bij het importeren. Een stap in Power Query houdt duplicaten uit elke vernieuwing, en werkt goed voor CSV-bestanden en andere systemen.
  • Voorkom duplicaten bij invoer. Gegevensvalidatie met een aangepaste formule kan zo worden ingesteld dat gebruikers geen waarde meer kunnen typen die al bestaat. Test het eerst op een klein werkblad.
  • Automatiseer terugkerende taken met een macro, en leer hoe je die veilig op een kopie uitvoert.
  • Gegevens samenvoegen uit meerdere bronnen, waar duplicaten vaak vandaan komen. Plan de volgorde van opschonen en samenvoegen.

Volgende stap

Kopieer de gegevens voordat je de volgende opschoning doet, markeer de duplicaten met voorwaardelijke opmaak en kijk hoeveel er zijn. Voer daarna Duplicaten verwijderen uit op de kopie, en vergelijk de aantallen met de getallen die Excel rapporteert.

Bronnen

Entities in this article

Microsoft Excel Power Query Visual Basic for Applications (VBA)

Related articles

RSS