Un tableau croisé dynamique résume en quelques clics des centaines de lignes : chiffre d’affaires par région, nombre de commandes par commercial, évolution mois par mois. Aucune formule à écrire, et le résultat se réorganise en faisant glisser des champs. Ce guide vous accompagne de la préparation des données jusqu’aux segments et à l’actualisation, sur un fichier d’exercice que vous pouvez télécharger pour reproduire chaque étape.
Le fichier contient 120 ventes fictives de fournitures de bureau, de janvier à septembre 2026, avec la date, le numéro de commande, le client, la région, le commercial, la catégorie, la quantité, le prix unitaire et le montant hors taxes. Un onglet « Consignes » liste six exercices et un onglet « Corrigé » calcule les résultats attendus par formules, pour vérifier votre tableau.
Préparer vos données avant de créer le tableau
La plupart des difficultés avec les tableaux croisés dynamiques viennent de la source, pas de l’outil. Microsoft le rappelle dans son guide de création d’un tableau croisé dynamique : les données doivent être organisées en colonnes, avec une seule ligne d’en-tête.
- Un en-tête par colonne : chaque colonne porte un titre unique et non vide. Une colonne sans titre bloque la création du tableau.
- Une ligne par enregistrement : une vente, une facture ou un salarié par ligne, sans ligne de sous-total ni ligne vide au milieu.
- Un seul type de données par colonne : des dates réelles dans la colonne Date, des nombres dans la colonne Montant. Un montant saisi comme texte sera compté, pas additionné.
- Aucune cellule fusionnée : les fusions empêchent Excel de lire correctement la structure.
Dernière préparation, la plus utile : cliquez dans les données puis Insertion › Tableau, ou Ctrl+L, et validez. Vos données deviennent un tableau Excel, qui s’étend automatiquement quand vous ajoutez des lignes. Le tableau croisé dynamique construit dessus intégrera les nouvelles ventes à la prochaine actualisation, sans modifier sa source.
Créer votre premier tableau croisé dynamique en 5 étapes
Objectif de l’exercice 1 : afficher le chiffre d’affaires hors taxes de chaque région.
- Cliquez dans une cellule des données : inutile de tout sélectionner, Excel détecte le tableau.
- Ouvrez Insertion › Tableau croisé dynamique : dans les versions récentes, choisissez « À partir d’un tableau ou d’une plage ». La boîte de dialogue affiche le nom du tableau comme source.
- Choisissez l’emplacement : gardez « Nouvelle feuille de calcul » pour séparer les données de l’analyse, puis cliquez sur OK.
- Placez Région dans Lignes : dans le volet « Champs de tableau croisé dynamique », à droite, faites glisser le champ Région dans la zone Lignes.
- Placez Montant HT dans Valeurs : Excel affiche « Somme de Montant HT » pour chaque région, avec un total général.
Si le volet des champs disparaît, cliquez de nouveau dans le tableau croisé dynamique : il ne s’affiche que lorsque la cellule active est à l’intérieur. Pour ajouter le format monétaire, passez par Paramètres des champs de valeurs › Format de nombre plutôt que par l’onglet Accueil : le format sera conservé quand le tableau changera de forme.
Maîtriser les quatre zones : Filtres, Colonnes, Lignes, Valeurs
Tout le fonctionnement repose sur le placement des champs dans quatre zones. Changer un champ de zone suffit à obtenir une autre analyse des mêmes données.
| Zone | Rôle | Exemple avec le fichier d’exercice |
|---|---|---|
| Lignes | Une ligne par valeur distincte du champ | Région, Client, Commercial |
| Colonnes | Une colonne par valeur distincte, pour croiser deux critères | Catégorie (exercice 2) |
| Valeurs | Le calcul affiché à chaque intersection | Somme de Montant HT, Nombre de N° commande |
| Filtres | Un filtre global au-dessus du tableau | Commercial, pour n’afficher qu’un portefeuille |
Pour l’exercice 2, ajoutez Catégorie dans Colonnes : le tableau croise les quatre régions et les quatre catégories. Placez ensuite Client sous Région dans la zone Lignes pour obtenir un détail par client, repliable avec les boutons + et –. Pour un tableau plus lisible à l’impression, l’onglet Création propose Disposition du rapport › Afficher sous forme tabulaire.
Changer le calcul : somme, nombre, moyenne et pourcentage
Par défaut, un champ numérique est additionné et un champ texte est compté. Faites un clic droit sur une valeur du tableau, puis Paramètres des champs de valeurs. L’onglet « Synthèse des valeurs par » propose notamment Somme, Nombre, Moyenne, Max et Min ; l’onglet « Afficher les valeurs » transforme les montants en pourcentages ou en écarts.
- Exercice 3, part de chaque région : glissez une seconde fois Montant HT dans Valeurs, puis réglez ce second champ sur % du total général. Vous obtenez le montant et sa part côte à côte.
- Exercice 5, commandes par commercial : Commercial en Lignes, N° commande en Valeurs. Comme le champ est du texte, Excel choisit Nombre : 35 commandes pour Karim Benali, 33 pour Thomas Leroy, 28 pour Julie Martin et 24 pour Sophie Durand.
- Panier moyen : Montant HT en Valeurs avec la synthèse Moyenne donne le montant moyen d’une commande par région.
L’Île-de-France arrive en tête avec 32,5 % du chiffre d’affaires, devant l’Est (29,0 %), l’Ouest (22,6 %) et le Sud (15,9 %). Si votre tableau affiche d’autres chiffres, comparez-le à l’onglet « Corrigé » : l’écart vient presque toujours d’une source qui ne couvre pas toutes les lignes.
Grouper les dates par mois et par trimestre
Placée dans Lignes, la colonne Date est souvent regroupée automatiquement par les versions récentes d’Excel, en années, trimestres et mois. Si vous voyez au contraire une ligne par jour, faites un clic droit sur une date du tableau, choisissez Grouper, puis sélectionnez Mois et Trimestres et validez. C’est l’exercice 4 du fichier.
Si la commande Grouper renvoie le message « Impossible de grouper cette sélection », au moins une cellule de la colonne Date contient du texte ou est vide. Corrigez la source, actualisez, puis relancez le regroupement. Le même menu Grouper permet de créer des tranches sur un champ numérique, par exemple des quantités de 1 à 10, 11 à 20, et ainsi de suite.
Filtrer avec des segments et une chronologie
Le filtre classique fonctionne, mais il ne montre pas d’un coup d’œil ce qui est filtré. Les segments affichent des boutons cliquables, plus adaptés à un tableau consulté par d’autres personnes.
- Cliquez dans le tableau croisé dynamique : l’onglet Analyse du tableau croisé dynamique apparaît dans le ruban.
- Choisissez Insérer un segment : cochez Catégorie et validez. Un bouton par catégorie s’affiche ; Ctrl+clic en sélectionne plusieurs.
- Ajoutez une chronologie : Insérer une chronologie, puis cochez Date. Une barre de temps permet de filtrer par mois ou par trimestre.
- Ajoutez un graphique croisé dynamique : Graphique croisé dynamique, dans le même onglet. Il suit les filtres du segment, ce qui donne une première ébauche de tableau de bord.
Un même segment peut piloter plusieurs tableaux croisés dynamiques construits sur la même source : clic droit sur le segment, puis Connexions de rapport. C’est la base des tableaux de bord travaillés dans le niveau Expert et IA de la formation Excel CPF de CNFN, avec Power Query pour préparer les données et Power Pivot pour les modèles plus lourds.
Actualiser le tableau quand les données changent
Un tableau croisé dynamique ne se recalcule pas à chaque saisie, contrairement à une formule. Après une modification de la source, faites un clic droit dans le tableau puis Actualiser. Pour mettre à jour tous les tableaux du classeur, utilisez Analyse du tableau croisé dynamique › Actualiser › Actualiser tout.
Testez-le sur le fichier d’exercice : ajoutez une vente en bas du tableau Ventes, actualisez, et le total de la région concernée change. Si la nouvelle ligne n’est pas prise en compte, la source est une plage fixe et non un tableau : passez par Analyse du tableau croisé dynamique › Changer la source de données. Pour un fichier mis à jour chaque semaine, l’option « Actualiser les données lors de l’ouverture du fichier », dans les Options du tableau croisé dynamique, onglet Données, évite d’oublier l’actualisation.
Choisir entre tableau croisé dynamique et formules
Les mêmes totaux peuvent s’obtenir avec SOMME.SI.ENS, comme dans l’onglet « Corrigé ». Les deux approches ont leur place.
| Besoin | Tableau croisé dynamique | Formules (SOMME.SI.ENS, RECHERCHEX) |
|---|---|---|
| Explorer des données rapidement | Idéal, quelques glisser-déposer | Lent, une formule par cellule |
| Mise à jour | Manuelle (Actualiser) | Automatique |
| Mise en page imposée | Contrainte par la structure du tableau | Libre, cellule par cellule |
| Nouvelles catégories | Ajoutées automatiquement | À ajouter à la main |
En pratique, le tableau croisé dynamique sert à analyser et à explorer, les formules à construire un rapport à la mise en page fixe. Les fonctions conditionnelles comme SOMME.SI sont détaillées dans notre article sur la fonction SI dans Excel, et les fonctions de recherche dans le comparatif RECHERCHEX ou RECHERCHEV.
Résoudre les problèmes fréquents
- « Le nom de champ de tableau croisé dynamique n’est pas valide » : une colonne de la source n’a pas d’en-tête. Donnez-lui un titre, puis recommencez.
- « Nombre de Montant HT » au lieu de « Somme » : la colonne contient au moins une cellule vide ou un texte. Corrigez la source, puis changez la synthèse en Somme.
- Une ligne « (vide) » apparaît : la source inclut des lignes vides, souvent parce qu’elle a été définie sur des colonnes entières. Utilisez un tableau Excel comme source.
- Deux lignes pour le même client : l’orthographe diffère, ou un espace traîne en fin de nom. La fonction SUPPRESPACE ou Power Query nettoient ces écarts.
- Les largeurs de colonnes changent à chaque actualisation : décochez « Ajuster automatiquement la largeur des colonnes lors de la mise à jour » dans les Options du tableau croisé dynamique.
Le tableau croisé dynamique fait partie du niveau Avancé de la formation CNFN (chapitres 8 à 16) et revient dans les cas pratiques métiers, comme le tableau de bord RH ou le reporting service client, d’abord guidés puis chronométrés, avec des exercices corrigés en visioconférence. Il fait aussi partie du référentiel du niveau Avancé de la certification ICDL Excel. Pour savoir si vous pouvez attaquer directement ce niveau, remplissez le formulaire de vérification des prérequis Excel.
Questions fréquentes sur le tableau croisé dynamique
Comment faire un tableau croisé dynamique sur Excel ?
Cliquez dans vos données, puis Insertion › Tableau croisé dynamique et validez avec OK pour le placer sur une nouvelle feuille. Dans le volet Champs de tableau croisé dynamique, faites glisser un champ texte dans Lignes et un champ numérique dans Valeurs : Excel calcule la somme automatiquement.
Pourquoi mon tableau croisé dynamique ne se met-il pas à jour ?
Un tableau croisé dynamique ne se recalcule pas tout seul quand les données source changent. Faites un clic droit dans le tableau puis Actualiser. Si les nouvelles lignes n’apparaissent pas, la source est une plage fixe : convertissez les données en tableau (Ctrl+L) pour qu’elle s’étende automatiquement.
Comment afficher un pourcentage dans un tableau croisé dynamique ?
Faites un clic droit sur une valeur, choisissez Paramètres des champs de valeurs, puis l’onglet Afficher les valeurs. Sélectionnez % du total général, % du total de la colonne ou % du total de la ligne selon la comparaison voulue.
Pourquoi Excel compte au lieu d’additionner dans mon tableau croisé dynamique ?
Excel choisit la synthèse Nombre quand la colonne contient du texte ou des cellules vides. Vérifiez que les montants sont de vrais nombres, puis changez la synthèse en Somme dans Paramètres des champs de valeurs.
Le tableau croisé dynamique est-il au programme des certifications Excel ?
Oui. Les tableaux croisés dynamiques figurent dans le niveau Avancé de la certification ICDL (International Certification of Digital Literacy) et sont évalués par le TOSA Excel. Ils sont aussi fréquents dans les tests Excel de recrutement pour les postes administratifs et commerciaux.
Retenez la méthode : des données propres converties en tableau, un champ texte en Lignes, un champ numérique en Valeurs, puis les segments et l’actualisation. Le fichier d’exercice vous permet de refaire chaque étape à votre rythme, avant de passer à vos propres fichiers. Pour aller plus loin, jusqu’à Power Query, Power Pivot et les tableaux de bord, découvrez le programme de la formation Excel CPF.
