Créer un tableau croisé dynamique dans Excel : le guide complet

Un tableau croisé dynamique résume des centaines ou des milliers de lignes de données brutes en un rapport que vous pouvez modifier en quelques clics. Vous n’avez pas besoin d’écrire de formules. Nous allons le construire étape par étape, de la préparation des données jusqu’à la mise en forme et l’actualisation après une modification.

Un tableau croisé dynamique regroupe et calcule vos données sans formules

Un tableau croisé dynamique est un rapport interactif qui résume une longue liste en groupant les lignes et en additionnant, comptant ou moyennant les valeurs. Vous glissez des champs dans les zones et Excel recalcule le résumé en direct. Changez la disposition et le tableau est recalculé immédiatement, sans toucher aux données sources.

Les questions typiques auxquelles un tableau croisé dynamique répond en quelques secondes : quel est le chiffre d’affaires par région, combien de commandes par mois, ou la valeur moyenne des commandes par commercial. Plutôt que d’empiler des RECHERCHEV ou SOMME.SI, laissez le tableau croisé dynamique effectuer le regroupement. Pour rechercher des valeurs isolées, les formules restent plus pratiques ; découvrez la différence dans notre article RECHERCHEV vs RECHERCHERX.

Des données sources propres sont la base d’un tableau croisé dynamique fiable

Un tableau croisé dynamique n’est fiable que si les données sources sont bien structurées. Vérifiez votre liste selon ces critères avant de commencer.

• Une seule ligne d’en-tête. Chaque colonne possède en haut un en-tête unique et concis ; ces en-têtes deviennent les champs du tableau croisé dynamique.
• Pas de lignes ni de colonnes vides. Une ligne vide interrompt la plage contiguë qu’Excel peut reconnaître.
• Un seul enregistrement par ligne. Chaque ligne correspond à une observation, par exemple une commande ou une transaction.
• Données cohérentes. Nombres au format nombre, dates au format date, pas de texte tel que « 12 unités » dans une colonne de nombres.

Convertir vos données en tableau Excel avec Ctrl+T est vivement recommandé. Si vous ajoutez des lignes plus tard, le tableau s’agrandit automatiquement et le tableau croisé dynamique intègre les nouvelles lignes après actualisation, sans que vous modifiiez la plage. Un tableau croisé dynamique basé sur une plage normale ne prend pas automatiquement en compte les nouvelles lignes.

Faites également attention aux cellules fusionnées dans votre ligne d’en-tête ou aux sous-titres qui fragmentent la liste. Un tableau croisé dynamique attend une liste plate où chaque colonne contient le même type de données du début à la fin. S’il y a des sous-totaux ou titres intermédiaires dans les données sources, supprimez-les d’abord ; sinon ils apparaîtront comme catégories distinctes dans le rapport et les totaux seront faussés.

Insérer un tableau croisé dynamique depuis l’onglet Insertion

Avec des données nettoyées, créez le tableau croisé dynamique en quelques étapes.

1. Cliquez sur une cellule quelconque de votre plage de données. Dès que le curseur se trouve dans la liste, Excel reconnaît automatiquement toute la plage contiguë ; vous n’avez rien à sélectionner.
2. Allez dans l’onglet Insertion et cliquez en haut à gauche sur Tableau croisé dynamique. Si vous utilisez un tableau Ctrl+T, choisissez À partir d’un tableau ou d’une plage.
3. Dans la fenêtre, la plage est déjà renseignée. Choisissez Nouvelle feuille de calcul pour que le tableau croisé dynamique s’affiche sur un onglet séparé et clair.
4. Cliquez sur OK. Excel ouvre un canevas vide de tableau croisé dynamique avec, à droite, le volet Champs de tableau croisé dynamique.

Si vous êtes pressé ou indécis sur la disposition, utilisez Insertion > Tableaux croisés dynamiques recommandés. Excel propose des modèles prêts à l’emploi issus de vos données, que vous insérez en un clic.

Les quatre zones déterminent la forme du tableau croisé dynamique

À droite, vous voyez le volet Champs de tableau croisé dynamique : en haut tous vos en-têtes de colonnes, en dessous quatre zones. Vous construisez le rapport en glissant les champs vers ces zones.

Exemple concret avec une liste de ventes. Glissez le champ Région dans Lignes et le champ CA dans Valeurs. C’est tout : Excel affiche par région le chiffre d’affaires total sous l’en-tête Somme de CA. Si vous ajoutez également Année dans Colonnes, le rapport se décline par année. En plaçant deux champs l’un sous l’autre dans la zone Lignes, par exemple Région puis Client, vous obtenez une hiérarchie avec des sous-totaux par région.

Choisissez si Excel additionne, compte ou moyenne

Par défaut, Excel additionne les nombres (Somme de CA). Pour les champs de texte, il effectue un comptage. Il est facile de modifier ce calcul.

1. Faites un clic droit sur une valeur de la colonne, ou cliquez sur le champ dans la zone Valeurs.
2. Choisissez Paramètres des champs de valeur.
3. Sous Synthétiser les valeurs par, sélectionnez le calcul : Somme, Nombre, Moyenne, Max ou Min.
4. Dans la même fenêtre, cliquez sur Format de nombre pour appliquer tout de suite une mise en forme propre, par exemple le symbole euro ou le séparateur de milliers.

Sur l’onglet Afficher les valeurs, vous trouvez une option pratique. Choisissez % du total général pour que chaque nombre soit affiché en pourcentage de l’ensemble, idéal pour une répartition par région ou par gamme de produits. Vous pouvez placer le même champ deux fois dans la zone Valeurs, par exemple une fois en Somme et une fois en % du total général, pour voir le montant et la part côte à côte.

Zoomer avec les filtres, le regroupement et les segments

Un tableau croisé dynamique devient vraiment utile quand vous pouvez y filtrer et y grouper.

• Regrouper des dates. Faites un clic droit sur une date du tableau et choisissez Grouper. Sélectionnez par exemple Mois et Années pour résumer les dates individuelles en totaux mensuels.
• Ajouter des segments. Allez dans Analyse de tableau croisé dynamique > Insérer un segment et cochez un champ. Vous obtenez des boutons qui filtrent en un clic, par exemple sur une région.
• Ajouter une chronologie. Pour les champs de date, choisissez Insérer une chronologie, une barre de défilement qui permet de sélectionner rapidement une période.
• Trier et top 10. Cliquez sur la flèche près d’un libellé de ligne pour trier ou pour afficher uniquement les dix valeurs les plus élevées via les Filtres de valeurs.

Placer un filtre dans la zone Filtres ajoute un menu déroulant en haut du tableau ; les segments font la même chose mais restent visibles sous forme de boutons, ce qui est plus pratique dans un tableau de bord.

Rendre le tableau croisé dynamique lisible avec l’onglet Création

Par défaut, Excel affiche le tableau croisé dynamique en mode compact, où les champs imbriqués sont en retrait. Pour un rapport, une autre disposition est souvent plus agréable.

1. Cliquez dans le tableau croisé dynamique et ouvrez l’onglet Création.
2. Ouvrez Disposition du rapport et choisissez Afficher sous forme de tableau si vous voulez que chaque champ ait sa propre colonne, pratique pour copier les données ultérieurement.
3. Via Sous-totaux et Totaux généraux, vous pouvez activer ou désactiver ces agrégats.
4. Choisissez un des Styles de tableau croisé dynamique pour la couleur et les bordures, et activez les Lignes à bandes pour une meilleure lisibilité.

Pour visualiser les chiffres, allez dans Analyse de tableau croisé dynamique > Graphique croisé dynamique. Le graphique est lié au tableau et se met à jour lorsque vous filtrez ou modifiez la disposition.

Ajouter votre propre calcul avec un champ calculé

Parfois, vous avez besoin d’un chiffre qui ne se trouve pas en tant que colonne dans les données sources, comme le bénéfice (CA moins achats). Vous le créez avec un champ calculé, sans toucher aux données sources.

1. Cliquez dans le tableau croisé dynamique et allez dans Analyse de tableau croisé dynamique > Champs, éléments et jeux > Champ calculé.
2. Nommez le champ, par exemple Bénéfice.
3. Dans Formule, tapez le calcul avec les noms de champs, comme = CA - Achats. Double-cliquez sur un champ de la liste pour l’insérer dans la formule.
4. Cliquez sur Ajouter puis sur OK. Le nouveau champ apparaît dans la liste des champs et dans la zone Valeurs.

Un champ calculé opère toujours sur les totaux de chaque groupe, pas ligne par ligne. Pour des rapports simples, cela fonctionne bien ; si vous avez besoin d’un calcul par ligne individuelle, ajoutez une colonne classique dans les données sources.

Double-cliquez pour afficher les lignes sous-jacentes

Pour savoir quels enregistrements se cachent derrière un nombre, double-cliquez sur une valeur du tableau croisé dynamique. Excel crée une nouvelle feuille avec uniquement les données sources qui alimentent ce nombre. Par exemple, si vous remarquez un chiffre d’affaires anormalement élevé pour une région, un double-clic fait apparaître immédiatement les commandes correspondantes.

Cette fonction, également appelée zoom avant, est pratique pour vérifier les chiffres avant de partager un rapport. La nouvelle liste est une vue instantanée ; elle ne se met pas à jour lorsque vous actualisez le tableau croisé dynamique, pensez donc à supprimer cette feuille temporaire quand vous n’en avez plus besoin.

Ces problèmes et leurs solutions rapides

Si votre tableau croisé dynamique ne donne pas les résultats attendus, cela provient généralement d’un de ces points.

• Excel compte au lieu d’additionner. Il y a du texte ou une cellule vide dans la colonne de nombres. Nettoyez-la et repassez le calcul en Somme dans les Paramètres des champs de valeur.
• Les nouvelles lignes n’apparaissent pas. Le tableau est basé sur une plage fixe. Utilisez un tableau Ctrl+T ou modifiez la source via Analyse de tableau croisé dynamique > Modifier la source de données.
• Une colonne source supprimée provoque des erreurs. Si une colonne référencée par le tableau disparaît, un calcul peut se briser ; découvrez comment corriger cette erreur de référence dans corriger l’erreur #REF!.
• D’anciens éléments restent dans la liste des filtres. Dans les options du tableau croisé dynamique, réglez la conservation des éléments par champ sur Aucun, puis actualisez.
• Impossible de grouper des dates. Une colonne contient une date au format texte, ou une cellule vide s’est glissée. Convertissez la colonne en type date réel et supprimez les lignes vides, le regroupement fonctionnera à nouveau.

Après une modification des données sources, actualisez le tableau

Un tableau croisé dynamique fonctionne avec une copie de vos données et ne se met pas à jour automatiquement. Si vous modifiez ou ajoutez des données, vous devez l’actualiser.

1. Faites un clic droit n’importe où dans le tableau croisé dynamique et choisissez Actualiser, ou utilisez Alt+F5.
2. Pour mettre à jour tous les tableaux croisés dynamiques du classeur en une fois, allez dans Analyse de tableau croisé dynamique > Actualiser > Actualiser tout (Ctrl+Alt+F5).

Une bonne habitude consiste à activer l’actualisation automatique. Faites un clic droit dans le tableau, choisissez Options du tableau croisé dynamique, allez dans l’onglet Données et cochez Actualiser les données lors de l’ouverture du fichier. Ainsi votre rapport sera à jour à chaque ouverture sans que vous ayez à y penser.

Si le courriel où vous souhaitez envoyer le rapport se bloque, consultez Outlook ne parvient pas à se connecter au serveur. Si vous ne retrouvez plus un rapport précédemment envoyé, réparer la fonction de recherche d’Outlook vous aidera. Une explication détaillée sur la disposition des tableaux croisés dynamiques est disponible dans la documentation Microsoft.