Gegevensvalidatie in Excel: gegevensvalidatie toevoegen, gebruiken, kopiëren en verwijderen in Excel

In Excel is de functie Gegevensvalidatie een krachtig hulpmiddel waarmee u beperkt wat gebruikers in een cel mogen invoeren. Stel bijvoorbeeld regels in om de lengte van tekst te beperken, invoer te beperken tot specifieke indelingen, unieke waarden af te dwingen of ervoor te zorgen dat tekst begint of eindigt met bepaalde tekens. Zo behoudt u de gegevensintegriteit en voorkomt u fouten in uw werkbladen.

Deze handleiding behandelt hoe u gegevensvalidatie in Excel kunt toevoegen, gebruiken en verwijderen. Het bespreekt zowel fundamentele als geavanceerde bewerkingen en biedt gedetailleerde stapsgewijze instructies om u te helpen deze functie effectief toe te passen op uw taken.

1. Wat is gegevensvalidatie in Excel?

Met de functie **Gegevensvalidatie** beperkt u de invoer in uw werkblad. Normaal gesproken stelt u validatieregels in om te voorkomen dat ongewenste gegevenstypen worden ingevoerd — of juist om alleen specifieke gegevenstypen toe te staan in een lijst met geselecteerde cellen.

Enkele basisgebruiken van de functie Gegevensvalidatie:

1. „Elke waarde”: er wordt geen validatie toegepast; u kunt alles invoeren in de opgegeven cellen. 2. „Geheel getal”: alleen gehele getallen zijn toegestaan. 3. „Decimaal”: u kunt zowel gehele getallen als decimalen invoeren. 4. „Lijst”: u kunt alleen waarden invoeren of selecteren uit de vooraf gedefinieerde lijst; deze worden weergegeven in een keuzelijst. 5. „Datum”: alleen datums zijn toegestaan. 6. „Tijd”: alleen tijdstippen zijn toegestaan. 7. „Tekstlengte”: alleen tekst met de opgegeven lengte is toegestaan. 8. „Aangepast”: stel uw eigen formulegebaseerde regels op om gebruikersinvoer te valideren.

2. Hoe voegt u gegevensvalidatie toe in Excel?

In een Excel-werkblad voegt u gegevensvalidatie toe met de volgende stappen:

1. Selecteer een reeks cellen waarop u gegevensvalidatie wilt toepassen en klik vervolgens op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”, zie schermafbeelding:

2. Ga in het dialoogvenster „Gegevensvalidatie” naar het tabblad „Instellingen” en stel uw eigen validatieregels in. In de criteria-velden kunt u een van de volgende typen opgeven:

• „Waarden”: Typ getallen rechtstreeks in de criteriavakken;
• „Celverwijzing”: Verwijs naar een cel in het werkblad of een ander werkblad;
• „Formules”: Stel complexere formules in als voorwaarden.

Stel bijvoorbeeld een regel in die alleen gehele getallen tussen 100 en 1000 toestaat, zoals weergegeven op de onderstaande schermafbeelding:

3. Nadat u de voorwaarden hebt ingesteld, gaat u naar het tabblad „Invoerbericht” of „Foutmelding” om, indien gewenst, een melding in te stellen voor de validatiecellen. (Wilt u geen melding instellen? Klik dan direct op „OK” om af te sluiten.)

3,1) Invoerbericht toevoegen (optioneel):

U kunt een bericht instellen dat verschijnt zodra een cel met gegevensvalidatie wordt geselecteerd — zo weet de gebruiker direct wat er in die cel mag worden ingevoerd.

Ga naar het tabblad „Invoerbericht” en voer het volgende uit:

Schakel de optie „Toon invoerbericht wanneer cel is geselecteerd” in; Voer de titel en het herinneringsbericht in dat u wilt in de bijbehorende velden; Klik op ‘OK’ om dit dialoogvenster te sluiten.

Wanneer u nu een gevalideerde cel selecteert, verschijnt een berichtvenster zoals hieronder wordt weergegeven:

3,2) Betekenisvolle foutmeldingen maken (optioneel):

Naast het instellen van een invoerbericht kunt u ook foutmeldingen weergeven zodra ongeldige gegevens worden ingevoerd in een cel met gegevensvalidatie.

Ga naar het tabblad „Foutmelding” van het dialoogvenster „Gegevensvalidatie” en doe het volgende:

Schakel de optie „Toon foutwaarschuwing na invoer van ongeldige gegevens” in; Selecteer in de keuzelijst „Stijl” het gewenste waarsuwingstype: „Stop (standaard)”: dit waarsuwingstype voorkomt dat gebruikers ongeldige gegevens invoeren. „Waarschuwing”: waarschuwt gebruikers dat de gegevens ongeldig zijn, maar blokkeert de invoer niet. „Informatie”: informeert gebruikers uitsluitend over ongeldige gegevensinvoer. Voer de titel en het waarschuwingsbericht in dat u wilt in de bijbehorende velden; Klik op „OK” om het dialoogvenster te sluiten.

Wanneer een ongeldige waarde wordt ingevoerd, verschijnt een waarschuwingsvenster, zoals op de onderstaande schermafbeelding wordt weergegeven:

Optie 'Stop': klik op 'Opnieuw' om een nieuwe waarde in te voeren of op 'Annuleren' om de invoer te verwijderen.

Optie „Waarschuwing”: klik op „Ja” om de ongeldige invoer te accepteren, op „Nee” om deze aan te passen of op „Annuleren” om deze te verwijderen.

Optie „Informatie”: klik op „OK” om de ongeldige invoer te accepteren of op „Annuleren” om deze te verwijderen.

Opmerking: als u geen aangepast bericht instelt in het vak „Foutmelding”, wordt standaard een „Stop”-waarschuwing weergegeven, zoals hieronder wordt weergegeven:

3. Basisvoorbeelden van gegevensvalidatie

Bij gebruik van deze functie Gegevensvalidatie zijn er 8 ingebouwde opties beschikbaar om gegevensvalidatie in te stellen, zoals: elke waarde, gehele getallen en decimalen, datum en tijd, lijst, Tekstlengte en aangepaste formule. In dit gedeelte bespreken we hoe u sommige van deze ingebouwde opties in Excel gebruikt.

3,1 Gegevensvalidatie voor gehele getallen en decimalen

1. Selecteer een reeks cellen waarin u alleen gehele getallen of decimalen wilt toestaan en klik op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”.

