Fin novembre, il faisait déjà nuit noire dehors et j'étais encore à mon bureau, seul avec mon écran. Le silence de l'open space en fin de journée était seulement rompu par le clic régulier de ma souris sur les menus Excel. Je me battais contre un fichier de suivi des ventes où la ville de 'Paris' était écrite de trois façons différentes : 'Paris', 'paris' (sans majuscule) et, le pire de tous, 'Paris ' avec un espace invisible à la fin. Ce petit espace, c'est le diable. Il ruinait tous mes calculs de synthèse et mes tableaux croisés dynamiques ne ressemblaient à rien.
J'ai passé deux heures ce soir-là à nettoyer des données que mes collègues avaient saisies n'importe comment. Ce n'était pas de leur faute, ils n'avaient tout simplement pas de cadre. Excel possède pourtant une puissance de traitement phénoménale — avec ses 1048576 lignes par feuille — mais si la donnée d'entrée est bancale, le résultat est forcément faux. C'est ce soir-là que j'ai décidé que plus personne ne taperait manuellement un nom de ville dans mes fichiers.
Le déclic : la validation de données
Un après-midi pluvieux de janvier, entre deux dossiers urgents, j'ai pris le temps de fouiller dans les onglets que je n'ouvrais jamais. J'ai cliqué par hasard sur l'onglet 'Données' puis sur un petit bouton qui ne paye pas de mine : 'Validation de données'. C'est là que j'ai compris que je pouvais forcer le choix de l'utilisateur. Au lieu de laisser une cellule libre comme un champ sauvage, je pouvais construire une clôture protectrice.
Pour commencer, c'est très simple. Vous sélectionnez la cellule (ou la colonne entière) où vous voulez la liste. Vous allez dans l'onglet Données, vous cliquez sur Validation de données, et dans la fenêtre qui s'ouvre, sous l'onglet 'Options', vous changez 'Tout autoriser' par 'Liste'.
À ce stade, beaucoup de gens font l'erreur de taper les options directement dans la petite case 'Source', séparées par des points-virgules (comme : Paris;Lyon;Marseille). C'est pratique pour un choix binaire comme 'Oui;Non', mais c'est un piège dès que la liste s'allonge. En effet, il existe une limite de caractères pour une liste saisie manuellement dans la validation de données : 255 caractères maximum. Si vous avez vingt villes à lister, vous allez vite vous retrouver coincé. Sans compter que si vous voulez rajouter une ville, il faut retourner dans le menu, recliquer partout... une corvée que l'on finit par oublier.
La méthode de la feuille cachée (plus propre, plus pro)
Après quelques semaines de pratique, j'ai réalisé qu'il fallait séparer le 'moteur' de la 'carrosserie'. J'ai commencé à créer une feuille dédiée, que j'appelle souvent 'LISTES' ou 'DATA', où je range tous mes menus déroulants. C'est un peu comme avoir un tiroir bien rangé pour ses épices au lieu de les laisser traîner sur le plan de travail.
Dans cette feuille, j'écris ma liste de villes dans une colonne. Ensuite, je retourne dans ma feuille principale. Dans la case 'Source' de la validation de données, au lieu de taper du texte, je clique sur la petite flèche et je vais sélectionner ma liste dans l'autre feuille. C'est magique : si je change un nom dans ma feuille 'LISTES', il se met à jour partout dans mon fichier. C'est à ce moment-là que j'ai ressenti cette petite accélération du rythme cardiaque quand j'ai testé ma liste pour la première fois et qu'elle s'affichait exactement comme prévu.
D'ailleurs, si vous commencez à avoir beaucoup de listes, vous aurez peut-être envie de les mettre en valeur. Je vous conseille de jeter un œil sur comment utiliser la mise en forme conditionnelle Excel pour colorer vos cellules afin que les choix importants sautent aux yeux immédiatement.
L'angle mort : pourquoi vos listes vont finir par casser
Mais attention, il y a un problème que personne ne vous dit au début. Si vous ajoutez une ville à la fin de votre liste dans votre feuille source, Excel ne l'inclura pas automatiquement dans votre menu déroulant. Vous devrez retourner dans les paramètres de validation pour agrandir la zone de sélection. C'est là que j'ai découvert l'astuce qui a tout changé pour moi.
Arrêtez d'utiliser la validation de données classique sur des plages de cellules fixes. Utilisez plutôt les Tableaux structurés. Sélectionnez votre liste de villes, faites Ctrl + L (ou Ctrl + T selon votre version). Maintenant, votre liste est un 'Tableau' officiel pour Excel. Donnez-lui un nom dans l'onglet 'Conception de tableau' (par exemple : 'TableauVilles').
Ensuite, créez un 'Nom' (via l'onglet Formules > Gestionnaire de noms) qui pointe vers ce tableau. C'est ici que j'utilise parfois la fonction INDIRECT pour appeler le nom de mon tableau dans la source de ma liste déroulante. Pourquoi s'embêter ? Parce que maintenant, dès que vous rajoutez une ville en bas de votre tableau source, la liste déroulante s'agrandit toute seule, sans que vous n'ayez jamais à retoucher aux réglages. C'est la fin de l'obsolescence de vos fichiers.
Petits trucs de bureau pour briller en réunion
Un lundi matin le mois dernier, j'aidais une collègue qui pestait contre sa souris capricieuse. Je lui ai montré un petit raccourci : quand vous êtes sur une cellule avec une liste déroulante, maintenez Alt et appuyez sur la Flèche Bas. La liste s'ouvre instantanément. Pas besoin de viser le petit triangle avec le curseur. Elle m'a regardé comme si j'avais deviné son code de carte bleue. Ce sont ces petits riens qui nous font gagner en confort.
Une autre chose à savoir : la validation de données Excel ne fonctionne pas sur les feuilles protégées si l'option n'est pas cochée au préalable lors de la protection de la feuille. Si vous verrouillez votre fichier pour que personne ne casse vos formules, assurez-vous de laisser les cellules de saisie 'déverrouillées' avant d'appliquer la protection.
Parfois, malgré toutes ces précautions, Excel nous renvoie des erreurs bizarres, surtout quand on commence à lier des listes entre elles avec des formules complexes. Si vous vous retrouvez face à un message d'erreur incompréhensible, j'avais écrit un petit mémo pour savoir comment corriger les erreurs RECHERCHEV qui pourra sans doute vous aider, car les causes sont souvent similaires.
Conclusion : le plaisir d'un fichier qui tourne
Aujourd'hui, mes fichiers sont compatibles avec toutes les versions courantes, de Excel 2010 jusqu'à Microsoft 365. Je ne passe plus mes soirées à corriger des fautes de frappe. Voir mon tableau se remplir sans une seule erreur, avec des données parfaitement uniformes, me procure une satisfaction presque thérapeutique. C'est un peu comme finir un puzzle difficile : tout s'emboîte enfin.
Apprendre à créer une liste déroulante, ce n'est pas seulement apprendre une fonction technique. C'est décider de reprendre le contrôle sur son temps de travail. Au lieu de faire le ménage derrière les autres, on construit un système qui empêche la poussière de s'accumuler. Et croyez-moi, une fois que vous aurez goûté à la propreté des données validées, vous ne pourrez plus jamais revenir en arrière.