Maîtriser le Tableau Croisé Dynamique EXCEL en 1 HEURE

Dans ce tutoriel, nous allons aborder un des grands sujets du tableur Excel: le tableau croisé dynamique – également connu sous le nom de "table pivot". Il s'agit d'un outil puissant et incontournable pour gérer vos données de manière simple et rapide. Pour de nombreux utilisateurs d'Excel, le tableau croisé dynamique reste un "tabou" – trop compliqué et trop abstrait… Mais la réalité est tout autre. L'utilisation du "TCD" est assez simple et une fois que l'on a compris le principe, cela va tout seul…

N'ayez pas peur de la longueur de ce tutoriel. Installez-vous confortablement et dites-vous que d'ici une heure, vous allez maîtriser cet outil mieux que la grande majorité d'autres utilisateurs d'Excel! Je vous assure que cette heure sera un très bon investissement 😉

À la fin de ce tutoriel, vous aurez appris comment utiliser de manière efficace les fonctionnalités de base des tableaux croisés dynamiques. Les fonctionnalités plus avancées seront abordées dans le(s) tutoriel(s) suivant(s).

Alors commençons… par le commencement…

Qu'est-ce qu'un tableau croisé dynamique (TCD) dans Excel?

Vous pouvez considérer un tableau croisé dynamique comme un rapport. Cependant, contrairement à un rapport statique, un tableau croisé dynamique fournit une vue interactive de vos données. Avec très peu d'effort (et sans formules), vous pouvez examiner les mêmes données sous de nombreux angles différents. Vous pouvez regrouper les données en catégories, répartir les données en années et en mois, filtrer les données pour inclure ou exclure des catégories et même créer des graphiques.

Tutoriel pratique – comprendre et utiliser le TCD en 10 étapes simples

Dans ce tutoriel, nous allons procéder pas à pas: à partir d'un fichier des données (pour lequel un TCD est particulièrement bien adapté), nous allons suivre les différentes étapes de la création et de la personnalisation d'un TCD. Comme le "tableau croisé dynamique" est un sujet qui peut sembler complexe, j'ai divisé ce tutoriel en plusieurs étapes assez simples. Cela devrait rendre la compréhension de ce sujet plus facile.

Et pour pouvoir suivre plus facilement ce tutoriel, vous pouvez télécharger gratuitement le fichier Excel avec les données et les exemples de tableaux croisés dynamiques abordés dans ce tutoriel.

Étape 1: La création d'un tableau croisé dynamique en Excel

Pour commencer, on doit disposer des données qui seront utilisées par le TCD. Dans ce tutoriel, j'ai préparé un échantillon des données de ventes des fournitures de bureau. Cet échantillon contient 5 colonnes et 500 lignes – vous le trouverez dans le fichier Excel téléchargeable.