2. Voer in het dialoogvenster „Gegevensvalidatie” onder het tabblad „Instellingen” de volgende stappen uit:

• Selecteer „Geheel getal” of „Decimaal” in de vervolgkeuzelijst „Toestaan”.
• Kies vervolgens één van de criteria in het vak „Gegevens” (in dit voorbeeld kies ik „tussen”).
• Tip: De beschikbare criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, en kleiner dan of gelijk aan.
• Voer daarna de gewenste waarden in voor ‘Minimum’ en ‘Maximum’ (in dit geval getallen tussen 0 en 100).
• Klik ten slotte op de knop „OK”.

3. Nu kunt u alleen gehele getallen tussen 0 en 100 invoeren in de geselecteerde cellen.

3,2 Gegevensvalidatie voor datum en tijd

Om een specifieke datum of tijd te valideren, is het eenvoudig met deze Gegevensvalidatie. Volg deze stappen:

1. Selecteer een reeks cellen waarin u alleen specifieke datums of tijden wilt toestaan en klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**.

2. Voer in het dialoogvenster 'Gegevensvalidatie', onder het tabblad 'Instellingen', de volgende stappen uit:

• Selecteer 'Datum' of 'Tijd' in de vervolgkeuzelijst 'Toestaan'.
• Kies vervolgens één van de criteria in het vak „Gegevens” (hier kies ik „groter dan”).
• Tip: De beschikbare criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, en kleiner dan of gelijk aan.
• Voer daarna de gewenste „Startdatum” in (ik wil dat datums later zijn dan)8/20/2021).
• Klik ten slotte op de knop „OK”.

3. Er kunnen nu alleen datums later dan 8/20/2021 worden ingevoerd in de geselecteerde cellen.

3,3 Gegevensvalidatie voor Tekstlengte

Als u het aantal tekens dat in een cel mag worden ingevoerd wilt beperken — bijvoorbeeld tot maximaal 10 tekens voor een bepaald bereik — helpt Gegevensvalidatie u daarbij.

1. Selecteer een reeks cellen waarin u de tekstlengte wilt beperken en klik vervolgens op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”.

2. Voer in het dialoogvenster 'Gegevensvalidatie', onder het tabblad 'Instellingen', de volgende stappen uit:

• Selecteer 'Tekstlengte' in de vervolgkeuzelijst 'Toestaan'.
• Kies vervolgens één van de criteria in het vak „Gegevens” (in dit voorbeeld kies ik „kleiner dan”).
• Tip: De beschikbare criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, en kleiner dan of gelijk aan.
• Voer daarna het gewenste maximale aantal tekens in waarmee u de lengte wilt beperken (bijvoorbeeld: Tekstlengte mag niet meer dan 10 tekens bevatten).
• Klik ten slotte op de knop „OK”.

3. De geselecteerde cellen accepteren nu alleen tekenreeksen met minder dan 10 tekens.

3,4 Lijst met gegevensvalidatie (Keuzelijst)

Met de krachtige functie Gegevensvalidatie kunt u snel en eenvoudig een keuzelijst in cellen maken. Volg deze stappen:

1. Selecteer de doelcellen waarin u de keuzelijst wilt invoegen en klik op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**.

2. Voer in het dialoogvenster 'Gegevensvalidatie', onder het tabblad 'Instellingen', de volgende stappen uit:

• Selecteer 'Lijst' in de vervolgkeuzelijst 'Toestaan'.
• Typ in het tekstvak „Bron” de lijstitems direct, gescheiden door komma’s. Typ bijvoorbeeld Niet gestart, In uitvoering, Afgerond om de gebruikersinvoer te beperken tot drie keuzes, of selecteer een reeks cellen met de waarden waarop de vervolgkeuzelijst moet worden gebaseerd.
• Klik ten slotte op de knop „OK”.

3. De keuzelijst is nu aangemaakt in de cellen, zoals te zien is op de onderstaande schermafbeelding:

Klik voor meer gedetailleerde informatie over Keuzelijst…

4. Geavanceerde aangepaste regels voor gegevensvalidatie

In dit gedeelte leg ik uit hoe u geavanceerde aangepaste gegevensvalidatieregels kunt maken om diverse problemen op te lossen, zoals het toestaan van alleen getallen of tekst, het accepteren van uitsluitend unieke waarden, of het valideren van specifieke indelingen zoals telefoonnummers, e-mailadressen enzovoort.

4,1 Gegevensvalidatie die alleen getallen of teksten toestaat

Sta alleen getallen toe om in te voeren met de functie Gegevensvalidatie

Volg deze stappen om alleen getallen in een bereik cellen toe te staan:

1. Selecteer een celbereik waarin u uitsluitend getallen wilt toestaan.

2. Klik op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”. Voer in het dialoogvenster „Gegevensvalidatie” dat verschijnt, onder het tabblad „Instellingen”, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de volgende formule in het tekstvak „Formule” in. („A2” is de eerste cel van het geselecteerde bereik dat u wilt beperken.)

  =ISNUMBER(A2)

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

3. Vanaf nu kunt u alleen cijfers invoeren in de geselecteerde cellen.

Opmerking: De ISGETAL-functie accepteert alle numerieke waarden in gevalideerde cellen, inclusief gehele getallen, decimalen, breuken, datums en tijden.

Sta alleen tekstreeksen toe om in te voeren met de functie Gegevensvalidatie

Om invoer in cellen te beperken tot alleen tekst, gebruikt u de functie **Gegevensvalidatie** met een aangepaste formule op basis van de **ISTEKST**-functie. Volg deze stappen:

1. Selecteer een celbereik waarin u uitsluitend tekststrings wilt toestaan.

2. Klik op "Gegevens" > "Gegevensvalidatie" > „Gegevensvalidatie". Voer in het dialoogvenster „Gegevensvalidatie" dat verschijnt, onder het tabblad „Instellingen", de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de volgende formule in het tekstvak „Formule” in. („A2” is de eerste cel van het bereik dat u wilt beperken.)

  =ISTEXT(A2)

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

3. Bij het invoeren van gegevens in de specifieke cellen mag u uitsluitend gegevens in tekstformaat invoeren.

4,2 Gegevensvalidatie staat alleen alfanumerieke waarden toe

Voor bepaalde doeleinden wilt u mogelijk alleen letters en cijfers toestaan en speciale tekens zoals ~, %, $ of spaties beperken. In deze sectie vindt u enkele handige methoden.

Sta alleen alfanumerieke waarden toe met de functie Gegevensvalidatie

Volg deze stappen om speciale tekens te voorkomen en alleen alfanumerieke waarden toe te staan door een aangepaste formule te maken in de functie „Gegevensvalidatie”:

1. Selecteer een celbereik waarin u uitsluitend alfanumerieke waarden wilt toestaan.

2. Klik op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”. Voer in het dialoogvenster „Gegevensvalidatie” dat verschijnt, onder het tabblad „Instellingen”, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik dat u wilt beperken.

3. Alleen letters en cijfers zijn nu toegestaan bij invoer; speciale tekens worden tijdens het typen automatisch beperkt, zoals te zien is in de onderstaande schermafbeelding:

Alleen alfanumerieke waarden toestaan met een geweldige functie

De bovenstaande formule lijkt misschien ingewikkeld om te begrijpen en te onthouden. Daarom introduceer ik hier een handige functie van Kutools voor Excel: ‘Beperk invoer’ – een oplossing die deze taak aanzienlijk vereenvoudigt.

1. Selecteer een celbereik waarin u uitsluitend alfanumerieke waarden wilt toestaan.

2. Klik vervolgens op „Kutools” > „Beperk invoer” > „Beperk invoer”, zoals te zien is op de schermafbeelding:

3. Selecteer in het dialoogvenster „Beperk invoer” dat verschijnt de optie „Verbied het invoeren van speciale tekens”, zie schermafbeelding:

4. Klik daarna op de knop „OK” en bevestig in de volgende meldingsvensters achtereenvolgens met „Ja” > „OK” om de bewerking af te ronden. Vanaf nu zijn in de geselecteerde cellen uitsluitend letters en cijfers toegestaan, zie schermafbeelding:

4,3 Gegevensvalidatie staat alleen teksten toe die beginnen of eindigen met specifieke tekens

Als alle waarden in een bepaald bereik moeten beginnen of eindigen met een specifiek teken of substring, gebruik dan gegevensvalidatie met een aangepaste formule op basis van de functies EXACT, LINKS, RECHTS of AANTAL.ALS.

Sta teksten toe die beginnen of eindigen met specifieke tekens met slechts één voorwaarde

Als u bijvoorbeeld wilt dat tekstitems in specifieke cellen beginnen of eindigen met „CN”, volgt u deze stappen:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan die beginnen of eindigen met specifieke tekens.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de onderstaande formule in het tekstvak „Formule” in.
  Sta alleen tekst toe die begint met CN:

  =EXACT(LEFT(A2,2),"CN")

  Sta alleen tekst toe die eindigt met CN:

  =EXACT(RIGHT(A2,2),"CN")

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik, is „2” het aantal tekens dat u hebt opgegeven, en is „CN” de tekst waarmee de items moeten beginnen of eindigen.

3. Vanaf nu kunt u in de geselecteerde cellen alleen een tekststring invoeren die begint of eindigt met de opgegeven tekens; anders verschijnt een waarschuwingsmelding, zoals in de onderstaande schermafbeelding wordt weergegeven:

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

Opmerking: Het sterretje (*) is een jokerteken dat één of meer tekens vertegenwoordigt.

Sta teksten toe die beginnen of eindigen met specifieke tekens met meerdere criteria (OF-logica)

Als u bijvoorbeeld wilt dat tekstitems beginnen of eindigen met „CN” of „UK”, zoals in de onderstaande schermafbeelding wordt weergegeven, voegt u een extra exemplaar van EXACT toe met behulp van een plusteken (+). Volg hiervoor de volgende stappen:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan die voldoen aan meerdere criteria, zoals beginnen of eindigen met bepaalde tekens.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de onderstaande formule in het tekstvak „Formule” in.
  Sta alleen tekst toe die begint met CN of UK:

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  Sta alleen tekst toe die eindigt met CN of UK:

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik, is „2” het aantal opgegeven tekens, en zijn „CN” en „UK” de specifieke teksten waarmee de items moeten beginnen of eindigen.

3. Nu kunt u alleen een tekstreeks invoeren die begint of eindigt met de opgegeven tekens, in de geselecteerde cellen.

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Opmerking: Het sterretje * is een jokerteken dat één of meer tekens vertegenwoordigt.

4,4 Gegevensvalidatie: invoer moet wel/niet specifieke tekst bevatten

In deze sectie leg ik uit hoe u Gegevensvalidatie kunt gebruiken om waarden toe te staan die al dan niet één specifieke substring of één van meerdere substrings moeten bevatten in Excel.

Sta alleen invoer toe die één of één van meerdere specifieke teksten bevat

Invoer toestaan die één specifieke tekst moet bevatten

Om invoer toe te staan die een specifieke tekstreeks bevat – bijvoorbeeld dat alle ingevoerde waarden de tekst „KTE” moeten bevatten, zoals in de onderstaande schermafbeelding wordt weergegeven – past u gegevensvalidatie toe met een aangepaste formule op basis van de functies VIND.ALLES en ISGETAL. Volg hiervoor deze stappen:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan die bepaalde tekst bevatten.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens één van de onderstaande formules in het tekstvak „Formule” in.
  Hoofdlettergevoelig:

  =ISNUMBER(FIND("KTE",A2)) 

  Niet-Hoofdlettergevoelig:

  =ISNUMBER(SEARCH("KTE",A2))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik en „KTE” de tekststring die de invoer moet bevatten.

3. Als de ingevoerde waarde de vereiste tekst niet bevat, verschijnt er nu een waarschuwingsvenster.

Invoer toestaan die één van meerdere specifieke teksten moet bevatten

De bovenstaande formule werkt alleen voor één tekststring. Als u wilt toestaan dat een van meerdere tekststrings in de cellen mag worden ingevoerd – zoals te zien is in de volgende schermafbeelding – combineert u de functies SOMPRODUCT, VIND.ALLES en ISGETAL om een formule te maken.

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan die één of meer items bevatten.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens, afhankelijk van uw behoefte, één van de onderstaande formules in het tekstvak „Formule” in.
  Hoofdlettergevoelig:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  Niet-Hoofdlettergevoelig:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• Klik daarna op „OK” om het dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik en „C2:C4” de lijst met waarden waarvan de invoer er één moet bevatten.

3. Nu kunt u alleen invoerwaarden opgeven die overeenkomen met één van de waarden uit de opgegeven lijst.

Sta geen invoer toe die één of één van meerdere specifieke teksten bevat

Invoer toestaan die één specifieke tekst niet mag bevatten

Om te valideren dat invoer een specifieke tekst niet mag bevatten – bijvoorbeeld om waarden toe te staan die de tekst „KTE” niet bevatten in een cel – kunt u de functies ISFOUT en VIND.ALLES combineren in een gegevensvalidatieregel. Volg hiervoor deze stappen:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan die bepaalde tekst niet bevatten.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens één van de onderstaande formules in het tekstvak „Formule” in.
  Hoofdlettergevoelig:

  =ISERROR(FIND("KTE",A2))

  Niet-Hoofdlettergevoelig:

  =ISERROR(SEARCH("KTE",A2))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik en „KTE” de tekststring die niet in de invoer mag voorkomen.

3. Invoer die de specifieke tekst bevat, wordt nu voorkomen.

Invoer toestaan die geen van meerdere specifieke teksten mag bevatten

Volg deze stappen om te voorkomen dat een van meerdere tekststrings uit een lijst wordt ingevoerd, zoals in de onderstaande schermafbeelding wordt weergegeven:

1. Selecteer een celbereik waarin u specifieke teksten wilt blokkeren.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de onderstaande formule in het tekstvak „Formule” in.
  Hoofdlettergevoelig:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  Niet-Hoofdlettergevoelig:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• Klik daarna op „OK” om het dialoogvenster te sluiten.

Opmerking: In de bovenstaande formules is „A2” de eerste cel van het geselecteerde bereik en „C2:C4” de lijst met waarden die u wilt blokkeren wanneer de invoer één van deze waarden bevat.

3. Vanaf nu wordt invoer die één van de opgegeven teksten bevat, geblokkeerd.

4,5 Gegevensvalidatie staat alleen unieke waarden toe

Als u dubbele invoer wilt voorkomen binnen een bereik van cellen, vindt u in deze sectie enkele snelle methoden om deze taak eenvoudig in Excel uit te voeren.

Sta alleen unieke waarden toe met behulp van de functie Gegevensvalidatie

Normaal gesproken helpt de functie Gegevensvalidatie met een aangepaste formule op basis van de AANTAL.ALS-functie u hierbij. Volg hiervoor de onderstaande stappen:

1. Selecteer de cellen of kolom waarin u alleen unieke waarden wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

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

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is „A2:A9” het celbereik waarin u alleen unieke waarden wilt toestaan, en is „A2” de eerste cel van het geselecteerde bereik.

3. Nu kunnen alleen unieke waarden worden ingevoerd; bij het invoeren van dubbele gegevens verschijnt een waarschuwingsbericht, zoals in de onderstaande schermafbeelding wordt weergegeven:

Sta alleen unieke waarden toe met behulp van VBA-code

De volgende VBA-code helpt u ook om dubbele invoer te voorkomen. Volg hiervoor deze stappen:

1. Klik met de rechtermuisknop op het werkbladtabblad waarop u alleen unieke waarden wilt toestaan en kies ‘Code weergeven’ in het contextmenu. Plak in het venster ‘Microsoft Visual Basic for Applications’ de volgende code in de lege module:

VBA-code: Alleen unieke waarden toestaan in een bereik van cellen:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Opmerking: In de bovenstaande code verwijzen „A1:A100” en „A:A” naar de cellen in de kolom waarvoor u Dubbele Invoer wilt voorkomen; pas deze indien nodig aan.

2. Voer vervolgens deze code in, sla op en sluit af. Zodra u nu een dubbele waarde invoert in cellen A1:A100, verschijnt er een waarschuwingsvenster, zoals te zien is in de onderstaande schermafbeelding:

Sta alleen unieke waarden toe met behulp van een handige functie

Als u Kutools voor Excel gebruikt, kunt u met de functie ‘Voorkom dubbele invoer’ in slechts enkele klikken gegevensvalidatie instellen om dubbele invoer in een celbereik te voorkomen.

1. Selecteer het celbereik waarin u alleen unieke gegevens wilt toestaan en dubbele waarden wilt voorkomen.

2. Klik vervolgens op **Kutools** > **Beperk invoer** > **Voorkom dubbele invoer** (zie schermafbeelding):

3. Er verschijnt een waarschuwingsbericht dat aangeeft dat gegevensvalidatie wordt verwijderd wanneer u deze functie toepast. Klik op **Ja** en vervolgens in het daaropvolgende venster op **OK**, zoals te zien is in de onderstaande schermafbeeldingen:

4. Wanneer u nu dubbele gegevens invoert in de opgegeven cellen, verschijnt er een meldingsvenster dat u eraan herinnert dat dubbele waarden niet zijn toegestaan – zie schermafbeelding:

4,6 Gegevensvalidatie staat alleen hoofdletters / kleine letters / Eerste letter in hoofdletters toe

De functie Gegevensvalidatie is een krachtig hulpmiddel waarmee gebruikers invoer in hoofdletters, kleine letters of met een hoofdletter aan het begin kunnen afdwingen in een bereik van cellen. Volg de volgende stappen:

1. Selecteer het celbereik waarin u alleen tekst in hoofdletters, kleine letters of met een hoofdletter aan het begin wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens, afhankelijk van uw behoefte, één van de onderstaande formules in het tekstvak „Formule” in.
  sta alleen Hoofdletters toe:

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

  Sta alleen Kleine letters toe

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  Sta alleen Eerste letter in hoofdletters-tekst toe

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Nu worden alleen invoerwaarden geaccepteerd die voldoen aan de regel die u hebt ingesteld.

4,7 Gegevensvalidatie staat alleen waarden toe die wel/niet voorkomen in een andere lijst

Het toestaan of blokkeren van waarden op basis van hun aanwezigheid in een andere lijst kan voor veel gebruikers een uitdaging zijn. Gelukkig lost u dit eenvoudig op met de functie Gegevensvalidatie en een simpele formule met AANTAL.ALS.

Stel dat u alleen de waarden uit het bereik C2:C4 wilt toestaan in een celbereik, zoals in de onderstaande schermafbeelding. Volg deze stappen om dit in te stellen:

1. Selecteer het celbereik waarop u gegevensvalidatie wilt toepassen.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens, afhankelijk van uw behoefte, één van de onderstaande formules in het tekstvak „Formule” in.
  Sta alleen waarden toe die bestaan in een andere kolom

  =COUNTIF($C$2:$C$4,A2)>0

  Voorkom waarden die bestaan in een andere kolom

  =COUNTIF($C$2:$C$4,A2)=0

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt valideren, en "C2:C4" de lijst met waarden die u als invoer wilt toestaan of blokkeren.

3. Nu kunnen alleen invoerwaarden die voldoen aan de regel die u hebt ingesteld, worden ingevoerd; alle andere worden geblokkeerd.

4,8 Gegevensvalidatie dwingt alleen invoer in Telefoonnummer-indeling af

Wanneer u informatie over uw bedrijfsmedewerkers invoert, moet in één kolom het telefoonnummer worden ingevoerd. Om ervoor te zorgen dat het telefoonnummer snel en foutloos wordt ingevoerd, kunt u gegevensvalidatie toepassen op die kolom. Stel dat u alleen telefoonnummers in de indeling (123) 456-7890 wilt toestaan in een werkblad. In dit gedeelte vindt u twee snelle trucs om deze taak eenvoudig uit te voeren.

Alleen invoer in Telefoonnummer-indeling afdwingen met de functie Gegevensvalidatie

Volg deze stappen om alleen een specifieke Telefoonnummer-indeling toe te staan:

1. Selecteer de celbereik waarin u een specifieke telefoonnummerindeling wilt toestaan, klik met de rechtermuisknop en kies ‘Celopmaak instellen’ in het contextmenu (zie schermafbeelding):

