Chapitre 2 · Intermédiaire · Leçon 14 / 26
Tableaux croisés dynamiques
C'est quoi un tableau croisé dynamique ?
Un tableau croisé dynamique (TCD) est un outil qui prend une liste brute de données et en produit instantanément un résumé structuré. Tu lui dis quoi regrouper, quoi calculer, et il s'occupe du reste.
Imagine un fichier de ventes avec 10 000 lignes : une par transaction, avec la date, le vendeur, la région, le produit et le montant. Pour savoir combien chaque région a vendu au total, tu pourrais écrire une formule SOMME.SI.ENS. Mais si demain tu veux le même résultat par vendeur, puis par produit, puis par trimestre, tu dois réécrire chaque fois. Le TCD, lui, te donne ces quatre vues en quelques clics, sans aucune formule.
Le mot « dynamique » signifie que tu peux réorganiser l'analyse à la volée : changer l'axe, ajouter un filtre, permuter les lignes et colonnes, tout ça sans modifier la source. C'est le principal avantage sur les formules classiques.
Ce que le TCD fait mieux que les formules
Regroupement en 1 clic, vue croisée (lignes × colonnes), sous-totaux automatiques, tri par valeur, filtres interactifs via segments. Les formules restent utiles pour les calculs ponctuels ou très spécifiques, mais pour explorer un jeu de données, le TCD est imbattable.
Quand utiliser un TCD ?
- Tu as une liste brute de données (ventes, dépenses, effectifs, tickets) avec plusieurs colonnes catégorielles.
- Tu veux comparer des groupes entre eux : région contre région, mois contre mois, produit contre produit.
- Tu dois présenter un résumé à un manager qui ne veut pas voir 10 000 lignes.
- Tu vas poser la même question à tes données sous plusieurs angles différents.
Le tableau croisé dynamique en vidéo
Un guide visuel complet pour créer et configurer ton premier TCD de A à Z, avec des exemples concrets directement dans Excel.
Les prérequis : des données propres
Un TCD est aussi bon que sa source. Si tes données sont mal structurées, le TCD te donnera des résultats faux ou incompréhensibles. Voici les cinq règles à respecter avant de créer ton premier TCD.
Chaque colonne a un en-tête unique
La première ligne doit contenir les noms de colonnes : « Région », « Vendeur », « Montant »… Excel utilise ces en-têtes dans le volet des champs. Pas de cellules fusionnées dans cette ligne.
Pas de ligne entièrement vide
Une ligne vide coupe la plage source en deux. Excel ne voit que la portion au-dessus du blanc. Supprime toutes les lignes vides de ton tableau de données.
Les colonnes numériques ne contiennent que des nombres
Si ta colonne « Montant » contient « 5 000 € » comme texte, Excel ne pourra pas faire de somme. Le TCD comptera les lignes au lieu de les additionner. Nettoie les symboles monétaires et les espaces insécables.
Les dates sont reconnues comme dates
Vérifie que ta colonne de dates est au format « Date » (pas texte). Si elle est en texte, le regroupement par mois ou trimestre ne fonctionnera pas. Utilise la fonction DATEVAL() pour convertir.
Une seule valeur par cellule
Si tu as plusieurs régions dans la même cellule (« Nord, Est »), le TCD ne peut pas les séparer. Chaque cellule doit contenir une valeur atomique.
Créer ton premier tableau croisé dynamique pas à pas
L'exemple ci-dessous part d'un fichier de ventes comme celui-ci, avec une ligne par transaction et les colonnes Date, Vendeur, Région, Catégorie et Chiffre d'affaires. L'objectif : voir le chiffre d'affaires par région et par catégorie.
Extrait d'une source de 5 000 lignes : chaque ligne est une vente.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Vendeur | Région | Catégorie | CA |
| 2 | 12/01 | Léa | Nord | Bureautique | 1 200 |
| 3 | 12/01 | Karim | Sud | Mobilier | 3 400 |
| 4 | 13/01 | Léa | Nord | Mobilier | 900 |
| 5 | 13/01 | Sofia | Est | Bureautique | 2 100 |
Préparer la source
Assure-toi que chaque colonne a un en-tête, qu'il n'y a pas de lignes entièrement vides et que toutes les cellules numériques contiennent bien des nombres (pas du texte).
Insérer le TCD
Clique n'importe où dans ta plage de données. Onglet Insertion > Tableau croisé dynamique. Excel propose une nouvelle feuille par défaut : accepte.
Choisir les champs
Le volet à droite liste tous tes en-têtes. Fais glisser « Région » dans Lignes, « Catégorie » dans Colonnes, et « Chiffre d'affaires » dans Valeurs. Ton TCD se construit en temps réel.
Lire le résultat
Excel affiche un tableau croisé avec les totaux par ligne et par colonne. Chaque cellule correspond à l'intersection de la ligne et de la colonne que tu as choisies.
Trier et filtrer
Clique sur la flèche d'en-tête pour trier par valeur. Utilise le champ Filtres pour restreindre l'analyse à un sous-ensemble de données (une année, une catégorie, un vendeur).
Ce que tu vois à la fin
Un tableau avec les régions en ligne, les catégories en colonne, et dans chaque cellule la somme du chiffre d'affaires correspondant. Excel calcule aussi les totaux par ligne (total par région) et par colonne (total par catégorie). Le tout s'actualise si tu modifies la source et cliques sur « Actualiser ».
Pour changer l'analyse, fais simplement glisser les champs : déplace « Région » dans Colonnes et « Catégorie » dans Lignes. La vue se reconstruit instantanément. Aucune formule à réécrire.
Les 4 zones du tableau croisé dynamique
Le volet de configuration du TCD est divisé en quatre zones. Comprendre le rôle de chacune, c'est comprendre comment Excel construit le tableau. Elles se lisent deux par deux : Lignes et Colonnes posent les axes d'analyse, Valeurs porte la mesure, et Filtres restreint l'ensemble.
Lignes et Colonnes sont les deux axes du croisement. Chaque valeur distincte du champ placé en Lignes crée une ligne dans le TCD : mets « Région » en Lignes et tu obtiens une ligne par région (Nord, Sud, Est, Ouest). Les Colonnes suivent la même logique, mais à l'horizontale : « Trimestre » en Colonnes te donne T1, T2, T3, T4 côte à côte. Tu peux empiler plusieurs champs en Lignes pour un regroupement hiérarchique rétractable (Région, puis Département, puis Vendeur).
Le piège, c'est la cardinalité en Colonnes. Un champ avec beaucoup de valeurs distinctes, comme « Vendeur » avec 50 noms, donne un tableau de 50 colonnes illisible. Garde les champs riches en Lignes ou en Filtres, et réserve les Colonnes aux champs courts (un trimestre, une catégorie, un type de contrat).
La zone Valeurs, c'est ce qu'on mesure. Tu y déposes le champ à calculer : Chiffre d'affaires, Quantité, Marge. Par défaut Excel fait une somme sur les colonnes numériques et un compte sur les textes. Pour changer ce calcul, clic droit sur une valeur dans le TCD, puis « Paramètres des champs de valeurs » : tu choisis entre Somme, NB, Moyenne, Max, Min, Produit ou Écartype, et tu peux même afficher le résultat en pourcentage du total.
La zone Filtres restreint tout le TCD d'un coup. Place « Année » dans Filtres et une liste déroulante s'affiche au-dessus du tableau, qui limite l'ensemble à une seule année en un clic. C'est proche d'un segment, à une différence près : le filtre vit dans le TCD, alors que les segments (vus plus loin) sont des objets indépendants connectables à plusieurs TCD à la fois.
Regrouper les données : dates, tranches et textes
Le regroupement te permet de consolider des valeurs détaillées en catégories. Si tu as des dates au quotidien, tu peux les regrouper par mois, trimestre ou année en deux clics. Si tu as des montants individuels, tu peux les regrouper par tranches (0-100, 100-500, etc.).
Regrouper par dates
Fais glisser ton champ « Date » dans la zone Lignes ou Colonnes. Clic droit sur une date dans le TCD > « Grouper ». Une boîte de dialogue s'ouvre avec les options : Secondes, Minutes, Heures, Jours, Mois, Trimestres, Années. Sélectionne « Mois » et « Années » ensemble pour obtenir une hiérarchie Année > Mois.
Dans Microsoft 365, Excel regroupe souvent les dates automatiquement dès que tu les glisses dans le TCD. Si c'est gênant, clic droit > Dissocier pour revenir au détail jour par jour.
Regrouper par tranches numériques
Glisse un champ numérique (par exemple « Montant de la commande ») dans Lignes. Clic droit > Grouper. Indique le début, la fin et l'intervalle. Avec un début à 0, une fin à 10 000 et un pas de 1 000, tu obtiens des tranches 0-1000, 1001-2000, etc.
C'est particulièrement utile pour analyser une distribution : combien de commandes sont inférieures à 500 €, combien se situent entre 500 € et 2 000 €, combien dépassent 2 000 €.
Regrouper des textes manuellement
Excel ne peut pas grouper des textes automatiquement comme il le fait pour les dates. Mais tu peux sélectionner plusieurs éléments de la liste (par exemple « Espagne », « Portugal », « Italie »), faire un clic droit et « Grouper ». Excel crée un groupe que tu peux renommer « Europe du Sud ». Utile pour créer des régions commerciales ou des familles de produits ad hoc.
Les champs calculés : ajouter tes propres calculs
Un champ calculé est une colonne supplémentaire dans le TCD, calculée à partir des autres champs. Tu n'as pas besoin de modifier la source ni d'écrire des formules dans des cellules externes.
Exemple : ta source contient les colonnes « Chiffre d'affaires » et « Coût des ventes ». Tu veux afficher la marge brute et le taux de marge directement dans le TCD.
Comment créer un champ calculé
- 1Clique n'importe où dans le TCD.
- 2Onglet « Analyse du tableau croisé dynamique » (ou « Options » selon ta version Excel) > « Champs, éléments et jeux » > « Champ calculé ».
- 3Dans la boîte de dialogue, donne un nom à ton champ (par exemple « Marge brute »).
- 4Dans la zone « Formule », saisit ton calcul : = 'Chiffre d'affaires' - 'Coût des ventes'. Les noms de champs doivent correspondre exactement aux en-têtes de ta source.
- 5Valide. Le champ calculé apparaît dans la zone Valeurs et dans le tableau.
Pour le taux de marge, la formule serait : = (Chiffre d'affaires - Coût des ventes) / Chiffre d'affaires. Tu peux ensuite formater ce champ en pourcentage via « Paramètres des champs de valeurs » > Format de nombre.
Les champs calculés ont une particularité importante : ils travaillent sur les sommes des champs, pas sur les lignes individuelles. Pour des calculs qui nécessitent une opération ligne par ligne (par exemple un ratio qui doit être calculé avant d'être agrégé), ajoute plutôt une colonne dans ta source.
Actualiser les données du TCD
Le tableau croisé dynamique ne suit pas la source en temps réel. Quand tu modifies ou ajoutes des données, tu dois demander explicitement à Excel de recalculer.
Pour actualiser un seul TCD, fais un clic droit sur n'importe quelle cellule du tableau, puis « Actualiser » (ou onglet « Analyse » > « Actualiser »). Si ton classeur contient plusieurs TCD, déroule la flèche sous « Actualiser » et choisis « Actualiser tout » pour les recalculer d'un coup.
Pour ne plus jamais y penser, active l'actualisation automatique : clic droit > Options du tableau croisé dynamique > onglet « Données » > coche « Actualiser les données lors de l'ouverture du fichier ». Le TCD se met à jour tout seul à chaque ouverture du classeur.
Données ajoutées hors plage source
Si tu n'as pas utilisé un tableau structuré comme source, ajouter des lignes sous la plage initiale ne les inclut pas dans le TCD. Tu dois d'abord modifier la plage source : clic droit sur le TCD > « Modifier la source de données » > élargis la plage pour couvrir les nouvelles lignes.
C'est la raison principale pour laquelle le tableau structuré (Ctrl+T) est recommandé : il élimine ce problème définitivement.
Segments (slicers) et graphique croisé dynamique
Les segments : des filtres visuels interactifs
Un segment est un bouton de filtre flottant que tu ajoutes à côté de ton TCD. Au lieu d'aller dans le volet des champs pour filtrer, tu cliques sur un bouton et le TCD se met à jour instantanément.
Pour insérer un segment : clique dans le TCD, onglet « Analyse du tableau croisé dynamique » > « Insérer un segment ». Coche les champs sur lesquels tu veux filtrer (Région, Année, Catégorie). Chaque champ crée un segment indépendant.
L'avantage des segments sur les filtres classiques : tu peux les connecter à plusieurs TCD en même temps (clic droit sur le segment > « Connexions au rapport »). Un seul clic sur « Nord » filtre simultanément tous les tableaux et graphiques de ton dashboard.
Le graphique croisé dynamique
Un graphique croisé dynamique est un graphique lié à un TCD : il reflète exactement les mêmes données, les mêmes filtres et les mêmes regroupements. Quand tu modifies le TCD, le graphique se met à jour automatiquement.
Pour le créer : clique dans le TCD, onglet « Analyse » > « Graphique croisé dynamique ». Choisis le type de graphique (barres groupées pour des comparaisons, courbes pour des tendances, histogramme pour des distributions).
Le graphique croisé dynamique a ses propres boutons de filtre intégrés, que tu peux masquer via clic droit > « Masquer les boutons de champ ». Combine un TCD + graphique croisé + 2-3 segments et tu as un mini-dashboard interactif sans VBA ni Power BI.
Limites du graphique croisé dynamique
Les types de graphiques combinés (courbe + histogramme) sont parfois limités avec les TCD. Si tu as besoin d'un graphique très personnalisé, copie-colle les valeurs du TCD dans une plage ordinaire et crée un graphique classique dessus. Tu gardes la flexibilité maximale.
Exemples métier concrets
Le TCD est utilisé dans tous les domaines. Voici quatre cas réels avec la configuration exacte pour chaque analyse.
Ventes par région et par commercial
Source : Tu as un fichier de 5 000 lignes avec les colonnes : Date, Commercial, Région, Produit, Quantité, Chiffre d'affaires.
Configuration TCD : Lignes : Région. Colonnes : Commercial. Valeurs : Somme Chiffre d'affaires.
Ce que tu obtiens : En 30 secondes tu vois quels commerciaux performent dans quelle région, et tu peux filtrer sur un trimestre grâce à un slicer Date.
Effectifs RH par service et contrat
Source : Un fichier RH avec : Nom, Service, Type de contrat (CDI, CDD, intérim), Date d'entrée, Salaire.
Configuration TCD : Lignes : Service. Colonnes : Type de contrat. Valeurs : NB (nombre d'employés).
Ce que tu obtiens : Tu vois instantanément combien d'intérimaires sont dans chaque service, et tu peux basculer la valeur sur Somme Salaire pour avoir la masse salariale.
Dépenses par catégorie et par mois
Source : Un export de compte bancaire ou de note de frais : Date, Libellé, Catégorie (transport, hébergement, repas), Montant.
Configuration TCD : Lignes : Catégorie. Colonnes : Mois (regroupement de la date). Valeurs : Somme Montant.
Ce que tu obtiens : Tu identifies les postes de dépenses qui dérapent mois après mois, sans formule SOMME.SI.ENS à maintenir.
Suivi de stock par entrepôt et référence
Source : Un suivi de stock : Référence produit, Entrepôt, Mouvement (entrée/sortie), Quantité, Date.
Configuration TCD : Lignes : Référence. Colonnes : Entrepôt. Valeurs : Somme Quantité. Filtre : Mouvement.
Ce que tu obtiens : Filtrer sur « sortie » te donne les quantités sorties par entrepôt et par référence. Basculer sur « entrée » te donne les réceptions. Deux vues, une seule source.
Erreurs fréquentes et comment les corriger
Les quatre erreurs ci-dessous représentent 90 % des problèmes rencontrés avec les TCD. Pour chacune, la cause et la solution directe.
La plage source ne couvre pas toutes les données
Cause : Tu as ajouté 200 lignes après avoir créé le TCD, mais la plage source était figée à la ligne 300. Le TCD ignore les nouvelles données.
Solution : Utilise un tableau structuré (Ctrl+T) comme source. La plage s'étend automatiquement. Sinon, clic droit > Modifier la source de données.
Des cellules vides dans la colonne clé
Cause : Des cellules vides dans la colonne « Région » ou « Catégorie » créent une ligne « (vide) » dans le TCD, qui perturbe l'analyse.
Solution : Nettoie la source avant de créer le TCD. Utilise Ctrl+H pour remplacer les blancs, ou Power Query pour les combler.
La colonne de valeurs contient du texte
Cause : Si une cellule de ta colonne « Chiffre d'affaires » contient « 5 000 € » au lieu de 5000 (nombre), Excel ne peut pas faire la somme et passe en mode NB.
Solution : Nettoie la colonne : supprime les symboles monétaires, convertis le texte en nombre via Données > Convertir.
Oublier d'actualiser après modification
Cause : Tu modifies la source, mais le TCD affiche toujours les anciennes valeurs.
Solution : Clic droit sur le TCD > Actualiser. Pour une actualisation automatique à l'ouverture du fichier, coche « Actualiser les données lors de l'ouverture du fichier » dans les options du TCD.
6 astuces pour maîtriser le TCD
Ces six astuces font passer du statut « je sais créer un TCD » à « je maîtrise vraiment le TCD ».
Double-clique sur une cellule de valeur dans le TCD pour voir exactement les lignes source qui composent ce total : Excel crée une nouvelle feuille avec les données filtrées.
Copie un TCD (Ctrl+C > Ctrl+V) : tu obtiens un second TCD indépendant que tu peux reconfigurer sans toucher au premier.
Formate les valeurs directement dans le TCD : clic droit sur une valeur > Format de nombre. Le format persiste même après actualisation, contrairement à un formatage de cellule classique.
Utilise « Afficher les valeurs en tant que... » pour afficher des pourcentages du total général, du total de la colonne ou de la ligne, sans créer de champ calculé.
Clique sur « Sous-totaux » dans l'onglet Création pour masquer les sous-totaux intermédiaires et obtenir un tableau plus lisible.
Trie directement en cliquant sur les flèches d'en-tête. Tu peux trier par valeur (par exemple, classer les régions de la plus forte à la plus faible) en faisant un clic droit > Trier.
Continuer le parcours Excel
Le tableau croisé dynamique est une des briques essentielles d'Excel. Retrouve toutes les leçons du parcours pour progresser étape par étape.
Questions fréquentes sur les tableaux croisés dynamiques
SOMME.SI.ENS calcule une somme avec des conditions écrites dans une formule. Le tableau croisé dynamique fait la même chose, mais en quelques clics, sans formule, et surtout il te permet de changer l'axe d'analyse à la volée. Si tu dois changer souvent de critère, le TCD est bien plus rapide. Si le calcul est ponctuel et fixe, SOMME.SI.ENS suffit.
Le TCD n'actualise pas les données automatiquement quand la source change. Tu dois cliquer sur « Actualiser » (clic droit sur le TCD > Actualiser, ou onglet Analyse du tableau croisé dynamique > Actualiser). Si tu as ajouté des lignes hors de la plage source, tu dois d'abord élargir la plage via « Modifier la source de données ».
Pas directement depuis l'interface classique. Pour consolider plusieurs feuilles, tu peux utiliser Power Query pour les fusionner en une table unique, puis créer ton TCD dessus. C'est la méthode la plus solide et la plus facile à maintenir.
Ce message apparaît quand des cellules de ta source sont vides. Deux solutions : nettoyer la source (remplir les cellules vides avec une valeur ou un zéro) ou, dans le TCD, faire un clic droit > Options du tableau croisé dynamique > onglet Disposition et format, et cocher « Pour les cellules vides, afficher : » avec un champ vide.
Excel bascule automatiquement sur Nombre (NB) quand il détecte des cellules vides ou du texte dans la colonne de valeurs. Vérifie d'abord que ta colonne source ne contient que des nombres. Ensuite, dans le TCD, clique sur la valeur, « Paramètres des champs de valeurs » et choisis « Somme ».
Oui, grâce aux champs calculés. Dans l'onglet Analyse du tableau croisé dynamique > Champs, éléments et jeux > Champ calculé, tu peux créer une colonne calculée avec une formule basée sur les autres champs du TCD. Par exemple, un taux de marge = (Bénéfice / Chiffre d'affaires) sans toucher à la source.
Oui, et c'est même recommandé. Convertis ta plage en tableau structuré (Ctrl+T) avant de créer le TCD. L'avantage : quand tu ajoutes des lignes dans le tableau structuré, la plage source du TCD s'élargit automatiquement. Il te suffit alors d'actualiser le TCD.
Tu veux maîtriser les TCD et aller plus loin ?
Le Dojo Club te donne accès à des formations Excel structurées, des lives d'experts chaque semaine et une communauté pour poser tes questions et progresser rapidement.
Rejoindre le Dojo