Hoe je op een veilige manier dubbele waarden in Excel kunt verwijderen

Stel je een boekhouder voor met een exportbestand van 12.000 rijen met klantbestellingen. Twee klanten hebben dezelfde achternaam, een handvol bestellingen komt dubbel voor omdat de export twee keer is uitgevoerd, en ergens tussenin zitten vermeldingen die alleen maar op duplicaten lijken. Ze selecteert het hele werkblad, klikt op ‘Duplicaten verwijderen’ en Excel meldt vrolijk: ‘347 dubbele waarden gevonden en verwijderd.’ Binnen enkele seconden klaar. Alleen waren er ongeveer 90 daarvan helemaal geen duplicaten, en nu zijn ze verdwenen.

Dat is juist de valkuil. Hoe je duplicaten veilig uit Excel verwijdert, begint al voordat je op de knop klikt. De functie ‘Duplicaten verwijderen’ werkt snel, maakt permanente wijzigingen aan en geeft helemaal niet aan wat er precies wordt vergeleken.

De meeste handleidingen gaan meteen naar die knop. Wat ze echter over het hoofd zien, is het gedeelte waarbij je je gegevens bewaart: kijk eerst goed voordat je iets verwijdert. Selecteer eerst de cellen, controleer wat Excel als een duplicaat beschouwt en verwijder het vervolgens. Laten we het dus eens stap voor stap doornemen.

Waarom het gebruikelijke advies mensen gegevens kost

De meeste handleidingen bestaan uit ongeveer drie zinnen. Selecteer je gegevens, ga naar het tabblad ‘Gegevens’ en klik op ‘Duplicaten verwijderen’. Technisch gezien klopt dit. Maar in de praktijk is het gevaarlijk.

Hier spelen twee problemen een rol. Ten eerste is deze handeling destructief. Excel verplaatst dubbele rijen niet naar een apart werkblad en markeert ze ook niet ter controle – het verwijdert de rijen en schuift de rest naar boven. Als je het bestand opslaat en sluit, kun je de volgende ochtend niets meer ongedaan maken.

Ten tweede, en dit is wat niemand uitlegt, bepalen de kolommen die je in het dialoogvenster aanvinkt wat als een duplicaat wordt beschouwd. Die ene keuze verandert het hele resultaat. Vink alleen de kolom „E-mail” aan en Excel beschouwt twee rijen als identiek als de e-mailadressen overeenkomen, zelfs als de namen, besteldata en bedragen totaal verschillend zijn. Het behoudt de eerste, verwijdert de rest, en echte bestellingen zijn verdwenen.

In de eigen richtlijnen van Microsoft wordt toegegeven dat het verwijderen van dubbele waarden leidt tot het definitief wissen van gegevens. Daarom raadt Microsoft Support aan om je bereik naar een ander werkblad te kopiëren voordat je de tool uitvoert. Een goede gewoonte. Maar bijna niemand doet het.

Dit is de methode waarmee je gegevens daadwerkelijk worden beschermd. Het kost je misschien twee minuten extra, en het zorgt ervoor dat er niets wordt gewist zonder dat je het eerst hebt kunnen zien.

Stap 1 – Duplicaten opsporen in Excel met voorwaardelijke opmaak

Voordat je iets ongedaan maakt, moet je de dubbele waarden eerst zichtbaar maken. Selecteer het bereik en ga vervolgens naar Start > Voorwaardelijke opmaak > Regels voor het markeren van cellen > Dubbele waarden. Kies een vulkleur en bevestig.

Excel markeert elke dubbele waarde. Dit is de snelste manier om duplicaten op te sporen zonder ook maar één cel te wijzigen, en het is volledig ongedaan te maken. Bevalt het resultaat je niet? Wis de regel dan. Er is niets gewijzigd.

Een belangrijk punt om te weten over de markeerfunctie: deze werkt kolom voor kolom op de cellen die je hebt geselecteerd. Als je één kolom selecteert, markeert Excel alleen de herhaalde waarden in die kolom. Selecteer je meerdere kolommen, dan behandelt het programma elke cel afzonderlijk. Dit kan er wat rommelig uitzien, maar het laat je wel precies zien waar de herhalingen zich bevinden.

