Comment créer une liste de validation sur Excel avec une taille flexible

Article mise à jour le: 24 août 2019

Prenez un instant avant de commencer la lecture (merci!) pour découvrir mon nouveau projet: Vous êtes une PME et souhaitez analyser vos données commerciales, sans investir beaucoup ni embaucher un data scientist? DataInsightOut est fait pour vous! Venez en apprendre plus sur le site et consulter le blog.

Commencer l'analyse de données

Lorsque vous travaillez sur Excel, vous devez parfois créer des listes de validation à destination des utilisateurs pour leur proposer un choix prédéfini de valeurs à entrer pour compléter notamment un tableau. Le problème de la mise en forme se pose lorsque la liste des valeurs possibles est variable. 

Si vous sélectionnez une plage plus large que la liste de valeurs possibles à l’instant T, vous aurez un volet déroulant avec de nombreuses lignes blanches et vides. Pas très esthétique et encore moins professionnel.

La solution pour y remédier est expliquée ci dessous.

Tout d’abord, rendons à César ce qui lui appartient, l’astuce a été publiée sur ce site, elle est est donc ré-expliquée dans cet article pour vous permettre de mieux la comprendre, pas à pas car cette fonction est très utile et vous ne pourrez plus vous en passez si vous avez recours fréquemment aux listes de validation sous Excel.

la première étape consiste à créer une liste de valeurs qui seront disponibles pour l’utilisateur. Juste à côté de cette liste, en utilisant la formule =NBVAL(plage_de_données), on va compter le nombre de lignes de la plage en ajoutant les cellules vides en dessous – à vous de voir la taille que vous souhaitez.

Très facile jusque là, vraiment rien de très compliqué.

excel liste dynamique1

 

La deuxième étape consiste à aller dans le ruban du menu, de choisir « formules » et dans ce sous-menu de cliquer sur « gestionnaire de noms ». la il vous suffit de faire comme indiqué dans la capture écran ci dessous. Vous allez taper la formule:

=Feuil1!$A$2:DECALER(Feuil1!$A$2;Feuil1!$B$2-1;;)

…n’oubliez pas les deux points-virgule à la fin de la formule.

excel liste dynamique2

 

Une fois que cela est fait, enregistrez: vous pouvez maintenant avancer et créer la liste de validation excel.

Pour faire cela, positionnez-vous sur la cellule où vous souhaitez faire apparaître la liste, allez dans Données>Validation des données comme vous avez sûrement l’habitude de le faire dans vos modélisations.

Dans l’invite de dialogue, choisissez « liste » et dans la source vous allez donner le nom de la plage que vous venez de créer. soit =titre_variable

Si a été parfaitement suivi, cela devrait fonctionner correctement.

Faîte un essai, si vous ajoutez maintenant de nouvelles entrées dans votre liste initiale, vous verrez votre liste s’agrandir automatiquement. Vos utilisateurs pourront disposer d’une mise en forme propre et vous pourrez ajouter de nouvelles valeurs dans la liste sans avoir à revoir systématiquement la taille des cellules.

Voici une sélection de produits spécifiquement sur Excel à retrouver sur Amazon

 

Cliquez, partagez!

Le coin des achats professionnels