Om een vervolgkeuzelijst te maken in Excel selecteer je de cellen, ga je naar Gegevens → Gegevensvalidatie, stel je Toestaan in op Lijst en typ je bij Bron je items gescheiden door puntkomma's, of selecteer je de cellen die ze bevatten. Klik op OK en elke cel krijgt een pijltje dat je lijst opent.
De ene keuze die ertoe doet, is waar de items vandaan komen. Een getypte lijst is snel gemaakt maar lastig te wijzigen. Als de lijst gaat groeien, bewaar de items dan in een Excel-tabel, zodat elke keuzelijst nieuwe items automatisch oppikt.
Let op: de formules in dit artikel staan in de vorm van Nederlandstalige Excel (met puntkomma's als scheidingsteken), met de Engelse functienaam één keer tussen haakjes voor wie Engelse Excel gebruikt. In Engelse Excel gebruik je komma's als scheidingsteken.
| Bron van de items | Voorbeeld in het vak Bron | Werkt automatisch bij? | Het best voor |
|---|---|---|---|
| Getypte lijst | Ja;Nee;Misschien | Nee, handmatig bewerken | Korte lijsten die nooit veranderen |
| Celbereik | =$F$2:$F$5 | Alleen als je binnen het bereik bewerkt | Vaste lijsten op het blad |
| Excel-tabel | Selecteer de cellen van de tabelkolom | Ja, zodra je rijen toevoegt | Lijsten die in de loop van de tijd groeien |
| Overloopbereik (spill range) | =$H$2# | Ja, vanuit een formule zoals UNIEK | Lijsten op basis van bestaande gegevens |
| Afhankelijke lijst | =INDIRECT($A2) | Hangt af van de eerste keuze | Eerst categorie, dan item |
Stap voor stap een vervolgkeuzelijst maken
Deze stappen werken in Excel voor Microsoft 365, Excel 2021 en 2019 op Windows, en zijn vrijwel identiek op Mac en in Excel voor het web (verschillen hieronder).
- Typ de items van je lijst in één kolom zonder lege cellen, bijvoorbeeld Niet gestart, Bezig, Klaar en Geblokkeerd in F2:F5. Een apart blad met de naam "Lijsten" werkt hier goed voor.
- Selecteer de cel of cellen waarin de keuzelijst moet verschijnen, bijvoorbeeld C2:C50.
- Ga naar Gegevens → Gegevensvalidatie (in de groep Hulpmiddelen voor gegevens). In Windows is de sneltoets Alt, A, V, V (voor de Engelse versie van Excel).
- Stel op het tabblad Instellingen Toestaan in op Lijst.
- Klik in het vak Bron en selecteer je items op het blad (F2:F5, zonder de kop). Excel vult
=$F$2:$F$5in. - Zorg dat Vervolgkeuzelijst in cel is aangevinkt. Laat Lege cellen negeren aangevinkt als lege cellen geen probleem zijn.
- Klik op OK.
Selecteer een van die cellen en er verschijnt een pijltje naast. Klik erop, of druk op Alt+Pijl-omlaag (Option+Pijl-omlaag op Mac), om de lijst te openen.
De items typen in plaats van cellen selecteren
Voor een korte, vaste lijst typ je rechtstreeks in Bron: Ja;Nee;Misschien. Zet geen spaties na de puntkomma's, anders worden die onderdeel van elk item. In Nederlandstalige Excel is de puntkomma het scheidingsteken voor lijsten; in Engelse regio-instellingen gebruik je komma's (Yes,No,Maybe). Het vak Bron bevat maximaal 255 tekens, dus langere lijsten horen op het blad.
Excel voor Mac en Excel voor het web
- Mac: Gegevens → Gegevensvalidatie (in sommige versies heet het Valideren in de groep Hulpmiddelen). Het dialoogvenster heeft dezelfde drie tabbladen.
- Web: Gegevens → Gegevensvalidatie (onder Hulpmiddelen voor gegevens bij een smal venster), daarna dezelfde stappen Toestaan → Lijst.
- Mobiel: in de apps voor iPhone, iPad en Android kun je kiezen uit keuzelijsten die op een computer zijn gemaakt.
De lijst automatisch laten groeien met een tabel
Een gewoon bereik als $F$2:$F$5 bevat een item dat je in F6 typt niet. Zet de lijst om in een tabel en dat gebeurt wel:
- Klik op een cel in je lijst en druk op
Ctrl+T(Cmd+Top Mac). Vink Tabel met kopteksten aan en klik op OK. - Maak de keuzelijst zoals hierboven en selecteer in Bron de gegevenscellen van de tabel (zonder de kop).
- Typ een nieuw item in de rij direct onder de tabel. De tabel breidt uit en elke keuzelijst die erop is gebaseerd, toont het nieuwe item.
Je kunt een tabelverwijzing zoals =Tabel1[Status] niet rechtstreeks in Bron typen; Excel weigert dat. Selecteer in plaats daarvan de cellen, of gebruik =INDIRECT("Tabel1[Status]").
Een benoemd bereik werkt ook: selecteer de items, typ een naam zoals Statussen in het Naamvak (links van de formulebalk), druk op Enter en gebruik =Statussen als bron.
Een dynamische lijst bouwen uit bestaande gegevens met UNIEK
Als de opties moeten komen uit gegevens die je al hebt, bijvoorbeeld elke klantnaam in kolom A, laat dan een formule de lijst bouwen. In Excel voor Microsoft 365, Excel 2021 of later, of Excel voor het web:
- Voer in een lege cel, zeg H2, in:
=SORTEREN(UNIEK(FILTEREN(A2:A500;A2:A500<>"")))(in Engelse Excel:=SORT(UNIQUE(FILTER(A2:A500,A2:A500<>"")))). De gesorteerde lijst zonder dubbelen loopt vanaf H2 naar beneden over (het deelFILTERENlaat lege cellen weg, die anders als 0 zouden verschijnen). - Maak de keuzelijst met Bron ingesteld op
=$H$2#.
De # zegt tegen Excel dat het hele overloopbereik moet worden gebruikt, hoe lang het ook wordt, zodat nieuwe namen automatisch in de keuzelijst verschijnen. Je kunt UNIEK zelf niet in het vak Bron zetten, daarom is de hulpcel nodig. Meer over UNIEK lees je in Dubbele waarden verwijderen in Excel.
Een lijst op een ander blad gebruiken
Klik terwijl het vak Bron actief is op het tabblad van het andere blad en selecteer de cellen; Excel schrijft iets als =Lijsten!$A$2:$A$10. Je kunt het blad Lijsten daarna verbergen (klik met de rechtermuisknop op het tabblad → Verbergen), zodat niemand het per ongeluk bewerkt. Een bron in een andere werkmap werkt alleen zolang die werkmap open is, dus houd de lijst in hetzelfde bestand.
Een afhankelijke (trapsgewijze) keuzelijst maken met INDIRECT
Een afhankelijke keuzelijst wijzigt zijn opties op basis van een andere cel: kies Fruit in kolom A en kolom B biedt alleen fruit aan.
- Zet op je blad Lijsten elke categorie in een kop met de items eronder. Bijvoorbeeld Fruit in D1 met Appel, Banaan, Kers eronder, en Groenten in E1 met Wortel, Prei, Erwt.
- Selecteer D1:E4 en ga naar Formules → Maken op basis van selectie. Vink alleen Bovenste rij aan en klik op OK. Excel maakt voor elke kolom een benoemd bereik met de naam van de kop.
- Maak in A2 op je hoofdblad een normale keuzelijst met als bron de twee kopcellen,
=Lijsten!$D$1:$E$1(de categorienamen). - Selecteer B2, open Gegevens → Gegevensvalidatie, kies Lijst en voer
=INDIRECT($A2)in als bron. - Als Excel waarschuwt dat de bron momenteel een fout oplevert, klik dan op Ja. Dat komt alleen doordat A2 nog leeg is.
Namen mogen geen spaties bevatten, dus Maken op basis van selectie maakt van "Zuivelproducten en eieren" de naam Zuivelproducten_en_eieren. Gebruik voor categorieën met spaties in plaats daarvan =INDIRECT(SUBSTITUEREN($A2;" ";"_")) (in Engelse Excel: =INDIRECT(SUBSTITUTE($A2," ","_"))).
Een invoerbericht en een foutmelding toevoegen
- Invoerbericht: vink Invoerbericht weergeven als cel is geselecteerd aan en voer een titel en een kort bericht in, zoals "Kies de taakstatus". Het verschijnt telkens als iemand de cel selecteert.
- Foutmelding: vink Foutmelding weergeven nadat ongeldige gegevens zijn ingevoerd aan en kies een Stijl. Stop blokkeert alles wat niet in de lijst staat, terwijl Waarschuwing en Informatie je bericht tonen maar de invoer toch toelaten.
Let op: validatie controleert alleen wat mensen typen. Plakken over een cel vervangt de keuzelijst samen met de inhoud.
Een keuzelijst doorzoeken door te typen
In huidige Microsoft 365-versies van Excel filtert typen in een keuzelijstcel de lijst op items die die letters bevatten, waardoor lange lijsten veel sneller gaan. Excel 2021 en eerder hebben dit niet.
Een vervolgkeuzelijst bewerken, kopiëren of verwijderen
Items toevoegen of wijzigen. Typ bij een tabel het nieuwe item eronder. Bewerk bij een bereik de cellen, open daarna opnieuw Gegevens → Gegevensvalidatie en pas Bron aan als het bereik langer is geworden. Bewerk bij een getypte lijst de tekst in Bron. Vink Deze wijzigingen toepassen op alle andere cellen met dezelfde instellingen aan om alle overeenkomende keuzelijsten tegelijk bij te werken.
De keuzelijst naar meer cellen kopiëren. Sleep de vulgreep omlaag, of kopieer de cel, selecteer de bestemming en kies Start → Plakken → Plakken speciaal → Validatie om alleen de keuzelijst zonder de waarde te kopiëren.
Een keuzelijst verwijderen. Selecteer de cellen, ga naar Gegevens → Gegevensvalidatie, klik op Alles wissen en daarna op OK. Waarden die al in de cellen staan, blijven behouden. Om elke cel met een keuzelijst te vinden, gebruik je Start → Zoeken en selecteren → Gegevensvalidatie.
Waarom je keuzelijst niet werkt
- Er verschijnt geen pijltje. Open Gegevens → Gegevensvalidatie en vink Vervolgkeuzelijst in cel aan. Het pijltje verschijnt ook alleen als de cel is geselecteerd. Ontbreken de pijltjes overal, controleer dan Bestand → Opties → Geavanceerd → Weergaveopties voor deze werkmap en stel Voor objecten weergeven in op Alles.
- Nieuwe items verschijnen niet. De bron is een vast bereik. Breid het uit, of zet de lijst om in een tabel of overloopbereik zodat hij vanzelf groeit.
- Gegevensvalidatie is grijs. Het blad is beveiligd (Controleren → Beveiliging van blad opheffen), de werkmap gebruikt de verouderde functie Gedeelde werkmap, meerdere bladen zijn gegroepeerd (klik op één bladtabblad) of je bent nog een cel aan het bewerken (druk op Enter of Esc).
- De lijst toont lege items. Het bronbereik bevat lege cellen. Verklein het of gebruik een tabel.
- Iemand heeft een waarde getypt die niet in de lijst staat. De stijl van de Foutmelding is Waarschuwing of Informatie, of de melding staat uit. Zet hem op Stop.
Veelgestelde vragen
Hoe voeg ik een vervolgkeuzelijst toe aan een hele kolom? Selecteer het gewenste kolombereik, zoals C2:C1000, voordat je Gegevensvalidatie opent. Staan je gegevens in een tabel, dan wordt een keuzelijst die op de kolom is toegepast automatisch overgenomen in nieuwe rijen.
Kan een Excel-keuzelijst meerdere selecties toestaan? Niet met de ingebouwde Gegevensvalidatie. Elke cel bevat één keuze. Voor meerdere waarden heb je een VBA-macro of een andere opzet nodig, zoals één kolom per optie.
Hoeveel items kan een keuzelijst bevatten? Een lijst uit cellen kan duizenden items bevatten, al zijn lange lijsten lastig te scrollen. Een getypte lijst wordt beperkt door het vak Bron van 255 tekens.
Hoe maak ik een keuzelijst met Ja en Nee? Stel Toestaan in op Lijst en typ Ja;Nee in Bron.
Hoe verwijder ik een keuzelijst maar behoud ik de waarden? Selecteer de cellen, ga naar Gegevens → Gegevensvalidatie → Alles wissen en klik op OK. De huidige waarden blijven als gewone tekst staan.
Houd je lijstgegevens schoon
Een keuzelijst werkt alleen goed als de gegevens erachter consistent zijn. Met CoreGPT voor Excel beschrijf je een opschoning in gewone taal, zoals "verwijder dubbele klanten en corrigeer de verschillende schrijfwijzen van elk land", en wordt dat uitgevoerd in je werkmap, zodat de lijsten waarvan je keuzelijsten maakt netjes blijven. Gratis om mee te beginnen en werkt in Excel voor het web, Windows en Mac.
CoreGPTKlaar. Dit heb ik aangepast:
- 12 dubbele rijen verwijderd
- “US”, “DE”… omgezet naar volledige namen
- Telefoonnummers opgemaakt
Probeer het: CoreGPT voor Excel
Lees ook:
