Une liste déroulante dans Excel évite les variantes de saisie, comme « Carte », « carte » ou une faute de frappe dans un formulaire partagé. Mais lorsque la source de données s’allonge, une plage fixe exclut les nouvelles options et oblige à modifier la validation manuellement.
Pour une liste de types de paiement, par exemple, l’ajout de « Bitcoin » devrait actualiser le menu déroulant sans nouvelle manipulation. Le choix entre tableau Excel, plage nommée et formule dynamique dépend surtout de la simplicité recherchée et de la structure du classeur.
A retenir :
- Une source extensible pour accueillir les nouvelles valeurs sans intervention répétée
- Un tableau structuré, généralement plus simple à maintenir au quotidien
- Une validation des données claire pour limiter les erreurs de saisie
- Des tests après modification pour repérer les valeurs manquantes
Mettre à jour une liste déroulante Excel avec une plage dynamique
Comprendre la limite d’une source fixe
Une plage telle que =$B$5:$B$10 contient six cellules précises, même si de nouvelles options sont saisies juste en dessous. Dans un suivi de paiements, « Bitcoin » ajouté en B11 restera absent du menu déroulant tant que la source n’aura pas été étendue.
Selon l’aide Microsoft sur la validation des données, cette fonction permet de limiter les valeurs saisies dans une cellule. Elle ne rend toutefois pas automatiquement extensible une plage ordinaire : c’est la définition de la source qui détermine les éléments proposés.
Utiliser DECALER et NBVAL avec précaution
Pour une liste courte et bien rangée, une formule dynamique peut calculer la hauteur de la plage à partir des cellules remplies. Dans Excel en français, une source possible est =DECALER($B$5;0;0;NBVAL($B$5:$B$1000);1), si les options commencent en B5.
La formule suppose que les valeurs sont continues, sans ligne vide au milieu, et que la zone de comptage ne contient que les options. Une cellule oubliée ou un titre inclus dans la plage peut fausser le résultat ; vérifiez la liste après chaque changement de structure.
Méthodes selon le classeur :
- Plage fixe pour une liste stable et rarement modifiée
- DECALER et NBVAL pour une plage continue qui s’allonge
- Tableau structuré pour des ajouts réguliers et faciles à suivre
| Méthode | Comportement | Point de vigilance |
|---|---|---|
| Plage fixe | Les cellules indiquées restent la source | Étendre la référence à la main |
| DECALER et NBVAL | La hauteur suit les cellules remplies | Éviter les cellules vides dans la liste |
| Tableau Excel | Les lignes ajoutées prolongent le tableau | Référencer correctement sa colonne |
| Plage nommée | Un nom désigne la source de données | Contrôler la référence associée |
Pour la plupart des fichiers partagés, un tableau structuré réduit les réglages manuels ; le paramétrage de la validation mérite donc un examen tout aussi attentif.
Créer une liste déroulante dynamique avec un tableau Excel
Transformer la source en tableau structuré
Cette méthode convient quand les moyens de paiement évoluent au fil du temps. Sélectionnez les options, créez un tableau depuis l’onglet Insertion, puis donnez-lui un nom explicite, par exemple TableauPaiements.
Selon Microsoft Support, les tableaux Excel peuvent s’étendre lorsque des données sont ajoutées à leur suite. Cette organisation garde les valeurs regroupées et facilite leur maintenance, notamment lorsque plusieurs personnes alimentent le même classeur.
Relier la validation à une plage nommée
La validation des données n’accepte pas toujours directement une référence structurée dans le champ Source, selon la configuration. Une solution robuste consiste à définir une plage nommée, par exemple Paiements, qui fait référence à la colonne des types de paiement.
Dans la cellule destinataire, ouvrez Données, puis Validation des données, choisissez Liste et indiquez =Paiements comme source. Selon la documentation Microsoft sur les références structurées, les noms de tableaux et de colonnes aident à suivre les données ; testez néanmoins le résultat dans votre version d’Excel.
Contrôles après création :
- Ajouter une option en bas du tableau source
- Vérifier son apparition dans le menu déroulant
- Tester une cellule déjà soumise à la validation
- Confirmer que les titres ne figurent pas parmi les choix
| Action de contrôle | Résultat attendu |
|---|---|
| Ajouter « Bitcoin » au tableau | La nouvelle valeur rejoint les choix disponibles |
| Ouvrir la liste dans la cellule cible | Les options apparaissent sans valeur d’en-tête |
| Saisir une valeur non prévue | L’alerte dépend des réglages de validation |
| Renommer la plage | La source de validation reste cohérente |
Un tableau associé à une plage nommée apporte une automatisation pratique sans macro ; pour des choix liés entre eux, la formule INDIRECT ouvre une autre possibilité.
Fiabiliser la mise à jour et les listes dépendantes
Employer INDIRECT pour des choix conditionnels
Une liste dépendante adapte ses choix à une première réponse : une région peut déterminer les villes proposées, ou un mode de paiement afficher des options associées. Pour cela, créez des plages nommées correspondant aux catégories, puis utilisez =INDIRECT(A2) dans la source de la seconde liste.
Le texte en A2 doit correspondre au nom de la plage appelée. Les espaces et caractères spéciaux compliquent cette correspondance ; des noms simplifiés avec des traits de soulignement, ou une formule de remplacement adaptée, limitent les erreurs.
Choisir entre formule et macro VBA
Pour actualiser uniquement les valeurs proposées, le tableau structuré ou la plage dynamique suffit généralement. VBA répond à un besoin différent : afficher une zone de liste ActiveX et proposer des suggestions pendant la saisie, avec des réglages supplémentaires propres au classeur.
Cette automatisation exige d’activer les macros et de vérifier le code sur la feuille concernée. Dans un fichier destiné à plusieurs collègues, une solution sans macro est souvent plus simple à partager, tandis qu’une liste standard reste plus facile à dépanner.
Vérifications avant partage :
- Nom de tableau ou de plage cohérent avec la validation
- Absence de lignes vides dans la source dynamique
- Message d’erreur utile pour les saisies non autorisées
- Test de la liste après ajout et suppression d’options
Si une option manque, commencez par contrôler la référence Source dans la fenêtre de validation, puis vérifiez les cellules réellement incluses. Cette vérification distingue rapidement une erreur de formule d’une simple plage trop courte.
Source : Microsoft Support, « Créer une liste déroulante », documentation Microsoft ; Microsoft Support, documentation sur les tableaux Excel et les références structurées.