Stap 2 – Controleer alles voordat je het definitief vastlegt

Blader door de gemarkeerde cellen. Dit is de stap die de boekhouder heeft overgeslagen. Ga na of een gemarkeerde waarde daadwerkelijk een dubbele rij is of slechts een waarde die terecht wordt herhaald. Als je de naamkolom markeert, worden twee verschillende personen met de naam „James Miller“ beide gemarkeerd. Geen van beide is een dubbele order.

In dit overzicht zie je het verschil tussen „deze waarde komt twee keer voor” en „dit hele record is een kopie”. Dat is helemaal niet hetzelfde – en de functie „Duplicaten verwijderen” houdt alleen rekening met de kolommen die je erin invoert.

Stap 3: Verwijder, terwijl de juiste kolommen zijn geselecteerd

Nu ga je verwijderen, en nu weet je wat je verwijdert. Selecteer je gegevens, ga naar het tabblad ‘Gegevens’ en klik op ‘Duplicaten verwijderen’. In het dialoogvenster worden alle kolommen weergegeven met een selectievakje. Dat dialoogvenster is waar het allemaal om draait.

Hoe de keuze van de kolom alles verandert

Dit is het deel dat de meeste concurrenten volledig buiten beschouwing laten, dus laten we eens goed bekijken wat er precies gebeurt.

Bij het verwijderen van dubbele waarden in meerdere kolommen verwijdert Excel een rij pas als elke aangevinkte kolom exact overeenkomt met een andere rij. Als je alle kolommen aanvinkt, moet een rij een exacte kopie zijn voordat deze wordt verwijderd. Dat is de veiligste instelling, omdat hierdoor vrijwel nooit een record wordt verwijderd dat op een wezenlijke manier afwijkt.

Als je het vinkje bij kolommen verwijdert, wordt de definitie minder strikt. Stel dat je gegevens de velden Naam, E-mail, Bestelnummer en Bedrag bevatten. Hieronder zie je hoe dezelfde knop zich gedraagt, afhankelijk van wat je aanvinkt:

Zie je het patroon? Hoe minder kolommen je aanvinkt, hoe ingrijpender de verwijdering. Wie elke unieke bestelling wil behouden, moet alle kolommen aanvinken die een rij uniek maken, terwijl iemand die een gewone mailinglijst opschoont, gerust alleen ‘E-mail’ kan aanvinken – een herhaling van een e-mailadres is daar immers echt een dubbel contact.

Vuistregel: vink de kolommen aan die samen een uniek record vormen. Bij bestellingen zijn dat meestal het bestelnummer en een paar andere velden. Bij een contactenlijst kan dat bijvoorbeeld alleen het e-mailadres zijn.

Soms wil je het origineel helemaal niet vernietigen. Je wilt gewoon een schone lijst met unieke waarden die je apart krijgt aangeleverd. Hier zijn twee veilige manieren om dat te doen.

De functie UNIQUE is de meest overzichtelijke oplossing, mits je Excel-versie deze ondersteunt. Voer de functie in een lege cel in en er wordt een lijst zonder dubbele waarden in de aangrenzende cellen weergegeven, terwijl de bron ongewijzigd blijft.

=UNIQUE(A2:A5000)

De oorspronkelijke gegevens blijven precies waar ze zijn. Als je de bron wijzigt, wordt de unieke lijst automatisch bijgewerkt. Dat is het grote voordeel ten opzichte van ‘Duplicaten verwijderen’: het is per definitie niet-destructief.

De andere manier is via ‘Geavanceerd filter’. Data > Advanced > “Kopiëren naar een andere locatie” met “Alleen unieke records” aangevinkt. Hiermee wordt een kopie zonder dubbele records ergens anders op het werkblad opgeslagen. ExcelDemy behandelt verschillende niet-destructieve methoden als u variaties wilt, en DataCamp gaat dieper in op de aanpak met de functie UNIQUE. Bij beide blijft de bron intact, en dat is precies de bedoeling.

Problemen met veelvoorkomende valkuilen oplossen

Er zijn een paar dingen waar mensen over struikelen, zelfs als ze de volgorde aanhouden.

“Het heeft rijen verwijderd die ik nodig had.” Het is bijna altijd een probleem met de selectie van kolommen. Je hebt te weinig kolommen aangevinkt, waardoor Excel op onvolledige gegevens heeft gezocht. Heb je nog niet opgeslagen en gesloten? Druk dan onmiddellijk op Ctrl+Z. Al gesloten? Dan werk je vanuit een back-up. Dit is precies waarom het belangrijk is om altijd eerst een kopie te maken.

Duplicaten die niet worden opgemerkt. Meestal gaat het om spaties aan het einde of inconsistent gebruik van hoofdletters. „[email protected] “ met een spatie is voor Excel niet hetzelfde als „[email protected]“. Pas eerst de functies TRIM en CLEAN toe op de kolom, anders worden deze fouten bij het markeren onopgemerkt overgeslagen.

Getallen die als tekst zijn opgeslagen. Een waarde die als tekst is opgemaakt en dezelfde waarde als een getal zullen niet met elkaar overeenkomen. Zet de kolom om naar één consistent formaat voordat je de dubbele waarden verwijdert, anders krijg je valse negatieven.

Samengevoegde cellen geven fouten weer. ‘Remove Duplicates’ werkt niet op bereiken met samengevoegde cellen. Maak de cellen eerst weer los van elkaar.

Wordt bij het verwijderen van dubbele waarden in Excel de hele rij verwijderd of alleen de cel?
De hele rij. Excel verwijdert het volledige record en schuift de rijen eronder naar boven. Er wordt nooit één enkele cel leeggemaakt; daarom is de kolomselectie zo belangrijk: de vergelijking gebeurt per kolom, maar het verwijderen gebeurt per rij.

Kan ik mijn gegevens terugkrijgen nadat ik de dubbele gegevens heb verwijderd?
Alleen met Ctrl+Z voordat je opslaat en sluit, of vanuit een opgeslagen back-up. Zodra het bestand is gesloten, is herstel niet meer mogelijk. Kopieer je selecteerde gegevens eerst naar een ander werkblad, voor de zekerheid.

Hoe kan ik dubbele waarden markeren in plaats van ze te verwijderen?
Home > Voorwaardelijke opmaak > Regels voor het markeren van cellen > Dubbele waarden. Hiermee wordt elke herhaalde waarde gekleurd zonder dat je gegevens worden gewijzigd, en je kunt de regel op elk moment ongedaan maken. Dit is de veiligste manier om dubbele waarden op te sporen die Excel biedt.

Wat is het verschil tussen ‘UNIQUE’ en ‘Remove Duplicates’?
UNIQUE is een formule die een aparte lijst met unieke waarden genereert, die in realtime wordt berekend, waarbij je bron ongewijzigd blijft. De functie ‘Duplicaten verwijderen’ brengt permanente wijzigingen aan in de gegevens zelf. Gebruik UNIQUE als je het origineel wilt behouden.

Hoe verwijder ik dubbele waarden in meerdere kolommen?
Vink in het dialoogvenster alle kolommen aan die samen een uniek record vormen. Excel verwijdert een rij pas als alle aangevinkte kolommen exact overeenkomen met een andere rij. Hoe meer kolommen je aanvinkt, hoe veiliger; hoe minder, hoe agressiever.

De korte versie

‘Duplicaten verwijderen’ is niet de vijand. Het blindelings uitvoeren wel. Markeer je duplicaten met voorwaardelijke opmaak, zodat je ze daadwerkelijk kunt zien. Controleer vervolgens of het echte duplicaten zijn of slechts herhaalde waarden, en verwijder ze vervolgens met de juiste kolommen aangevinkt. En kopieer je gegevens elke keer eerst naar een ander werkblad, want de tool geeft geen tweede kans zodra het bestand is gesloten.

Zo verwijder je dubbele gegevens in Excel zonder de gegevens te verwijderen die je nodig hebt.

Hetzelfde geldt voor de software waarop je dit allemaal draait. Met een legitieme, correct gelicentieerde versie van Office weet je zeker dat je formules, functies zoals UNIQUE en je bestanden zich gedragen zoals in de documentatie wordt beschreven.