Een vervolgkeuzelijst maken in Excel (de enige gids die u nodig hebt)

Een vervolgkeuzelijst is een uitstekende manier om de gebruiker een optie te geven om uit een vooraf gedefinieerde lijst te kiezen.

Het kan worden gebruikt om een ​​gebruiker een formulier te laten invullen of om interactieve Excel-dashboards te maken.

Vervolgkeuzelijsten zijn vrij gebruikelijk op websites/apps en zijn zeer intuïtief voor de gebruiker.

Bekijk video - Een vervolgkeuzelijst maken in Excel

In deze zelfstudie leert u hoe u een vervolgkeuzelijst in Excel maakt (het duurt maar een paar seconden om dit te doen), samen met alle geweldige dingen die u ermee kunt doen.

Een vervolgkeuzelijst maken in Excel

In deze sectie leert u de exacte stappen om een ​​Excel-vervolgkeuzelijst te maken:

1. Gegevens uit cellen gebruiken.
2. Handmatig gegevens invoeren.
3. Met behulp van de OFFSET-formule.

#1 Gegevens uit cellen gebruiken

Stel dat u een lijst met items heeft, zoals hieronder weergegeven:

Hier zijn de stappen om een ​​Excel-vervolgkeuzelijst te maken:

1. Selecteer een cel waar u de vervolgkeuzelijst wilt maken.
2. Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
3. Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen de optie Lijst als de Validatiecriteria.
   • Zodra u Lijst selecteert, verschijnt het bronveld.
4. Voer in het bronveld =$A$2:$A$6 in, of klik gewoon in het veld Bron en selecteer de cellen met de muis en klik op OK. Hiermee wordt een vervolgkeuzelijst in cel C2 ingevoegd.
   • Zorg ervoor dat de vervolgkeuzelijst In-cel is aangevinkt (die standaard is aangevinkt). Als deze optie niet is aangevinkt, toont de cel geen vervolgkeuzelijst, maar u kunt de waarden handmatig in de lijst invoeren.

Opmerking: Als u in één keer vervolgkeuzelijsten in meerdere cellen wilt maken, selecteert u alle cellen waar u deze wilt maken en volgt u de bovenstaande stappen. Zorg ervoor dat de celverwijzingen absoluut zijn (zoals $A$2) en niet relatief (zoals A2, of A$2, of $A2).

#2 Door gegevens handmatig in te voeren

In het bovenstaande voorbeeld worden celverwijzingen gebruikt in het veld Bron. U kunt items ook rechtstreeks toevoegen door deze handmatig in het bronveld in te voeren.

Stel dat u bijvoorbeeld twee opties, Ja en Nee, wilt weergeven in de vervolgkeuzelijst in een cel. U kunt het als volgt rechtstreeks invoeren in het veld Gegevensvalidatiebron:

• Selecteer een cel waar u de vervolgkeuzelijst wilt maken (cel C2 in dit voorbeeld).
• Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
• Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen de optie Lijst als de Validatiecriteria.
  • Zodra u Lijst selecteert, verschijnt het bronveld.
• Voer in het bronveld Ja, Nee . in
  • Zorg ervoor dat de vervolgkeuzelijst In-cel is aangevinkt.
• Klik OK.

Hiermee wordt een vervolgkeuzelijst in de geselecteerde cel gemaakt. Alle items die in het bronveld worden vermeld, gescheiden door een komma, worden in verschillende regels in het vervolgkeuzemenu weergegeven.

Alle items die in het bronveld zijn ingevoerd, gescheiden door een komma, worden weergegeven in verschillende regels in de vervolgkeuzelijst.

Opmerking: Als u in één keer vervolgkeuzelijsten in meerdere cellen wilt maken, selecteert u alle cellen waar u deze wilt maken en volgt u de bovenstaande stappen.

#3 Excel-formules gebruiken

Naast het selecteren uit cellen en het handmatig invoeren van gegevens, kunt u ook een formule in het bronveld gebruiken om een ​​Excel-vervolgkeuzelijst te maken.

