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

In Excel is de functie Gegevensvalidatie een krachtig hulpmiddel waarmee je kunt bepalen wat een gebruiker in een cel mag invoeren. Je kunt bijvoorbeeld regels instellen om de lengte van tekst te beperken, invoer tot specifieke formaten te beperken, alleen unieke waarden toe te staan of ervoor te zorgen dat tekst begint of eindigt met bepaalde tekens. Deze mogelijkheden helpen de gegevensintegriteit te waarborgen en fouten in je werkbladen te verminderen.

Deze handleiding laat zien hoe je gegevensvalidatie toevoegt, gebruikt en verwijdert in Excel. Zowel basis- als geavanceerde handelingen komen aan bod, met gedetailleerde stapsgewijze instructies zodat je deze functie effectief kunt toepassen op je werkzaamheden.

1. Wat is gegevensvalidatie in Excel?

Met de functie "Gegevensvalidatie" kun je de invoer in je werkblad beperken. Je kunt bijvoorbeeld validatieregels instellen om te voorkomen dat bepaalde gegevens worden ingevoerd of juist alleen bepaalde soorten gegevens toestaan in een lijst met geselecteerde cellen.

Enkele basisgebruiksmogelijkheden van de functie Gegevensvalidatie:

1. "Elke waarde": er wordt geen validatie uitgevoerd, je kunt alles invoeren in de opgegeven cellen. 2. "Gehele waarde": alleen gehele getallen zijn toegestaan. 3. "Decimaal": zowel gehele getallen als decimalen mogen worden ingevoerd. 4. "Lijst": alleen waarden uit de vooraf gedefinieerde lijst mogen worden ingevoerd of geselecteerd. De waarden worden weergegeven in een keuzelijst. 5. "Datum": alleen datums zijn toegestaan. 6. "Tijd": alleen tijden zijn toegestaan. 7. "Tekstlengte": alleen een opgegeven lengte van de tekst mag worden ingevoerd. 8. "Aangepast": maak aangepaste formules voor het valideren van gebruikersinvoer.

2. Hoe voeg je gegevensvalidatie toe in Excel?

In een Excel-werkblad kun je gegevensvalidatie toevoegen met de volgende stappen:

1. Selecteer een lijst met cellen waarvoor je de gegevensvalidatie wilt instellen en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie", zie screenshot:

2. In het dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" je eigen validatieregels maken. In de criteriavelden kun je een van de volgende typen opgeven:

• "Waarden": Typ direct getallen in de criteriavelden;
• "Celverwijzing": Verwijs naar een cel in het werkblad of een ander werkblad;
• "Formules": Maak complexere formules als voorwaarden.

Als voorbeeld maak ik een regel die alleen gehele getallen tussen100 en1000 toestaat. Stel de criteria in zoals hieronder weergegeven:

3. Nadat je de voorwaarden hebt ingesteld, kun je naar het tabblad "Invoerbericht" of "Foutmelding" gaan om een invoerbericht of foutmelding voor de validatiecellen in te stellen. (Als je geen melding wilt instellen, klik dan direct op "OK" om af te ronden.)

3.1) Invoerbericht toevoegen (optioneel):

Je kunt een bericht maken dat verschijnt wanneer je een cel met gegevensvalidatie selecteert. Dit bericht helpt de gebruiker eraan te herinneren wat er in de cel mag worden ingevoerd.

Ga naar het tabblad "Invoerbericht" en doe het volgende:

Vink de optie "Toon invoerbericht wanneer cel is geselecteerd" aan; Voer de titel en het herinneringsbericht in die je wilt tonen in de bijbehorende velden; Klik op "OK" om dit dialoogvenster te sluiten.

Nu verschijnt er een berichtvenster wanneer je een gevalideerde cel selecteert, zoals hieronder weergegeven:

3.2) Betekenisvolle foutmeldingen maken (optioneel):

Naast het maken van een invoerbericht kun je ook foutmeldingen tonen wanneer ongeldige gegevens in een gevalideerde cel worden ingevoerd.

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

Vink de optie "Toon foutmelding nadat ongeldige gegevens zijn ingevoerd" aan; Selecteer in de vervolgkeuzelijst "Stijl" het gewenste type melding: "Stop (standaard)": Dit type melding voorkomt dat gebruikers ongeldige gegevens invoeren. "Waarschuwing": Waarschuwt gebruikers dat de gegevens ongeldig zijn, maar voorkomt niet dat ze worden ingevoerd. "Informatie": Informeert gebruikers alleen over een ongeldige invoer. Voer de titel en het waarschuwingsbericht in die je wilt tonen in de bijbehorende velden; Klik op "OK" om het dialoogvenster te sluiten.

Wanneer een ongeldige waarde wordt ingevoerd, verschijnt er een waarschuwingsvenster zoals hieronder weergegeven:

Optie "Stop": Klik op "Opnieuw" om een waarde opnieuw in te voeren of op "Annuleren" om de invoer te negeren.

Optie "Waarschuwing": Klik op "Ja" om de ongeldige invoer te accepteren, "Nee" om deze te wijzigen of "Annuleren" om de invoer te negeren.

Optie "Informatie": Klik op "OK" om de ongeldige invoer te accepteren of op "Annuleren" om deze te negeren.

Opmerking: Als je geen eigen bericht instelt in het vak "Foutmelding", wordt standaard een "Stop"-melding weergegeven, zoals hieronder:

3. Basisvoorbeelden van gegevensvalidatie

