Wat is gegevensvalidatie in Excel? Een uitgebreide gids om gegevensvalidatieregels in Excel toe te voegen, te bewerken en te verwijderen

Het begrijpen en toepassen van principes voor gegevensvalidatie is essentieel voor zowel beginnende data-analisten als ervaren specialisten. In deze tutorial laten we zien hoe je gegevensvalidatie uitvoert in Excel. We behandelen de verschillende technieken voor gegevensvalidatie en demonstreren hoe je gegevensvalidatieregels toevoegt, bewerkt en verwijdert.

Wil je eerst de basis van Excel opfrissen, inclusief de belangrijkste formules, operatoren, wiskundefuncties en meer? Bekijk dan ons Excel Formulas Cheat Sheet.

Wat is gegevensvalidatie in Excel?

Gegevensvalidatie in Excel verwijst naar een specifieke functie die de waarden beheert die in een cel kunnen worden ingevoerd. Het biedt een reeks technieken waarmee je zowel bestaande waarden kunt controleren als regels kunt maken die op nieuwe invoer worden toegepast.

Zo vind je de functie Data Validation (Gegevensvalidatie) in Excel:

1. Open het tabblad Data.
2. Ga naar de groep Data Tools.
3. Klik op de knop Data Validation.

De functie Data Validation openen

Vervolgens verschijnt het volgende pop-upvenster op je scherm.

Pop-upvenster Data Validation

Later in deze tutorial bekijken we dit venster in meer detail, inclusief hoe je onze gegevensvalidatieregels aanpast en verwijdert. Laten we eerst meer leren over gegevensvalidatie. 

Waarom is gegevensvalidatie belangrijk?

Gegevensvalidatie is het controleren van de juistheid en consistentie van gegevens die in een database zijn of zullen worden ingevoerd voor verdere opslag en verwerking. Met gegevensvalidatie zorgen we ervoor dat de gegevens voldoen aan specifieke, vooraf gedefinieerde criteria. Deze criteria kunnen het juiste gegevenstype omvatten, waarden binnen een opgegeven bereik en gegevens met de juiste lengte.

Robuuste gegevensvalidatie is om veel redenen een must. Het beschermt tegen invoerfouten en zorgt ervoor dat de informatie die in analyses wordt gebruikt betrouwbaar en valide is. Laten we de belangrijkste redenen voor gegevensvalidatie van dichterbij bekijken.

Datakwaliteit

Gegevensvalidatie voorkomt dat onjuiste of inconsistente waarden in de database worden opgeslagen. Dit verkleint fouten, verhoogt de algehele datakwaliteit en vergroot de zeggingskracht van data-analyse.

Het belang van hoogwaardige data wordt perfect samengevat in het GIGO-principe, dat populair is in de informatica en staat voor garbage in, garbage out. Kortom: het analyseren van foutieve data leidt tot onjuiste en misleidende resultaten.

Hier zijn enkele voorbeelden waarbij geen gegevensvalidatieregels zijn toegepast en onze data vreemde waarden bevatten.

• Universiteitsregistraties: Een universiteit kan geen kommagetal aan studenten hebben; alleen gehele getallen zijn geldig.
• Medische dossiers: De maximale lichaamstemperatuur van een persoon kan niet hoger zijn dan 50°C.
• Leeftijdsvelden: Een leeftijdsveld mag alleen realistische waarden accepteren, doorgaans tussen 0 en 120.
• E-mailadressen: Een e-mailveld moet een specifiek formaat volgen, met een '@'-symbool. 
• Voorraad aantallen: Productaantallen in een voorraadsysteem mogen niet negatief zijn.

Onthoud dat gegevensvalidatie weliswaar de dataintegriteit waarborgt, maar geen absolute juistheid garandeert. In een tabel voor katten en honden kan een validatieregel bijvoorbeeld afdwingen dat alleen "cat" of "dog" is toegestaan, maar het kan niet voorkomen dat iemand per ongeluk een kat als hond labelt.

Dataintegriteit en compliance

Gegevensvalidatie is cruciaal voor het behouden van dataintegriteit en het naleven van regelgeving en normen binnen de sector.

In de zorg is naleving van HIPAA verplicht om patiëntinformatie te beschermen. Evenzo moeten bedrijven die in de EU actief zijn voldoen aan de AVG (GDPR), om privacy en beveiliging van persoonsgegevens te waarborgen. 

Opschonen en pre-processen van data

Een bekend principe stelt dat data-analisten ongeveer 80% van hun tijd besteden aan het gereedmaken van data en slechts 20% aan de analyse zelf. Omdat data-analisten vaak onevenredig veel tijd kwijt zijn aan het opschonen en voorbereiden, kunnen vroeg toegepaste gegevensvalidatieregels veel tijd besparen. 

Besluitvorming

Het analyseren van gevalideerde data leidt tot betrouwbare en waardevolle inzichten die helpen om beter onderbouwde, datagedreven beslissingen te nemen. Op de lange termijn bespaart gegevensvalidatie tijd en geld en resulteert het in een gezonder, kostenefficiënter en winstgevender bedrijf.

Verschillende technieken voor gegevensvalidatie in Excel

Excel heeft verschillende benaderingen voor gegevensvalidatie voor uiteenlopende behoeften. Voor het gemak delen we ze op in groepen en kenmerken we elke groep.

Beperken van gegevenstype en bereik/lengte

In Excel kun je kiezen uit de volgende Excel-gegevenstypen: Whole number, Decimal, Date, Time en Text.

Laten we nu een tabel maken om te bekijken hoe bepaalde logische voorwaarden toepasbaar zijn op onze verschillende gegevenstypen. De logische voorwaarden zijn: between, not between, equal to, not equal to, greater than, less than, greater than or equal to en less than or equal to.

• Between en Not Between: Deze voorwaarden worden doorgaans gebruikt bij numerieke waarden, datums en tijden. Toegepast op text verwijzen between en not between naar de lengte, ofwel het aantal tekens, in een cel.

• Equal to en Not Equal to: Deze kunnen op alle gegevenstypen worden toegepast. Voor text wordt gecontroleerd op exacte overeenkomsten.

• Greater Than, Less Than, Greater Than or Equal To en Less Than or Equal To: Deze voorwaarden zijn geschikt voor de gegevenstypen whole number, date en time, maar zijn niet direct toepasbaar op text, tenzij je kijkt naar tekstlengte of, in specifieke gevallen, alfabetische volgorde.

Vervolgkeuzelijsten maken

Soms is het nodig de opties in bepaalde Excel-cellen te beperken tot een vooraf gedefinieerde set waarden. Neem ons eerdere voorbeeld waarin alleen cats en dogs geldige invoer zijn in een tabel. De beste manier om met een gegevensvalidatieregel krokodillen en andere dieren uit onze tabel te weren, is voor elke relevante cel een vervolgkeuzelijst te maken. Laten we de stappen bekijken.

1. Voer de items in die je in je vervolgkeuzelijst wilt laten verschijnen. Je kunt de items in de lijst ook sorteren door de hele lijst te selecteren, met de rechtermuisknop te klikken, Sort te kiezen en een methode te selecteren.

Items voor een vervolgkeuzelijst invoeren

2. Zet je lijst om in een Excel-tabel. Selecteer een cel in het bereik en druk op Ctrl+T. Deze stap is belangrijk, omdat bij toevoegen of verwijderen van items alle gekoppelde vervolgkeuzelijsten automatisch worden bijgewerkt.

De lijst voor een vervolgkeuze omzetten naar een tabel

3. Selecteer de Excel-cel(len) in het werkblad waar je de vervolgkeuzelijst wilt toevoegen.
4. Open het pop-upvenster Data Validation. Open het tabblad Data. Ga naar de groep Data Tools en klik op de knop Data Validation.
5. Selecteer op het tabblad Settings in het veld Allow de optie List. Laat In-cell dropdown aangevinkt. Laat Ignore blank aangevinkt als de cel(len) leeg mogen blijven; zo niet, haal het vinkje weg:

Een vervolgkeuzelijst instellen als validatiemethode

6. Plaats de cursor in het veld Source, selecteer je listrange en druk op OK. Laat de koprij weg, anders verschijnt die als keuzemogelijkheid.

De bron voor een vervolgkeuzelijst selecteren

7. Selecteer de cel(len) waarvoor je deze gegevensvalidatie hebt gemaakt. Je ziet nu rechts van de cel een pijltje voor de vervolgkeuzelijst. Klik op het pijltje en kies de gewenste waarde.

Een vervolgkeuzelijst in een cel openen

Aangepaste formules toepassen

• range: Hiermee bedoelen we het bereik van Excel-cellen waarop we formulegebaseerde gegevensvalidatie willen toepassen.
• uppermost_cell: Hiermee bedoelen we de eerste, of bovenste, cel in ons bereik.

Alleen nummers of alleen tekst

De volgende twee formules controleren of de inhoud van een specifieke cel respectievelijk een getal of tekst is. Dit is handig als je numerieke en niet-numerieke waarden wilt onderscheiden, of tekst en niet-tekst.

=ISNUMBER(uppermost_cell)Mogelijk gemaakt door

=ISTEXT(uppermost_cell)Mogelijk gemaakt door

Alleen hoofdletters of alleen kleine letters

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))Mogelijk gemaakt door

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))Mogelijk gemaakt door

Geen duplicaten

=COUNTIF(range, uppermost_cell)<=1Mogelijk gemaakt door

Alleen waarden die beginnen met een specifieke substring

De volgende formule controleert of de eerste n tekens van een string exact overeenkomen. Dit geeft een TRUE of FALSE terug. 

=EXACT(LEFT(uppermost_cell, n), substring)Mogelijk gemaakt door

Alleen weekdagen of alleen weekenddagen

=WEEKDAY(uppermost_cell, 2)<=5Mogelijk gemaakt door

=WEEKDAY(uppermost_cell, 2)>5Mogelijk gemaakt door

Alleen datums in het verleden

=uppermost_cell<TODAY()Mogelijk gemaakt door

Geen spaties aan het begin of einde

=uppermost_cell=TRIM(uppermost_cell)Mogelijk gemaakt door

Geen spaties

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")Mogelijk gemaakt door

Voorkomen dat het totaal wordt overschreden

=SUM(range)<=total_cellMogelijk gemaakt door

Wil je je formuledeskundigheid in Excel snel vergroten? Volg dan een complete en praktijkgerichte cursus Data Analysis in Excel.

Gegevensvalidatie gebruiken in Excel

Voor de duidelijkheid heb ik dit gedeelte opgedeeld in twee subsecties. In de eerste laten we zien hoe je je werkmap inricht om gegevensvalidatieregels op te nemen. In de tweede leggen we uit hoe je deze regels toepast op cellen die al data bevatten. Laten we beginnen. 

Een Excel-werkmap configureren voor gegevensvalidatieregels

1. Selecteer de cel(len) in Excel waar je gegevensvalidatie wilt toepassen.
2. Open het pop-upvenster Data Validation. Open het tabblad Data. Ga naar de groep Data Tools en klik op de knop Data Validation.
3. Selecteer op het tabblad Settings in het veld Allow de benodigde methode, die standaard is ingesteld op Any value (geen validatie).

De benodigde methode selecteren

4. Laat het vakje Ignore blank aangevinkt als de cel(len) leeg mogen blijven; zo niet, haal het vinkje weg.

Lege cellen behandelen

5. Als je de methode List hebt gekozen, selecteer dan een vooraf gedefinieerde regel die past bij je gegevenstype. Als je de methode Custom hebt gekozen, voer dan een aangepaste formule in.

Een logische voorwaarde selecteren om bereik/lengte te beperken

Onder- en bovengrenzen invoeren om bereik/lengte te beperken

Een aangepaste formule toepassen

6. Op het tabblad Input Message kun je een Title en Input message instellen. Standaard is er geen invoerbericht. 

Een invoerbericht instellen om de gebruiker te sturen naar de juiste waarden

Bekijken hoe het invoerbericht eruitziet

7. Op het tabblad Error Alert kun je de Style (Stop, Warning of Information), de Title en de Error message instellen. Standaard is de foutmelding van het type Stop.

Een foutmelding instellen

Klik na OK om te testen: selecteer een lege cel waarop deze gegevensvalidatie van toepassing is en voer een ongeldige waarde in. Ter vergelijking zie je hier een voorbeeld van een foutmelding in elk van de stijlen. 

Een foutmelding van het type Stop bij een ongeldige waarde

Een foutmelding van het type Warning bij een ongeldige waarde

Een foutmelding van het type Information bij een ongeldige waarde

De standaardfoutmelding bij een ongeldige waarde

Opmerking: Foutmeldingen van het type Warning of Information voorkomen niet dat mensen ongeldige data invoeren; foutmeldingen van het type Stop doen dat wel.

Gegevensvalidatie toepassen op cellen die al gevuld zijn

Wat als sommige cellen waarop je gegevensvalidatie wilt toepassen al waarden bevatten? De functie Data Validation ondersteunt zowel validatie vóór als na invoer. 

1. Selecteer de cellen waarop je gegevensvalidatie wilt toepassen. Sommige van die cellen bevatten al waarden.

De cellen selecteren voor gegevensvalidatie, inclusief reeds gevulde cellen

2. Open het pop-upvenster Data Validation. Open het tabblad Data. Ga naar de groep Data Tools en klik op de knop Data Validation. 
3. Selecteer op het tabblad Settings in het veld Allow de benodigde methode, die standaard is ingesteld op Any value.

Gegevensvalidatie instellen voor de geselecteerde cellen

3. Selecteer een cel waarop deze validatie is toegepast, open het tabblad Data, klik op het pijltje naast de knop Data Validation in de groep Data Tools en kies Circle Invalid Data.

Ongeldige data omcirkelen in Excel

Omg cirkelde ongeldige data in Excel

4. Pas nu elke ongeldige waarde aan zodat deze aan de opgelegde criteria voldoet. De cirkel verdwijnt dan automatisch. Let op: als je alleen de ongeldige waarde verwijdert, blijft de cirkel staan.
5. Om validatiecirkels te verwijderen, selecteer je een cel waarop deze validatie is toegepast, open je het tabblad Data, ga je naar de groep Data Tools, open je de knop Data Validation en kies je Clear Validation Circles. 

Validatiecirkels verwijderen bij ongeldige data

Gegevensvalidatie bewerken in Excel

1. Selecteer een cel waarop al gegevensvalidatie is toegepast.
2. Open het pop-upvenster Data Validation. Open het tabblad Data. Ga naar de groep Data Tools en klik op de knop Data Validation.
3. Breng de gewenste wijzigingen aan op een van de tabbladen van het venster Data Validation. Raadpleeg zo nodig de instructies uit het gedeelte Gegevensvalidatie gebruiken in Excel.
4. Ga naar het tabblad Settings en vink Apply these changes to all other cells with the same settings aan.
5. Druk op OK.

Wijzigingen in gegevensvalidatie toepassen op alle cellen met dezelfde instellingen

Gegevensvalidatie verwijderen in Excel

1. Selecteer een cel waarop al gegevensvalidatie is toegepast.
2. Open het pop-upvenster Data Validation. Open het tabblad Data. Ga naar de groep Data Tools en klik op de knop Data Validation.
3. Ga naar het tabblad Settings en vink Apply these changes to all other cells with the same settings aan.
4. Klik op de knop Clear All.
5. Druk op OK.

Gegevensvalidatie verwijderen

Conclusie

In deze tutorial hebben we de definitie van gegevensvalidatie, de voordelen, de implementatie in Excel, de verschillende technieken en het gebruik, bewerken en verwijderen van gegevensvalidatie in Excel behandeld.

Wil je alles uit Excel halen en leren hoe je data voorbereidt, visualiseert en analyseert met praktijkoefeningen, en enkele geavanceerde functies ontdekken? Probeer dan de uitgebreide en toegankelijke skill track Excel Fundamentals. Overweeg ook gespecialiseerde cursussen zoals Data Preparation in Excel of Financial Modeling in Excel voor gerichte training om je expertise te verdiepen. Met deze resources groei je van basisgegevensvalidatie naar het beheersen van geavanceerde Excel-functies.