Listes déroulantes et validation des données dans Excel : tutoriel pas-à-pas

📍 Guide principal de la série : Excel pour PME : tableaux structurés, fonctions dynamiques, Copilot et Python.

Quand plusieurs personnes saisissent dans le même classeur, les variations d’orthographe et les fautes de frappe pourrissent les analyses. Dakar, dakar, Dakar avec un espace à la fin — autant de valeurs distinctes pour Excel. La validation des données et les listes déroulantes résolvent ce problème en restreignant ce que l’utilisateur peut taper. Ce tutoriel couvre la mise en place complète, des cas simples aux listes en cascade.

Prérequis

• Excel 2016 ou plus récent
• Notion de plage et de référence absolue (signe $)
• Temps : 35 minutes

Étape 1 — La validation simple : liste fixe

Le cas le plus courant : restreindre une cellule à un nombre limité de valeurs. Imaginez un suivi de ventes où la colonne Région ne doit contenir que Dakar, Thiès, Saint-Louis, Kaolack, Ziguinchor.

Sélectionnez la plage qui doit recevoir les régions, par exemple D2:D100. Allez dans Données → Validation des données (raccourci Alt + D + L). Dans l’onglet Options, choisissez Liste dans Autoriser. Dans le champ Source, tapez :

Dakar;Thiès;Saint-Louis;Kaolack;Ziguinchor

Validez. Désormais, cliquer sur D2 affiche une petite flèche : c’est la liste déroulante. L’utilisateur ne peut sélectionner qu’une de ces 5 valeurs. Toute saisie hors liste génère un message d’erreur.

Étape 2 — La validation par référence : liste dynamique

Le souci de la liste fixe : si vous voulez ajouter une région, il faut modifier toutes les cellules validées. Mieux : pointer vers une plage qui contient les valeurs autorisées.

Créez une feuille Listes. En A1, tapez Régions. De A2 à A6, saisissez vos cinq régions. Sélectionnez à nouveau D2:D100 sur la feuille principale, ouvrez la validation, choisissez Liste, et dans Source tapez :

=Listes!$A$2:$A$6

Maintenant pour ajouter Diourbel, il suffit de la saisir en A7 sur la feuille Listes et d’ajuster la plage. Pour éviter cet ajustement manuel, convertissez la plage en tableau structuré.

Étape 3 — Liste qui s’auto-étend : tableau structuré

Sur la feuille Listes, sélectionnez A1:A6 et appuyez sur Ctrl + L. Cochez Mon tableau comporte des en-têtes. Excel crée un tableau nommé par défaut Tableau1. Renommez-le proprement : sélectionnez le tableau, allez dans Création de tableau, et changez le nom en tblRegions.

Mais attention : la validation des données n’accepte pas directement un nom de tableau dans Source. Il faut une astuce. Créez une plage nommée qui pointe vers la colonne du tableau. Allez dans Formules → Définir un nom. Nom : Regions. Référence :

=tblRegions[Régions]

Maintenant, dans la validation des données de D2:D100, mettez :

=Regions

Quand vous ajoutez Diourbel à la fin du tableau, la validation se met à jour automatiquement. Pas besoin de modifier la formule.

Étape 4 — Listes en cascade : choisir le pays puis la ville

Cas concret : un suivi de clients en plusieurs pays. La colonne Pays propose Sénégal, Côte d’Ivoire, Mali. La colonne Ville doit proposer uniquement les villes du pays choisi.

Sur Listes, créez trois plages avec des noms identiques aux pays. Pour le Sénégal, sélectionnez B2:B10 (Dakar, Thiès, Saint-Louis…), Formules → Définir un nom, nom : Sénégal. Idem avec Côte_d_Ivoire (les espaces et apostrophes sont remplacés par des soulignés) et Mali.

Sur la feuille principale, en colonne E (Pays), validation classique avec liste Sénégal;Côte d’Ivoire;Mali. En colonne F (Ville), validation avec source :

=INDIRECT(SUBSTITUE(SUBSTITUE(E2;" ";"_");"'";"_"))

La fonction INDIRECT convertit un texte en référence. Si E2 contient Côte d’Ivoire, SUBSTITUE remplace l’espace et l’apostrophe par des soulignés pour coller au nom de plage Côte_d_Ivoire. INDIRECT renvoie alors la plage correspondante, et la liste déroulante affiche les villes ivoiriennes.

Étape 5 — Validation par formule : règles personnalisées

La validation ne se limite pas aux listes. On peut imposer une règle calculée. Exemples utiles :

Pour un numéro de téléphone sénégalais (9 chiffres), la cellule doit contenir exactement 9 caractères numériques. Validation type Personnalisé avec formule :

=ET(NBCAR(A2)=9;ESTNUM(CNUM(A2)))

Pour une date de facture qui ne peut pas être dans le futur :

=A2<=AUJOURDHUI()

Pour un montant qui doit être positif et inférieur à 5 000 000 FCFA :

=ET(A2>0;A2<=5000000)

Pour interdire les doublons dans une colonne (chaque code produit doit être unique) :

=NB.SI($A$2:$A$1000;A2)=1

Étape 6 — Personnaliser le message d’alerte

Dans la fenêtre Validation des données, l’onglet Message de saisie permet d’afficher une infobulle quand l’utilisateur sélectionne la cellule. Mettre Format attendu : 9 chiffres sans espace, ex 778268377. C’est une aide bienveillante qui évite l’erreur en amont.

L’onglet Alerte d’erreur personnalise le message en cas de saisie invalide. Trois styles : Stop (refuse la saisie), Avertissement (avertit mais accepte), Information (signale sans bloquer). Pour une formation pédagogique, Avertissement est moins frustrant. Pour une saisie en production, Stop est plus sûr.

Étape 7 — Mettre en valeur les cellules invalides

Quand une validation est ajoutée a posteriori sur des cellules déjà remplies, Excel n’efface pas les valeurs invalides — il les laisse en place. Pour les repérer : Données → Validation des données → Entourer les données non valides. Excel encadre en rouge toutes les cellules qui contredisent la règle.

C’est utile pour migrer un classeur ancien vers un classeur normalisé : appliquer la validation, repérer les anciennes saisies douteuses, les corriger une par une.

Étape 8 — Vérifier que ça marche

Construisez un cas test. Sur Listes, créez les plages Sénégal (Dakar, Thiès, Saint-Louis), Mali (Bamako, Ségou, Sikasso). Sur Données, en colonne A : Pays, validation liste Sénégal;Mali. En colonne B : Ville, validation cascade par INDIRECT. En colonne C : Code, validation personnalisée =NB.SI($C$2:$C$1000;C2)=1 pour empêcher les doublons.

Saisissez Sénégal en A2 ; la liste de B2 doit afficher Dakar/Thiès/Saint-Louis. Changez A2 en Mali ; B2 affiche maintenant Bamako/Ségou/Sikasso. Tentez de saisir un code déjà utilisé en C ; Excel doit refuser ou avertir.

Erreurs fréquentes

Symptôme	Cause	Solution
Liste cascade ne s’affiche pas	Nom de plage avec caractère interdit	SUBSTITUE pour remplacer espaces et apostrophes
Source liste ne fonctionne pas	Formule sans = en début	Toujours commencer par =
Liste vide après ajout	Référence figée à 6 lignes	Convertir en tableau structuré
Validation ignorée par copier-coller	Le collage écrase la validation	Coller en valeurs uniquement (Collage spécial → Valeurs)
Erreur sur INDIRECT après partage	Compatibilité versions	Tester sur la version cible avant déploiement

Sur le même thème

• La mise en forme conditionnelle — colorer les cellules invalides ou hors normes.
• RECHERCHEV et INDEX/EQUIV — exploiter les valeurs validées dans des recherches.

🔝 Retour au guide principal : Excel pour PME.

Foire aux questions

Comment supprimer une validation ?

Sélectionner les cellules concernées, ouvrir Données → Validation des données, cliquer sur Effacer tout.

Peut-on copier la validation d’une cellule sur une autre ?

Oui, par Copier → Collage spécial → Validation. Très utile pour appliquer rapidement une règle existante à de nouvelles colonnes.

La validation s’applique-t-elle aux formules ?

Non. Elle ne contrôle que la saisie utilisateur. Une formule qui produit une valeur hors plage ne génère pas d’erreur — c’est une limite à connaître.

Comment cacher la flèche de la liste déroulante ?

Impossible directement. Une astuce visuelle : superposer une forme rectangle blanche pour masquer la flèche quand la cellule n’est pas active. Mais cela complique la maintenance.

Peut-on avoir plusieurs valeurs sélectionnées dans une cellule ?

Pas nativement. Excel n’a pas de liste à choix multiples. Pour ce besoin, soit créer plusieurs colonnes (chaque option = une colonne), soit recourir à VBA.

La validation tient-elle dans Google Sheets ?

Oui pour les listes simples. Les validations cascade par INDIRECT fonctionnent aussi mais avec une syntaxe légèrement différente. Tester avant publication si le classeur doit circuler en .gsheet.

Pourquoi la validation des données change la fiabilité d’un fichier Excel

Dans la majorité des PME à Dakar, Abidjan, Bamako ou Cotonou, les fichiers Excel circulent entre 5 à 15 personnes par mois. Sans validation, chaque utilisateur saisit ses données à sa façon : « DAKAR », « Dakar », « dakar », « DK »… Au bout de 3 mois, la base est inexploitable et un tableau croisé dynamique remonte 12 lignes pour la même ville.

Mettre en place des listes déroulantes et règles de validation prend 30 minutes par fichier mais économise 4 à 8 heures de nettoyage chaque trimestre. Pour un comptable qui facture 25 000 FCFA (38 EUR au taux fixe 1 EUR = 655,957 FCFA) la journée, c’est 100 000 à 200 000 FCFA de temps libéré par an, juste en activant des fonctions Excel intégrées et gratuites.

Étape 1 : créer une liste déroulante simple en 2 minutes

La forme la plus basique : vous voulez qu’une cellule n’accepte que « Oui » ou « Non ». Pas de saisie libre, pas de fautes possibles. Cette validation prend littéralement 2 minutes pour une plage entière.

1. Sélectionnez la plage (ex: B2:B100)
2. Onglet "Données" → bouton "Validation des données"
3. Onglet "Options" → Autoriser : "Liste"
4. Source : Oui;Non
5. Cochez "Ignorer si vide" et "Liste déroulante dans la cellule"
6. Onglet "Alerte d'erreur" → Style : "Arrêt"
7. OK

Testez immédiatement : tapez « Peut-être » dans B2, vous obtenez un message d’erreur « Cette valeur ne correspond pas aux restrictions ». C’est exactement ce qu’on cherche — l’utilisateur ne peut plus introduire de bruit dans les données. La flèche déroulante apparaît au clic dans chaque cellule de la plage.

Étape 2 : alimenter la liste depuis une plage nommée pour scaler

Pour une liste de 30 villes ou 50 produits, le séparateur point-virgule devient ingérable. La solution propre est de créer une plage nommée et de pointer la validation dessus. La liste se met à jour automatiquement quand vous éditez la plage source.

1. Sur une feuille "Référentiels" :
   A1 : "Ville"
   A2:A30 : Dakar, Thiès, Abidjan, Bamako, Cotonou, ...
2. Sélectionnez A2:A30
3. Onglet "Formules" → "Définir un nom"
4. Nom : "Villes"
5. Champ "Fait référence à" : =Référentiels!\$A\$2:\$A\$30
6. OK
7. Sur la feuille de saisie, validation des données :
   Source : =Villes

Le retour attendu : la cellule affiche désormais 30 choix dans le déroulant. Si vous ajoutez « Lomé » en A31, la liste reste figée à 30 — voir l’étape 3 pour rendre la plage dynamique. C’est néanmoins suffisant pour 80 % des cas réels en PME.

Étape 3 : transformer la plage en Tableau structuré pour une liste auto-extensible

Une plage classique est statique. Si vous voulez ajouter « Lomé » en bas et que la liste se mette à jour seule, convertissez la plage en Tableau structuré (Ctrl+T). C’est la fonctionnalité Excel la plus sous-utilisée par les PME locales.

1. Cliquez sur A1 de la plage référentiel
2. Ctrl + T (ou Insertion → Tableau)
3. Cochez "Mon tableau comporte des en-têtes"
4. OK
5. Onglet "Création de tableau" → Nom : "tabVilles"
6. Pour la validation : Source = INDIRECT("tabVilles[Ville]")
7. Désormais, ajouter une ligne au tableau étend la liste

L’astuce INDIRECT est obligatoire car la validation des données ne supporte pas directement les références structurées (limitation Microsoft Excel toujours présente en 2026). Avec cette config, votre liste s’étend automatiquement à 35, 50, 100 villes sans toucher à la validation.

Étape 4 : créer des listes déroulantes en cascade (Pays → Ville)

Cas typique : vous gérez une équipe commerciale dans plusieurs pays. La cellule « Pays » contient Sénégal, Côte d’Ivoire, Mali, Bénin. Quand on choisit « Sénégal », la cellule « Ville » doit proposer Dakar, Thiès, Saint-Louis. Quand on choisit « Mali », elle doit proposer Bamako, Sikasso, Ségou. C’est la liste en cascade.

1. Créer 4 plages nommées :
   - Senegal : Dakar, Thiès, Saint-Louis, Ziguinchor
   - CoteIvoire : Abidjan, Bouaké, Yamoussoukro
   - Mali : Bamako, Sikasso, Ségou
   - Benin : Cotonou, Porto-Novo, Parakou
2. Pays (A2) : validation Liste = Pays
3. Ville (B2) : validation Liste
   Source : =INDIRECT(SUBSTITUE(A2;" ";""))
4. Si A2 contient "Côte d'Ivoire", le SUBSTITUE retire l'espace
   et l'apostrophe pour matcher le nom de plage "CoteIvoire"