Selon si vos données sont en état brut (voir capture d'écran n°1) ou sous forme d'un "Tableau Excel" (voir n°2), la manière d'insérer un tableau croisé dynamique dans votre classeur varie légèrement.

En gros, vous devez sélectionner une cellule à l'intérieur de vos données et ensuite cliquer, dans le Ruban, dans le tab "Insertion", sur le bouton "Tableau croisé dynamique":

Une fenêtre va alors s'ouvrir pour vous demander 2 choses:

1. La source des données pour le TCD
2. L'emplacement où le TCD va se trouver

En ce qui concerne la source des données, Excel va lui-même proposer la source. Selon si vous travaillez avec des données brutes ou avec un Tableau Excel, il va vous proposer soit l'adresse d'une plage (ici par exemple Donnees_1!$A$1:$E$500), soit le nom de la table Excel (ici Tableau1) – voir les captures d'écran.

Pour l'emplacement du futur tableau croisé dynamique, Excel va, par défaut vous proposer de créer une nouvelle Feuille. Vous pouvez changer cela et indiquer un endroit précis dans une Feuille existante. Comme le TCD est "dynamique", sa taille peut changer. L'adresse de l'emplacement que vous indiquez correspondra donc au coin supérieur gauche du futur TCD.

Une fois la source des données et l'emplacement du TCD choisi, vous cliquez sur "OK" et vous allez vous retrouver en face de votre Tableau croisé dynamique vide…

Étape 2: Ajouter un champ dans un tableau croisé dynamique

Maintenant que notre TCD est prêt, nous allons pouvoir commencer à l'utiliser en lui indiquant ce qu'il doit afficher.

En cliquant dans l'intérieur du tableau (pour le moment vide), vous verrez apparaitre à droite de votre écran le panneau de configuration de votre TCD. Il se compose de plusieurs parties:

1. la liste des champs disponibles
2. les 4 zones où peuvent être placés les différents champs selon ce que vous souhaitez voir comme information: "Filtres", "Colonnes", "Lignes" et "Valeurs"

Nous allons maintenant ajouter un champ (une colonne de notre tableau de données) dans le tableau croisé dynamique (que j'ai placé juste à côté de mes données pour une visualisation plus facile).

Simplement, cliquez sur le champ "Montant_vente" et tirez le vers la zone "Valeurs".

Vous verrez alors dans votre Tableau Croisé Dynamique le montant de la somme des "Montant_vente" de toute votre ensemble des données (ici dans l'exemple, le total est de 76461) .

Maintenant que l'on voit le grand total, nous pouvons ajouter un champ pour lequel nous voulons voir le détail. Dans notre exemple, on va utiliser le "Produit".

Pour cela, tout comme avec le champ précédent, cliquez sur "Produit" et tirez-le vers la zone "Lignes". Une fois cela fait, vous verrez les totaux par "Produit"…

Vous avez donc la séparation des totaux dans les Lignes (par "Produit") et il s'agit des totaux des valeurs qui se trouvent dans la zone Valeurs (ici "Montant_vente").

Comme vous pouvez le constater, le "Total général" affiche le même montant qu'avant l'ajout de champ "Produit". C'est logique vu que l'on travaille avec le même ensemble des données.

Type de calcul des "Valeurs"

Pour des valeurs numériques (comme le "Montant_vente"), Excel va par défaut afficher la "Somme" des valeurs. Mais il est également possible d'afficher un autre type d'agrégation. Notamment le "Nombre", "Moyenne", "Minimum", "Maximum" et d'autres.

Pour le choisir, faites un click droit dans les valuers du TCD. Choisissez "Paramètres des champs de valeurs…". Ensuite, vous pourrez choisir le type d'agrégation que vous souhaitez:

Sachez que pour les valeurs textuelles, Excel utilisera par défaut l'agrégation de type "Nombre". Ce qui est logique vu qu'il n'est pas possible de faire une somme ou de déterminer le Maximum/Minimum/etc.

Vous pouvez donc afficher dans le TCD de nombreuses informations différentes qui découlent de vos données…

Ajouter un autre champ dans les "Valeurs"

Vous pouvez aussi facilement afficher plusieurs champs de valeurs en même temps. Pour cela, il suffit de tirer un autre champ vers la zone "Valeurs". Ainsi, nous pourrons voir, dans notre exemple, la somme des ventes par produit mais également le nombre de différents articles vendus. Tout cela en tirant le champ "Quantite" vers la zone "Valeurs":

Vous pouvez remarquer le champ "∑ Valeurs" dans la zone "Colonnes". Sachez que c'est Excel qui l'a ajouté automatiquement pour signifier que les différentes colonnes afficheront les sommes des différentes valeurs.

Étape 3: Trier les données par la valeur

Maintenant que nous avons nos montants de ventes par "Produit", on pourrait se demander lesquels des produits se vendent le mieux. Pour cela, nous pouvons trier les totaux dans notre tableau croisé dynamique.

Pour cela, vous pouvez faire un click droit sur une des valeurs de la colonne du TCD que vous souhaitez trier (ici donc une des valeurs de la colonne "Somme de Montant_vente") et dans le menu qui apparait, vous choisissez "Trier" et ensuite le type du tri: du petit au grand ou du grand au petit:

Vous obtiendrez donc la liste des montants de ventes par Produit: du produit aux plus grandes ventes à celui qui s'est vendu le moins…

Vous pouvez également trier par des champs contenant du texte (par exemple le champ "Produit"). Dans ce cas, Excel va vous proposer comme type de tri "A à Z" ou "Z à A"…

Étape 4: Rafraîchir les données

Le "Tableau croisé dynamique" porte bien son nom pour deux raisons:

1. il est dynamique en ce qui concerne son apparence et sa portée – selon les champs que vous choisissez d'y afficher et sous quelle forme
2. il est dynamique car il s'adapte aux changements dans les données – si vos données changent (que ce soit en valeurs ou en nombre de lignes si vous utilisez comme source de données les colonnes entières comme c'est mentionné dans l'étape n°1), il suffit de "Actualiser" le TCD et il affichera des valeurs mises à jour.

Il y a deux moyens simples pour rafraîchir un tableau croisé dynamique:

Soit via le bouton "Actualiser" qui se trouve sur le Ruban d'Excel:

Soit avec un click droit n'importe où à l'intérieur du TCD et ensuite click sur "Actualiser":

Vous avez également la possibilité d'indiquer à Excel dans les options du TCD d'actualiser le tableau à chaque ouverture du Classeur. Pour cela, click droit à l'intérieur du TCD, ensuite "Options du tableau croisé dynamique" et ensuite, dans le volet "Données", cocher l'option "Actualiser les données lors de l'ouverture du fichier".

Ceci est particulièrement utile si vos données se trouvent dans un fichier séparé ou si vous y accédez via une requête/connexion.

En ce qui concerne notre exemple pratique, pour tester le comportement de votre tableau, vous pouvez simplement changer (sensiblement pour que ce soit mieux visible) le montant d'une des ventes. Ensuite vous actualisez le TCD et vous verrez que le total du Produit en question a changé ainsi que le total général.

Étape 5: Ajouter d'autres champs de données dans le TCD (TCD bi-dimensionnel)

Maintenant que nous avons ajouté le premier champ ("Produit") au TCD, nous pouvons en ajouter d'autres pour pour afficher les informations souhaités.

Nous allons donc ajouter le champ "Region" pour aller plus en détails de nos données de ventes. Et vous verrez la différence que vous obtiendrez selon si vous ajouter le champ "Region" dans la zone "Lignes" ou dans la zone "Colonnes".

Voici donc la comparaison:

Le champ "Region" ajouté dans la zone:	Lignes	Colonnes
Aperçu du panneau de gestion des champs
Aperçu du Résultat (TCD)
Remarque	Cet affichage a l'avantage d'être plus simple mais on ne voit pas les totaux par région – uniquement la répartition par "Region" des ventes de chaque "Produit". Ce qui est intéressant ici, c'est la possibilité de cacher ou d'afficher les détails par "Produit". Vous pouvez donc afficher par exemple le détail par "Region" uniquement pour les crayons noirs… Vous pouvez le faire en cliquant sur les petites icônes "+" et "–" à côté des intitulés des produits.	Cette configuration est très compacte et on peut facilement voir les différences entre les différent sous-totaux. On voit aussi très clairement les deux "grands totaux" – celui des ventes par Produit et celui des ventes par Région…

Étape 6: Formater les données dans un tableau croisé dynamique

Nous avons maintenant un rapport pratique qui affiche des informations intéressantes. Mais pour le rendre plus présentable, nous avons la possibilité de formatter les données qui se trouvent dans le tableau croisé dynamique. Il s'agit de mêmes possibilités de formatage que pour les cellules "normales".

Attention: il ne suffit pas seulement de choisir les cellules du TCD et de changer le format. Car comme il est dynamique, dès que l'affichage de votre TCD change, les formats liés aux cellules ne seront plus corrects.

Il faut donc lier les formats aux différents champs du TCD. Voici comment s'y prendre:

1. click droit sur n'importe quelle valeur du champs à formatter (dans notre exemple le "Montant_vente")
2. click "Format de nombre…"
3. choix du format à afficher (Nombre, Monétaire,…)

Notre tableau croisé dynamique après le formatage des nombres ressemblera par exemple à ceci:

C'est déjà plus présentable…

Étape 7: Grouper les données par date

Une autre fonctionnalité pratique que le tableau croisé dynamique vous offre est le groupement des données par date.

Il est ainsi possible d'afficher, dans notre exemple, les montants de vente par jour, par mois ou par année. Très pratique! Voyons alors comment cela marche.

Pour commencer, dans notre exemple, nous allons enlever le champ "Region" pour garder les champs "Montant_vente" dans la zone "Valeurs" et "Produit" dans la zone "Lignes".

Pour pouvoir grouper les données par date, il nous faut, logiquement, un champ qui contient des dates sur lesquelles se baser. Dans notre exemple, il s'agit du champ "Date_vente". Nous allons maintenant tirer ce champ et le glisser dans la zone "Colonnes".

Comme vous pouvez le constater, Excel a automatiquement ajouté les champs "Années" et "Trimestres". Ces champs, il les a "deviné" sur la base des dates qui se trouvent dans la colonne des dates que vous voulez utiliser (dans notre exemple la colonne "Date_vente"). Si vos données ne contiennent que les dates d'une année, Excel ne va pas créer le champs "Années" car il n'est pas nécessaire.

Voici à quoi va ressembler notre TCD maintenant:

Excel prend le plus petit groupe possible (sur la base des dates qui se trouvent dans les données) et l'affiche comme colonnes dans le TCD. Dans notre exemple, il s'agit des mois. Mais comme nous avons laissé les champs "Trimestres" et "Années" dans le setup du TCD, on verra alors le tableau croisé dynamique avec des données agrégées.

Vous remarquerez les petites icônes "+" près des années. Elles vous permettent de voir le détail de l'année – par trimestre. Et ensuite le détail de chaque trimestre par mois:

Si vous ne souhaitez pas avoir cette possibilité, il vous suffit de retirer le champ "Trimestres" et/ou "Années" de la zone "Colonnes" du panneau des champs du TCD. Ainsi, vous ne verrez que les totaux par mois. Mais attention! Les totaux des mois correspondront aux ventes de chaque mois peu importe l'année! Donc dans notre exemple, le total de "janvier" sera la somme des ventes de janvier 2020 et de janvier 2021!

Il faut le garder à l'esprit et bien choisir ce que vous souhaitez vraiment voir dans votre TCD.

Il existe encore une autre manière de grouper les dates dans un tableau croisé dynamique: si vous faites un click droit sur les intitulés des dates, vous pourrez choisir dans le menu la fonctionnalité "Grouper". Elle vous permettra de choisir par quel unité de temps vous souhaitez grouper vos données (vous pouvez choisir plusieurs niveaux en même temps). En plus, elle vous permet de limiter l'effet de groupement de vos données à afficher à une certaine période…

Et si vous souhaitez revenir à l'affichage normale des dates ou si Excel a regroupé vos dates sans que vous le souhaitiez: il suffit de les "Dissocier".

Étape 8: Afficher les pourcentages du total

Maintenant, nous allons aborder une autre fonctionnalité analytique très prisée qui est disponible dans les TCD de Excel: le pourcentage du total. Si, en plus des valeurs absolues, vous avez besoin d'afficher les valeurs relatives, c'est assez simple.

Toujours dans notre exemple, nous allons afficher le pourcentage des ventes de chaque produit par rapport au total des ventes. Il n'est pas nécessaire calculer les pourcentages manuellement. Le TCD peut calculer les pourcentages automatiquement.

Alors, comment on procède?

Il suffit d'effectuer un click droit sur la colonne avec les valeurs (dans notre exemple donc "Montant_vente"). Dans le menu qui apparaît, vous choisissez "Afficher les valeurs >" et ensuite "% du total général". Voyez sur la capture d'écran:

Et voici le résultat:

Mais on peut aller encore un peu plus loin… Le TCD d'Excel nous donne la possibilité d'afficher plusieurs fois le même champ. Vous pouvez donc tirer le champ "Montant_vente" vers la zone "Valeurs" une seconde fois! Comme mentionné dans l'Étape 1 de ce tutoriel, Excel va attribuer un nouveau nom à ce champ. Il deviendra "Montant_vente2". Et pourquoi faire cela?

Cela va nous permettre de voir, côte à côte, les montants des totaux de ventes ainsi que leur contribution au grand total. On affichera une fois la somme des montants de ventes "sans calcul" et la seconde en tant que "% du grand total"…

Pour rappel, vous pouvez toujours modifier le format de pourcentage – par exemple afficher les pourcentages sans décimales (voir Étape 6 du tutoriel).

Étape 9: Les possibilités d'affichage d'un tableau croisé dynamique

L'outil qu'est le tableau croisé dynamique est très flexible. Non seulement en ce qui concerne les possibilités d'affichage des données mais également en ce qui concerne son aspect visuel. Vous trouverez la plupart de ces possibilités sur le Ruban. Une fois que vous cliquez à l'intérieur d'un TCD, deux nouveaux onglets apparaîtront sur votre Ruban Excel.

Il s'agit de "Analyse du tableau croisé dynamique" et de "Création":

C'est dans l'onglet "Création" que vous trouverez de quoi gérer l'apparence de votre TCD.

Vous pouvez y:

• modifier la disposition du TCD en choisissant d'afficher (ou pas) les totaux généraux, les sous-totaux, …
• choisir si oui ou non vous souhaitez afficher les en-têtes des colonnes et lignes
• changer les couleurs de votre TCD

À vous de découvrir toutes les possibilités de modification de l'apparence d'un tableau croisé dynamique! Il y en a pour tous les goûts et tous les besoins…

Étape 10: Les possibilités avancées des tableaux croisés dynamiques

Nous venons de lister les principes de base d'utilisation d'un tableau croisé dynamique dans Excel. Avec ces explications, vous allez probablement couvrir 90% de vos besoins mais sachez, qu'Excel propose encore d'autres fonctionnalités dans son TCD… En voici quelques unes…

• possibilité d'ajouter des "champs calculés": c'est à dire des données qui ne se trouvent pas directement dans votre ensemble des données
• possibilité de filtrer les données à afficher dans le TCD
• déplacer les colonnes selon l'ordre souhaité
• modifier les noms des colonnes affichées
• possibilité de créer des graphiques liés directement aux résultats du tableau croisé dynamique
• utilisation des "Segments" – une manière ultra simple et rapide de filtrer votre TCD. Très peu connu mais très pratique!
• … et bien d'autres…

Pour que ce tutoriel ne soit pas trop long, j'expliquerai ces fonctionnalités avancées dans un tutoriel séparé.

Quelques observations à la fin

Pour finir, j'aimerais bien attirer votre attention sur les points que l'on retrouve souvent dans les Fora et autres Foires aux questions…

• Un Classeur Excel peut contenir plusieurs Tableaux Croisés Dynamiques. Il peut y en avoir plusieurs même sur la même feuille. Dans ce cas, attention à leur taille "dynamique".
• Il est possible de créer plusieurs TCD qui utilisent les mêmes données.
• Le même champ (colonne des données) ne peut pas se trouver en même temps dans la zone "Lignes" et dans la zone "Colonnes".
• Si vous voulez copier les résultats affichés par un TCD (vers un autre Classeur par exemple), vous devrez le coller en tant que valeurs (et ensuite éventuellement en tant que formats). Si vous faites simplement un "coller", vous allez coller le TCD en tant que tel – avec les liens vers la source originale des données. Et si après, les données changent, votre TCD collé changera également. Ou pire, il n'aura plus accès aux données d'origine et n'affichera plus rien.
• Il n'est pas possible d'insérer des colonnes, ni des lignes à l'endroit où se trouve un tableau croisé dynamique.
• Il se peu que votre TCD affiche des "vieilles" valeurs (valeurs qui ne se trouvent plus dans votre ensemble de données). Et cela malgré que vous Actualisiez votre TCD. Le problème vient de la mémoire "cache" de votre tableau. Il faut alors "purger" la mémoire cache du TCD manuellement. Voici comment faire…

Conclusion de ce tutoriel

Nous voici à la fin de ce tutoriel. J'espère que ma promesse de départ est tenue et que vous ne considérerez plus le tableau croisé dynamique comme quelque chose d'obscur et compliqué. S'il est correctement utilisé, le TCD peut vous économiser énormément de temps. Il peut remplacer de nombreuses formules de recherche (RECHERCHEV etc.) et ainsi prévenir des erreurs "humaines". Les Actualisations sont aussi très utiles et vous permettent d'utiliser le même TCD indépendamment des données qui peuvent changer.

Donc aucun utilisateur +/- avancé d'Excel ne devrait négliger cet outil. Il peut rendre de nombreux services pour un investissement minimal en temps…

Pour ce qui est des fonctionnalités avancées du TCD, n'hésitez pas à consulter le tutoriel suivant qui sera bientôt disponible!

Pour aller plus loin en Excel

Si ce tutoriel vous a été utile, vous apprécierez peut-être d'autres articles et tutoriels sur Excel. J'espère qu'ils vont vous aider à rendre votre travail plus rapide et plus efficace…

• Liste de toutes les fonctions Excel (déjà plus de 500 fonctions à votre disposition!)
• Tutoriels sur les fonctions d'Excel les plus utilisées: SI, NB.SI, RECHERCHEV & la grande nouveauté: RECHERCHEX
• Calculer la moyenne pondérée en Excel
• Calculer l'âge actuel dans Excel
• Tout savoir sur les ARRONDIS en Excel
• Comment convertir Excel en PDF: méthode simple et gratuite