Dubbele waarden verwijderen in Excel: drie manieren
Bijgewerkt op
Dubbele waarden verwijder je in Excel via het tabblad Gegevens en de knop Duplicaten verwijderen. Excel haalt dan alle rijen weg die al eerder in de lijst voorkomen en houdt steeds de bovenste over. Wil je eerst zien wat er dubbel is, of je originele lijst houden, gebruik dan voorwaardelijke opmaak of de functie UNIEK.
Duplicaten verwijderen: stappenplan
Zo haal je dubbele rijen in één keer weg:
- Maak eerst een kopie van je werkblad, want Excel verwijdert de rijen echt
- Klik op een cel in je lijst
- Ga naar het tabblad Gegevens en klik op Duplicaten verwijderen
- Vink Mijn gegevens bevatten kopteksten aan als de eerste rij kopjes bevat
- Kies de kolommen die Excel moet vergelijken
- Klik op OK
Excel meldt daarna hoeveel dubbele waarden er zijn verwijderd en hoeveel unieke waarden er overblijven. Van elke groep dubbele rijen blijft de bovenste staan. Klopt het niet, druk dan meteen op Ctrl + Z om alles terug te zetten.
Welke kolommen vink je aan?
De kolommen die je aanvinkt, bepalen wat Excel als dubbel ziet:
- Alle kolommen: alleen rijen die in elke kolom precies hetzelfde zijn, gaan weg. Dat is de veiligste keuze
- Eén kolom, zoals Klantnummer of E-mail: elke rij met een waarde die al eerder voorkwam, gaat weg, ook als de andere kolommen verschillen
Kies je één kolom, denk dan na over welke rij je wilt houden. Excel houdt altijd de bovenste. Wil je bijvoorbeeld van elke klant de nieuwste regel bewaren, sorteer de lijst dan eerst op datum van nieuw naar oud. Daarna verwijder je de duplicaten op klantnummer.
Eerst bekijken: dubbele waarden markeren
Wil je eerst zien wat er dubbel is voordat je iets weghaalt, kleur de dubbele waarden dan:
- Selecteer de kolom
- Kies op het tabblad Start voor Voorwaardelijke opmaak
- Kies Markeringsregels voor cellen en dan Dubbele waarden
Alle waarden die vaker voorkomen, krijgen een kleur. Filter daarna op celkleur om ze bij elkaar te zien.
Met een hulpkolom krijg je nog meer controle. =AANTAL.ALS($B$2:$B$100;B2) laat zien hoe vaak elke waarde voorkomt. =AANTAL.ALS($B$2:B2;B2)>1 geeft alleen WAAR bij de tweede, derde en volgende keer. Filter op WAAR, dan controleer en verwijder je precies die rijen. De eerste keer blijft gewoon staan.
Een lijst zonder dubbelen met UNIEK
In Excel 365 en Excel 2021 kan het ook met een formule. =UNIEK(B2:B100) zet elke waarde één keer onder elkaar, op een andere plek. Je originele lijst blijft dus staan.
Dat heeft twee voordelen. Je kunt niets kwijtraken, en de lijst werkt vanzelf bij als er gegevens bijkomen. Zet er SORTEREN omheen voor een lijst op alfabet: =SORTEREN(UNIEK(B2:B100)).
Wil je weten hoeveel dubbelen er in een lijst staan, trek dan het aantal unieke waarden af van het totaal:
=AANTALARG(B2:B100)-AANTALARG(UNIEK(B2:B100))
Gebruik een bereik zonder lege cellen. Dan is de uitkomst precies het aantal rijen dat Duplicaten verwijderen zou weghalen als je alleen die kolom aanvinkt.
Waarom Excel sommige dubbelen niet ziet
Soms lijken twee waarden hetzelfde, maar ziet Excel ze toch als verschillend. De bekendste oorzaken:
- Een spatie aan het eind, zoals Bakkerij Vos met een spatie erachter
- Een andere schrijfwijze, zoals Garage Smit en Garage Smit BV
- Een getal dat als tekst is opgeslagen naast hetzelfde getal als echt getal
Hoofdletters maken niet uit. Jansen en JANSEN ziet Excel als dubbel.
Maak de gegevens daarom eerst schoon. Met =SPATIES.WISSEN(B2) in een hulpkolom haal je overbodige spaties weg. Kopieer die kolom en plak hem terug met Plakken speciaal en Waarden. Verwijder daarna pas de duplicaten.
Voorbeelden
Hoe vaak komt elk adres voor?
Voor een workshop schreven sommige mensen zich twee keer in. AANTAL.ALS laat per rij zien hoe vaak het e-mailadres in de lijst staat.
=AANTAL.ALS($B$2:$B$7;B2) in cel C2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Naam | Aantal keer | ||
| 2 | Sanne de Vries | sanne@voorbeeld.nl | 2 | |
| 3 | Mark Jansen | mark@voorbeeld.nl | 2 | |
| 4 | Fatima Bakker | fatima@voorbeeld.nl | 1 | |
| 5 | Sanne de Vries | sanne@voorbeeld.nl | 2 | |
| 6 | Lisa Visser | lisa@voorbeeld.nl | 1 | |
| 7 | Mark Jansen | mark@voorbeeld.nl | 2 | |
| 8 | ||||
| 9 |
Klik op cel C2 om de formule te zien of aan te passen.
Het adres van Sanne staat twee keer in de lijst, dus de uitkomst is 2. Dat van Mark ook, die van Fatima en Lisa één keer.
Alleen de tweede keer markeren
Je wilt de eerste inschrijving houden en alleen de herhalingen weghalen. Het bereik groeit mee naar beneden, doordat alleen de eerste B2 vaststaat.
=AANTAL.ALS($B$2:B5;B5)>1 in cel C5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Naam | Dubbel? | ||
| 2 | Sanne de Vries | sanne@voorbeeld.nl | ONWAAR | |
| 3 | Mark Jansen | mark@voorbeeld.nl | ONWAAR | |
| 4 | Fatima Bakker | fatima@voorbeeld.nl | ONWAAR | |
| 5 | Sanne de Vries | sanne@voorbeeld.nl | WAAR | |
| 6 | Lisa Visser | lisa@voorbeeld.nl | ONWAAR | |
| 7 | Mark Jansen | mark@voorbeeld.nl | WAAR | |
| 8 | ||||
| 9 |
Klik op cel C5 om de formule te zien of aan te passen.
In rij 5 staat Sanne voor de tweede keer, dus WAAR. Haar eerste inschrijving in rij 2 geeft ONWAAR en blijft staan.
Hoeveel dubbelen staan erin?
Voordat je iets verwijdert, wil je weten hoeveel rijen Duplicaten verwijderen zou weghalen.
=AANTALARG(B2:B7)-AANTALARG(UNIEK(B2:B7)) in cel D2
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Naam | Aantal dubbelen | |||
| 2 | Sanne de Vries | sanne@voorbeeld.nl | 2 | ||
| 3 | Mark Jansen | mark@voorbeeld.nl | |||
| 4 | Fatima Bakker | fatima@voorbeeld.nl | |||
| 5 | Sanne de Vries | sanne@voorbeeld.nl | |||
| 6 | Lisa Visser | lisa@voorbeeld.nl | |||
| 7 | Mark Jansen | mark@voorbeeld.nl | |||
| 8 | |||||
| 9 |
Klik op cel D2 om de formule te zien of aan te passen.
Er staan zes adressen in de lijst, waarvan vier verschillende. Er zijn dus 2 dubbelen.
Veelgemaakte fouten
Geen kopie gemaakt
Duplicaten verwijderen haalt rijen echt weg. Werk op een kopie van het blad, of druk direct na het verwijderen op Ctrl + Z als het niet klopt.
Alleen één kolom geselecteerd
Selecteer je één kolom, dan vraagt Excel of je de selectie wilt uitbreiden. Kies je Doorgaan met de huidige selectie, dan verdwijnen alleen cellen uit die kolom en passen de rijen niet meer bij elkaar. Kies De selectie uitbreiden.
Te weinig kolommen aangevinkt
Vink je alleen Naam aan, dan verdwijnt ook een tweede Jan Jansen met een ander adres. Vink de kolommen aan die samen echt bepalen of een rij dubbel is.
Dubbelen blijven staan
Er zit een verschil in dat je niet ziet, zoals een spatie aan het eind. Maak de kolom eerst schoon met SPATIES.WISSEN en verwijder daarna de duplicaten.
Oefen het zelf
Lezen is één, het zelf typen is wat blijft hangen. Deze lessen gaan erover:
Veelgestelde vragen
Hoe verwijder ik dubbele waarden in Excel?
Klik in je lijst, kies op het tabblad Gegevens voor Duplicaten verwijderen, vink de kolommen aan die moeten overeenkomen en klik op OK.
Hoe vind ik dubbele waarden zonder ze te verwijderen?
Gebruik Voorwaardelijke opmaak, Markeringsregels voor cellen en dan Dubbele waarden. Of zet =AANTAL.ALS($A$2:$A$100;A2)>1 in een hulpkolom.
Welke rij houdt Excel over bij Duplicaten verwijderen?
Altijd de bovenste. Sorteer de lijst daarom eerst zo dat de rij die je wilt houden bovenaan staat.
Is Duplicaten verwijderen hoofdlettergevoelig?
Nee. Jansen en JANSEN tellen als dezelfde waarde. Een extra spatie maakt wel verschil.
Hoe tel ik hoeveel dubbele waarden er zijn?
Trek het aantal unieke waarden af van het totaal: =AANTALARG(A2:A100)-AANTALARG(UNIEK(A2:A100)). Gebruik een bereik zonder lege cellen.
Kan ik Duplicaten verwijderen ongedaan maken?
Ja, met Ctrl + Z, zolang je het bestand nog niet hebt gesloten. Maak voor de zekerheid altijd eerst een kopie.