Comment mettre à jour automatiquement une liste déroulante dans Excel ?
Les listes déroulantes sont couramment utilisées dans Excel pour standardiser et optimiser la saisie de données, notamment dans les rapports quotidiens, la gestion d’inventaire ou le classement d’informations. Toutefois, de nombreux utilisateurs butent sur une limitation bien connue : lorsqu’on ajoute de nouveaux éléments juste en dessous de la plage source d’origine, la liste déroulante ne les intègre pas automatiquement. Par défaut, Excel ne reconnaît que la plage initialement définie ; ainsi, toute nouvelle entrée située en dehors de cette zone reste invisible dans la liste. Heureusement, Excel propose plusieurs méthodes pour créer une liste déroulante dynamique, qui s’agrandit automatiquement dès l’ajout de nouvelles données.
Ce guide présente des méthodes pratiques pour implémenter une liste déroulante à mise à jour automatique dans Excel, réduisant ainsi les efforts de maintenance et les risques d’erreurs de saisie, notamment dans les tableaux et listes qui s’allongent régulièrement.
- Formule Excel – Mettre à jour automatiquement une liste déroulante à l’aide d’une formule
- Tableau Excel – Utiliser un tableau comme source de la liste déroulante (extension automatique avec les nouveaux éléments)
- Code VBA – Utiliser VBA pour mettre à jour automatiquement la liste déroulante Plage source
Mettre à jour automatiquement une liste déroulante à l’aide d’une formule
Il existe plusieurs cas où vous avez besoin que votre liste déroulante se mette à jour automatiquement — par exemple, pour maintenir une liste de produits à jour, gérer les inscriptions à un formulaire ou suivre des tâches de projet régulièrement modifiées. Cette méthode utilise la fonction OFFSET pour créer une plage dynamique, permettant ainsi à votre liste déroulante d’intégrer automatiquement tous les éléments dès que vous ajoutez de nouvelles entrées dans une colonne.
1. Sélectionnez la cellule dans laquelle vous souhaitez insérer la liste déroulante, puis accédez à Données > Validation des données > Validation des données. Voir la capture d’écran :
2. Dans la boîte de dialogue Validation des données, rendez-vous à l’onglet Paramètres, sélectionnez Liste dans les options d’Autoriser, puis saisissez la formule de plage dynamique ci-dessous dans le champ Source :
=DECALER($A$2;0;0;NBVAL(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 cette référence pour qu’elle corresponde à la première cellule de votre liste réelle.
- A:A fait référence à la colonne entière contenant vos données de liste. Cette configuration garantit que, dès que vous ajoutez de nouveaux éléments dans cette colonne, la fonction recalcule automatiquement la taille de la plage.
- Si votre colonne contient des cellules vides ou des sous-titres, vous devrez peut-être ajuster la formule ou veiller à la cohérence du placement de vos données afin d’éviter des éléments vides dans votre liste déroulante.
- Pour les grands jeux de données, gardez à l’esprit que les fonctions volatiles comme OFFSET peuvent légèrement nuire aux performances, puisqu’elles se recalculent à chaque modification.
3. Cliquez sur OK. Vous venez de créer une liste déroulante qui se met à jour automatiquement dès que de nouvelles données sont ajoutées dans la colonne d’origine. Dès que vous saisissez de nouveaux éléments dans la plage concernée, ils apparaissent instantanément comme options sélectionnables dans votre liste déroulante.
Dépannage et conseils :
- Si la liste déroulante affiche des entrées vides inattendues, vérifiez la présence d’espaces superflus ou de lignes masquées dans votre colonne source.
- Si la formule renvoie une erreur, vérifiez que vos données ne contiennent ni plages non contiguës ni colonnes entièrement vides.
- N’oubliez pas d’adapter votre formule source si votre liste commence à une autre ligne que la ligne 2, en modifiant à la fois la référence de cellule et COUNTA(A:A) en conséquence.
Utiliser un tableau comme source de la liste déroulante (extension automatique avec les nouveaux éléments)
Utiliser un tableau Excel comme Plage source de votre liste déroulante constitue une approche efficace et conviviale pour les débutants. Les tableaux Excel s’élargissent automatiquement dès que de nouveaux éléments sont ajoutés, ce qui permet à la liste déroulante de rester à jour sans nécessiter d’ajustement manuel des références de plage ou des formules.
Cette méthode convient particulièrement aux utilisateurs qui gèrent des listes en constante évolution, telles que des fiches de personnel, des inventaires ou des listes d’inscriptions à des événements. Son principal avantage réside dans sa simplicité et sa fiabilité pour maintenir des listes actualisées, mais notez qu’elle fonctionne mieux lorsque la Données source se trouve sur la même feuille ou dans le même classeur, car les tableaux ne prennent pas en charge les références inter-classeurs dans la validation des données.
1. Sélectionnez votre plage de données source (par exemple,)A2:A6).
2. Accédez à l’onglet Insertion et choisissez Tableau. Cochez la case « Mon tableau comporte des en-têtes » si votre liste inclut des en-têtes.
3. Excel applique le format Tableau à votre plage. Par défaut, celle-ci pourrait être nommée Tableau1(vous pouvez consulter ou renommer ce tableau depuis l’onglet)Conception de tableau, à l’aide du champ « Nom du tableau » situé à gauche).
4. Cliquez sur la cellule où vous souhaitez insérer la liste déroulante, puis accédez à Données > Validation des données.
5. Sélectionnez l’option Liste dans le menu déroulant Autoriser, puis saisissez, dans le champ Source, une référence vers la colonne de votre tableau, par exemple :
=INDIRECT("Table1[Column1]")6. Cliquez sur OK. Désormais, chaque fois que vous ajouterez de nouvelles données sous le tableau, la colonne et la liste déroulante seront automatiquement mises à jour pour inclure ces nouvelles entrées.
Remarque et conseils :
- Les tableaux Excel offrent une plage structurée qui s’agrandit ou se réduit automatiquement selon les modifications apportées aux données, ce qui les rend idéaux pour les listes appelées à évoluer fréquemment.
- Si vous devez faire référence à votre liste déroulante depuis une autre feuille, utilisez
=INDIRECT("Table1[Column1]"), car certaines versions d’Excel limitent les références directes aux tableaux dans la validation des données à la feuille de calcul active. - Cette approche évite les valeurs vides dans la liste déroulante à condition que votre liste ne contienne que des entrées non vides.
Utiliser VBA pour mettre à jour automatiquement la liste déroulante Plage source
Pour des scénarios avancés et entièrement automatisés — notamment avec des listes longues ou lors de la maintenance automatisée de classeurs —, vous pouvez utiliser du code VBA pour mettre à jour automatiquement la plage utilisée par votre liste déroulante dès l’ajout de nouvelles données. Cette solution s’avère particulièrement utile dans les environnements complexes où plusieurs listes déroulantes doivent refléter des sources en constante évolution, ou encore lorsque vous gérez des listes déroulantes destinées à plusieurs utilisateurs.
1. Appuyez sur Alt+F11 pour ouvrir l’éditeur VBA, puis double-cliquez sur la feuille contenant votre validation des données dans le projet VBA.
2. Copiez-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. Fermez ensuite la fenêtre de code. À chaque ajout de données dans 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ù la liste déroulante existe)
- Le code s’exécute automatiquement dès qu’une modification est apportée à la feuille de calcul
- Il détecte la Dernière ligne contenant des données et met à jour en conséquence la plage de validation
- Assurez-vous d’activer les macros pour que cela fonctionne
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Articles associés :
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels