Power Pivot Excel : guide pas à pas

L’analyse de gros fichiers Excel finit souvent par ralentir les performances. 

Power Pivot propose une autre approche. Il relie les tables et gère les calculs sans sacrifier les performances. Plutôt que de vous battre avec des chaînes de RECHERCHEV() et des colonnes auxiliaires, vous travaillez dans un système structuré intégré directement à Excel.

Dans ce guide, vous apprendrez à configurer des modèles de données, créer des relations entre tables, écrire des formules DAX et construire des rapports interactifs avec Power Pivot.

Qu’est-ce que Power Pivot et pourquoi est-ce utile ?

Power Pivot est le moteur de modélisation de données intégré à Excel. Il permet d’ingérer de plus grands jeux de données, de connecter plusieurs tables et d’exécuter des calculs complexes sans la lenteur des feuilles de calcul traditionnelles.

En quoi Power Pivot diffère

Au lieu de stocker les données directement dans une feuille, Power Pivot charge tout dans le modèle de données interne d’Excel. 

Une feuille standard atteint environ un million de lignes et ralentit généralement bien avant. Power Pivot contourne cette limite en compressant les données et en les gérant à part, ce qui vous permet de travailler sur des dizaines de millions de lignes tout en préservant les performances du classeur.

Une structure relationnelle plutôt que des chaînes de RECHERCHEV

Une fois vos données dans le modèle, vous pouvez relier les tables à l’aide de clés, comme dans une base de données légère. Vous n’avez pas à aplatir toutes les données dans une seule feuille géante ni à imbriquer des fonctions RECHERCHEV() pour forcer l’assemblage des tables. Power Pivot vous permet d’analyser des tables liées côte à côte, proprement et de manière fiable.

Des calculs plus puissants avec DAX

Power Pivot utilise DAX (Data Analysis Expressions), un langage de formules conçu spécialement pour l’analyse. Vous pouvez créer des mesures qui vont bien au-delà des capacités d’un simple Tableau croisé dynamique : des sommes basiques aux indicateurs temporels, ratios, cumul glissant et autres calculs avancés.

Exemples d’usage

Voici deux exemples de l’utilisation de Power Pivot dans les opérations des entreprises : 

• Suivi de la performance commerciale : combinez l’historique des commandes, les tables produits et les attributs clients, puis construisez des mesures DAX pour le chiffre d’affaires en glissement annuel ou la valeur vie client, sans fusion manuelle.
• Reporting opérationnel : reliez stocks, expéditions et données fournisseurs, puis calculez taux de service, délais d’approvisionnement ou écarts de prévision à partir du même modèle.

En clair, Power Pivot apporte une expérience proche d’une base de données au sein d’Excel. Si vous travaillez avec de gros volumes ou des données multi-tables, il peut transformer des processus de reporting chaotiques en modèles rapides, évolutifs et solides.

Configurer Power Pivot dans Excel

Voyons maintenant comment commencer à utiliser Power Pivot dans Excel. 

Activer Power Pivot

Vous n’avez rien à télécharger : Power Pivot est déjà présent dans Excel. Pour l’activer :

1. Ouvrez votre fichier Excel 
2. Cliquez sur Fichier dans le ruban
3. Sélectionnez Options > Compléments 
4. Dans la liste déroulante, choisissez Compléments COM puis cliquez sur Atteindre
5. Une fenêtre s’ouvre. Cochez Microsoft Power Pivot for Excel, puis cliquez sur OK

L’onglet Power Pivot apparaît maintenant dans votre ruban.

Activez le complément Power Pivot dans Excel. Image par l’auteur.

Remarque : Power Pivot fonctionne uniquement avec Excel Professional Plus ou Microsoft 365. Si l’onglet n’apparaît pas après l’activation, la version d’Excel installée sur votre ordinateur ne l’inclut peut-être pas.

Importer des données depuis plusieurs sources

Vous pouvez désormais importer des données depuis différentes sources, comme un fichier Excel, un CSV ou même une base SQL Server. 

Dans cet exemple, nous avons deux jeux de données dans un fichier .xlsb :

1. sales.xlsb 

2. customer.xlsb 

Pour les importer dans Power Pivot :

1. Cliquez sur l’onglet Power Pivot et sélectionnez Gérer. Une nouvelle fenêtre s’ouvre
2. Allez dans Accueil, puis cliquez sur Obtenir des données externes et choisissez Depuis d’autres sources
3. Défilez puis cliquez sur Fichier Excel

Importer des données depuis d’autres sources. Image par l’auteur.

4. Dans la fenêtre, cliquez sur Parcourir et sélectionnez le fichier customer.xlsb 

5. Cochez Utiliser la première ligne comme en-tête de colonnes puis cliquez sur Suivant

Importez le fichier Excel dans Power Pivot. Image par l’auteur.

Dans la fenêtre suivante, cliquez sur Aperçu & Filtrer pour prévisualiser les données avant import. Une fois satisfait, cliquez sur OK, puis le message de transfert réussi s’affiche. Cliquez ensuite sur Fermer.

Prévisualisez les données sélectionnées. Image par l’auteur. 

Répétez la même opération pour le fichier sales.xlsb. En bas de l’écran, les deux fichiers apparaissent comme importés. Double-cliquez pour les renommer. 

Les deux fichiers importés. Image par l’auteur.

Construire des relations et des modèles de données

Maintenant que vos données sont chargées dans Power Pivot, il est temps de relier les tables pour qu’Excel comprenne leurs connexions. Cette étape sert de fondation à tous vos rapports.

Créer des relations entre tables

Pour créer une relation entre les tables Sales et Customers :

1. Dans l’onglet Accueil cliquez sur Vue Diagramme. Vous y verrez les tables importées
2. Cliquez sur CustomerID dans la table Sales
3. Faites-le glisser vers CustomerID dans la table Customer pour créer la relation

Remarque : pour modifier la relation, cliquez droit sur la ligne puis sur Modifier la relation…. Dans la fenêtre, sélectionnez les colonnes à relier.

Créez une relation entre les tables. Image par l’auteur.

Dans cette relation, un même client peut apparaître plusieurs fois dans la table Sales, tandis que chaque client n’apparaît qu’une seule fois dans la table Customers. Il s’agit d’une relation un-à-plusieurs qui permet d’utiliser des champs des deux tables dans les Tableaux croisés dynamiques et de calculer sans formules de recherche.

Concevoir avec un schéma en étoile

Le schéma en étoile est l’une des manières les plus simples d’organiser un modèle Power Pivot. Il garde vos tables en ordre et rend les calculs prévisibles.

Commencez par choisir la table de faits. Ici, Sales sert de table de faits car elle contient les enregistrements transactionnels : date, client, produit, quantité et montant.

Identifiez ensuite les tables de dimensions qui décrivent les données de Sales. Exemples courants :

• Customers (clé primaire : CustomerID)
• Products (clé primaire : ProductID)
• Regions (clé primaire : RegionID)

Chaque table de dimension possède une clé primaire. Vous reliez cette clé à la clé étrangère correspondante dans la table de faits :

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

Une fois les liens créés, la table Sales se trouve au centre avec les tables de dimension autour : c’est votre étoile. Cette structure clarifie le modèle, accélère les calculs et améliore la cohérence des rapports.

Créez un schéma en étoile. Image par l’auteur.

Ajouter des colonnes calculées

Une fois les relations en place, vous pouvez créer de nouveaux champs directement dans le modèle de données.

1. Basculez vers la Vue Données

2. Sélectionnez le champ vide Ajouter une colonne en fin de table.

3. Saisissez = [TotalAmount] / [Qty] puis appuyez sur Entrée pour remplir toute la colonne

4. Renommez l’en-tête en PricePerUnit

Ainsi, les colonnes calculées font partie intégrante de la table. Elles sont stockées dans le modèle, se mettent à jour avec vos données et restent disponibles pour tout Tableau croisé dynamique ou mesure DAX ultérieure.

Ajoutez une colonne calculée. Image par l’auteur.

Écrire des formules DAX pour l’analyse

Maintenant que le modèle est prêt, nous pouvons commencer à créer des formules DAX pour analyser les données. Ces formules permettent de construire des totaux, des comparaisons et des calculs temporels dans nos rapports.

Créer des mesures

Utilisez des mesures lorsque vous souhaitez des calculs qui se mettent à jour automatiquement dans un Tableau croisé dynamique.

Pour créer une mesure :

1. Ouvrez la fenêtre Power Pivot

2. Allez dans Accueil > Calculs > Nouvelle mesure

3. Saisissez une formule telle que = SUM(Sales[TotalAmount])

4. Nommez-la Total Sales et cliquez sur OK

Créez des mesures. Image par l’auteur. 

Ajouter une mesure en pourcentage du total

Vous pouvez également utiliser cette formule pour ajouter une mesure « pourcentage du total » :

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

Ceci affiche la part de chaque région dans le chiffre d’affaires global.

Ajoutez une mesure en pourcentage du total. Image par l’auteur.

Utiliser la time intelligence

Les fonctions de time intelligence sont des formules DAX qui tiennent compte des jours, mois, trimestres et années. Elles permettent de calculer des cumul annuels, de comparer à des périodes précédentes et d’évaluer des tendances sans ajuster manuellement les filtres.

Pour qu’elles fonctionnent dans votre modèle, vous avez d’abord besoin d’une véritable table de dates.

Configurer la table Date

Pour la configurer : 

1. Allez dans Power Pivot > Ajouter au modèle de données
2. Dans Power Pivot, sélectionnez la table puis choisissez Conception > Définir comme table de dates

Créez une table de dates. Image par l’auteur.

3. Depuis Accueil > Vue Diagramme, reliez Date[Date] → Sales[OrderDate].

Reliez Date Table[Date] à Sales[OrderDate]. Image par l’auteur.

Créer des mesures de time intelligence

Une fois la table Date prête, vous pouvez créer des mesures pour évaluer la performance selon différentes périodes.

Depuis le début de l’année (YTD) :

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

Comparaison avec l’année précédente :

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

Calculez dans le temps. Image par l’auteur. 

Une fois les mesures créées, retournez dans Excel et construisez un Tableau croisé dynamique en utilisant le modèle de données. Placez ensuite des champs de la table Date dans la zone Lignes et ajoutez Total Sales, Total Sales YTD et Sales Last Year dans Valeurs. 

Vous visualisez ainsi le fonctionnement des mesures de time intelligence avec la table Date du modèle.

Tableau croisé dynamique avec Total Sales, YTD et Last Year Date. Image par l’auteur. 

Modèles DAX fréquents

Certaines formules DAX reviennent souvent car elles permettent de décomposer rapidement les données et de répondre à des questions courantes. Voici deux modèles efficaces dans de nombreux cas :

Moyenne par catégorie :

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

Cumul sur la période (running total) :

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Lorsque vous créez des mesures, adoptez ces quelques réflexes : 

• Des noms clairs
• Des formules lisibles
• Des variables (VAR) lorsque la mesure s’allonge. 

Vous comprendrez ainsi plus facilement votre modèle lorsque vous y reviendrez plus tard.

Visualiser et interagir avec votre modèle

Maintenant que le modèle et les mesures sont en place, transformons les données en visuels que vous pouvez explorer et ajuster en temps réel.

Créer des Tableaux croisés dynamiques et des graphiques croisés dynamiques

Voici comment insérer un Tableau croisé dynamique à partir du modèle de données pour travailler directement avec vos tables connectées :

1. Ouvrez une feuille Excel
2. Allez dans Insertion > Tableau croisé dynamique > À partir du modèle de données
3. Sélectionnez Nouvelle feuille

Dans le volet Champs de tableau croisé dynamique, vous pouvez tirer des champs depuis n’importe quelle table. Par exemple :

• Faites glisser RegionName de la table Regions vers Lignes
• Faites glisser Total Sales vers Valeurs

Comme nous avons créé des relations plus tôt, Excel assemble automatiquement l’ensemble.

Créez un tableau croisé dynamique à partir des données Power Pivot. Image par l’auteur.

Pour un visuel, cliquez n’importe où dans le tableau croisé dynamique, allez dans Insertion > Graphique croisé dynamique, choisissez un type (par ex. Histogramme groupé) et validez. Le graphique reste lié au tableau, tout se met donc à jour ensemble.

Ajoutez un graphique croisé dynamique. Image par l’auteur.

Ajouter des segments et des filtres

Les segments offrent des filtres sous forme de boutons pour rendre le rapport interactif. Pour les ajouter :

1. Cliquez sur votre tableau croisé
2. Allez dans Insertion > Segment
3. Choisissez des champs comme RegionName ou ProductName

Le segment apparaît dans une zone de la feuille. En cliquant sur différents éléments, le tableau et le graphique se mettent instantanément à jour. Si vous avez plusieurs tableaux, vous pouvez connecter un seul segment à tous pour un filtrage cohérent dans la page.

Ajoutez des segments. Image par l’auteur.

Construire des KPI

Les KPI permettent de visualiser la performance par rapport à un objectif sans ajouter de calculs supplémentaires dans la feuille. Pour créer les vôtres :

1. Dans la fenêtre Power Pivot, allez dans KPI > Nouveau KPI
2. Définissez Total Sales comme mesure de base
3. Choisissez Valeur absolue, entrez votre objectif (par exemple 4000), ajustez les seuils et choisissez un style d’icône
4. Cliquez sur OK pour créer le KPI

Définissez le KPI d’une mesure. Image par l’auteur.

5. Dans le volet des champs, développez la table Sales puis Total Sales
6. Ensuite, faites glisser Total Sales et Status dans le champ Valeurs

Vous visualisez désormais la performance par rapport à l’objectif et aux seuils. 

Affichez le statut du KPI dans un tableau croisé dynamique Excel. Image par l’auteur.

Optimiser les performances de Power Pivot

Une fois le modèle en place, l’objectif est de le garder rapide et agréable à utiliser. Power Pivot gère de grands volumes, mais quelques ajustements légers aident à conserver la réactivité, notamment quand vous ajoutez des données au fil du temps.

Réduire la taille du modèle

Un modèle plus léger tourne plus vite : supprimez donc tout ce qui est inutile.

Vous pouvez effacer les colonnes non utilisées dans la Vue Données. Même si une colonne n’apparaît jamais dans un tableau croisé, elle occupe de la mémoire : élaguer garde le modèle propre.

Lors de l’import, utilisez Power Query pour filtrer lignes et colonnes avant chargement dans le modèle. Ainsi, seuls les champs utiles sont chargés et tout reste plus clair.

Limitez les colonnes calculées au strict nécessaire car elles stockent une valeur par ligne et gonflent vite la taille. À l’inverse, les mesures sont plus efficaces car elles se calculent à la demande par les tableaux croisés. 

Choisir des types de données efficaces

Power Pivot compresse différemment selon le type de données. Choisir le bon type peut faire une vraie différence.

Dans la Vue Données, sélectionnez une colonne et choisissez le type le plus adapté sous Type de données dans le ruban. Par exemple :

• Nombres entiers > Nombre entier
• Valeurs décimales > Nombre décimal
• Identifiants ou codes non utilisés en calcul > Texte

En choisissant le type adéquat, Power Pivot compresse mieux la colonne, ce qui réduit la taille et accélère les calculs.

Vérifiez et utilisez le type de données approprié. Image par l’auteur.

Gérer l’actualisation et les problèmes de calcul

Si vos tableaux ne reflètent pas les dernières données, allez dans l’onglet Power Pivot et cliquez sur Actualiser tout. Cela recharge tout depuis vos sources.

Si des chiffres paraissent incohérents, ouvrez la Vue Diagramme et vérifiez vos relations : une relation manquante ou rompue peut fausser des totaux ou des filtres.

En cas d’erreur DAX, surtout sur des mesures complexes, il s’agit souvent d’une auto-référence indirecte. Réécrivez alors la mesure avec une logique plus simple ou utilisez des blocs VAR pour résoudre la référence circulaire.

Intégration avec Power Query et Power BI

L’un des atouts de Power Pivot est sa facilité d’intégration avec le reste de l’écosystème Microsoft. Vous pouvez utiliser Power Query pour nettoyer et façonner les données avant leur arrivée dans le modèle, ou migrer l’ensemble du modèle vers Power BI lorsque vous avez besoin de tableaux de bord interactifs.

Nettoyer et transformer avec Power Query

Power Query est l’endroit idéal pour préparer vos données avant de les charger dans Power Pivot. Vous nettoyez, filtrez et structurez en amont pour garder un modèle ordonné.

Ouvrez Power Query via Données > Depuis texte/CSV > Transformer. Les données s’ouvrent dans l’éditeur, où vous pouvez :

• Supprimer les doublons
• Renommer ou réorganiser des colonnes
• Filtrer les valeurs non pertinentes
• Définir les types de données avant chargement dans le modèle

Power Query enregistre chaque étape à droite de la fenêtre. Le nettoyage se rejoue automatiquement à chaque actualisation du fichier.

Quand tout est bon, sélectionnez Fermer & Charger vers, puis choisissez Modèle de données. Les données nettoyées sont alors chargées directement dans Power Pivot.

Exporter des modèles vers Power BI

Vous pouvez aussi emporter votre modèle Power Pivot dans Power BI lorsque vous avez besoin de visuels plus riches ou de tableaux de bord partagés. Procédez ainsi :

1. Enregistrez votre classeur Excel
2. Ouvrez Power BI Desktop
3. Allez dans Obtenir des données > Classeur Excel
4. Sélectionnez votre fichier

Power BI importe les tables et relations telles qu’elles existent dans Power Pivot. Vous pouvez ensuite créer des tableaux de bord, collaborer avec vos équipes et planifier des actualisations pour garder des rapports à jour sans actions manuelles.

Bonnes pratiques pour des modèles pérennes

Au fur et à mesure que le modèle grandit, une bonne organisation facilite les mises à jour, le débogage et l’évolution. Voici quelques habitudes pour conserver un modèle propre et fiable dans le temps :

Conventions de nommage et organisation

Des noms explicites font la différence quand vous rouvrez un fichier des semaines ou des mois plus tard. Utilisez des noms lisibles pour les mesures comme Total_Sales, Total_Quantity ou Profit_Margin pour savoir immédiatement à quoi elles correspondent.

Vous pouvez aussi regrouper des mesures liées dans des dossiers d’affichage dans Power Pivot. Quand le modèle grossit, ces dossiers facilitent la recherche des calculs utiles.

Validation des données

Avant de vous fier aux résultats, effectuez quelques contrôles rapides :

• Comparez les totaux de la source avec ceux de vos tableaux croisés
• Utilisez des vérifications DAX simples comme :

• COUNTROWS() pour confirmer le nombre de lignes d’une table

• DISTINCTCOUNT() pour vérifier des valeurs uniques, comme les clients ou produits

Ces tests simples aident à détecter des relations manquantes, des filtres incorrects ou des problèmes de données avant qu’ils ne prennent de l’ampleur.

Maintenir et mettre à jour vos modèles

Quand de nouvelles données arrivent, allez dans l’onglet Power Pivot et choisissez Actualiser ou Actualiser tout. Power Pivot recharge alors toutes les sources connectées.

Avant d’opérer des changements structurels majeurs (nouvelles relations, réécriture de mesures clés), enregistrez une copie de sauvegarde. Vous aurez ainsi un filet de sécurité si quelque chose ne se passe pas comme prévu.

Conclusion

Power Pivot réunit vos données en un seul endroit et vous aide à bâtir des rapports clairs et fiables. Une fois le modèle configuré, explorez vos chiffres, créez des visuels et mettez tout à jour en un seul rafraîchissement.

Pour découvrir toute la panoplie d’outils Excel, consultez notre parcours Data Analysis with Excel Power Tools ainsi que notre cours Power Pivot in Excel.