Vérifiez que les noms de plages sont sans espace ni accent ni apostrophe — Excel refuse « Côte d’Ivoire » comme nom de plage. La fonction SUBSTITUE imbriquée transforme la valeur affichée en clé valide. Testez avec chaque pays pour confirmer que la 2e liste se met à jour au changement.

Étape 5 : valider une saisie numérique avec borne min/max

Les listes déroulantes ne sont qu’une famille de validation. La validation numérique évite les saisies aberrantes : un âge de 250 ans, un montant négatif, un pourcentage à 1 500 %. Configuration en 1 minute.

1. Sélectionnez la colonne "Âge" (C2:C500)
2. Données → Validation des données
3. Autoriser : "Nombre entier"
4. Données : "comprise entre"
5. Minimum : 18
6. Maximum : 110
7. Onglet "Message de saisie" :
   Titre : "Âge employé"
   Message : "Saisissez un âge entre 18 et 110 ans"
8. Onglet "Alerte d'erreur" : Style "Arrêt"

Le message de saisie apparaît en infobulle quand l’utilisateur clique sur la cellule. C’est une aide proactive qui réduit les erreurs avant qu’elles n’arrivent. Pour les montants en FCFA, configurez un minimum à 0 et un maximum réaliste (10 000 000 par exemple) pour bloquer les fautes de frappe à 6 zéros.

Étape 6 : valider un format de texte avec formule personnalisée

Pour un numéro de téléphone sénégalais qui doit commencer par 77, 78, 76 ou 70 et faire exactement 9 chiffres, ni la liste ni la borne numérique ne suffisent. La validation par formule personnalisée résout ce cas.

1. Sélectionnez la colonne "Téléphone" (D2:D500)
2. Données → Validation des données
3. Autoriser : "Personnalisé"
4. Formule :
   =ET(NBCAR(D2)=9;OU(GAUCHE(D2;2)="77";GAUCHE(D2;2)="78";GAUCHE(D2;2)="76";GAUCHE(D2;2)="70"))
5. Alerte d'erreur :
   Titre : "Format téléphone Sénégal"
   Message : "9 chiffres commençant par 77, 78, 76 ou 70"

Cette formule combine NBCAR (compte les caractères) et OU (teste plusieurs préfixes). Adaptez à la Côte d’Ivoire (préfixes 01, 05, 07) ou au Mali (préfixe 65, 70, 75, 76, 77, 79, 80, 90, 91, 94). C’est la même logique, vous changez juste les préfixes.

Étape 7 : protéger les cellules de validation contre la suppression accidentelle

Une validation peut être contournée par un simple copier-coller : si l’utilisateur copie une cellule sans validation et colle dessus, la validation saute. Pour blinder, combinez validation + protection de feuille.

1. Sélectionnez tout le tableau (Ctrl + A)
2. Format de cellule (Ctrl + 1) → onglet "Protection"
3. Décochez "Verrouillée" pour les cellules de saisie libre
4. Sélectionnez UNIQUEMENT les colonnes à liste déroulante
5. Format de cellule → "Verrouillée" cochée
6. Onglet "Révision" → "Protéger la feuille"
7. Mot de passe (optionnel mais recommandé)
8. Cochez : "Sélectionner les cellules déverrouillées"
   Décochez les autres options

Désormais l’utilisateur peut saisir dans les cellules libres mais ne peut plus modifier les colonnes verrouillées par copier-coller. La validation est inviolable. C’est la configuration standard pour les fichiers RH ou comptabilité partagés.

Étape 8 : auditer un fichier existant et trouver les cellules sans validation

Quand vous récupérez un fichier mal structuré, identifiez d’abord toutes les cellules qui auraient dû avoir une validation. Excel propose un raccourci direct pour visualiser les zones de validation existantes.

1. Onglet "Accueil" → "Rechercher et sélectionner"
2. "Atteindre… spécial" (raccourci F5 puis Cellules)
3. Cochez "Validation des données" → "Tout"
4. OK : Excel sélectionne toutes les cellules avec validation
5. Pour voir les cellules SANS validation :
   Sélectionnez d'abord la plage de données (Ctrl + Maj + *)
   Puis Atteindre Spécial → "Validation"
   Inversez mentalement le résultat
6. Documentez sur une feuille "Audit" la liste à compléter

Cet audit prend 10 minutes sur un fichier de 1 000 lignes et révèle systématiquement 3 à 5 colonnes critiques sans validation. C’est la base de toute mission de fiabilisation Excel facturée 50 000 à 150 000 FCFA à un client PME local.

Dans la continuité sur Excel et l’automatisation

La validation n’est qu’un pilier de la fiabilisation Excel. Lisez notre tutoriel marketing de contenu blog pour structurer vos formations Excel à vendre, et notre tutoriel articles piliers SEO pour positionner votre offre Excel sur Google.