Comment mettre à jour automatiquement une liste déroulante dans Excel ?
Les listes déroulantes sont fréquemment utilisées dans Excel pour rendre la saisie de données plus standardisée et efficace, en particulier pour les rapports quotidiens, la sélection d'inventaire et les tâches de classification des données. Cependant, de nombreux utilisateurs rencontrent une limitation courante : lorsque vous ajoutez de nouveaux éléments immédiatement sous la plage source originale, la liste déroulante n'inclut pas automatiquement ces ajouts. Par défaut, Excel ne reconnaît que la plage initialement spécifiée, donc les nouvelles entrées en dehors de cette plage n'apparaissent pas par défaut dans la liste déroulante. Pour remédier à cela, Excel propose plusieurs méthodes pour créer une liste déroulante à mise à jour dynamique qui s'agrandit automatiquement lorsque vous ajoutez de nouvelles données.
Ce guide présente des méthodes pratiques pour implémenter une liste déroulante à mise à jour automatique dans Excel, aidant à réduire les efforts de maintenance et les erreurs potentielles de saisie, en particulier dans les tableaux et listes qui augmentent régulièrement.
- Formule Excel - Mise à jour automatique de la liste déroulante avec une formule
- Tableau Excel - Utiliser un tableau comme source de la liste déroulante (s'étend automatiquement avec de nouveaux éléments)
- Code VBA - Utiliser VBA pour mettre à jour automatiquement la plage source de la liste déroulante
Mise à jour automatique de la liste déroulante avec une formule
Il existe plusieurs scénarios où il est nécessaire que la liste déroulante se mette à jour automatiquement — par exemple, maintenir une liste de produits, gérer les membres dans un formulaire d'inscription ou suivre les tâches de projet qui sont modifiées régulièrement. Cette méthode utilise la fonction OFFSET pour créer une plage dynamique afin que votre liste déroulante puisse inclure automatiquement tous les éléments lorsque vous ajoutez de nouvelles entrées dans une colonne.
1. Sélectionnez la cellule où vous souhaitez insérer la liste déroulante, puis accédez à Données > Validation des données > Validation des données. Voir capture d'écran :
2. Dans la boîte de dialogue Validation des données allez à l'onglet Paramètres, sélectionnez Liste dans le menu Autoriser puis entrez la formule de plage dynamique ci-dessous dans la zone Source :
=OFFSET($A$2,0,0,COUNTA(A:A)-1)
Explication des paramètres et conseils pratiques :
- A2 est la première cellule de votre plage de données prévue. Ajustez-la pour correspondre à la cellule de départ de votre liste réelle.
- A:A fait référence à toute la colonne contenant vos données de liste. Ce paramétrage garantit que lorsque vous ajoutez plus d'éléments dans cette colonne, la fonction recalcule dynamiquement la taille de la plage.
- Si vous avez des cellules vides dans la colonne ou utilisez des sous-titres, vous devrez peut-être ajuster la formule ou assurer la cohérence du placement de vos données pour éviter les éléments vides dans votre liste déroulante.
- Pour les grands ensembles de données, gardez à l'esprit que les fonctions volatiles comme OFFSET peuvent légèrement affecter les performances, car elles se recalculent à chaque modification.
3. Cliquez sur OK. Vous avez maintenant créé une liste déroulante qui se met à jour chaque fois que de nouvelles données sont saisies dans la colonne d'origine. Lorsque vous ajoutez plus d'éléments dans la plage attendue, ils apparaîtront instantanément comme valeurs sélectionnables dans la liste déroulante.
Dépannage et conseils :
- Si la liste déroulante affiche des entrées vides inattendues, vérifiez s'il y a des espaces supplémentaires ou des lignes masquées dans votre colonne source.
- Si la formule retourne une erreur, vérifiez que vos données ne contiennent pas de plages non contiguës ou de colonnes complètement vides.
- N'oubliez pas d'étendre votre formule source si votre liste commence ailleurs que sur la ligne 2, en modifiant à la fois la référence de cellule et COUNTA(A:A) de manière appropriée.
Utiliser un tableau comme source de la liste déroulante (s'étend automatiquement avec de nouveaux éléments)
Utiliser un tableau Excel comme plage source pour votre liste déroulante est une approche efficace et conviviale pour les débutants. Les tableaux Excel s'agrandissent automatiquement à mesure que de nouveaux éléments sont ajoutés, donc la liste déroulante reste à jour sans qu'il soit nécessaire d'ajuster manuellement les références de plage ou les formules.
Cette méthode est particulièrement adaptée aux utilisateurs qui gèrent des listes qui augmentent ou changent fréquemment, comme des listes de salariés, des inventaires ou des feuilles d'inscription à des événements. Le principal avantage est la simplicité et la fiabilité de la maintenance des listes à jour, mais notez que cette approche fonctionne mieux lorsque les données sources sont sur la même feuille ou classeur, car les tableaux ne supportent pas les références inter-classeurs dans la validation des données.
1. Mettez en surbrillance votre plage de données source (par exemple, A2:A6).
2. Allez à l'onglet Insertion et choisissez Tableau. Assurez-vous que la case « Mon tableau a des en-têtes » est cochée si votre liste inclut des en-têtes.
3. Excel va formater votre plage comme un tableau. Par défaut, il pourrait être nommé Tableau1 (vous pouvez vérifier ou renommer le tableau depuis l'onglet Conception du tableau, en utilisant la boîte Nom du tableau sur la gauche).
4. Cliquez sur la cellule où vous avez besoin de la liste déroulante, puis allez à Données > Validation des données.
5. Sélectionnez l'option Liste dans le menu Autoriser, puis dans la zone Source, entrez une référence à la colonne de votre tableau, par exemple :
=INDIRECT("Table1[Column1]")6. Cliquez sur OK. Maintenant, chaque fois que vous ajoutez de nouvelles données sous le tableau, la colonne et la liste déroulante se mettront automatiquement à jour pour inclure les nouvelles entrées.
Notes et conseils :
- Les tableaux Excel fournissent une plage structurée qui s'agrandit et se contracte à mesure que les données changent, ce qui les rend idéaux pour les listes censées changer fréquemment.
- Si vous avez besoin de référencer votre liste déroulante sur une autre feuille, utilisez
=INDIRECT("Tableau1[Colonne1]"), car les références directes aux tableaux dans la validation des données peuvent être limitées à la feuille actuelle dans certaines versions d'Excel. - Cette approche évite les valeurs vides dans la liste déroulante si votre liste ne contient que des entrées non vides.
Utiliser VBA pour mettre à jour automatiquement la plage source de la liste déroulante
Pour des scénarios avancés et automatisés, en particulier lors de la manipulation de longues listes ou de l'automatisation des tâches de maintenance des classeurs, vous pouvez utiliser un code VBA pour mettre à jour automatiquement la plage utilisée dans votre liste déroulante chaque fois que de nouvelles données sont ajoutées. Cela est utile dans des solutions complexes où plusieurs listes déroulantes doivent refléter des listes sources évolutives, ou lors de la gestion de listes déroulantes pour plusieurs utilisateurs.
1. Appuyez sur Alt+F11 pour ouvrir l'éditeur VBA, double-cliquez sur la feuille de calcul où existe votre validation des données dans le VBAProject.
2. Copiez et collez le code suivant dans le module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceColumn As Range
Dim validationCell As Range
Dim lastRow As Long
Set sourceColumn = Me.Range("A:A") ' Change to your source column
If Not Intersect(Target, sourceColumn) Is Nothing Then
Application.EnableEvents = False
lastRow = Me.Cells(Me.Rows.Count, sourceColumn.Column).End(xlUp).Row
Set validationCell = Me.Range("D1:D100") ' Change to your validation cell
With validationCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=$A$1:$A$" & lastRow
End With
Application.EnableEvents = True
End If
End Sub
3. Ensuite, fermez la fenêtre de code. Chaque fois que vous ajoutez des données à votre plage source, la liste déroulante sera mise à jour automatiquement.
- Colonne source ("A:A" où vos données sont ajoutées)
- Cellule/plage de validation ("D1:D100" où se trouve la liste déroulante)
- Le code s'exécute automatiquement lorsqu'une modification est apportée à la feuille de calcul.
- Il trouve la dernière ligne avec des données et met à jour la plage de validation en conséquence.
- Assurez-vous d'activer les macros pour que cela fonctionne.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Articles connexes :
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels