Come creare un elenco a discesa in Excel (l'unica guida di cui hai bisogno)

Un elenco a discesa è un ottimo modo per offrire all'utente un'opzione per selezionare da un elenco predefinito.

Può essere utilizzato durante la compilazione di un modulo da parte di un utente o durante la creazione di dashboard interattivi di Excel.

Gli elenchi a discesa sono abbastanza comuni su siti Web/app e sono molto intuitivi per l'utente.

Guarda il video - Creazione di un elenco a discesa in Excel

In questo tutorial imparerai come creare un elenco a discesa in Excel (ci vogliono solo pochi secondi per farlo) insieme a tutte le cose fantastiche che puoi fare con esso.

Come creare un elenco a discesa in Excel

In questa sezione imparerai i passaggi esatti per creare un elenco a discesa di Excel:

1. Utilizzo dei dati dalle celle.
2. Inserimento manuale dei dati.
3. Utilizzando la formula OFFSET.

#1 Utilizzo dei dati dalle celle

Supponiamo che tu abbia un elenco di elementi come mostrato di seguito:

Ecco i passaggi per creare un elenco a discesa di Excel:

1. Seleziona una cella in cui desideri creare l'elenco a discesa.
2. Vai su Dati -> Strumenti dati -> Convalida dati.
3. Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco come Criteri di convalida.
   • Non appena si seleziona Elenco, viene visualizzato il campo di origine.
4. Nel campo sorgente, inserisci =$A$2:$A$6, oppure fai semplicemente clic nel campo Sorgente e seleziona le celle usando il mouse e fai clic su OK. Questo inserirà un elenco a discesa nella cella C2.
   • Assicurati che l'opzione a discesa In-cell sia selezionata (che è selezionata per impostazione predefinita). Se questa opzione non è selezionata, la cella non mostra un menu a discesa, tuttavia, puoi inserire manualmente i valori nell'elenco.

Nota: Se desideri creare elenchi a discesa in più celle contemporaneamente, seleziona tutte le celle in cui desideri crearlo e quindi segui i passaggi precedenti. Assicurati che i riferimenti di cella siano assoluti (come $A$2) e non relativi (come A2 o A$2 o $A2).

#2 Inserendo i dati manualmente

Nell'esempio precedente, i riferimenti di cella vengono utilizzati nel campo Origine. Puoi anche aggiungere elementi direttamente inserendoli manualmente nel campo sorgente.

Ad esempio, supponiamo che tu voglia mostrare due opzioni, Sì e No, nel menu a discesa in una cella. Ecco come puoi inserirlo direttamente nel campo della fonte di convalida dei dati:

• Seleziona una cella in cui desideri creare l'elenco a discesa (cella C2 in questo esempio).
• Vai su Dati -> Strumenti dati -> Convalida dati.
• Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco come Criteri di convalida.
  • Non appena si seleziona Elenco, viene visualizzato il campo di origine.
• Nel campo della fonte, inserisci Sì, No
  • Assicurati che l'opzione a discesa In-cell sia selezionata.
• Fare clic su OK.

Questo creerà un elenco a discesa nella cella selezionata. Tutti gli elementi elencati nel campo sorgente, separati da una virgola, sono elencati in righe diverse nel menu a discesa.

Tutti gli elementi inseriti nel campo sorgente, separati da una virgola, vengono visualizzati in righe diverse nell'elenco a discesa.

Nota: Se desideri creare elenchi a discesa in più celle contemporaneamente, seleziona tutte le celle in cui desideri crearlo e quindi segui i passaggi precedenti.

#3 Utilizzo delle formule di Excel

Oltre a selezionare dalle celle e inserire i dati manualmente, puoi anche utilizzare una formula nel campo di origine per creare un elenco a discesa di Excel.

Qualsiasi formula che restituisce un elenco di valori può essere utilizzata per creare un elenco a discesa in Excel.

Ad esempio, supponiamo di avere il set di dati come mostrato di seguito:

Ecco i passaggi per creare un elenco a discesa di Excel utilizzando la funzione OFFSET:

• Seleziona una cella in cui desideri creare l'elenco a discesa (cella C2 in questo esempio).
• Vai su Dati -> Strumenti dati -> Convalida dati.
• Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco come Criteri di convalida.
  • Non appena si seleziona Elenco, viene visualizzato il campo di origine.
• Nel campo Origine, inserisci la seguente formula: =OFFSET($A$2,0,0,5)
  • Assicurati che l'opzione a discesa In-cell sia selezionata.
• Fare clic su OK.

Questo creerà un elenco a discesa che elenca tutti i nomi dei frutti (come mostrato di seguito).

Nota: Se desideri creare un elenco a discesa in più celle contemporaneamente, seleziona tutte le celle in cui desideri crearlo e quindi segui i passaggi precedenti. Assicurati che i riferimenti di cella siano assoluti (come $A$2) e non relativi (come A2 o A$2 o $A2).

Come funziona questa formula??

Nel caso precedente, abbiamo utilizzato una funzione OFFSET per creare l'elenco a discesa. Restituisce un elenco di elementi dal ra

Restituisce un elenco di elementi dell'intervallo A2:A6.

Ecco la sintassi della funzione OFFSET: =OFFSET(riferimento, righe, colonne, [altezza], [larghezza])

Richiede cinque argomenti, dove abbiamo specificato il riferimento come A2 (il punto di partenza dell'elenco). Righe/Col sono specificati come 0 poiché non vogliamo compensare la cella di riferimento. L'altezza è specificata come 5 poiché ci sono cinque elementi nell'elenco.

Ora, quando usi questa formula, restituisce un array che ha l'elenco dei cinque frutti in A2: A6. Nota che se inserisci la formula in una cella, selezionala e premi F9, vedresti che restituisce una matrice dei nomi dei frutti.

Creazione di un elenco a discesa dinamico in Excel (utilizzando OFFSET)

La suddetta tecnica di utilizzo di una formula per creare un elenco a discesa può essere estesa anche per creare un elenco a discesa dinamico. Se utilizzi la funzione OFFSET, come mostrato sopra, anche se aggiungi più elementi all'elenco, il menu a discesa non si aggiornerà automaticamente. Dovrai aggiornarlo manualmente ogni volta che cambi l'elenco.

Ecco un modo per renderlo dinamico (e non è altro che un piccolo ritocco nella formula):

• Seleziona una cella in cui desideri creare l'elenco a discesa (cella C2 in questo esempio).
• Vai su Dati -> Strumenti dati -> Convalida dati.
• Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco come Criteri di convalida. Non appena si seleziona Elenco, viene visualizzato il campo di origine.
• Nel campo sorgente, inserisci la seguente formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,””))
• Assicurati che l'opzione a discesa In-cell sia selezionata.
• Fare clic su OK.

In questa formula, ho sostituito l'argomento 5 con COUNTIF($A$2:$A$100,””).

La funzione CONTA.SE conta le celle non vuote nell'intervallo A2:A100. Quindi, la funzione OFFSET si regola per includere tutte le celle non vuote.

Nota:

• Perché funzioni, NON devono esserci celle vuote tra le celle riempite.
• Se desideri creare un elenco a discesa in più celle contemporaneamente, seleziona tutte le celle in cui desideri crearlo e quindi segui i passaggi precedenti. Assicurati che i riferimenti di cella siano assoluti (come $A$2) e non relativi (come A2 o A$2 o $A2).

Copia incolla elenchi a discesa in Excel

Puoi copiare incollare le celle con la convalida dei dati su altre celle e copierà anche la convalida dei dati.

Ad esempio, se hai un elenco a discesa nella cella C2 e desideri applicarlo anche a C3:C6, copia semplicemente la cella C2 e incollala in C3:C6. Questo copierà l'elenco a discesa e lo renderà disponibile in C3: C6 (insieme al menu a discesa, copierà anche la formattazione).

Se vuoi solo copiare il menu a discesa e non la formattazione, ecco i passaggi:

• Copia la cella con il menu a discesa.
• Seleziona le celle in cui desideri copiare il menu a discesa.
• Vai a Home -> Incolla -> Incolla speciale.
• Nella finestra di dialogo Incolla speciale, seleziona Convalida nelle opzioni Incolla.
• Fare clic su OK.

Questo copierà solo il menu a discesa e non la formattazione della cella copiata.

Attenzione durante il lavoro con l'elenco a discesa di Excel

Devi stare attento quando lavori con elenchi a discesa in Excel.

Quando si copia una cella (che non contiene un elenco a discesa) su una cella che contiene un elenco a discesa, l'elenco a discesa viene perso.

La parte peggiore di questo è che Excel non mostrerà alcun avviso o richiesta per far sapere all'utente che un menu a discesa verrà sovrascritto.

Come selezionare tutte le celle che contengono un elenco a discesa

A volte, è difficile sapere quali celle contengono l'elenco a discesa.

Quindi, ha senso contrassegnare queste celle assegnandole un bordo distinto o un colore di sfondo.

Invece di controllare manualmente tutte le celle, esiste un modo rapido per selezionare tutte le celle che contengono elenchi a discesa (o qualsiasi regola di convalida dei dati).

• Vai a Home -> Trova e seleziona -> Vai a Speciale.
• Nella finestra di dialogo Vai a speciale, seleziona Convalida dati
  • La convalida dei dati ha due opzioni: Tutto e Uguale. Tutto selezionerebbe tutte le celle a cui è applicata una regola di convalida dei dati. Stesso selezionerebbe solo quelle celle che hanno la stessa regola di convalida dei dati di quella della cella attiva.
• Fare clic su OK.

Ciò selezionerebbe istantaneamente tutte le celle a cui è applicata una regola di convalida dei dati (questo include anche gli elenchi a discesa).

Ora puoi semplicemente formattare le celle (dare un bordo o un colore di sfondo) in modo che siano visivamente visibili e non copi accidentalmente un'altra cella su di essa.

Ecco un'altra tecnica di Jon Acampora che puoi usare per mantenere sempre visibile l'icona della freccia a discesa. Puoi anche vedere alcuni modi per farlo in questo video di Mr. Excel.

Creazione di un elenco a discesa Excel dipendente / condizionale

Ecco un video su come creare un elenco a discesa dipendente in Excel.

Se preferisci leggere piuttosto che guardare un video, continua a leggere.

A volte, potresti avere più di un elenco a discesa e desideri che gli elementi visualizzati nel secondo menu a discesa dipendano da ciò che l'utente ha selezionato nel primo menu a discesa.

Questi sono chiamati elenchi a discesa dipendenti o condizionali.

Di seguito è riportato un esempio di elenco a discesa condizionale/dipendente:

Nell'esempio sopra, quando gli elementi elencati in "Drop Down 2" dipendono dalla selezione effettuata in "Drop Down 1".

Ora vediamo come crearlo.

Ecco i passaggi per creare un elenco a discesa dipendente / condizionale in Excel:

• Seleziona la cella in cui desideri visualizzare il primo elenco a discesa (principale).
• Vai su Dati -> Convalida dati. Questo aprirà la finestra di dialogo di convalida dei dati.
• Nella finestra di dialogo di convalida dei dati, all'interno della scheda delle impostazioni, selezionare Elenco.
• Nel campo Origine, specificare l'intervallo che contiene gli elementi che devono essere visualizzati nel primo elenco a discesa.
• Fare clic su OK. Questo creerà il menu a discesa 1.
• Selezionare l'intero set di dati (A1:B6 in questo esempio).
• Vai su Formule -> Nomi definiti -> Crea da selezione (oppure puoi usare la scorciatoia da tastiera Ctrl + Maiusc + F3).
• Nella finestra di dialogo "Crea nome dalla selezione", seleziona l'opzione Riga superiore e deseleziona tutte le altre. In questo modo vengono creati 2 intervalli di nomi ("Frutta" e "Verdura"). L'intervallo denominato Frutta si riferisce a tutti i frutti nell'elenco e L'intervallo denominato Verdura si riferisce a tutte le verdure nell'elenco.
• Fare clic su OK.
• Selezionare la cella in cui si desidera l'elenco a discesa Dipendente/Condizionale (E3 in questo esempio).
• Vai su Dati -> Convalida dati.
• Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, assicurati che Elenco sia selezionato.
• Nel campo Origine, inserisci la formula =INDIRETTO(D3). Qui, D3 è la cella che contiene il menu a discesa principale.
• Fare clic su OK.

Ora, quando effettui la selezione nel menu a discesa 1, le opzioni elencate nell'elenco a discesa 2 si aggiorneranno automaticamente.

Scarica il file di esempio

Come funziona? - L'elenco a discesa condizionale (nella cella E3) fa riferimento a =INDIRECT(D3). Ciò significa che quando selezioni "Frutti" nella cella D3, l'elenco a discesa in E3 fa riferimento all'intervallo denominato "Frutti" (tramite la funzione INDIRETTO) e quindi elenca tutti gli elementi in quella categoria.

Nota importante durante l'utilizzo di elenchi a discesa condizionali in Excel:

• Dopo aver effettuato la selezione e quindi modificato il menu a discesa padre, il menu a discesa dipendente non cambierebbe e, pertanto, sarebbe una voce errata. Ad esempio, se si selezionano gli Stati Uniti come paese e poi si seleziona Florida come stato, quindi si torna indietro e si cambia il paese in India, lo stato rimarrebbe come Florida. Ecco un ottimo tutorial di Debra sulla cancellazione degli elenchi a discesa dipendenti (condizionali) in Excel quando la selezione viene modificata.
• Se la categoria principale è più di una parola (ad esempio, 'Frutta di stagione' invece di 'Frutta'), è necessario utilizzare la formula =INDIRETTO(SOSTITUTO(D3,” “,”_”)), invece della semplice funzione INDIRETTA mostrata sopra. Il motivo è che Excel non consente spazi negli intervalli denominati. Pertanto, quando crei un intervallo denominato utilizzando più di una parola, Excel inserisce automaticamente un carattere di sottolineatura tra le parole. Quindi l'intervallo denominato "Frutta stagionale" sarebbe "Frutta_stagionale". L'utilizzo della funzione SOSTITUISCI all'interno della funzione INDIRETTO assicura che gli spazi sono convertito in caratteri di sottolineatura.