Elke formule die een zoeklijst retourneert, kan worden gebruikt om een ​​vervolgkeuzelijst in Excel te maken.

Stel dat u de gegevensset hebt zoals hieronder weergegeven:

Hier zijn de stappen om een ​​Excel-vervolgkeuzelijst te maken met behulp van de OFFSET-functie:

• Selecteer een cel waar u de vervolgkeuzelijst wilt maken (cel C2 in dit voorbeeld).
• Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
• Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen de optie Lijst als de Validatiecriteria.
  • Zodra u Lijst selecteert, verschijnt het bronveld.
• Voer in het veld Bron de volgende formule in: =OFFSET($A$2,0,0,5)
  • Zorg ervoor dat de vervolgkeuzelijst In-cel is aangevinkt.
• Klik OK.

Hiermee wordt een vervolgkeuzelijst gemaakt met alle fruitnamen (zoals hieronder weergegeven).

Opmerking: Als u in één keer een vervolgkeuzelijst in meerdere cellen wilt maken, selecteert u alle cellen waar u deze wilt maken en volgt u de bovenstaande stappen. Zorg ervoor dat de celverwijzingen absoluut zijn (zoals $A$2) en niet relatief (zoals A2, of A$2, of $A2).

Hoe werkt deze formule??

In het bovenstaande geval hebben we een OFFSET-functie gebruikt om de vervolgkeuzelijst te maken. Het retourneert een lijst met items uit de ra

Het retourneert een lijst met items uit het bereik A2:A6.

Hier is de syntaxis van de OFFSET-functie: =OFFSET(referentie, rijen, cols, [hoogte], [breedte])

Er zijn vijf argumenten nodig, waarbij we de referentie hebben gespecificeerd als A2 (het startpunt van de lijst). Rijen/Kollen worden gespecificeerd als 0 omdat we de referentiecel niet willen verschuiven. Hoogte wordt gespecificeerd als 5 omdat er vijf elementen in de lijst staan.

Als u nu deze formule gebruikt, wordt een matrix geretourneerd met de lijst van de vijf vruchten in A2:A6. Merk op dat als u de formule in een cel invoert, deze selecteert en op F9 drukt, u zou zien dat deze een reeks fruitnamen retourneert.

Een dynamische vervolgkeuzelijst maken in Excel (met OFFSET)

De bovenstaande techniek om een ​​formule te gebruiken om een ​​vervolgkeuzelijst te maken, kan worden uitgebreid om ook een dynamische vervolgkeuzelijst te maken. Als u de OFFSET-functie gebruikt, zoals hierboven weergegeven, wordt de vervolgkeuzelijst niet automatisch bijgewerkt, zelfs als u meer items aan de lijst toevoegt. Elke keer dat u de lijst wijzigt, moet u deze handmatig bijwerken.

Hier is een manier om het dynamisch te maken (en het is niets anders dan een kleine aanpassing in de formule):

• Selecteer een cel waar u de vervolgkeuzelijst wilt maken (cel C2 in dit voorbeeld).
• Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
• Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen de optie Lijst als de Validatiecriteria. Zodra u Lijst selecteert, verschijnt het bronveld.
• Voer in het bronveld de volgende formule in: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,””))
• Zorg ervoor dat de vervolgkeuzelijst In-cel is aangevinkt.
• Klik OK.

In deze formule heb ik argument 5 vervangen door AANTAL.ALS($A$2:$A$100,””).

De AANTAL.ALS-functie telt de niet-lege cellen in het bereik A2:A100. Daarom past de OFFSET-functie zichzelf aan om alle niet-lege cellen op te nemen.

Opmerking:

• Om dit te laten werken, mogen er GEEN lege cellen tussen de gevulde cellen staan.
• Als u in één keer een vervolgkeuzelijst in meerdere cellen wilt maken, selecteert u alle cellen waar u deze wilt maken en volgt u de bovenstaande stappen. Zorg ervoor dat de celverwijzingen absoluut zijn (zoals $A$2) en niet relatief (zoals A2, of A$2, of $A2).

Kopieer vervolgkeuzelijsten in Excel plakken

U kunt de cellen met gegevensvalidatie naar andere cellen kopiëren en plakken, en de gegevensvalidatie wordt ook gekopieerd.

Als u bijvoorbeeld een vervolgkeuzelijst in cel C2 heeft en deze ook op C3:C6 wilt toepassen, kopieert u eenvoudig cel C2 en plakt u deze in C3:C6. Hiermee wordt de vervolgkeuzelijst gekopieerd en beschikbaar gemaakt in C3:C6 (samen met de vervolgkeuzelijst wordt ook de opmaak gekopieerd).

Als u alleen de vervolgkeuzelijst wilt kopiëren en niet de opmaak, volgen hier de stappen:

• Kopieer de cel met de vervolgkeuzelijst.
• Selecteer de cellen waarnaar u de vervolgkeuzelijst wilt kopiëren.
• Ga naar Home -> Plakken -> Plakken speciaal.
• Selecteer in het dialoogvenster Plakken speciaal de optie Validatie in Plakken-opties.
• Klik OK.

Hiermee wordt alleen de vervolgkeuzelijst gekopieerd en niet de opmaak van de gekopieerde cel.

Let op bij het werken met Excel-vervolgkeuzelijst

U moet voorzichtig zijn wanneer u met vervolgkeuzelijsten in Excel werkt.

Wanneer u een cel (die geen vervolgkeuzelijst bevat) kopieert over een cel die een vervolgkeuzelijst bevat, gaat de vervolgkeuzelijst verloren.

Het ergste hiervan is dat Excel geen waarschuwing of prompt toont om de gebruiker te laten weten dat een vervolgkeuzelijst zal worden overschreven.

Hoe alle cellen te selecteren die een vervolgkeuzelijst bevatten

Soms is het moeilijk om te weten welke cellen de vervolgkeuzelijst bevatten.

Daarom is het logisch om deze cellen te markeren door ze een duidelijke rand of een achtergrondkleur te geven.

In plaats van handmatig alle cellen te controleren, is er een snelle manier om alle cellen met vervolgkeuzelijsten (of een regel voor gegevensvalidatie) te selecteren.

• Ga naar Home -> Zoek & Selecteer -> Ga naar Speciaal.
• Selecteer in het dialoogvenster Ga naar speciaal Gegevensvalidatie
  • Gegevensvalidatie heeft twee opties: Alles en Zelfde. Allen zouden alle cellen selecteren waarop een regel voor gegevensvalidatie is toegepast. Hetzelfde zou alleen die cellen selecteren die dezelfde gegevensvalidatieregel hebben als die van de actieve cel.
• Klik OK.

Dit zou onmiddellijk alle cellen selecteren waarop een regel voor gegevensvalidatie is toegepast (dit omvat ook vervolgkeuzelijsten).

Nu kun je de cellen eenvoudig opmaken (geef een rand of een achtergrondkleur) zodat ze visueel zichtbaar zijn en je niet per ongeluk een andere cel erop kopieert.

Hier is nog een techniek van Jon Acampora die u kunt gebruiken om het pictogram met de vervolgkeuzepijl altijd zichtbaar te houden. U kunt ook enkele manieren zien om dit te doen in deze video van Mr. Excel.

Een afhankelijke/voorwaardelijke Excel-vervolgkeuzelijst maken

Hier is een video over het maken van een afhankelijke vervolgkeuzelijst in Excel.

Als je liever leest dan een video bekijkt, blijf dan lezen.

Soms heeft u meer dan één vervolgkeuzelijst en wilt u dat de items die in de tweede vervolgkeuzelijst worden weergegeven, afhankelijk zijn van wat de gebruiker in de eerste vervolgkeuzelijst heeft geselecteerd.

Dit worden afhankelijke of voorwaardelijke vervolgkeuzelijsten genoemd.

Hieronder ziet u een voorbeeld van een voorwaardelijke/afhankelijke vervolgkeuzelijst:

In het bovenstaande voorbeeld, wanneer de items vermeld in 'Drop Down 2' afhankelijk zijn van de selectie gemaakt in 'Drop Down 1'.

Laten we nu kijken hoe we dit kunnen maken.

Hier zijn de stappen om een ​​afhankelijke / voorwaardelijke vervolgkeuzelijst in Excel te maken:

• Selecteer de cel waar u de eerste (hoofd) vervolgkeuzelijst wilt.
• Ga naar Gegevens -> Gegevensvalidatie. Dit opent het dialoogvenster voor gegevensvalidatie.
• Selecteer Lijst in het dialoogvenster voor gegevensvalidatie op het tabblad Instellingen.
• Geef in het veld Bron het bereik op dat de items bevat die in de eerste vervolgkeuzelijst moeten worden weergegeven.
• Klik OK. Hiermee wordt de vervolgkeuzelijst 1 gemaakt.
• Selecteer de volledige dataset (A1:B6 in dit voorbeeld).
• Ga naar Formules -> Gedefinieerde namen -> Maken vanuit selectie (of je kunt de sneltoets Control + Shift + F3) gebruiken.
• Vink in het dialoogvenster 'Benoemd uit selectie maken' de optie Bovenste rij aan en schakel alle andere uit. Hierdoor ontstaan ​​er 2 namenreeksen (‘Fruits’ en ‘Groenten’). Vruchten genoemd assortiment verwijst naar alle soorten fruit in de lijst en Groenten genoemd assortiment verwijst naar alle groenten in de lijst.
• Klik OK.
• Selecteer de cel waar u de Afhankelijke/Voorwaardelijke Vervolgkeuzelijst wilt hebben (E3 in dit voorbeeld).
• Ga naar Gegevens -> Gegevensvalidatie.
• Zorg ervoor dat Lijst in is geselecteerd in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen.
• Voer in het veld Bron de formule =INDIRECT(D3) in. Hier is D3 de cel die de hoofdvervolgkeuzelijst bevat.
• Klik OK.

Wanneer u nu de selectie maakt in Vervolgkeuzelijst 1, worden de opties in Vervolgkeuzelijst 2 automatisch bijgewerkt.

Download het voorbeeldbestand

Hoe werkt dit? - De voorwaardelijke vervolgkeuzelijst (in cel E3) verwijst naar =INDIRECT(D3). Dit betekent dat wanneer u 'Fruit' selecteert in cel D3, de vervolgkeuzelijst in E3 verwijst naar het genoemde bereik 'Fruit' (via de INDIRECT-functie) en dus alle items in die categorie vermeldt.

Belangrijke opmerking tijdens het werken met voorwaardelijke vervolgkeuzelijsten in Excel:

• Als u de selectie hebt gemaakt en vervolgens de bovenliggende vervolgkeuzelijst wijzigt, verandert de afhankelijke vervolgkeuzelijst niet en zou daarom een ​​verkeerde invoer zijn. Als u bijvoorbeeld de VS als land selecteert en vervolgens Florida als staat selecteert en vervolgens teruggaat en het land wijzigt in India, blijft de staat Florida. Hier is een geweldige tutorial van Debra over het wissen van afhankelijke (voorwaardelijke) vervolgkeuzelijsten in Excel wanneer de selectie wordt gewijzigd.
• Als de hoofdcategorie uit meer dan één woord bestaat (bijvoorbeeld 'Seizoensfruit' in plaats van 'Fruit'), moet u de formule =INDIRECT(SUBSTITUTE(D3," ","_")) gebruiken in plaats van de eenvoudige INDIRECT-functie hierboven weergegeven. De reden hiervoor is dat Excel geen spaties in benoemde bereiken toestaat. Dus wanneer u een benoemd bereik maakt met meer dan één woord, voegt Excel automatisch een onderstrepingsteken in tussen woorden. Dus het bereik van 'Seasonal Fruits' zou 'Seasonal_Fruits' zijn. Het gebruik van de SUBSTITUTE-functie binnen de INDIRECT-functie zorgt ervoor dat spaties zijn omgezet in onderstrepingstekens.