Vous avez un extrait de logiciel RH : 800 lignes, une par salarié, avec le service, la date d’embauche, le sexe, le salaire. Votre direction veut la masse salariale par service, l’âge moyen par sexe et une pyramide des âges. À la main, c’est une demi-journée de formules et de tris. Avec un tableau croisé dynamique, c’est trois minutes.
Le TCD est l’outil le plus rentable d’Excel : c’est celui qui demande le moins d’apprentissage pour le plus de résultat. Et pourtant c’est celui sur lequel je vois le plus de blocages en formation, presque toujours pour la même raison — les données de départ ne sont pas propres.
Ce guide reprend les cinq vidéos de la série Gestion des employés, dans l’ordre. Le fichier d’exercice est à télécharger en fin d’article.
Ce que fait vraiment un tableau croisé dynamique
Un TCD prend une liste de données brutes et la résume selon les axes que vous choisissez. Pas de formule à écrire, pas de tri préalable, pas de sous-totaux à insérer. Vous glissez un champ dans une zone, Excel recalcule.
La logique tient en une phrase : une ligne du tableau source = un événement, et le TCD agrège ces événements. Une ligne par salarié, par vente, par ticket, par intervention. Si votre feuille n’est pas construite comme ça — par exemple douze colonnes de mois au lieu d’une colonne « Mois » — le TCD ne pourra rien en faire de correct. C’est le point qui coince le plus souvent, et aucune fonctionnalité d’Excel ne le rattrape.
Le fichier source pour tous les exercice
Le classeur Gestion des employés utilisé dans les cinq vidéos est téléchargeable ici. Il contient la base brute et les corrigés sur des feuilles séparées : entraînez vous sur la base avant d’ouvrir les corrigés.
Les cinq vidéos sont également réunies dans la playlist Tableaux croisés dynamiques sur la chaîne.
Préparer les données : l’étape que tout le monde saute
Cinq règles, toutes obligatoires :
- Une seule ligne d’en-tête, en haut, sans cellule vide. Chaque colonne a un nom, et deux colonnes ne portent pas le même.
- Aucune cellule fusionnée. Le TCD refuse ou renvoie des résultats faux.
- Aucune ligne ni colonne entièrement vide à l’intérieur de la plage.
- Une seule information par colonne. « Dupont Jean » dans une colonne « Nom » vous interdit de regrouper par nom de famille.
- Des types cohérents. Une date doit être une date, pas un texte qui y ressemble. Test rapide : sélectionnez la colonne, si le contenu est aligné à gauche par défaut, c’est du texte, et le regroupement par mois ne fonctionnera pas.
Une fois la source propre, placez le curseur dedans et faites Ctrl + L pour la convertir en tableau structuré. Nommez-le dans Création de tableau > Nom du tableau (par exemple T_Employes). Deux bénéfices immédiats : les nouvelles lignes ajoutées en bas sont automatiquement intégrées à la source du TCD, et vous n’aurez plus jamais à redéfinir une plage.
C’est trente secondes de travail qui évitent 80 % des problèmes d’actualisation.
Créer votre premier tableau croisé dynamique
Curseur dans le tableau, puis Insertion > Tableau croisé dynamique > Nouvelle feuille de calcul.
Le volet de droite affiche la liste des champs et quatre zones :
- Lignes : ce que vous voulez lister verticalement (les services, les commerciaux, les catégories).
- Valeurs : ce que vous voulez calculer (la somme des salaires, le nombre de salariés).
- Colonnes : un second axe d’analyse, croisé avec le premier. Un seul champ, deux au maximum. Au-delà, le tableau devient illisible.
- Filtres : un champ qui filtre l’ensemble du tableau sans apparaître dedans.
Glissez Service dans Lignes et Salaire dans Valeurs : vous avez la masse salariale par service. Ajoutez Sexe dans Colonnes : vous avez la répartition hommes / femmes de cette masse salariale. Vous venez de faire en dix secondes ce qui demandait une colonne de SOMME.SI et un tri.
Deux comportements automatiques à connaître : Excel applique Somme aux champs numériques et Nombre aux champs texte. Et si un champ numérique arrive en « Nombre » alors que vous attendiez « Somme », c’est qu’au moins une cellule de la colonne contient du texte ou un espace.
Choisir le bon calcul et afficher des pourcentages
Double-cliquez sur l’en-tête d’un champ de la zone Valeurs pour ouvrir Paramètres des champs de valeurs. Deux onglets, et c’est là que se joue l’essentiel du paramétrage.
Synthèse des valeurs par : Somme, Nombre, Moyenne, Max, Min, Écartype. Le même champ peut être placé deux fois dans la zone Valeurs avec deux calculs différents — salaire moyen et masse salariale côte à côte, par exemple.
Afficher les valeurs : c’est l’onglet que 90 % des utilisateurs ignorent, et c’est le plus utile.
| Option | Ce que vous obtenez |
|---|---|
| % du total général | Le poids de chaque ligne dans l’ensemble |
| % du total de la ligne | La répartition horizontale, ligne par ligne |
| % du total de la colonne | La répartition verticale |
| Différence par rapport à | L’écart avec une référence que vous choisissez |
| Total cumulé | Un cumul progressif, idéal sur un axe de dates |
Pensez à renommer l’en-tête (« Somme de Salaire » n’a rien à faire dans un document remis à une direction) et à appliquer un format nombre via le bouton Format de nombre de cette même boîte de dialogue. Formater directement les cellules ne survit pas à une actualisation, le format défini ici, oui.
Regrouper les dates par mois, trimestre ou année
Placez Date d'embauche dans Lignes : Excel affiche une ligne par date, ce qui ne sert à rien. Clic droit sur une des dates, Grouper, puis cochez Mois et Années.
Deux précisions qui font gagner du temps :
Cochez toujours Années en plus de Mois. Sans ça, janvier 2019 et janvier 2024 sont additionnés dans la même ligne « janv », et personne ne s’en aperçoit avant la réunion.
Depuis Excel 2016, le regroupement des dates est automatique à la création du TCD. Pratique, sauf quand ça ne l’est pas. Pour le désactiver : Fichier > Options > Données > Désactiver le regroupement automatique des champs de date/heure.
Si le menu Grouper est grisé, c’est que la colonne contient du texte, pas des dates. Retour à la source : sélectionnez la colonne, Données > Convertir, et forcez le format Date à la troisième étape de l’assistant.
Créer des tranches et une pyramide des âges
Le regroupement fonctionne aussi sur les nombres, et c’est ce qui permet de construire une pyramide des âges en quelques clics.
Placez l’âge en Lignes, clic droit, Grouper, puis renseignez trois valeurs : début 20, fin 65, par 5. Excel crée les tranches 20-24, 25-29, et ainsi de suite. Les valeurs hors bornes sont regroupées dans « <20 » et « >65 », ce qui est exactement le comportement souhaité.
Ajoutez ensuite Sexe en Colonnes et le matricule en Valeurs (synthèse : Nombre). Vous obtenez l’effectif par tranche d’âge et par sexe. Un graphique croisé dynamique en barres horizontales empilées termine le travail.
Si votre source contient une date de naissance et pas un âge, ajoutez une colonne dans le tableau source. Dans un tableau structuré, la formule se propage seule :
=DATEDIF([@[Date de naissance]];AUJOURDHUI();"y")DATEDIF n’apparaît pas dans l’assistant fonction d’Excel — elle existe, elle fonctionne, il faut juste la taper en entier.
Regroupements personnalisés
Vous pouvez aussi regrouper à la main : sélectionnez plusieurs éléments dans le TCD avec la touche Ctrl, clic droit, Grouper. Excel crée un « Groupe1 » que vous renommez directement dans la cellule. Utile pour réunir des services en directions, ou des départements en régions commerciales.
Un avertissement, parce que je vois souvent des fichiers en souffrir : ce regroupement est stocké dans le TCD, pas dans les données. Un nouveau service apparaît dans la source ? Il ne rejoint aucun groupe, il reste orphelin, et personne ne le remarque. Vous voulez recréer le même regroupement dans un autre TCD ? Tout est à refaire.
Dès que le regroupement a du sens métier et qu’il va durer, créez une colonne de correspondance dans la source plutôt qu’un groupe dans le TCD. Une table de correspondance et une RECHERCHEV, ou une requête de fusion dans Power Query si le volume le justifie. Le regroupement manuel est fait pour l’analyse ponctuelle, pas pour un reporting mensuel.
Actualiser sans rien casser
Un TCD est une photo. Modifiez la source, il ne bouge pas.
- Alt + F5 actualise le TCD sélectionné.
- Données > Actualiser tout (Ctrl + Alt + F5) actualise tous les TCD du classeur.
- Clic droit sur le TCD, Options du tableau croisé dynamique, onglet Données : cochez Actualiser les données lors de l’ouverture du fichier. À faire systématiquement sur un fichier partagé.
Dans ces mêmes options, onglet Disposition et mise en forme, décochez Ajuster automatiquement la largeur des colonnes lors de la mise à jour. Sinon votre mise en forme saute à chaque actualisation, et c’est agaçant.
Enfin, si votre source est un tableau structuré, les lignes ajoutées sont prises en compte sans rien changer. Si c’est une plage figée, il faut passer par Analyse du tableau croisé dynamique > Modifier la source de données à chaque fois. Encore un argument pour le Ctrl + L.
Les erreurs que je vois le plus souvent en formation
Sélectionner des colonnes entières comme source. A:H fonctionne, mais gonfle le fichier et fait apparaître un élément « (vide) » dans tous les filtres.
Fusionner les cellules d’en-tête pour faire joli. Excel refuse alors de créer le TCD, avec un message peu explicite sur le nom du champ.
Croire qu’un TCD se met à jour tout seul. Il ne le fait jamais sans actualisation. Les chiffres faux présentés en réunion viennent presque toujours de là.
Écrire des formules dans les cellules à droite d’un TCD. Le tableau change de taille à chaque actualisation et écrase vos formules. Placez vos calculs annexes sur une autre feuille.
Empiler deux TCD verticalement. Quand le premier s’agrandit, Excel affiche « Un rapport de tableau croisé dynamique ne peut pas en chevaucher un autre ». Placez-les côte à côte, ou sur des feuilles séparées.
Cliquer sur une cellule de TCD dans une formule. Excel insère LIREDONNEESTABCROISDYNAMIQUE(), qui casse dès que la structure change. Pour le désactiver : Analyse du TCD > flèche à côté d’Options > décocher Générer GetPivotData.
Questions fréquentes
Quelle différence entre un tableau structuré et un tableau croisé dynamique ?
Le tableau structuré (Ctrl + L) organise et nomme vos données brutes. Le TCD les résume. Le premier est la matière première, le second le rapport. On les utilise ensemble.
Pourquoi mon TCD compte au lieu d’additionner ?
Au moins une cellule de la colonne contient du texte, un espace ou une cellule vide. Sélectionnez la colonne dans la source et vérifiez que le compteur en bas de fenêtre affiche bien une somme.
Peut-on faire un TCD à partir de plusieurs feuilles ?
Oui, mais pas directement. Fusionnez les feuilles avec Power Query, puis créez le TCD sur le résultat. C’est plus propre et plus durable que les anciennes plages de consolidation multiples.
Comment supprimer un TCD ?
Analyse du TCD > Sélectionner > Tableau croisé dynamique entier, puis Suppr. Supprimer la feuille fonctionne aussi, mais vérifiez d’abord qu’aucun graphique ne s’y réfère.
Mon fichier est devenu très lourd, est-ce à cause des TCD ?
En partie : chaque TCD conserve une copie de la source en mémoire cache. Dans Options du TCD > Données, décochez « Enregistrer les données source avec le fichier » et cochez « Actualiser lors de l’ouverture ». Le fichier s’allège nettement.