Vous disposez de grandes listes de données et vous souhaitez en faire la synthèse dans un rapport ? Ou bien les analyser selon différents points de vue et critères de recherche ? Excel contient un outil spécialement dédié à ces tâches : le tableau croisé dynamique !

Lorsque vous travaillez avec de grandes quantités de données, il est fréquent de vouloir en faire une synthèse, que ce soit pour obtenir une vision globale et rapidement compréhensible d'un ensemble de valeurs ou, au contraire, pour étudier plus précisément un sous-ensemble répondant à des critères particuliers.

Vous pourriez bien sûr prendre le temps de trier, filtrer puis reporter manuellement vos précieuses données dans un nouveau tableau qui répond à vos besoins. Cependant, chaque fois que vous ajouterez ou supprimerez des données à votre liste initiale, vous prendrez le risque que votre tableau de destination les « oublie » et vous devrez régulièrement vérifier vos formules de calcul pour éviter cela. Par ailleurs, si vous souhaitez regrouper et synthétiser vos données selon des critères différents, vous devrez alors modifier manuellement la structure de votre tableau de destination, voire créer plusieurs tableaux de synthèse si vous souhaitez conserver différents points de vue sur vos données. Ces manipulations s'avèreront rapidement fastidieuses et sources d'erreur.

Heureusement, l'outil de tableau croisé dynamique d'Excel vous permet de réaliser toutes ces tâches en seulement quelques clics et sans taper aucune formule. Afin de vous présenter les différentes notions nécessaires à l'utilisation des tableaux croisés dynamiques, cette fiche pratique est accompagnée d'un classeur exemple, téléchargeable gratuitement ici. Toutes les manipulations décrites dans ce guide s'appuient sur ce fichier : nous vous recommandons donc vivement de l'utiliser en parallèle de votre lecture.

Télécharger un exemple de tableau croisé dynamique pour Excel

Cette fiche a été réalisée avec la version Office 2019 d'Excel. Cependant, toutes les fonctions présentées sont disponibles à l'identique dans les versions Office 2016 et Microsoft 365 (ex-Office 365) d'Excel, à l'exception de la création des styles personnalisés de tableau croisé dynamique qui n'est pas proposée dans la version Web du tableur de Microsoft.

Pourquoi utiliser un tableau croisé dynamique ?

Vous travaillez sur une importante liste de données, par exemple un journal des ventes, et vous souhaitez connaître rapidement la somme des éléments de cette liste en les regroupant simultanément selon plusieurs critères, par exemple par catégorie de produit et mois de vente. Dans ce cas de figure, utiliser un tableau croisé dynamique vous permettra d'obtenir rapidement ce résultat, de choisir comment seront disposées les données de synthèse et de modifier instantanément cette disposition chaque fois que vous en aurez besoin. Le schéma ci-dessous illustre la façon dont un tableau croisé dynamique synthétise des données et les organise visuellement.

On parle de tableau « croisé » car les catégories utilisées pour regrouper les données peuvent être réparties à la fois en lignes et en colonnes. Sur l'exemple de tableau croisé dynamique en haut à droite de l'image, la cellule jaune au « croisement » des champs « Janvier » et « Catégorie 1 » renvoie la somme de tous les éléments de la liste de données initiale qui contenaient les valeurs « Janvier » et « Catégorie 1 » dans les colonnes « Mois » et « Catégorie ». Dans le cas présent, un seul élément répond à ces deux critères, mais la liste de données pourrait contenir des milliers d'éléments qui seraient alors traités automatiquement par le tableau croisé dynamique, sans que vous ayez besoin de trier ou de filtrer les données au préalable.

On parle également de tableau « dynamique » car, d'une part, sa disposition peut être modifiée instantanément via de simples manipulations à la souris ou au clavier et, d'autre part, tout ajout, modification ou suppression de données dans la liste initiale est automatiquement prise en compte dans le résultat des regroupements.

Comment créer un tableau croisé dynamique avec Excel ?

Avant de générer un tableau croisé dynamique, vous devez vous assurer que les données de votre liste initiale respectent les prérequis suivants :

• Les données d'une même colonne doivent toutes être du même type (nombre, date, texte, etc.).
• La première ligne de votre liste de données doit contenir les en-têtes de colonnes.
• Votre liste ne doit pas contenir de cellules fusionnées.
• Votre liste ne doit pas contenir de cellules vides (Excel est capable de reconnaître et de gérer les cellules vides le cas échéant, mais il est préférable de les éviter au maximum).

Une fois que ces conditions sont remplies, vous pouvez vous lancer dans la création de votre tableau croisé dynamique.

• Cliquez quelque part dans la liste de données, puis, dans l'onglet Insertion du ruban, cliquez sur Tableau croisé dynamique.

• Dans la boite de dialogue Créer un tableau croisé dynamique, vérifiez que le champ Tableau/Plage contient bien les références de la liste de données. Par défaut, si vous avez bien cliqué quelque part dans la liste et qu'elle ne contient ni cellule vide ni cellule fusionnée, Excel a correctement déterminé sa taille. Si ce n'est pas le cas, cliquez sur l'icône de flèche vers le haut à droite du champ Tableau/Plage et sélectionnez manuellement l'ensemble de la liste de données.
• Dans la section Choisissez l'emplacement de votre rapport de tableau croisé dynamique, vérifiez que la case Nouvelle feuille de calcul est bien cochée. Vous pouvez bien sûr cocher la case Feuille de calcul existante et sélectionner manuellement un emplacement dans la même feuille que la liste de données, mais la lisibilité de l'ensemble risque d'être moins bonne.
• Validez la création du tableau croisé dynamique en cliquant sur le bouton OK de la boite de dialogue Créer un tableau croisé dynamique.

• Une nouvelle feuille de calcul est insérée par Excel dans la classeur et un volet nommé Champs de tableau croisé dynamique est apparu à droite de votre écran.

• Le volet Champs de tableau croisé dynamique est composé de cinq cadres. Le premier vous présente la liste des champs de données disponibles, qui correspond aux en-têtes de colonnes de la liste de données initiale. Les quatre autres cadres représentent les zones du tableau croisé dynamique : c'est en déplaçant les champs de données disponibles dans les différentes zones que vous allez créer la disposition du tableau croisé dynamique.
• Dans la liste des champs de données disponibles, cochez les cases à gauche des mots Région, Produit et Ventes. Un tableau croisé dynamique est apparu dans votre feuille de calcul et, si vous examinez les quatre cadres inférieurs, vous verrez que les mots Région et Produit ont été ajoutés dans la zone Lignes et que les mots Somme de Ventes sont apparus dans la zone Valeurs.

À ce stade, vous avez créé un tableau croisé dynamique qui regroupe vos données par région puis par nom de produit et présente un sous-total pour chaque catégorie ainsi qu'un total général au bas du tableau. Vous constaterez également que les catégories du tableau croisé dynamiques sont agencées dans le même ordre que les champs de données dans le cadre Lignes du volet Champs de tableau croisé dynamique disponible. Retenez ce principe, c'est le cœur du système d'organisation d'un tableau croisé dynamique.

• Dans le cadre Lignes du volet Champs de tableau croisé dynamique, cliquez sur la flèche grise à droite du nom Région et, dans le menu contextuel qui apparaît, cliquez sur Descendre, ou faites un clic-gauche maintenu sur le nom Région et faites le descendre sous le nom Produit avant de relâcher le bouton de la souris.

• Dans le cadre Lignes, le nom Produit est désormais en première position et le nom Région en seconde. Du côté du tableau croisé dynamique, vous constaterez que l'ordre des regroupements et des sous-totaux de données s'est également inversé.

Cette simple manipulation vous donne un aperçu de la puissance et de la souplesse d'un tableau croisé dynamique. En quelques secondes, vous avez obtenu un tout nouveau point de vue sur vos données sans avoir eu besoin de les trier ou de les réorganiser dans la liste initiale.

• Dans le volet Champs de tableau croisé dynamique disponibles à droite de votre écran, dans le cadre supérieur, faites un clic-droit sur le nom Mois puis sélectionnez Ajouter aux étiquettes de colonnes, ou faites un clic-gauche maintenu sur le nom Mois et déplacez-le dans la zone Colonnes avant de relâcher le bouton de la souris.

Dans la feuille de calcul, le tableau croisé dynamique s'est étendu sur trois colonnes supplémentaires et présente désormais, en plus des sous-totaux par nom de produit et par région, des sous-totaux par mois pour chacune de ces catégories ainsi qu'un total général à droite du tableau.

À ce stade, vous avez créé un tableau croisé dynamique complet : à partir d'une liste de données organisées en lignes, vous avez obtenu un rapport de synthèse que regroupe vos données en catégories, les répartit en lignes et en colonnes et en affiche les sous-totaux. Essayez de changer la répartition des catégories entre les lignes et les colonnes pour appréhender la façon dont Excel modifie le tableau croisé dynamique.

• Dans le volet Champs de tableau croisé dynamique disponibles, avec la souris, faites passer le nom Mois de la zone Colonnes vers la zone Lignes, en dernière position après les noms Produit et Région, puis faites passer le nom Produit de la zone Lignes vers la zone Colonnes et observez les modifications qui se produisent à chaque étape dans le tableau croisé dynamique.

Comment mettre en forme un tableau croisé dynamique avec Excel ?

Pour rendre votre tableau croisé dynamique plus facile et agréable à lire, surtout lorsqu'il comporte un grand nombre de catégories et de données, Excel offre de (très) nombreuses options de mise en forme. Il est, par exemple, possible de choisir une disposition différente pour les catégories, des formats de nombres personnalisés pour les données de la zone des valeurs, des couleurs pour les en-têtes et les bordures, et bien d'autres choses encore.

Comment modifier le format de nombre des valeurs ?

Vous remarquerez que les données de la zone Valeurs du tableau croisé dynamique apparaissent comme des nombres à deux décimales, sans séparateur de milliers, ce qui n'est pas très lisible pour des valeurs monétaires. Vous pouvez choisir d'afficher ces données selon n'importe quel format de nombre disponible dans Excel.

• Dans le volet Champs de tableau croisé dynamique disponibles, dans la zone Valeurs, cliquez sur le nom Somme de Ventes puis sur Paramètres des champs de valeurs.

• Dans la boite de dialogue Paramètres des champs de valeurs qui s'ouvre, cliquez sur le bouton Format de nombre.

• La boite de dialogue Format de cellule s'ouvre. Dedans, vous pouvez sélectionner n'importe quel format de cellule existant dans Excel ou créer un format personnalisé en cliquant sur la catégorie Personnalisé. Dans le cas présent, cliquez sur la catégorie Monétaire puis validez votre choix en cliquant sur le bouton OK en bas à droite de la boite de dialogue.

• De retour dans la boite de dialogue Paramètres des champs de valeurs, cliquez sur le bouton OK en bas à droite pour appliquer votre modification au tableau croisé dynamique. Les données de la zone Valeurs sont désormais affichées comme des valeurs monétaires, avec un séparateur de milliers, deux décimales et le symbole €.

Comment modifier la largeur des colonnes ?

Lors de la création d'un tableau croisé dynamique, la largeur des colonnes s'adapte automatiquement au contenu des cellules, ce qui peut ne pas donner le résultat voulu si vous souhaitez par exemple que toutes les colonnes aient la même largeur. Heureusement, il est très facile de modifier manuellement la largeur des colonnes et de la conserver lors des mises à jour du tableau croisé dynamique.

• Pour donner la même largeur à plusieurs colonnes, sélectionnez celles que vous souhaitez modifier en cliquant sur leur étiquette (les lettres A, B, C, D, etc., situés au-dessus de la première ligne de la feuille de calcul). Par exemple, faites un clic-gauche maintenu sur l'étiquette de la colonne B, déplacez votre curseur jusqu'à l'étiquette de la colonne E puis relâchez le bouton gauche de la souris. Toutes les colonnes de B à E sont alors sélectionnées.

• Déplacez ensuite votre souris sur la ligne de séparation entre l'étiquette des colonnes E et F jusqu'à ce que le curseur se transforme en double flèche noire. Faites alors un clic-gauche maintenu et déplacez votre curseur vers la droite pour augmenter la taille des colonnes ou vers la gauche pour la réduire. Lorsque vous relâchez le bouton gauche de la souris, toutes les colonnes précédemment sélectionnées adoptent la même largeur.

• Méthode alternative : sélectionnez les colonnes dont vous souhaitez modifier la largeur en cliquant sur leur étiquette (comme expliqué ci-dessus), faites un clic-droit n'importe-où dans la zone de sélection puis choisissez Largeur de colonne dans le menu contextuel. Une boite de dialogue Largeur de colonne s'ouvre dans laquelle vous pouvez entrer directement un nombre pour définir la largeur des colonnes sélectionnées. Cette méthode est plus précise, mais moins intuitive que la précédente.

Une fois que vous avez attribué les largeurs voulues à vos colonnes, il est nécessaire de faire un tour dans les options du tableau croisé dynamique afin que ces paramètres soient conservés lors de la mise à jour du tableau. En effet, par défaut, Excel ajuste automatiquement la largeur des colonnes à leur contenu lors de l'actualisation du tableau croisé dynamique. Heureusement, cette option est modifiable en quelques clics.

• Dans la feuille de calcul, cliquez n'importe où à l'intérieur du tableau croisé dynamique. Cela fait apparaître l'onglet Analyse du tableau croisé dynamique dans le ruban. Cliquez dessus, puis sur le menu Options du tableau croisé dynamique et enfin sur le bouton Options.

• Vous pouvez également faire un clic-droit dans n'importe quelle cellule du tableau croisé dynamique et, dans le menu contextuel, cliquez sur Options du tableau croisé dynamique.

• Avec l'une ou l'autre des méthodes décrites ci-dessus, la boite de dialogue Options du tableau croisé dynamique s'affiche sur votre écran. Elle comporte plusieurs onglets et s'ouvre par défaut sur celui intitulé Disposition et mise en forme. Ça tombe bien, c'est celui qui nous intéresse dans le cas présent. Chercher la case Ajuster automatiquement la largeur des colonnes lors de la mise à jour et décochez-la. Profitez-en pour vérifier que la case Conserver la mise en forme des cellules lors de la mise à jour, juste en dessus, est bien cochée. Ainsi, vous êtes certain que toute la mise en forme que vous aurez appliquée manuellement sera bien préservée lors de l'actualisation du tableau croisé dynamique.

Comment modifier le style et la disposition du tableau croisé dynamique ?

Excel offre de nombreuses possibilités pour modifier la façon dont le tableau croisé dynamique organise visuellement vos données, telles que l'affichage des totaux généraux de lignes et de colonnes, l'emplacement des sous-totaux ou l'imbrication des catégories de données. Le tableur de Microsoft vous permet également de choisir parmi une liste de styles de tableau prêts à l'emploi (et plutôt élégants pour la plupart) qui peuvent s'appliquer instantanément à votre tableau croisé dynamique, vous épargnant ainsi ce travail de mise en forme pour vous laisser vous concentrer sur le fond. Et, si aucun des styles prédéfinis ne répond à vos attentes, il est tout à fait possible de créer le vôtre qui sera ensuite disponible pour tous vos futurs tableaux croisés dynamiques. Notez que la création de styles personnalisés n'est pas disponible dans le version pour le Web d'Excel. En revanche, si vous avez créé un style de tableau personnalisé dans un fichier sur une version de bureau d'Excel, et que vous ouvrez ensuite ce fichier sur le version Web, votre style personnalisé sera bien présent et utilisable, mais non modifiable.

• Dans la feuille de calcul, cliquez sur n'importe quelle cellule du tableau croisé dynamique pour faire apparaître l'onglet Création dans le ruban, puis cliquez sur ce dernier. Cet onglet comporte trois sections : Disposition, Options de style de tableau croisé dynamique et Styles de tableau croisé dynamiques.

• Dans la section Disposition, le bouton Sous-totaux vous permet de choisir entre trois options d'affichage dont la dénomination est assez explicite : Ne pas afficher les sous-totaux, Afficher tous les sous-totaux en bas du groupe et Affichez tous les sous-totaux en haut du groupe (c'est l'option activée par défaut). Essayez chacune d'entre-elles pour voir comment le tableau croisé dynamique change d'apparence puis rétablissez l'affichage des sous-totaux en bas du groupe avant de passer à la suite. Une dernière option intitulée Inclure les éléments filtrés dans les totaux apparaît en fin de liste mais elle est inactive pour le moment, car vous n'avez appliqué aucun filtre à votre tableau croisé dynamique. Comme son nom l'indique, elle permet d'inclure dans les sous-totaux la valeur des données masquées par un filtre, ce qui peut conduire à des erreurs de lecture et d'interprétation des résultats d'un tableau croisé dynamique. À utiliser avec prudence donc.

• Toujours dans la section Disposition, le bouton Totaux généraux offre quatre possibilités d'affichage : Désactivé pour les lignes et les colonnes, Activé pour les lignes et les colonnes, Activé pour les lignes uniquement et Activé pour les colonnes uniquement. Encore une fois, faites quelques essais puis revenez à l'option Activé pour les lignes et les colonnes avant de passer à la suite.

• Le bouton suivant de la section Disposition se nomme Disposition du rapport. Il vous permet de choisir comment Excel organise les catégories de données que vous avez placées dans la zone Lignes du tableau croisé dynamique.

• La première option, Afficher sous forme compactée, est celle utilisée par défaut et donc celle que vous voyez depuis le début. Dans ce mode d'affichage, les catégories de données de la zone Lignes sont regroupées dans une seule colonne et affichée sous forme d'arborescence, avec un retrait vers la droite appliqué à chaque sous-catégorie et un sous-total pour chaque catégorie qui peut être affiché en haut ou en bas du groupe (voir la section précédente de cette fiche pratique). Cette disposition offre l'avantage d'optimiser l'espace occupé par les catégories en largeur et donc de laisser plus de place pour l'affichage de valeurs. Son inconvénient est qu'il regroupe les filtres applicables aux différentes catégories dans un seul et même bouton (présent dans la cellule A4 dans notre exemple), ce qui peut rendre le filtrage sur plusieurs catégories moins lisible. Cet aspect sera développé plus loin dans cette fiche pratique.

• La deuxième option disponible, Afficher en mode Plan, est assez similaire à la précédente : les catégories de données de la zone Lignes sont toujours affichées sous forme d'arborescence et un sous-total pour chaque catégorie peut être affiché en haut ou en bas du groupe. Cependant, les catégories de la zone Lignes sont cette fois réparties sur plusieurs colonnes, une colonne par catégorie plus exactement. Ce mode utilise donc plus d'espace en largeur pour l'affichage des catégories et peut donner une impression de « gâchis » avec les zones vides dans chaque groupe. Cependant, comme chaque catégorie dispose de sa propre colonne, ce mode offre l'avantage d'attribuer un bouton de filtre à chaque catégorie, ce qui rend le filtrage sur plusieurs critères beaucoup lisible.

• La troisième et dernière option s'intitule Afficher sous forme tabulaire. Cette fois, les catégories de la zone Lignes ne sont plus affichées sous forme d'arborescence mais sous forme de tableau classique et, point crucial, les sous-totaux se trouvent obligatoirement en bas de chaque groupe. Comme dans l'affichage en mode Plan, chaque catégorie de la zone Lignes est répartie dans une colonne individuelle et offre donc son propre bouton de filtre.

• Enfin, le bouton Disposition du rapport vous offre deux options supplémentaires : Répéter toutes les étiquettes d'éléments ou Ne pas répéter les étiquettes d'éléments. Ces deux options contrôlent en réalité un seul et même paramètre, permettant son activation ou sa désactivation. Par défaut, la répétition des étiquettes d'éléments est désactivée, c'est pour cela que vous voyez des espaces vides dans chaque groupe de catégories. Si vous cliquez sur l'option Répéter toutes les étiquettes d'éléments, alors le nom de chaque catégorie sera répété dans sa colonne jusqu'au groupe suivant. Cela peut-être utile si une catégorie comporte beaucoup d'éléments et que sa longueur dépasse la hauteur d'affichage de votre tableau. Notez que cette option ne fonctionne pas dans l'affichage sous forme compactée.

• La section Disposition possède un dernier menu appelé Lignes vides qui contient deux options : Insérer un saut de ligne après chaque élément ou Supprimer le saut de ligne après chaque élément. La première option insère une ligne vide après chaque groupe de catégories, ce qui peut être utile pour aérer votre tableau croisé dynamique, en particulier si vous avez opté pour la répétition des étiquettes d'éléments. La deuxième option supprime simplement les lignes vides insérées par la première.

La section suivante de l'onglet Création du ruban se nomme Options de style de tableau croisé dynamique et contient quatre cases à cocher vous permettant chacune d'activer une option de mise en forme spécifique. Par défaut, les options En-têtes de lignes et En-têtes de colonnes sont cochées et les options Lignes à bandes et Colonnes à bandes sont décochées.

• Si vous décochez la case En-têtes de lignes, la mise en forme de la première ligne de chaque groupe de catégories disparaîtra.

• Si vous décochez la case En-têtes de colonnes, c'est la mise en forme des en-têtes de colonnes du tableau croisé dynamique qui disparaît alors.

• Les cases Lignes à bandes et Colonnes à bandes produisent des effets différents selon le style de tableau croisé dynamique appliqué. Vous verrez un peu plus loin dans cette fiche pratique comment choisir un style de tableau, n'hésitez donc pas à activer et désactiver ces options après un changement de style pour voir comment elles affectent la mise en forme du tableau. De façon générale, les options Lignes à bandes et Colonnes à bandes permettent de mieux distinguer les différentes lignes et colonnes, en affichant des bordures de séparation ou en appliquant une mise en forme une ligne ou une colonne sur deux. Essayez d'activer et de désactiver successivement ces options pour voir le résultat sur le style de tableau actuel.

• La dernière partie de la section Disposition de l'onglet Création s'intitule Styles de tableau croisé dynamique et contient les fameux styles de tableau prêts à l'emploi que vous pouvez appliquer instantanément à votre tableau croisé dynamique.

• La section Styles de tableau croisé dynamique vous propose un premier échantillon des styles de tableau disponibles, mais il en existe bien plus : cliquez sur les flèches à droite de l'échantillon pour faire défiler les styles disponibles ou sur la flèche surmontée d'une ligne pour afficher une liste déroulante de tous les styles existants. En survolant les styles de tableau avec votre souris, sans même avoir à cliquer dessus, vous verrez qu'Excel applique temporairement la mise en forme au tableau croisé dynamique, ce qui est très utile pour comparer rapidement les diverses possibilités qui s'offrent à vous.

• Pour appliquer définitivement le style de tableau qui vous plaît, cliquez simplement dessus dans la liste déroulante, la mise en forme correspondante s'applique alors à l'ensemble du tableau croisé dynamique. Vous pouvez bien entendu changer de style de tableau aussi souvent que vous le souhaitez en répétant simplement l'étape précédente.

• Notez que les styles proposés sont dépendants du jeu de couleurs utilisé dans votre classeur Excel et que, si aucune des couleurs de tableau proposées ne vous convient, vous pouvez en obtenir de nouvelles en changeant le jeu de couleurs via l'onglet Mise en page du ruban, section Thèmes, menu Couleurs.

Si, malgré tout, vous ne trouvez pas votre bonheur parmi la multitude de styles de tableau proposés, il est possible de créer des styles entièrement personnalisés. Attention, comme expliqué précédemment, cette fonction n'est pas disponible dans la version Web d'Excel.

• Dans l'onglet Création, dans la section Styles de tableau croisé dynamique, affichez la liste des styles disponibles en cliquant sur la flèche surmontée d'une ligne puis, en dessous de la liste, cliquez sur Nouveau style de tableau croisé dynamique. Une boite de dialogue du même nom s'ouvre alors.

Dans le champ Nom, vous pouvez attribuer un nom personnalisé à votre style de tableau croisé dynamique. Ensuite, les choses se compliquent un peu. Chaque entrée de la liste Éléments de tableau représente une zone du tableau croisé dynamique qui peut recevoir une mise en forme particulière. Il vous faut donc paramétrer chaque élément individuellement jusqu'à obtenir le résultat voulu, ce qui peut s'avérer plus ou moins long en fonction de la complexité du style de tableau que vous voulez obtenir.

• Pour modifier la mise en forme d'une zone du tableau, cliquez sur son nom dans la liste Élément de tableau, par exemple sur Lignes d'en-têtes, puis sur le bouton Format sous cette même liste. Une boite de dialogue Format de cellule s'ouvre. Elle contient trois onglets, Police, Bordure et Remplissage, qui vous permettent de définir différents paramètres de mise en forme. Ils fonctionnent exactement de la même façon que lors de la mise en forme direct de cellules dans une feuille de calcul. Faites des essais et appliquez différents formatages puis cliquez sur le bouton OK en bas à droite de la boite de dialogue Format de cellule. Recommencez plusieurs fois l'opération avec d'autres entrées de la liste Élément de tableau. Lorsque que vous avez finis, observez la section Aperçu à droite de la boite de dialogue Nouveau style de tableau croisé dynamique. Comme son nom l'indique, elle vous offre un aperçu de la mise en forme qui sera appliquée par votre style de tableau personnalisé.

