Tableau croisé dynamique Excel : guide pas à pas

Une liste de ventes de milliers de lignes est devant vous, et votre responsable demande « quel produit s'est vendu combien et dans quel mois ? » Vous pourriez vous battre avec des formules et régler les filtres un par un ; mais Excel dispose d'un outil qui répond à la même question en quelques clics : le tableau croisé dynamique. Il synthétise vos données selon la ventilation de votre choix, par glisser-déposer, sans supprimer ni modifier les données.

Dans ce guide, nous parcourons pas à pas la création d'un tableau croisé dynamique à partir de zéro, le placement correct des champs, le regroupement des dates, l'ajout d'un segment et la mise à jour du tableau. Les noms des menus peuvent légèrement varier selon la version d'Excel et la langue, mais la logique est presque identique.

Qu'est-ce qu'un tableau croisé dynamique et à quoi sert-il ?

Un tableau croisé dynamique est un rapport de synthèse interactif qui s'appuie sur vos données brutes. Il présente des calculs comme le total, la moyenne, le nombre, la valeur maximale ou minimale, ventilés par lignes et par colonnes. Il ne touche pas aux données d'origine ; il produit seulement une « vue ». Ainsi, à partir d'un même jeu de données, vous obtenez en quelques secondes des rapports par région, par produit ou par mois.

Les cas d'usage typiques comprennent les rapports de ventes, le suivi des dépenses, les synthèses de stock, le décompte des clients, les résultats d'enquêtes et l'analyse budgétaire.

Étape 1 : préparer les données pour le tableau croisé

Pour qu'un tableau croisé dynamique fonctionne correctement, les données doivent être bien structurées. Avant de commencer, vérifiez les points suivants :

La première ligne doit contenir les en-têtes de colonnes, et chaque colonne doit avoir un en-tête.
Les en-têtes doivent être uniques ; deux colonnes portant le même nom créent de la confusion.
Il ne doit pas y avoir de lignes ou de colonnes entièrement vides dans la plage de données.
N'utilisez pas de cellules fusionnées.
Chaque colonne ne doit contenir qu'un seul type de données : une colonne de dates ne doit pas contenir de texte, et une colonne de nombres ne doit pas contenir de texte comme « aucun ».
Ne conservez pas de lignes de total dans les données brutes ; le tableau croisé calcule lui-même les totaux.

Sélectionner la plage de données et la convertir en tableau Excel avec Ctrl+T est très utile. Lorsque vous ajoutez de nouvelles lignes au tableau, la plage source du tableau croisé s'étend automatiquement, sans qu'il soit nécessaire de la mettre à jour manuellement.

Étape 2 : insérer le tableau croisé dynamique

Cliquez sur n'importe quelle cellule à l'intérieur de vos données.
Dans l'onglet supérieur Insertion, cliquez sur le bouton Tableau croisé dynamique.
Dans la fenêtre qui s'ouvre, vérifiez que la plage source est bien sélectionnée.
Choisissez de le créer dans une nouvelle feuille de calcul ; cela évite de mélanger vos données brutes et le rapport.
Cliquez sur OK. Une zone vide et le volet « Champs de tableau croisé dynamique » apparaissent à droite.

Étape 3 : placer les champs au bon endroit

Le volet des champs liste vos en-têtes de colonnes. Vous façonnez le tableau en faisant glisser ces champs dans quatre zones :

Zone	Ce qu'elle fait	Exemple
Lignes	Listées verticalement à gauche	Nom du produit
Colonnes	Listées côte à côte en en-têtes en haut	Mois ou région
Valeurs	La synthèse numérique calculée	Total du montant des ventes
Filtres	Une liste déroulante en haut pour filtrer tout le tableau	Commercial

Par exemple, si vos données de vente comportent les colonnes « Produit », « Région », « Date » et « Montant » : faites glisser Produit dans Lignes, Région dans Colonnes, et Montant dans Valeurs. Le tableau affiche le total des ventes de chaque produit par région. Lorsque vous réorganisez les champs, le tableau se remodèle instantanément ; vous pouvez expérimenter librement, car les données ne sont pas endommagées.

Étape 4 : modifier le calcul des valeurs

Par défaut, Excel additionne les champs numériques et compte les champs texte. Pour modifier cela, cliquez sur le champ dans la zone Valeurs et sélectionnez Paramètres des champs de valeurs. Les options disponibles comprennent :

Somme, Nombre, Moyenne, Max, Min
Afficher les valeurs en : pourcentage du total général, pourcentage du total de ligne, écart par rapport à une période précédente, et calculs similaires
Format de nombre : monnaie, nombre de décimales et réglages d'affichage similaires

Si vous définissez le format de nombre ici, il est conservé lors de la mise à jour du tableau. Mettre en forme la cellule à la main ne garantit pas cela.

Étape 5 : regrouper les dates

Si vous voulez synthétiser des données quotidiennes par mois, trimestre ou année, ajoutez le champ date aux Lignes. Dans la plupart des versions, Excel découpe automatiquement les dates en année, trimestre et mois. Si le regroupement automatique ne se produit pas, vous pouvez faire un clic droit sur une cellule de la colonne de dates et utiliser l'option Grouper. Si vous sélectionnez le mois et l'année, le mois de janvier de deux années différentes ne se mélangera pas.

Si le regroupement ne fonctionne pas, il y a en général une valeur texte ou une cellule vide dans la colonne de dates qui n'est pas une vraie date. Vérifiez les données source et convertissez les dates en texte en vraies dates.

Étape 6 : ajouter un segment et une chronologie

Les listes déroulantes de la zone Filtres fonctionnent, mais elles ne sont pas pratiques. Les segments sont une alternative plus visuelle et plus rapide. Après avoir cliqué dans le tableau croisé, sélectionnez Insérer un segment dans l'onglet Analyse du tableau croisé dynamique, et cochez un champ comme la région ou le produit. Des boutons apparaissent à l'écran ; lorsque vous en cliquez un, le tableau est filtré selon ce critère. Vous pouvez même construire un tableau de bord en reliant plusieurs tableaux croisés au même segment. Pour les champs de date, vous pouvez ajouter une Chronologie pour obtenir un filtre défilant par mois ou par trimestre.

Étape 7 : graphique croisé dynamique et mise en forme visuelle

Transformer un tableau de synthèse en graphique se fait en une seule étape : cliquez dans le tableau, puis sélectionnez Graphique croisé dynamique dans l'onglet Analyse du tableau croisé dynamique. Le graphique évolue avec les segments et les filtres. Pour les présentations de rapports, n'utiliser qu'un ou deux types de graphiques et garder le tableau simple renforce le message. Vous pouvez facilement ajuster l'apparence du tableau en choisissant des styles prêts à l'emploi dans l'onglet Création.

Champ calculé et astuces supplémentaires

Parfois, vous avez besoin d'un calcul qui n'existe pas dans les données ; par exemple, il y a des colonnes « Montant » et « Quantité » mais pas de prix unitaire. Vous pouvez définir un nouveau champ en sélectionnant Champ calculé dans le menu Champs, éléments et jeux de l'onglet Analyse du tableau croisé dynamique (par exemple, Montant / Quantité). Ce champ est propre au tableau croisé et ne modifie pas les données source.

Quelques astuces supplémentaires : si vous ajoutez plusieurs champs aux Lignes, vous obtenez une vue hiérarchique (région, avec les produits en dessous). Utilisez l'onglet Création pour réduire et développer les sous-totaux. Pour empêcher que la largeur des colonnes ne se dérègle à chaque actualisation, décochez « Ajuster automatiquement les largeurs de colonnes lors de la mise à jour » dans les options du tableau croisé. Dans les grands tableaux, pour voir les dix enregistrements aux valeurs les plus élevées, utilisez l'option « Top 10 » dans le filtre des étiquettes de lignes.

Actualisation : que faire lorsque les données changent

Un tableau croisé dynamique n'est pas mis à jour automatiquement lorsque les données source changent. Faites un clic droit sur le tableau et sélectionnez Actualiser, ou utilisez le raccourci Alt+F5. Pour mettre à jour simultanément tous les tableaux croisés du classeur, utilisez le bouton Actualiser tout. Si vous voulez une actualisation automatique à l'ouverture du fichier, vous pouvez cocher « Actualiser les données à l'ouverture du fichier » dans les options du tableau croisé. Si vous avez transformé votre plage source en tableau Excel, les nouvelles lignes sont incluses dans le tableau lors de l'actualisation.

Erreurs courantes

En-tête vide : une colonne sans en-tête empêche la création du tableau croisé.
Oublier d'actualiser : c'est la raison la plus fréquente pour laquelle un tableau affiche d'anciennes données.
Nombres enregistrés comme texte : si une colonne numérique est comptée en « Nombre », les cellules sont au format texte ; convertissez-les en nombres.
Ajouter une ligne de total aux données brutes : le total est compté deux fois.
Saisir dans le tableau croisé : ne modifiez pas les valeurs du tableau croisé à la main ; elles seront perdues à l'actualisation.

Quelle version d'Excel est nécessaire ?

Le tableau croisé dynamique est disponible dans presque toutes les versions de bureau d'Excel. Les nouvelles fonctions (regroupement automatique des dates, tableaux croisés suggérés, etc.) varient d'une version à l'autre. Une version à licence perpétuelle peut suffire : Office 2024 Illimité ou Office 2021 Illimité incluent les outils d'analyse de base comme les tableaux croisés, les segments et les chronologies. Si vous voulez des fonctions mises à jour en continu et une synchronisation cloud, Office 365 1 an ou Office 365 personnalisé pour un seul utilisateur peuvent être plus adaptés. Les utilisateurs de Mac doivent regarder les versions spécifiques à Mac comme Office 2024 BIND pour macOS. Vous pouvez toutes les consulter dans la catégorie des licences logicielles ; sur LisansX, la livraison est numérique et le périmètre est indiqué sur la page du produit.

Questions fréquemment posées

Quelle est la différence entre un tableau croisé dynamique et un tableau normal ?

Un tableau normal affiche les données brutes ligne par ligne. Un tableau croisé dynamique synthétise ces données en les additionnant, en les comptant ou en en calculant la moyenne ; vous décidez par glisser-déposer quel champ apparaît à quel endroit.

Un tableau croisé dynamique modifie-t-il mes données source ?

Non. Un tableau croisé dynamique produit une vue en lecture seule. Les données brutes restent intactes.

Puis-je créer un tableau croisé à partir de données d'un autre fichier ?

Oui. Vous pouvez créer des tableaux croisés à partir de sources différentes en configurant une connexion de données externe ou en utilisant le modèle de données. Au départ, toutefois, intégrer les données dans le même classeur reste la voie la plus simple.