Bij het gebruik van deze functie Gegevensvalidatie zijn er8 ingebouwde opties beschikbaar om de validatie in te stellen. Denk aan: elke waarde, gehele getallen en decimalen, datum en tijd, lijst, tekstlengte en aangepaste formule. In dit gedeelte bespreken we hoe je enkele van deze ingebouwde opties in Excel gebruikt.

3.1 Gegevensvalidatie voor gehele getallen en decimalen

1. Selecteer een lijst met cellen waarin je alleen gehele getallen of decimalen wilt toestaan en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer het bijbehorende item "Geheel getal" of "Decimaal" in het vak "Toestaan".
• Kies vervolgens een van de criteria in het vak "Gegevens" (in dit voorbeeld kies ik de optie "tussen").
• Tips: De criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens de minimale en maximale waarden in die je nodig hebt (in dit geval getallen tussen0 en100).
• Klik ten slotte op de knop "OK".

3. Nu kunnen alleen gehele getallen tussen0 en100 worden ingevoerd in de geselecteerde cellen.

3.2 Gegevensvalidatie voor datum en tijd

Om te valideren dat een specifieke datum of tijd wordt ingevoerd, kun je eenvoudig deze functie "Gegevensvalidatie" gebruiken. Ga als volgt te werk:

1. Selecteer een lijst met cellen waarin je alleen specifieke datums of tijden wilt toestaan en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer het bijbehorende item "Datum" of "Tijd" in het vak "Toestaan".
• Kies vervolgens een van de criteria in het vak "Gegevens" (hier kies ik de optie "groter dan").
• Tips: De criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens de gewenste "Startdatum" in (ik wil dat de datums groter zijn dan8/20/2021).
• Klik ten slotte op de knop "OK".

3. Nu kunnen alleen datums groter dan8/20/2021 worden ingevoerd in de geselecteerde cellen.

3.3 Gegevensvalidatie voor tekstlengte

Als je het aantal tekens wilt beperken dat in een cel kan worden getypt, bijvoorbeeld om de inhoud te beperken tot maximaal10 tekens voor een bepaald bereik, kan deze functie "Gegevensvalidatie" je ook helpen.

1. Selecteer een lijst met cellen waarin je de tekstlengte wilt beperken en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Tekstlengte" in het vak "Toestaan".
• Kies vervolgens een van de criteria in het vak "Gegevens" (in dit voorbeeld kies ik de optie "kleiner dan").
• Tips: De criteria zijn: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens het maximale aantal in dat je wilt beperken (ik wil dat de tekstlengte niet meer dan10 tekens is).
• Klik ten slotte op de knop "OK".

3. Nu mogen in de geselecteerde cellen alleen tekstreeksen met minder dan10 tekens worden ingevoerd

3.4 Gegevensvalidatielijst (keuzelijst)

Met deze krachtige functie "Gegevensvalidatie" kun je ook snel en eenvoudig een keuzelijst in cellen maken. Ga als volgt te werk:

1. Selecteer de doelcellen waarin je de keuzelijst wilt invoegen en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Lijst" in de vervolgkeuzelijst "Toestaan".
• Typ in het tekstvak "Koppeling bron" de lijstitems direct, gescheiden door komma's. Bijvoorbeeld, om de invoer te beperken tot drie keuzes, typ je Not Started,In Progress,Completed, of selecteer een lijst met cellen met de waarden waarop de keuzelijst is gebaseerd.
• Klik ten slotte op de knop "OK".

3. Nu is de keuzelijst aangemaakt in de cellen zoals hieronder weergegeven:

Klik voor meer gedetailleerde informatie over keuzelijsten…

4. Geavanceerde aangepaste regels voor gegevensvalidatie

In dit gedeelte laat ik zien hoe je enkele geavanceerde aangepaste gegevensvalidatieregels maakt om verschillende problemen op te lossen, zoals validatieformules maken om alleen nummers of tekstreeksen toe te staan, alleen unieke waarden, alleen bepaalde telefoonnummers, e-mailadressen enzovoort.

4.1 Gegevensvalidatie: alleen nummers of tekst toestaan

Alleen nummers mogen worden ingevoerd met de functie Gegevensvalidatie

Om alleen nummers toe te staan in een bereik van cellen, ga als volgt te werk:

1. Selecteer een bereik van cellen waarin je alleen nummers wilt toestaan.

2. Klik op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het dialoogvenster "Gegevensvalidatie" dat verschijnt, kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de volgende formule in het tekstvak "Formule" in. ("A2" is de eerste cel van het geselecteerde bereik dat je wilt beperken)

  =ISNUMBER(A2)

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

3. Vanaf nu kunnen alleen nummers worden ingevoerd in de geselecteerde cellen.

Opmerking: Deze "ISGETAL"-functie staat alle numerieke waarden toe in gevalideerde cellen, inclusief gehele getallen, decimalen, breuken, datums en tijden.

Alleen tekstreeksen mogen worden ingevoerd met de functie Gegevensvalidatie

Om celinvoer te beperken tot alleen tekst, kun je de functie "Gegevensvalidatie" gebruiken met een aangepaste formule op basis van de functie "ISTEKST". Ga als volgt te werk:

1. Selecteer een bereik van cellen waarin je alleen tekstreeksen wilt toestaan.

2. Klik op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het dialoogvenster "Gegevensvalidatie" dat verschijnt, kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de volgende formule in het tekstvak "Formule" in. ("A2" is de eerste cel van het geselecteerde bereik dat je wilt beperken)

  =ISTEXT(A2)

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

3. Nu kan bij het invoeren van gegevens in de specifieke cellen alleen tekstformaat worden toegestaan.

4.2 Gegevensvalidatie: alleen alfanumerieke waarden toestaan

Voor sommige doeleinden wil je misschien alleen letters en cijfers toestaan en speciale tekens zoals ~, %, $ of spaties uitsluiten. In dit gedeelte worden enkele handige methoden geïntroduceerd.

Alleen alfanumerieke waarden toestaan met de functie Gegevensvalidatie

Om speciale tekens te voorkomen en alleen alfanumerieke waarden toe te staan, maak je een aangepaste formule in de functie "Gegevensvalidatie" met de volgende stappen:

1. Selecteer een bereik van cellen waarin je alleen alfanumerieke waarden wilt toestaan.

2. Klik op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens 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 bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik dat je wilt beperken.

3. Nu mogen alleen letters en cijfers worden ingevoerd en worden speciale tekens geblokkeerd, zoals hieronder weergegeven:

Alleen alfanumerieke waarden toestaan met een handige functie

De bovenstaande formule kan ingewikkeld lijken om te begrijpen en te onthouden. Hier introduceer ik een handige functie genaamd "Beperk invoer" van Kutools voor Excel, waarmee deze taak aanzienlijk wordt vereenvoudigd.

1. Selecteer een bereik van cellen waarin je alleen alfanumerieke waarden wilt toestaan.

2. Klik vervolgens op "Kutools" > "Beperk invoer" > "Beperk invoer", zie screenshot:

3. In het geopende dialoogvenster "Beperk invoer" selecteer je de optie "Verbied het invoeren van speciale tekens", zie screenshot:

4. Klik vervolgens op "OK" en in de volgende promptvensters klik je op "Ja" > "OK" om de bewerking af te ronden. Nu zijn in de geselecteerde cellen alleen letters en cijfers toegestaan, zie screenshot:

4.3 Gegevensvalidatie: alleen tekst die begint of eindigt met specifieke tekens toestaan

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

Teksten toestaan die beginnen of eindigen met specifieke tekens met slechts één voorwaarde

Als je bijvoorbeeld wilt dat tekstinvoer in specifieke cellen begint of eindigt met "CN", volg dan deze stappen:

1. Selecteer een bereik van cellen waarin alleen teksten mogen worden ingevoerd die beginnen of eindigen met bepaalde tekens.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de onderstaande formule in het tekstvak "Formule" in.
  Alleen teksten toestaan die beginnen met CN:

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

  Alleen teksten toestaan die eindigen met CN:

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

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

Opmerking: In bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, het getal "2" is het aantal tekens dat je hebt opgegeven, "CN" is de tekst waarmee je wilt beginnen of eindigen.

3. Vanaf nu mogen alleen tekstreeksen die beginnen of eindigen met de opgegeven tekens worden ingevoerd in de geselecteerde cellen. Anders verschijnt er een waarschuwingsmelding, zoals hieronder weergegeven:

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

Opmerking: Het sterretje * is een wildcard die overeenkomt met één of meer tekens.

Teksten toestaan die beginnen of eindigen met specifieke tekens met meerdere criteria (OF-logica)

Als je bijvoorbeeld wilt dat tekstinvoer begint of eindigt met "CN" of "UK" zoals hieronder weergegeven, moet je een extra EXACT toevoegen met een plusteken (+). Ga als volgt te werk:

1. Selecteer een bereik van cellen waarin alleen teksten mogen worden ingevoerd die beginnen of eindigen met meerdere criteria.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de onderstaande formule in het tekstvak "Formule" in.
  Alleen teksten toestaan die beginnen met CN of UK:

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

  Alleen teksten toestaan die eindigen 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 bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, het getal "2" is het aantal tekens dat je hebt opgegeven, "CN" en "UK" zijn de specifieke teksten waarmee je wilt beginnen of eindigen.

3. Nu mogen alleen tekstreeksen die beginnen of eindigen met de opgegeven tekens worden ingevoerd in de geselecteerde cellen.

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

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

Opmerking: Het sterretje * is een wildcard die overeenkomt met één of meer tekens.

4.4 Gegevensvalidatie: invoer moet specifieke tekst bevatten / mag specifieke tekst niet bevatten

In dit gedeelte laat ik zien hoe je Gegevensvalidatie toepast om waarden toe te staan die een specifieke substring bevatten of juist niet bevatten, of één van meerdere substrings in Excel.

Invoer moet één of meerdere specifieke teksten bevatten

Invoer moet één specifieke tekst bevatten

Om invoer toe te staan die een specifieke tekst bevat, bijvoorbeeld dat alle ingevoerde waarden de tekst "KTE" moeten bevatten zoals hieronder weergegeven, kun je gegevensvalidatie toepassen met een aangepaste formule op basis van de functies VIND.ALLES en ISGETAL. Ga als volgt te werk:

1. Selecteer een bereik van cellen waarin alleen teksten mogen worden ingevoerd die bepaalde tekst bevatten.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een 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 bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, de tekst "KTE" is de tekst die de invoer moet bevatten.

3. Nu verschijnt er een waarschuwingsvenster als de ingevoerde waarde de vereiste tekst niet bevat.

Invoer moet één van meerdere specifieke teksten bevatten

De bovenstaande formule werkt alleen voor één tekst. Als je wilt dat een van meerdere tekststrings is toegestaan in de cellen, zoals hieronder weergegeven, gebruik dan de functies SOMPRODUCT, VIND.ALLES en ISGETAL samen om een formule te maken.

1. Selecteer een bereik van cellen waarin alleen teksten mogen worden ingevoerd die één van meerdere items bevatten.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een van de onderstaande formules in het tekstvak "Formule" in, afhankelijk van je behoefte.
  Hoofdlettergevoelig:

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

  Niet hoofdlettergevoelig:

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

• Klik vervolgens op "OK" om het dialoogvenster te sluiten.

Opmerking: In bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, "C2:C4" is de lijst met waarden waarvan je wilt dat de invoer er één van bevat.

3. Nu mogen alleen de invoerwaarden die één van de waarden uit de specifieke lijst bevatten worden ingevoerd.

Invoer mag niet één of meerdere specifieke teksten bevatten

Invoer mag niet één specifieke tekst bevatten

Om te valideren dat de invoer geen specifieke tekst bevat, bijvoorbeeld om waarden toe te staan die niet de tekst "KTE" bevatten, kun je de functies ISFOUT en VIND.ALLES gebruiken om een gegevensvalidatieregel te maken. Ga als volgt te werk:

1. Selecteer een bereik van cellen waarin alleen teksten mogen worden ingevoerd die bepaalde tekst niet bevatten.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een 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 bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, de tekst "KTE" is de tekst die de invoer niet mag bevatten.

3. Nu wordt invoer die de specifieke tekst bevat, geblokkeerd.

Invoer mag niet één van meerdere specifieke teksten bevatten

Om te voorkomen dat één van meerdere tekststrings uit een lijst wordt ingevoerd, zoals hieronder weergegeven, volg je deze stappen:

1. Selecteer een bereik van cellen waarin je wilt voorkomen dat bepaalde teksten worden ingevoerd.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "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 vervolgens op "OK" om het dialoogvenster te sluiten.

Opmerking: In bovenstaande formules is "A2" de eerste cel van het geselecteerde bereik, "C2:C4" is de lijst met waarden die je wilt blokkeren als de invoer er één van bevat.

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

4.5 Gegevensvalidatie: alleen unieke waarden toestaan

Als je wilt voorkomen dat dubbele gegevens worden ingevoerd in een bereik van cellen, worden in dit gedeelte enkele snelle methoden besproken om deze taak in Excel op te lossen.

Alleen unieke waarden toestaan met de functie Gegevensvalidatie

Normaal gesproken kan de functie Gegevensvalidatie met een aangepaste formule op basis van de functie AANTAL.ALS je hierbij helpen. Ga als volgt te werk:

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

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens 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 bovenstaande formule is "A2:A9" het bereik van cellen waarin je alleen unieke waarden wilt toestaan en "A2" is de eerste cel van het geselecteerde bereik.

3. Nu mogen alleen unieke waarden worden ingevoerd en verschijnt er een waarschuwingsmelding als er dubbele gegevens worden ingevoerd, zoals hieronder weergegeven:

Alleen unieke waarden toestaan met VBA-code

De volgende VBA-code kan je ook helpen om dubbele waarden te voorkomen. Ga als volgt te werk:

1. Klik met de rechtermuisknop op het werkbladtabblad waarin je alleen unieke waarden wilt toestaan en kies "Code weergeven" in het contextmenu. In het geopende venster "Microsoft Visual Basic for Applications" kopieer en plak je 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 bovenstaande code zijn "A1:A100" en "A:A" de cellen in de kolom waarin je duplicaten wilt voorkomen. Pas deze aan naar jouw situatie.

2. Sla deze code vervolgens op en sluit het venster. Nu verschijnt er een waarschuwingsvenster wanneer je een dubbele waarde invoert in cellen A1:A100, zoals hieronder weergegeven:

Alleen unieke waarden toestaan met een handige functie

Als je Kutools voor Excel hebt, kun je met de functie "Voorkom Dubbele Invoer" snel gegevensvalidatie instellen om dubbele waarden in een bereik van cellen te voorkomen met slechts een paar klikken.

1. Selecteer het bereik van cellen waarin je dubbele waarden wilt voorkomen en alleen unieke gegevens wilt toestaan.

2. Klik vervolgens op "Kutools" > "Beperk invoer" > "Voorkom Dubbele Invoer", zie screenshot:

3. Er verschijnt een waarschuwingsmelding dat Gegevensvalidatie wordt verwijderd bij het toepassen van deze functie. Klik op "Ja" en in het volgende promptvenster op "OK", zoals hieronder weergegeven:

4. Nu verschijnt er een promptvenster wanneer je dubbele gegevens invoert in de opgegeven cellen om je eraan te herinneren dat dubbele gegevens ongeldig zijn, zie screenshot:

4.6 Gegevensvalidatie: alleen hoofdletters / kleine letters / eerste letter hoofdletter toestaan

De functie Gegevensvalidatie is een krachtig hulpmiddel waarmee gebruikers kunnen afdwingen dat alleen hoofdletters, kleine letters of tekst met een hoofdletter aan het begin wordt ingevoerd in een bereik van cellen. Ga als volgt te werk:

1. Selecteer het bereik van cellen waarin je alleen hoofdletters, kleine letters of tekst met een hoofdletter aan het begin wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een van de onderstaande formules in het tekstvak "Formule" in, afhankelijk van je behoefte.
  Alleen hoofdletters toestaan:

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

  Alleen kleine letters toestaan

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

  Alleen tekst met een hoofdletter aan het begin toestaan

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

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

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

3. Nu worden alleen de invoerwaarden die voldoen aan de door jou gemaakte regel geaccepteerd.

4.7 Gegevensvalidatie: alleen waarden toestaan die wel/niet voorkomen in een andere lijst

Waarden toestaan of voorkomen op basis van hun aanwezigheid in een andere lijst kan voor veel gebruikers een uitdaging zijn. Je kunt echter de functie Gegevensvalidatie met een eenvoudige formule op basis van de functie AANTAL.ALS gebruiken om dit op te lossen.

Als ik bijvoorbeeld alleen de waarden in het bereik C2:C4 wil toestaan in een bereik van cellen zoals hieronder weergegeven, ga dan als volgt te werk:

1. Selecteer het bereik van cellen waarop je de gegevensvalidatie wilt toepassen.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een van de onderstaande formules in het tekstvak "Formule" in, afhankelijk van je behoefte.
  Alleen waarden toestaan die voorkomen in een andere kolom

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

  Waarden voorkomen die voorkomen in een andere kolom

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

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

Opmerking: In bovenstaande formule is "A2" de eerste cel van de kolom die je wilt gebruiken, "C2:C4" is de lijst met waarden die je wilt toestaan of voorkomen als de invoer er één van is.

3. Nu mogen alleen de invoerwaarden die voldoen aan de door jou gemaakte regel worden ingevoerd; andere waarden worden geblokkeerd.

4.8 Gegevensvalidatie: alleen telefoonnummerformaat toestaan

Wanneer je informatie van je medewerkers invoert, moet je in één kolom het telefoonnummer typen. Om ervoor te zorgen dat telefoonnummers snel en correct worden ingevoerd, kun je gegevensvalidatie instellen voor telefoonnummers. Bijvoorbeeld, ik wil alleen het telefoonnummerformaat (123)456-7890 toestaan in een werkblad. In dit gedeelte worden twee snelle methoden besproken om deze taak op te lossen.

Alleen telefoonnummerformaat afdwingen met de functie Gegevensvalidatie

Om alleen een specifiek telefoonnummerformaat toe te staan, volg je deze stappen:

1. Selecteer de lijst met cellen waarin je een specifiek telefoonnummerformaat wilt toestaan en klik met de rechtermuisknop, kies "Celopmaak instellen" in het contextmenu, zie screenshot:

2. In het dialoogvenster "Celopmaak instellen" selecteer je onder het tabblad "Getal" de optie "Aangepast" in de linker lijst "Categorie" en voer je het gewenste telefoonnummerformaat in het vak Type in, bijvoorbeeld "(###) ###-####", zie screenshot:

3. Klik vervolgens op "OK" om het dialoogvenster te sluiten.

4. Na het opmaken van de cellen selecteer je ze opnieuw en open je het dialoogvenster "Gegevensvalidatie" door te klikken op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het pop-upvenster kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de volgende formule in het vak Formule in.

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

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

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

5. Nu wordt een10-cijferig nummer automatisch omgezet naar het specifieke telefoonnummerformaat zoals je wilt, zie screenshots:

Opmerking: Als het ingevoerde nummer niet uit10 cijfers bestaat, verschijnt er een waarschuwingsvenster, zie screenshot:

Alleen telefoonnummerformaat afdwingen met een handige functie

Met de functie "Alleen telefoonnummers kunnen worden ingevoerd" van Kutools voor Excel kun je ook eenvoudig alleen het telefoonnummerformaat afdwingen met slechts enkele klikken.

1. Selecteer de lijst met cellen waarin je alleen een specifiek telefoonnummer wilt toestaan en klik vervolgens op "Kutools" > "Beperk invoer" > "Alleen telefoonnummers kunnen worden ingevoerd", zie screenshot:

2. In het dialoogvenster "Telefoonnummer" selecteer je het gewenste telefoonnummerformaat of maak je je eigen opmaak door op de knop "Toevoegen" te klikken, zie screenshot:

3. Nadat je het telefoonnummerformaat hebt geselecteerd of ingesteld, klik je op "OK". Nu mag alleen het telefoonnummer met de specifieke opmaak worden ingevoerd, anders verschijnt er een waarschuwingsvenster, zie screenshot:

4.9 Gegevensvalidatie: alleen e-mailadressen toestaan

Stel dat je meerdere e-mailadressen moet invoeren in een kolom van een werkblad. Om te voorkomen dat onjuiste e-mailadressen worden ingevoerd, kun je normaal gesproken een gegevensvalidatieregel instellen die alleen e-mailadressen toestaat.

Alleen e-mailadressenformaat afdwingen met de functie Gegevensvalidatie

Met de functie Gegevensvalidatie en een aangepaste formule kun je snel een regel maken om ongeldige e-mailadressen te voorkomen. Volg deze stappen:

1. Selecteer de cellen waarin je alleen e-mailadressen wilt toestaan en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de volgende formule in het vak Formule in:

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

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

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

3. Nu verschijnt er een waarschuwingsvenster als de ingevoerde tekst niet overeenkomt met het e-mailformaat, zie screenshot:

Alleen e-mailadressenformaat afdwingen met een handige functie

Kutools voor Excel ondersteunt een handige functie – "Alleen e-mailadressen kunnen worden ingevoerd". Met deze functie kun je ongeldige e-mailadressen met één klik voorkomen.

1. Selecteer de cellen waarin je alleen e-mailadressen wilt toestaan en klik vervolgens op "Kutools" > "Beperk invoer" > "Alleen e-mailadressen kunnen worden ingevoerd". Zie screenshot:

2. Nu mag alleen het e-mailformaat worden ingevoerd, anders verschijnt er een waarschuwingsvenster, zie screenshot:

4.10 Gegevensvalidatie: alleen IP-adressen toestaan

In dit gedeelte laat ik enkele snelle methoden zien om gegevensvalidatie in te stellen zodat alleen IP-adressen in een bereik van cellen worden geaccepteerd.

Alleen IP-adressenformaat afdwingen met de functie Gegevensvalidatie

Om alleen IP-adressen toe te staan in een specifiek bereik van cellen, volg je deze stappen:

1. Selecteer de cellen waarin je alleen IP-adressen wilt toestaan en klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie".

2. In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens 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 bovenstaande formule is "A2" de eerste cel van de kolom die je wilt gebruiken.

3. Nu verschijnt er een waarschuwingsvenster als een ongeldig IP-adres wordt ingevoerd, zoals hieronder weergegeven:

Alleen IP-adressenformaat afdwingen met VBA-code

Hier kan de volgende VBA-code je ook helpen om alleen IP-adressen toe te staan en andere invoer te blokkeren. Ga als volgt te werk:

1. Klik met de rechtermuisknop op het werkbladtabblad en klik op "Code weergeven" in het contextmenu. In het geopende venster "Microsoft Visual Basic for Applications" kopieer je onderstaande VBA-code.

VBA-code: cellen valideren zodat alleen IP-adressen worden geaccepteerd

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 bovenstaande code is "A2:A10" het celbereik waarin je alleen IP-adressen wilt toestaan.

2. Sla deze code vervolgens op en sluit het venster. Nu mogen alleen geldige IP-adressen worden ingevoerd in de opgegeven cellen.

Alleen IP-adressenformaat afdwingen met een eenvoudige functie

Als je Kutools voor Excel hebt geïnstalleerd in je werkmap, kan de functie "Alleen IP-adressen kunnen worden ingevoerd" je ook helpen om deze taak op te lossen.

1. Selecteer de cellen waarin je alleen IP-adressen wilt toestaan en klik vervolgens op "Kutools" > "Beperk invoer" > "Alleen IP-adressen kunnen worden ingevoerd". Zie screenshot:

2. Na het toepassen van deze functie mag nu alleen een IP-adres worden ingevoerd, anders verschijnt er een waarschuwingsvenster, zie screenshot:

4.11 Gegevensvalidatie: waarden beperken die de totale waarde overschrijden

Stel dat je een maandelijkse onkostendeclaratie hebt en het budgettotaal is €18.000. Je wilt ervoor zorgen dat het totaalbedrag in de onkostenlijst deze vooraf ingestelde limiet niet overschrijdt, zoals hieronder weergegeven. In dit geval kun je een gegevensvalidatieregel maken met de SOM-functie om te voorkomen dat het totaal een vooraf ingestelde waarde overschrijdt.

1. Selecteer de lijst met cellen waarin je de waarden wilt beperken.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens 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 bovenstaande formule is "B2:B7" het bereik van cellen waarin je de invoer wilt beperken.

3. Nu, wanneer je waarden invoert in het bereik B2:B7, wordt de validatie goedgekeurd als het totaal minder is dan €18.000. Als een waarde ervoor zorgt dat het totaal €18.000 overschrijdt, verschijnt er een waarschuwingsvenster.

4.12 Gegevensvalidatie: celinvoer beperken op basis van een andere cel

Als je gegevensinvoer in een lijst met cellen wilt beperken op basis van de waarde in een andere cel, kan de functie Gegevensvalidatie je ook hierbij helpen. Bijvoorbeeld: als cel C1 de tekst "Ja" bevat, mag het bereik A2:A9 elke invoer accepteren. Maar als cel C1 een andere tekst bevat, wordt invoer in het bereik A2:A9 beperkt, zoals hieronder weergegeven:

Om dit op te lossen, ga als volgt te werk:

1. Selecteer de lijst met cellen waarin je de waarden wilt beperken.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de onderstaande formule in het tekstvak "Formule" in.

  =$C$1="Yes"

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

Opmerking: In bovenstaande formule is "C1" de cel met de specifieke tekst die je wilt gebruiken en "Ja" is de tekst waarop je de cellen wilt baseren. Pas deze aan naar jouw situatie.

3. Nu, als cel C1 de tekst "Ja" bevat, mag alles worden ingevoerd in het bereik A2:A9. Als cel C1 een andere tekst bevat, kun je geen waarde invoeren, zie onderstaande demo:

4.13 Gegevensvalidatie: alleen werkdagen of weekenden toestaan

Als je alleen werkdagen (van maandag tot en met vrijdag) of weekenden (zaterdag en zondag) wilt toestaan in een lijst met cellen, kan de functie "Gegevensvalidatie" je hierbij helpen. Ga als volgt te werk:

1. Selecteer de lijst met cellen waarin je werkdagen of weekenden wilt invoeren.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens een van de onderstaande formules in het tekstvak "Formule" in, afhankelijk van je behoefte.
  Alleen werkdagen toestaan

  =WEEKDAY(A2,2)<6

  Alleen weekenden toestaan

  =WEEKDAY(A2,2)>5

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

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

3. Nu kun je alleen weekdagen of weekenddatums invoeren in de opgegeven cellen, afhankelijk van je selectie.

4.14 Gegevensvalidatie: ingevoerde datum toestaan op basis van de datum van vandaag

Soms wil je alleen datums toestaan die groter of kleiner zijn dan vandaag in een lijst met cellen. De functie "Gegevensvalidatie" met de functie "VANDAAG" kan je hierbij helpen. Ga als volgt te werk:

1. Selecteer de lijst met cellen waarin je alleen toekomstige datums (datums groter dan vandaag) wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de onderstaande formule in het tekstvak "Formule" in.

  =A2>Today()

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

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

3. Nu mogen alleen datums later dan vandaag worden ingevoerd in de cellen. Anders verschijnt er een waarschuwingsvenster, zie screenshot:

=A2<Today()

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

4.15 Gegevensvalidatie: ingevoerde tijd toestaan op basis van de huidige tijd

Als je gegevens wilt valideren op basis van de huidige tijd, bijvoorbeeld alleen tijden vóór of na de huidige tijd mogen worden ingevoerd, kun je je eigen gegevensvalidatieformule maken. Ga als volgt te werk:

1. Selecteer de lijst met cellen waarin je alleen tijden vóór of na de huidige tijd wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Tijd" in de vervolgkeuzelijst "Toestaan".
• Kies vervolgens "kleiner dan" om alleen tijden vóór de huidige tijd toe te staan, of "groter dan" om tijden na de huidige tijd toe te staan, afhankelijk van je behoefte, in de vervolgkeuzelijst "Gegevens".
• Voer vervolgens in het vak "Eindtijd" of "Starttijd" onderstaande formule in:

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

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

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

3. Nu mogen alleen tijden vóór of na de huidige tijd worden ingevoerd in de specifieke cellen.

4.16 Gegevensvalidatie: de datum van een specifiek of huidig jaar

Om alleen datums in een bepaald jaar of het huidige jaar toe te staan, kun je gegevensvalidatie gebruiken met een aangepaste formule op basis van de functie JAAR.

1. Selecteer de lijst met cellen waarin je alleen datums in een bepaald jaar wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Aangepast" in de vervolgkeuzelijst "Toestaan".
• Voer vervolgens de onderstaande formule in het tekstvak "Formule" in.

  =YEAR(A2)=2020

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

Opmerking: In bovenstaande formule is "A2" de eerste cel van de kolom die je wilt gebruiken, "2020" is het jaartal waarop je wilt beperken.

3. Nu mogen alleen datums in het jaar2020 worden ingevoerd. Anders verschijnt er een waarschuwingsvenster, zoals hieronder weergegeven:

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

4.17 Gegevensvalidatie: de datum in de huidige week of maand

Als je gebruikers alleen datums van de huidige week of maand wilt laten invoeren in specifieke cellen, worden in dit gedeelte enkele formules besproken om deze taak in Excel uit te voeren.

Toestaan om de datum van de huidige week in te voeren

1. Selecteer de lijst met cellen waarin je alleen datums van de huidige week wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Datum" in de vervolgkeuzelijst "Toestaan".
• Kies vervolgens "tussen" in de vervolgkeuzelijst "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. Nu mogen alleen datums binnen de huidige week worden ingevoerd. Andere datums worden geblokkeerd, zoals hieronder weergegeven:

Toestaan om de datum van de huidige maand in te voeren

Om alleen datums van de huidige maand toe te staan, ga als volgt te werk:

1. Selecteer de lijst met cellen waarin je alleen datums van de huidige maand wilt toestaan.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". In het geopende dialoogvenster "Gegevensvalidatie" kun je onder het tabblad "Opties" de volgende handelingen uitvoeren:

• Selecteer "Datum" in de vervolgkeuzelijst "Toestaan".
• Kies vervolgens "tussen" in de vervolgkeuzelijst "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 mogen alleen datums binnen de huidige maand worden ingevoerd in de geselecteerde cellen.

5. Hoe bewerk je de gegevensvalidatie in Excel?

Om een bestaande gegevensvalidatieregel te bewerken of te wijzigen, volg je onderstaande stappen:

1. Selecteer een van de cellen met de gegevensvalidatieregel.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie" om naar het dialoogvenster "Gegevensvalidatie" te gaan. Bewerk of wijzig de regels naar wens en vink de optie "Deze wijzigingen toepassen op alle andere cellen met dezelfde instellingen" aan om deze nieuwe regel toe te passen op alle andere cellen met dezelfde validatiecriteria. Zie screenshot:

3. Klik op "OK" om de wijzigingen op te slaan.

6. Hoe vind en selecteer je cellen met gegevensvalidatie in Excel?

Als je meerdere gegevensvalidatieregels hebt gemaakt in je werkblad en nu de cellen wilt vinden en selecteren waarop deze regels zijn toegepast, kun je met de opdracht "Ga naar speciaal" alle soorten gegevensvalidatie of een specifiek type gegevensvalidatie selecteren.

1. Activeer het werkblad waarin je de cellen met gegevensvalidatie wilt vinden en selecteren.

2. Klik vervolgens op "Start" > "Zoeken en selecteren" > "Ga naar speciaal", zie screenshot:

3. Selecteer in het dialoogvenster "Ga naar speciaal" de optie "Gegevensvalidatie" > "Alle", zie screenshot:

4. Nu zijn alle cellen met gegevensvalidatie geselecteerd in het huidige werkblad.

Tips: Als je een specifiek type gegevensvalidatie wilt selecteren, selecteer dan eerst een cel met de gewenste gegevensvalidatie, ga vervolgens naar het dialoogvenster "Ga naar speciaal" en kies "Gegevensvalidatie" > "Zelfde".

7. Hoe kopieer je de gegevensvalidatieregel naar andere cellen?

Stel dat je een gegevensvalidatieregel hebt gemaakt voor een lijst met cellen en deze nu wilt toepassen op andere cellen. In plaats van de regel opnieuw te maken, kun je de bestaande regel snel en eenvoudig kopiëren en plakken naar andere cellen.

1. Klik om een cel te selecteren met de validatieregel die je wilt gebruiken en druk vervolgens op "Ctrl + C" om deze te kopiëren.

2. Selecteer vervolgens de cellen die je wilt valideren. Om meerdere niet-aangrenzende cellen te selecteren, houd je de "Ctrl"-toets ingedrukt terwijl je de cellen selecteert.

3. Klik met de rechtermuisknop op de selectie en kies de optie "Plakken speciaal", zie screenshot:

4. Selecteer in het dialoogvenster "Plakken speciaal" de optie "Validatie", zie screenshot:

5. Klik op de knop "OK". Nu is de validatieregel gekopieerd naar de nieuwe cellen.

8. Hoe gebruik je gegevensvalidatie om ongeldige invoer in Excel te markeren?

Soms moet je gegevensvalidatieregels maken voor bestaande gegevens. In dat geval kunnen er ongeldige gegevens in het bereik voorkomen. Hoe kun je deze ongeldige gegevens controleren en aanpassen? In Excel kun je de functie "Ongeldige gegevens markeren" gebruiken om de ongeldige gegevens met een rode cirkel te markeren.

Om de ongeldige gegevens te markeren, moet je de functie "Gegevensvalidatie" toepassen om een regel in te stellen voor het gegevensbereik. Ga als volgt te werk:

1. Selecteer het gegevensbereik waarin je ongeldige gegevens wilt markeren.

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". Stel in het dialoogvenster "Gegevensvalidatie" de validatieregel in naar wens. Bijvoorbeeld: ik valideer waarden groter dan500, zie screenshot:

3. Klik vervolgens op "OK" om het dialoogvenster te sluiten. Nadat je de gegevensvalidatieregel hebt ingesteld, klik je op "Gegevens" > "Gegevensvalidatie" > "Ongeldige gegevens markeren". Alle ongeldige waarden die kleiner zijn dan500 worden nu gemarkeerd met een rode ovaal. Zie screenshots:

Opmerkingen:

• 1. Zodra je een ongeldige waarde corrigeert, verdwijnt de rode cirkel automatisch.
• 2. Deze functie "Ongeldige gegevens markeren" kan maximaal255 cellen markeren. Wanneer je het huidige werkboek opslaat, worden alle rode cirkels verwijderd.
• 3. Deze cirkels worden niet afgedrukt.
• 4. Je kunt de rode cirkels ook verwijderen door te klikken op "Gegevens" > "Gegevensvalidatie" > "Markeringen wissen".

9. Hoe verwijder je gegevensvalidatie in Excel?

Om gegevensvalidatieregels te verwijderen uit een bereik van cellen, het huidige werkblad of het hele werkboek, gebruik je de volgende methoden.

Gegevensvalidatie verwijderen in geselecteerd bereik met de functie Gegevensvalidatie

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

2. Klik vervolgens op "Gegevens" > "Gegevensvalidatie" > "Gegevensvalidatie". Klik in het geopende dialoogvenster onder het tabblad "Opties" op de knop "Alles wissen", zie screenshot:

3. Klik vervolgens op de knop "OK" om dit dialoogvenster te sluiten. De gegevensvalidatieregel die op het geselecteerde bereik was toegepast, is nu direct verwijderd.

Tips: Om de gegevensvalidatie uit het huidige werkblad te verwijderen, selecteer je eerst het hele werkblad en volg je daarna bovenstaande stappen.

Gegevensvalidatie verwijderen in geselecteerd bereik met een handige functie

Als je Kutools voor Excel hebt, kan de functie "Verwijder gegevensvalidatiebeperkingen" ook helpen om de gegevensvalidatieregels uit het geselecteerde bereik of het hele werkblad te verwijderen.

1. Selecteer het bereik van cellen of het hele werkblad met de gegevensvalidatie die je wilt verwijderen.

2. Klik vervolgens op "Kutools" > "Beperk invoer" > "Verwijder gegevensvalidatiebeperkingen", zie screenshot:

3. Klik in het promptvenster dat verschijnt op "OK". De gegevensvalidatieregel wordt nu verwijderd zoals gewenst.

Gegevensvalidatie verwijderen uit alle werkbladen met VBA-code

Om de gegevensvalidatieregels uit het hele werkboek te verwijderen, zijn bovenstaande methoden tijdrovend als er veel werkbladen zijn. De onderstaande code kan je helpen deze taak snel uit te voeren.

1. Houd de toetsen "ALT + F11" ingedrukt 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: Gegevensvalidatieregels verwijderen 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 vervolgens op de toets "F5" om deze code uit te voeren. Alle gegevensvalidatieregels zijn nu direct uit het hele werkboek verwijderd.