2. Selecteer in het dialoogvenster **Celopmaak instellen**, onder het tabblad **Getal**, de optie **Aangepast** in het linkerdeelvenster **Categorie** en typ de gewenste telefoonnummerindeling in het vak **Type**. Bijvoorbeeld: ik gebruik de indeling **„(###) ###-####"** – zie schermafbeelding:

3. Klik daarna op 'OK' om het dialoogvenster te sluiten.

4. Nadat u de cellen hebt opgemaakt, selecteert u ze opnieuw en opent u het dialoogvenster **Gegevensvalidatie** via **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het pop-upvenster, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de volgende formule in het tekstvak 'Formule' in.

  =AND(ISNUMBER(A2),LEN(A2)=10)

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom waarvan u het telefoonnummer wilt valideren.

5. Wanneer u nu een 10-cijferig nummer invoert, wordt dit automatisch omgezet naar de gewenste telefoonnummerindeling – zie de schermafbeeldingen:

Opmerking: Als het ingevoerde nummer geen 10 cijfers bevat, verschijnt een waarschuwingsvenster, zie schermafbeelding:

Alleen invoer in Telefoonnummer-indeling afdwingen met een handige functie

De functie 'Kutools voor Excel' – 'Alleen telefoonnummers kunnen worden ingevoerd' stelt u in staat om met slechts een paar klikken uitsluitend invoer in telefoonnummerindeling toe te staan.

1. Selecteer de celbereik waarin uitsluitend een specifiek telefoonnummer mag worden ingevoerd en klik daarna op **Kutools** > **Beperk invoer** > **Alleen telefoonnummers kunnen worden ingevoerd** (zie schermafbeelding):

2. Selecteer in het dialoogvenster 'Telefoonnummer' de gewenste telefoonnummerindeling of maak uw eigen opmaak aan door op de knop 'Toevoegen' te klikken (zie schermafbeelding).

3. Nadat u de opmaak voor telefoonnummers hebt geselecteerd of ingesteld, klikt u op ‘OK’. Vervolgens kunt u alleen telefoonnummers invoeren die voldoen aan deze specifieke opmaak; anders verschijnt een waarschuwingsvenster, zoals te zien is op de schermafbeelding:

4,9 Gegevensvalidatie dwingt alleen invoer van E-mailadres af

Stel dat u meerdere e-mailadressen moet typen in een kolom van een werkblad. Om onjuiste e-mailadresopmaak te voorkomen, kunt u normaal gesproken een gegevensvalidatieregel instellen die alleen geldige e-mailadressen toestaat.

Alleen invoer in E-mailadres-indeling afdwingen met de functie Gegevensvalidatie

Met de functie Gegevensvalidatie en een aangepaste formule stelt u in een handomdraai een regel in om ongeldige e-mailadressen te blokkeren. Volg deze stappen:

1. Selecteer de cellen waarin u alleen e-mailadressen wilt toestaan en klik op 'Gegevens' > 'Gegevensvalidatie' > 'Gegevensvalidatie'.

2. Voer in het geopende dialoogvenster 'Gegevensvalidatie', onder het tabblad 'Instellingen', de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer vervolgens de volgende formule in het tekstvak Formule in:

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Als de ingevoerde tekst niet voldoet aan de e-mailadresindeling, verschijnt er nu een waarschuwingsvenster – zie schermafbeelding:

Alleen invoer in E-mailadres-indeling afdwingen met een handige functie

Kutools voor Excel biedt een geweldige functie: ‘Alleen e-mailadressen toegestaan’. Met deze tool blokkeert u ongeldige e-mailadressen met één klik.

1. Selecteer de cellen waarin u alleen e-mailadressen wilt toestaan en klik op **Kutools** > **Beperk invoer** > **Alleen e-mailadressen kunnen worden ingevoerd**. Zie schermafbeelding:

2. Vervolgens is alleen invoer in e-mailadresopmaak toegestaan; anders verschijnt een waarschuwingsvenster, zie schermafbeelding:

4,10 Gegevensvalidatie dwingt alleen invoer van IP-adressen af

In dit gedeelte deel ik een paar snelle trucs om gegevensvalidatie in te stellen, zodat alleen IP-adressen worden geaccepteerd in een bereik van cellen.

Forceer alleen IP-adresindeling met de functie Gegevensvalidatie

Volg deze stappen om alleen IP-adressen toe te staan in een specifiek celbereik:

1. Selecteer de cellen waarin u uitsluitend IP-adressen wilt toestaan en klik op 'Gegevens' > 'Gegevensvalidatie' > 'Gegevensvalidatie'.

2. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Als er nu een ongeldig IP-adres in de cel wordt ingevoerd, verschijnt er een waarschuwingsvenster, zoals te zien is in de onderstaande schermafbeelding:

Forceer alleen IP-adresindeling met VBA-code

De volgende VBA-code zorgt ervoor dat u alleen IP-adressen kunt invoeren en alle andere invoer automatisch wordt geblokkeerd. Volg deze stappen:

1. Klik met de rechtermuisknop op het bladtabblad en kies 'Code weergeven' in het contextmenu. Kopieer de onderstaande VBA-code naar het venster Microsoft Visual Basic for Applications dat vervolgens wordt geopend.

VBA-code: valideer cellen om alleen IP-adressen te accepteren

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Opmerking: In de bovenstaande code is 'A2:A10' het celbereik waarin u uitsluitend IP-adressen wilt toestaan.

2. Sla de code op en sluit deze vervolgens af. Vanaf nu kunnen in de opgegeven cellen alleen geldige IP-adressen worden ingevoerd.

Alleen invoer in IP-adresindeling afdwingen met een eenvoudige functie

Als u Kutools voor Excel in uw werkmap hebt geïnstalleerd, helpt de functie ‘Alleen IP-adressen toegestaan’ u ook bij deze taak.

1. Selecteer de cellen waarin u uitsluitend IP-adressen wilt toestaan en klik op **Kutools** > **Beperk invoer** > **Alleen IP-adressen kunnen worden ingevoerd**. Zie schermafbeelding:

2. Na het toepassen van deze functie is alleen nog invoer van IP-adressen toegestaan; bij elke andere invoer verschijnt een waarschuwingsvenster, zie schermafbeelding:

4,11 Gegevensvalidatie beperkt waarden die de totale waarde overschrijden

Stel dat u een maandelijkse uitgavenrapportage heeft met een totaalbudget van $18.000. Zorg ervoor dat het totaalbedrag in de uitgavenlijst dit vooraf ingestelde budget niet overschrijdt, zoals te zien is in de onderstaande schermafbeelding. In dit geval kunt u een gegevensvalidatieregel instellen met behulp van de SOM-functie om te voorkomen dat de som van de waarden het vooraf ingestelde totaal overschrijdt.

1. Selecteer de celbereik waarin u de waarden wilt beperken.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

  =SUM($B$2:$B$7)<=18000

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is „B2:B7” het celbereik waarvoor u de invoer wilt beperken.

3. Wanneer u nu waarden invoert in het bereik B2:B7, wordt de validatie goedgekeurd zolang het totaal van de waarden onder de $18.000 blijft. Overschrijdt een ingevoerde waarde dit bedrag, dan verschijnt er een waarschuwingsvenster om u daarop te wijzen.

4,12 Gegevensvalidatie beperkt celinvoer op basis van een andere cel

Wanneer u gegevensinvoer in een reeks cellen wilt beperken op basis van de waarde in een andere cel, lost de functie Gegevensvalidatie dit probleem eenvoudig op. Bijvoorbeeld: als cel C1 de tekst „Yes” bevat, kunt u willekeurige waarden invoeren in het bereik A2:A9. Bevat cel C1 echter een andere tekst, dan wordt de invoer in datzelfde bereik beperkt — zoals te zien is in de onderstaande schermafbeeldingen:

Voer de volgende stappen uit om dit op te lossen:

1. Selecteer de celbereik waarin u de waarden wilt beperken.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

  =$C$1="Yes"

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is „C1” de cel met de specifieke tekst die u wilt gebruiken, en „Yes” de tekst waarop u de celbeperking baseert. Pas deze waarden aan uw eigen behoeften aan.

3. Als cel C1 de tekst „Yes” bevat, kunt u alles invoeren in het bereik A2:A9. Bevat cel C1 een andere tekst, dan kunt u geen Y-waarde invoeren; zie de onderstaande demo:

4,13 Gegevensvalidatie staat alleen weekdagen of weekenddagen toe

Als u alleen weekdagen (maandag t/m vrijdag) of weekenddagen (zaterdag en zondag) in een reeks cellen wilt toestaan, helpt Gegevensvalidatie u daarbij. Volg hiervoor deze stappen:

1. Selecteer de celbereik waarin u weekdagen of weekenddagen wilt invoeren.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer „Aangepast” in de Keuzelijst „Toestaan”.
• Voer vervolgens, afhankelijk van uw behoefte, één van de onderstaande formules in het tekstvak „Formule” in.
  Sta alleen weekdagen toe

  =WEEKDAY(A2,2)<6

  Sta alleen weekenddagen toe

  =WEEKDAY(A2,2)>5

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Vanaf nu kunt u in de opgegeven cellen uitsluitend weekdagen of weekenddata invoeren, afhankelijk van uw keuze.

4,14 Gegevensvalidatie staat alleen invoer van data toe op basis van de huidige datum

Soms wilt u mogelijk alleen datums invoeren die later of eerder zijn dan vandaag in een reeks cellen. De functie **Gegevensvalidatie** in combinatie met de functie **VANDAAG** helpt u daarbij. Volg hiervoor deze stappen:

1. Selecteer de celbereik waarin u alleen toekomstige datums (datums later dan vandaag) wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de onderstaande formule in het tekstvak 'Formule' in.

  =A2>Today()

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Vanaf nu kunt u in de cellen alleen datums invoeren die later zijn dan vandaag. In alle andere gevallen verschijnt een waarschuwingsvenster om u hierop te wijzen; zie de schermafbeelding:

=A2<Today()

=AND(A2>TODAY(),A2<=(TODAY()+30))

4,15 Gegevensvalidatie staat alleen invoer van tijden toe op basis van de huidige tijd

Als u gegevens wilt valideren op basis van de huidige tijd — bijvoorbeeld om alleen tijden vóór of ná het huidige moment in cellen toe te staan — kunt u een aangepaste gegevensvalidatieformule maken. Volg hiervoor deze stappen:

1. Selecteer de celbereik waarin u alleen tijden vóór of ná de huidige tijd wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer ‘Tijd’ in de keuzelijst ‘Toestaan’.
• Kies vervolgens in de keuzelijst „Gegevens” „kleiner dan” om alleen tijden vóór het huidige tijdstip toe te staan, of „groter dan” om tijden ná het huidige tijdstip toe te staan, afhankelijk van uw behoefte.
• Voer daarna in het vak „Eindtijd” of „Begintijd” de volgende formule in:

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is "A2" de eerste cel van de kolom die u wilt gebruiken.

3. Vanaf nu kunt u in de betreffende cellen alleen tijden invoeren die vóór of ná de huidige tijd liggen.

4,16 Gegevensvalidatie voor datums in een specifiek of huidig jaar

Om alleen datums uit een bepaald jaar – of het huidige jaar – toe te staan, gebruikt u gegevensvalidatie met een aangepaste formule op basis van de JAAR-functie.

1. Selecteer de celbereik waarin u alleen datums uit een bepaald jaar wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Aangepast' in de keuzelijst 'Toestaan'.
• Voer daarna de volgende formule in het tekstvak 'Formule' in.

  =YEAR(A2)=2020

• Klik op de knop „OK” om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule is „A2” de eerste cel van de kolom die u wilt gebruiken, en „2020” het jaartal waarnaar u de invoer wilt beperken.

3. Vervolgens kunt u alleen datums uit het jaar 2020 invoeren; anders verschijnt een waarschuwingsvenster zoals hieronder wordt weergegeven:

=YEAR(A2)=YEAR(TODAY())

4,17 Gegevensvalidatie voor datums in de huidige week of maand

Als u wilt dat gebruikers alleen datums uit de huidige week of maand invoeren in specifieke cellen, laat dit gedeelte u enkele formules zien om die taak in Excel eenvoudig uit te voeren.

Sta invoer toe van een datum in de huidige week

1. Selecteer de celbereik waarin u alleen datums uit de huidige week wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Datum' in de keuzelijst 'Toestaan'.
• Kies vervolgens ‘tussen’ in de keuzelijst ‘Gegevens’.
• Voer in het tekstvak „Startdatum” deze formule in:

  =TODAY()-WEEKDAY(TODAY(),3)

• Voer in het tekstvak „Einddatum” deze formule in:

  =TODAY()-WEEKDAY(TODAY(),3)+6

• Klik ten slotte op de knop „OK”.

3. Vervolgens kunt u alleen datums binnen de huidige week invoeren; alle andere datums zijn geblokkeerd, zoals in de onderstaande schermafbeelding wordt weergegeven:

Sta invoer toe van een datum in de huidige maand

Volg de onderstaande stappen om alleen datums uit de huidige maand toe te staan:

1. Selecteer de celbereik waarin u alleen datums uit de huidige maand wilt toestaan.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. Voer in het dialoogvenster **Gegevensvalidatie** dat verschijnt, onder het tabblad **Instellingen**, de volgende stappen uit:

• Selecteer 'Datum' in de keuzelijst 'Toestaan'.
• Kies vervolgens „tussen” in de keuzelijst „Gegevens”.
• Voer in het tekstvak „Startdatum” deze formule in:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• Voer in het tekstvak „Einddatum” deze formule in:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• Klik ten slotte op de knop „OK”.

3. Vanaf nu kunt u in de geselecteerde cellen alleen datums invoeren die binnen de huidige maand vallen.

5. Hoe bewerkt u gegevensvalidatie in Excel?

Volg de onderstaande stappen om een bestaande gegevensvalidatieregel te bewerken of te wijzigen:

1. Selecteer een cel met de gegevensvalidatieregel.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie** om het dialoogvenster **Gegevensvalidatie** te openen. Pas de regels in het venster naar wens aan en schakel de optie **Deze wijzigingen toepassen op alle andere cellen met dezelfde instellingen** in om de nieuwe regel toe te passen op alle cellen die voldoen aan de oorspronkelijke validatiecriteria. Zie de schermafbeelding:

3. Klik op „OK” om uw wijzigingen op te slaan.

6. Hoe zoekt en selecteert u in Excel cellen met gegevensvalidatie?

Als u meerdere gegevensvalidatieregels in uw werkblad hebt ingesteld en nu snel de cellen wilt vinden en selecteren waarop deze regels van toepassing zijn, helpt de functie „Speciaal gaan naar” u om alle cellen met gegevensvalidatie te selecteren of een specifiek type gegevensvalidatie te kiezen.

1. Activeer het werkblad waarin u cellen met gegevensvalidatie wilt zoeken en selecteren.

2. Klik vervolgens op 'Start' > 'Zoeken en selecteren' > 'Speciaal gaan naar' – zie de schermafbeelding:

3. Selecteer in het dialoogvenster „Speciaal gaan naar” de optie „Gegevensvalidatie” > „Alles”; zie de schermafbeelding:

4. Alle cellen met gegevensvalidatie zijn nu geselecteerd in het huidige werkblad.

Tip: als u een specifiek type gegevensvalidatie wilt selecteren, kiest u eerst een cel met de gewenste gegevensvalidatie, opent u het dialoogvenster „Ga naar speciaal” en selecteert u „Gegevensvalidatie” > „Hetzelfde”.

7. Hoe kopieert u een gegevensvalidatieregel naar andere cellen?

Stel dat u een gegevensvalidatieregel hebt gemaakt voor een reeks cellen en nu dezelfde regel op andere cellen wilt toepassen. In plaats van de regel opnieuw te maken, kunt u de bestaande regel eenvoudig en snel naar andere cellen kopiëren en plakken.

1. Klik op een cel met de validatieregel die u wilt overnemen en druk op Ctrl + C om deze te kopiëren.

2. Selecteer daarna de cellen die u wilt valideren. Houd de Ctrl-toets ingedrukt terwijl u klikt om meerdere niet-aangrenzende cellen te selecteren.

3. Klik met de rechtermuisknop op de selectie en kies „Plakken speciaal“ – zie de schermafbeelding:

4. Selecteer in het dialoogvenster „Plakken speciaal” de optie „Validatie”; zie de schermafbeelding:

5. Klik op de knop „OK” – de validatieregel is nu gekopieerd naar de nieuwe cellen.

8. Hoe gebruikt u gegevensvalidatie om ongeldige invoer in Excel duidelijk te markeren?

Soms moet u gegevensvalidatieregels toepassen op bestaande gegevens, waardoor er mogelijk ongeldige waarden in het celbereik voorkomen. Hoe controleert en corrigeert u die ongeldige gegevens? In Excel gebruikt u de functie „Ongeldige gegevens markeren” om dergelijke waarden duidelijk te markeren met een rode cirkel.

Om de ongeldige gegevens die u wilt markeren daadwerkelijk te markeren, moet u eerst de functie **Gegevensvalidatie** gebruiken om een regel in te stellen voor het gegevensbereik. Volg hiervoor deze stappen:

1. Selecteer het gegevensbereik waarvoor u ongeldige gegevens wilt markeren.

2. Klik vervolgens op „Gegevens” > „Gegevensvalidatie” > „Gegevensvalidatie”. Stel in het dialoogvenster „Gegevensvalidatie” de validatieregel naar wens in. In dit voorbeeld valideer ik waarden groter dan 500 – zie de schermafbeelding:

3. Klik op „OK” om het dialoogvenster te sluiten. Nadat u de gegevensvalidatieregel hebt ingesteld, klikt u op „Gegevens” > „Gegevensvalidatie” > „Ongeldige gegevens markeren”. Alle ongeldige waarden kleiner dan 500 worden nu gemarkeerd met een rode ovaal. Zie de schermafbeeldingen:

Opmerkingen:

• 1. Zodra u de ongeldige gegevens corrigeert, verdwijnt de rode cirkel automatisch.
• 2. De functie „Ongeldige gegevens markeren” kan maximaal 255 cellen markeren. Wanneer u het huidige werkboek opslaat, verdwijnen alle rode cirkels.
• 3. Deze cirkels worden niet afgedrukt.
• 4. U kunt de rode cirkels ook verwijderen door te klikken op 'Gegevens' > 'Gegevensvalidatie' > 'Validatiecirkels wissen'.

9. Hoe verwijdert u gegevensvalidatie in Excel?

Verwijder gegevensvalidatieregels uit een celbereik, het huidige werkblad of de gehele werkmap met de volgende methoden.

Verwijder gegevensvalidatie Geselecteerd bereik met de gegevensvalidatiefunctie

1. Selecteer de cellen met gegevensvalidatie die u wilt verwijderen.

2. Klik vervolgens op **Gegevens** > **Gegevensvalidatie** > **Gegevensvalidatie**. In het verschijnende dialoogvenster klikt u onder het tabblad **Instellingen** op de knop **Alles wissen** (zie screenshot).

3. Klik daarna op de knop „OK" om dit dialoogvenster te sluiten. De gegevensvalidatieregel die op het geselecteerde bereik was toegepast, is nu in één keer verwijderd.

Tip: Selecteer eerst het volledige werkblad en voer daarna bovenstaande stappen uit om de gegevensvalidatie uit het huidige werkblad te verwijderen.

Verwijder gegevensvalidatie Geselecteerd bereik met een handige functie

Als u Kutools voor Excel gebruikt, helpt de functie ‘Verwijder gegevensvalidatiebeperkingen’ u om gegevensvalidatieregels te verwijderen uit het geselecteerde bereik of het volledige werkblad.

1. Selecteer het celbereik of het volledige werkblad met de gegevensvalidatie die u wilt verwijderen.

2. Klik vervolgens op **Kutools** > **Beperk invoer** > **Verwijder gegevensvalidatiebeperkingen** (zie screenshot):

3. Klik in het verschijnende meldingsvenster op 'OK' en uw gegevensvalidatieregel wordt precies zoals gewenst verwijderd.

Verwijder gegevensvalidatie van alle werkbladen met VBA-code

Als u gegevensvalidatieregels uit de volledige werkmap wilt verwijderen, zijn de bovenstaande methoden tijdrovend bij een groot aantal werkbladen. Met de onderstaande code voert u deze taak snel uit.

1. Druk op „ALT + F11" om het venster 'Microsoft Visual Basic for Applications' te openen.

2. Klik vervolgens op 'Invoegen' > 'Module' en plak de volgende macro in het venster 'Module'.

VBA-code: Verwijder gegevensvalidatieregels in alle werkbladen:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Druk daarna op de F5-toets om de code uit te voeren – alle gegevensvalidatieregels worden dan onmiddellijk uit de volledige werkmap verwijderd.

Beste Office-productiviteitshulpmiddelen

🤖	KUTOOLS AI Assistant: Revolutioneer Data-analyse op basis van:Intelligente uitvoering   |  Genereer code|  Maak aangepaste formules  |  Analyseer gegevens en genereer grafieken|  Roep Verbeterde functies aan…
	Populaire functies:Zoeken, markeren of Dubbele waarden markeren   |  Verwijder lege rijen   |  Kolommen samenvoegen of cellen zonder gegevensverlies   |   Afronden zonder formule...
	Super ZOEKEN:VLookup met meerdere criteria  |  VLookup met meerdere waarden  |   VLookup over meerdere werkbladen   |   Fuzzy Match....
	Geavanceerde keuzelijst:Snel een keuzelijst maken   |  Afhankelijke keuzelijst   |  Keuzelijst met meervoudige selectie....
	Kolombeheerder:Voeg een specifiek aantal kolommen toe|Verplaats kolommen|Wissel zichtbaarheidsstatus van verborgen kolommen|Vergelijk bereiken en kolommen...
	Uitgelichte functies:Rasterfocus   |  Ontwerpweergave   |Verbeterde formulebalk   | Werkmap- en bladbeheerder   |  Bronnenbibliotheek(Automatische tekst)|  Datumkiezer   |  Werkbladen samenvoegen  |  Versleutelen/Cellen decoderen   | E-mails verzenden op basis van lijst   |  Superfilter   |   Speciaal filter(Filter cellen met vetgedrukt lettertype/cursief/doorgestreept...) ...
	Top 15 gereedschapssets:12 Teksthulpmiddelen(Tekst toevoegen,Specifieke tekens verwijderen, ...)|   50+Grafiektypen(Gantt-diagram, ...)|   40+ Praktische formules(Leeftijd berekenen op basis van geboortedatum, ...)|   19 Invoeghulpmiddelen(QR-code Invoegen,Afbeelding invoegen vanaf pad, ...)|   12 Conversiehulpmiddelen(Omzetten naar woorden,Wisselkoersconversie, ...)|   7 Samenvoegen en splitsenhulpmiddelen(Geavanceerd samenvoegen van rijen,Cellen splitsen, ...)|... en meer

Gebruik Kutools in uw voorkeurstaal – ondersteunt Engels, Spaans, Duits, Frans, Chinees en 40+ andere talen!

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...

Meer details over Kutools voor Excel...Gratis downloaden...

---

Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger

• Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
• Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!

Meer details over Office Tab...Gratis downloaden...

---

Alle Kutools-add-ins in één installatieprogramma.

Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.

• Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
• Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
• Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
• 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
• Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins

Meer details over Kutools for Office...Gratis downloaden...