• Si vous êtes satisfait du résultat, cliquez sur le bouton OK en bas à droite de la boite de dialogue Nouveau style de tableau croisé dynamique pour enregistrer votre style personnalisé. Il sera désormais disponible dans la liste des Styles de tableau croisé dynamique de l'onglet Création du ruban. Vous pourrez bien sûr le modifier, le dupliquer ou le supprimer à l'aide d'un simple clic-droit dessus.

Le nombre d'éléments de tableau qu'il est possible de personnaliser est assez important et il vous faudra probablement du temps pour bien comprendre à quelle zone du tableau croisé dynamique correspond chacun d'entre eux. N'hésitez pas à enregistrer un style personnalisé que vous ferez évoluer au fil du temps en le testant sur les différents tableaux croisés dynamiques avec lesquels vous travaillez. Le potentiel de création d'Excel est gigantesque et (presque) uniquement limité par votre imagination et votre expérience.

Besoin d'approfondir vos connaissances sur Excel ?
Suivez notre formation sur CCM Benchmark Institut !

Découvrir la formation Excel sur CCM Benchmark Institut

Comment trier et filtrer un tableau croisé dynamique avec Excel ?

Comme toute plage de données organisée sous forme de tableau dans Excel, un tableau croisé dynamique peut être trié et filtré automatiquement à l'aide d'outils prévus à cet effet. En matière de triage, on retrouve les fonctions classiques de tri croissant, décroissant et personnalisé. Côté filtrage, les auto-filtres et les segments sont présents ainsi qu'une zone de filtre spécifique aux tableaux croisés dynamiques, qui peut être utilisée pour plus de lisibilité.

Comment trier des données dans un tableau croisé dynamique ?

• Première solution : utiliser les auto-filtres. Cliquez sur le bouton blanc contenant un triangle noir qui se trouve à droite de la cellule d'en-tête de la colonne par laquelle vous souhaitez trier vos données, par exemple la colonne Région. Un menu contextuel apparaît. Les deux premières options vous permettent de trier vos données par ordre croissant ou décroissant (de A à Z ou de Z à A dans le cas d'une colonne contenant du texte).

• Vous pouvez également choisir d'utiliser des Options de tri supplémentaires. Ces options supplémentaires seront différentes en fonction du type de données de la colonne (texte, nombre, date, heure). Dans tous les cas sauf pour les nombres, vous pourrez choisir de trier vos données manuellement par glisser-déposer à l'aide de la souris.

• Deuxième solution : faites un clic-droit sur une cellule du tableau croisé dynamique contenant une donnée, peu importe qu'il s'agisse d'une catégorie ou d'une valeur, et dans le menu contextuel qui apparaît, cliquez sur Trier puis choisissez votre option de tri comme précédemment.

Comment filtrer des données dans un tableau croisé dynamique ?

• Comme pour le tri, la méthode la plus intuitive et la plus directe consiste à utiliser les auto-filtres. Cliquez sur le bouton blanc contenant un triangle noir qui se trouve à droite de la cellule d'en-tête de la colonne par laquelle vous souhaitez filtrer vos données, par exemple la colonne Région. Un menu contextuel apparaît.

• Dans la deuxième moitié de ce menu, une liste de cases à cocher vous permet de sélectionner les données à afficher ou à masquer, en cochant ou décochant simplement la case correspondante. Décochez par exemple les cases Ile-de-France et Nouvelle-Aquitaine puis validez en cliquant sur le bouton OK en bas du menu. Le tableau croisé dynamique affiche désormais uniquement les données de la région Auvergne-Rhône-Alpes.

• Vous pouvez cumuler les critères de filtrage sur plusieurs catégories différentes. Par exemple, ouvrez l'auto-filtre de la colonne Mois et dans la liste de cases à cocher, décochez celles correspondant aux mois de Janvier et Février, puis validez ce filtrage en cliquant sur le bouton OK. Le tableau croisé dynamique affiche maintenant les données de la région Auvergne-Rhône-Alpes du mois de mars uniquement.

• Pour supprimer les critères de filtrage appliqués à une colonne, ouvrez l'auto-filtre de la colonne en question et cliquez sur Effacer le filtre de. Répétez cette opération pour chaque colonne dont vous souhaitez supprimer le filtrage.

• Deuxième méthode : utiliser la zone de filtres spécifique du tableau croisé dynamique. Cliquez quelque part dans le tableau croisé dynamique pour que le volet Champs de tableau croisé dynamique s'affiche à droite de la fenêtre. Choisissez l'une des catégories présentes dans les zones Lignes ou Colonnes, cliquez dessus puis sélectionnez Déplacer dans la zone Filtre du rapport dans le menu contextuel. Vous pouvez également déplacer la catégorie choisie de sa zone actuelle vers la zone Filtre par glisser-déposer à l'aide de la souris.

• Pour notre exemple, faites d'abord glisser la catégorie Région de la zone Lignes vers la zone Filtre, puis la catégorie Produit de la zone Colonnes vers la zone Filtre.

• Vous constaterez que la structure du tableau croisé dynamique s'est modifiée : les sous-totaux par région et par produit ont disparu du tableau et deux lignes sont apparues au-dessus qui correspondent aux filtres de tableau croisé dynamique. Elles contiennent chacune un auto-filtre qui fonctionne comme ceux vu précédemment, mais de façon légèrement simplifiée.

• Cliquez sur l'auto-filtre de la ligne intitulée Région, un menu contextuel s'ouvre dans lequel vous pouvez sélectionnez l'une des entrées de cette catégorie. Par défaut, vous ne pouvez sélectionner qu'une seule entrée de la liste. En cochant la case Sélectionner plusieurs éléments en bas de la liste, vous pourrez alors effectuer une sélection multiple. Cependant, ce type de filtrage à plusieurs critères au sein d'une même catégorie n'est pas conseillé dans ce mode d'affichage, comme nous allons l'illustrer juste après. Il existe un outil plus adapté pour le filtrage à critères multiples (les segments) que nous verrons plus loin dans cette fiche pratique. Pour notre exemple, ouvrez l'auto-filtre de la ligne Région, sélectionnez l'élément Ile-de-France et validez en cliquant sur le bouton OK, puis ouvrez l'auto-filtre de la ligne Produit, sélectionnez l'élément Produit A et validez en cliquant sur OK.

Le tableau croisé dynamique affiche désormais uniquement les valeurs correspondant aux catégories Ile-de-France et Produit A et les critères de filtrage utilisés sont clairement visibles au-dessus de la plage du tableau, ce qui facilite grandement la lecture et évite les erreurs d'interprétation des données lorsqu'on effectue plusieurs filtrages successifs. Si vous aviez coché l'option Sélectionner plusieurs éléments à l'étape précédente, et aviez effectué un filtrage à plusieurs critères dans la même catégorie, alors la ligne de filtre aurait affiché "Plusieurs éléments" au lieu du nom précis du critère de filtrage. Cela vous obligerait à ré-ouvrir régulièrement la liste de filtre pour vous souvenir quels critères sont appliqués.

L'utilisation de la zone Filtres du tableau croisé dynamique, plutôt que les auto-filtres classiques présents dans chaque colonne, s'avère donc particulièrement utile pour travailler sur des sous-ensembles précis d'une liste de données comportant de nombreuses catégories.

• Enfin, troisième méthode, qui va combiner les avantages des deux précédentes, à savoir afficher les sous-totaux de toutes les catégories dans le tableau croisé dynamique et afficher en permanence les critères utilisés en cas de filtrage multiple : les segments. Commencez par rétablir la précédente structure du tableau croisé dynamique. Dans le volet Champs de tableau croisé dynamique, ramenez la catégorie Région en première position dans la zone Lignes et la catégorie Produit dans la zone Colonnes.

• Cliquez sur n'importe quelle cellule du tableau croisé dynamique pour faire apparaître l'onglet Analyse du tableau croisé dynamique dans le ruban et cliquez sur ce dernier. Dans la section Filtrer, cliquez sur le bouton Insérer un segment.

• Une boite de dialogue intitulée Insérer des segments s'ouvre. Elle contient la liste des catégories de votre tableau croisé dynamique sous forme de cases à cocher. Pour notre exemple, cochez les cases Région et Produit puis validez votre sélection en cliquant sur le bouton OK en bas de la boite de dialogue.

• Deux objets sont apparus dans la feuille de calcul, par-dessus le tableau croisé dynamique. Il s'agit des fameux segments d'Excel, des outils de filtrage visuel très pratiques et ergonomiques. Tout d'abord, à l'aide de la souris, déplacez-les par glisser-déposer à droite du tableau croisé dynamique afin de ne pas masquer celui-ci.

• Les segments fonctionnent de la même manière que les auto-filtres, en vous permettant de choisir les catégories à afficher ou à masquer en cliquant simplement sur leur nom à l'intérieur de la liste, mais à la différence de taille que les segments resteront visibles en permanence. Dans le segment Région, cliquez sur l'icône représentant une liste de validation à droite du nom « Région » afin d'activer la sélection multiple, puis cliquez sur les éléments Auvergne-Rhône-Alpes et Nouvelle-Aquitaine. Dans le segment Produit, activez la sélection multiple comme précédemment, puis cliquez sur les éléments Produit B et Produit C. Le tableau croisé dynamique s'adapte instantanément à vos modifications et les critères de filtrage utilisés restent visibles à tout moment dans les segments. Vous pouvez évidemment changer vos filtres en cliquant simplement sur les éléments correspondants dans les segments.

• Pour effacer les filtres appliqués à l'aide des segments, cliquez sur l'icône représentant un entonnoir et une croix rouge dans l'en-tête du segment concerné.

• Pour supprimer entièrement un segment, faites un clic-droit dessus et sélectionnez Supprimer " ... ".

Comment actualiser un tableau croisé dynamique avec Excel ?

Un autre avantage des tableaux croisés dynamiques est qu'ils peuvent être mis à jour instantanément lorsque des modifications sont apportées à la liste de données initiale. Que vous ayez ajouté, modifié ou supprimé des lignes, ou même ajouté des colonnes représentant de nouvelles catégories, il est possible d'actualiser le tableau croisé dynamique en quelques clics.

• Effectuez quelques modifications dans la liste de données initiale : remplacez certaines valeurs par un zéro et ajoutez une ligne en fin de liste avec de nouvelles entrées pour les catégories Régions et Mois, par exemple « Occitanie » et « Avril ».

• Revenez à la feuille de calcul contenant le tableau croisé dynamique et cliquez sur n'importe quelle cellule de ce dernier pour faire apparaître l'onglet Analyse du tableau croisé dynamique dans le ruban, puis cliquez sur ce dernier. Dans la section Données, cliquez sur le bouton Actualiser puis sur Actualiser tout. Vous pouvez aussi faire un clic-droit sur une cellule du tableau croisé dynamique et choisir Actualiser.

• Le tableau croisé dynamique s'est mis à jour : les valeurs font bien apparaître les montants que vous avez modifiés dans la liste de données initiale (les zéros). Cependant, la ligne ajoutée en fin de liste n'apparaît pas, car elle est située en dehors de la plage de données sur laquelle est basée le tableau croisé dynamique. Pas d'inquiétude, il est très facile de modifier la plage de données source et même de la paramétrer pour qu'elle s'étende automatiquement à chaque ajout de ligne ou de colonne.

• Dans l'onglet Analyse du tableau croisé dynamique, dans la section Données, cliquez sur le bouton Changer la source de données. Excel vous ramène à la feuille contenant la liste de données initiale et ouvre une boite de dialogue nommée Modifier la source de données du tableau croisé dynamique. Vous pouvez entrer directement les références de la source de données dans le champ Tableau/Plage à l'aide du clavier ou sélectionner la plage dans la feuille à l'aide de la souris. Validez la modification de la plage en cliquant sur le bouton OK au bas de la boite de dialogue.

• Vous revenez automatiquement à la feuille contenant le tableau croisé dynamique qui, cette fois, fait bien apparaître une nouvelle catégorie nommée Occitanie et une sous-catégorie nommée Avril.

• Pour éviter d'avoir à modifier la plage de données source manuellement à chaque ajout de ligne ou de colonne, la meilleur méthode consiste à convertir la plage de données en tableau structuré. Pour ce faire, revenez à la feuille de calcul contenant la plage de données source et sélectionnez-la en entier. Cliquez ensuite sur l'onglet Insertion du ruban puis sur le bouton Tableau.

• La boite de dialogue Créer un tableau s'ouvre. Vérifiez que les références contenues dans le champ Où se trouvent les données de votre tableau ? recouvrent bien la totalité de la plage de données source et que la case Mon tableau comporte des en-têtes est bien cochée, puis validez en cliquant sur le bouton OK.

• La liste de données change alors d'apparence. Surtout, elle est devenue un tableau structuré qui présente plusieurs avantages : il peut être référencé par un nom plutôt que par son adresse, et les ajouts de lignes et de colonnes seront automatiquement pris en compte dans son étendue. Avant de passer à la suite, attribuez un nom à ce tableau structuré : restez sur la même feuille et cliquez sur l'onglet Conception de la table dans le ruban, puis dans le champ Nom du tableau et attribuez-lui un nom parlant, par exemple Ventes.

• Revenez à la feuille contenant le tableau croisé dynamique, cliquez sur une cellule de ce dernier, sur l'onglet Analyse du tableau croisé dynamique du ruban et, enfin, sur le bouton Changer la source de données. Dans la boite de dialogue Modifier la source de données du tableau croisé dynamique, dans le champ Tableau/Plages, entrez simplement le nom de votre tableau structuré, Ventes dans le cas présent, et validez en appuyant sur le bouton OK.

Le tableau croisé dynamique est désormais lié au tableau structuré Ventes. Tout ajout de ligne ou de colonne à ce tableau structuré sera pris en compte lors de l'actualisation du tableau croisé dynamique.

• Revenez à la feuille contenant le tableau structuré Ventes et positionnez-vous sur la dernière cellule du tableau (D29). Appuyez sur la touche Tabulation de votre clavier. Une ligne vide est ajoutée au tableau structuré, avec la même mise en forme que les lignes précédentes.

• Remplissez cette ligne avec de nouvelles données, par exemple « Normandie », « Mai », « Produit C » et « 4952.38 ». Revenez ensuite à la feuille contenant le tableau croisé dynamique, cliquez sur l'onglet Analyse du tableau croisé dynamique du ruban, sur le bouton Actualiser puis Actualiser tout. Le tableau croisé dynamique s'actualise et fait bien apparaître une nouvelle catégorie nommée Normandie et une sous-catégorie nommée Mai.

Vous pouvez désormais ajouter autant d'éléments que vous le souhaitez à votre liste de données initiale sans craindre qu'ils ne soient pas pris en compte lors de l'actualisation du tableau croisé dynamique.

Les tableaux croisés dynamiques offrent une multitude d'autres fonctions plus ou moins sophistiquées, permettant d'obtenir des résultats plus ou moins complexes. Par exemple, ils peuvent afficher les valeurs non pas sous forme de sommes mais de pourcentages ou de différences par rapport à une valeur de base de la liste de données initiales, et bien d'autres choses encore. Cependant ces fonctions excèdent le cadre de cette fiche pratique d'introduction à l'utilisation des tableaux croisés dynamiques. Les notions présentées dans cette fiche vous donnent néanmoins les connaissances nécessaires à une utilisation des tableaux croisés dynamiques qui répondra à la majorité des cas de figure que vous pourrez rencontrer.