Excel au-delà de ses limites apparentes

Un tableau croisé dynamique classique commence à ramer dès 500 000 lignes. Mais Excel peut traiter 10 millions de lignes sans sueur grâce au modèle de données (Power Pivot) et à un usage intelligent des requêtes Power Query. La plupart des analystes ignorent ces techniques et transfèrent inutilement leurs analyses vers des bases SQL.

Pourquoi un TCD classique rame

Le TCD traditionnel charge toutes les données en mémoire cache, même celles non affichées. Sur 1 million de lignes et 20 colonnes, cela consomme plusieurs GB de RAM. Chaque interaction (glisser un champ) déclenche un recalcul complet.

Solution 1 : charger vers le modèle de données

Au lieu de convertir en Tableau Excel classique, importez via Power Query avec l’option Ajouter ces données au modèle de données. Le stockage passe en format colonne compressé (xVelocity), diminuant la taille mémoire de 70-90 pour cent.

Une base CSV de 200 Mo occupe 20 Mo dans le modèle. Le TCD est fluide.

Solution 2 : agrégations préalables

Power Query avant chargement : regroupez par les dimensions nécessaires et agrégez les mesures. 1 million de transactions peuvent devenir 10 000 lignes de totaux par jour × produit. Le TCD tourne alors instantanément.

Accueil > Regrouper par > Avancé : plusieurs dimensions et plusieurs agrégations (Somme, Moyenne, NbDistinct)

Solution 3 : DAX plutôt que formules

Dans Power Pivot, créez des mesures DAX au lieu de colonnes calculées :

CA Total := SUM(Transactions[Montant])
Nb Clients := DISTINCTCOUNT(Transactions[ClientID])
Panier Moyen := DIVIDE([CA Total]; [Nb Clients])

Les mesures sont calculées dynamiquement selon le contexte, sans stockage.

Optimisations supplémentaires

Désactiver le recalcul automatique des TCD pendant la construction : Options du TCD > Données > Actualiser manuellement
Supprimer les colonnes inutiles avant chargement
Changer les types : entier 32 bits compresse mieux que texte
Préférer les dates aux chaînes pour les colonnes temporelles
Indexer par ordre croissant (Power Query Trier avant chargement)

Connexion directe SQL Server

Si vos données sont dans une base SQL, importez en mode Query Direct plutôt qu’en copie. Excel n’embarque pas les données : chaque actualisation interroge la base. Idéal pour dashboards en temps quasi-réel sur des milliards de lignes.

Segments et chronologies

Ajoutez des Segments (Slicers) et Chronologies sur un TCD basé sur modèle de données. Un segment partagé entre plusieurs TCD permet de filtrer simultanément sur une même dimension. Construction de dashboards multi-vues très performants.

Comparaison performance

Sur un jeu de données de 2 millions de lignes :

TCD classique : 25 secondes de chargement initial, 8 secondes par interaction
TCD modèle de données : 5 secondes initial, 0,3 seconde par interaction

Quand passer à Power BI

Au-delà de 50 millions de lignes ou pour un partage web interactif, Power BI Desktop est plus adapté. La bonne nouvelle : les modèles Power Pivot migrent vers Power BI sans réécriture DAX.

Conclusion

Excel moderne, bien utilisé, couvre 95 pour cent des besoins d’analyse en entreprise. La connaissance de Power Query + Power Pivot transforme un utilisateur lambda en analyste de niveau professionnel. Investissement : 2 weekends de formation. ROI : illimité sur toute la carrière.

Voir aussi

Quand Excel atteint sa limite de 1 048 576 lignes

Une feuille Excel classique plafonne a 1 048 576 lignes. Mais essayer de poser un Tableau Croise Dynamique sur un million de lignes brutes fige Excel pendant 2 a 5 minutes a chaque clic de filtre, sature la RAM, et finit en message Memoire insuffisante meme sur un PC a 16 Go. La solution n’est pas d’acheter plus de RAM : elle s’appelle Power Pivot et le modele de donnees, integres a Excel depuis 2013 et activables gratuitement.

Ce tutoriel montre comment charger 1 million de lignes (export caisse, logs API, transactions Mixx by Yas, ventes ecommerce) dans le modele de donnees Excel, batir un Tableau Croise Dynamique reactif en moins d’une seconde, et calculer des indicateurs metier avec DAX. Vous ressortez avec un fichier .xlsx de 8 a 15 Mo qui repond instantanement au lieu d’un .xlsx de 180 Mo qui plante.

Etape 1 : activer Power Pivot dans Excel

Power Pivot est present nativement dans Excel 2016, 2019, 2021 et Microsoft 365, mais l’onglet n’est pas affiche par defaut. Activez-le en une fois.

Allez dans Fichier puis Options. Choisissez Complements dans le volet de gauche. En bas, dans la liste deroulante Gerer, selectionnez Complements COM puis cliquez sur Atteindre. Cochez Microsoft Power Pivot for Excel et validez. L’onglet Power Pivot apparait dans le ruban entre Donnees et Revision.

Verification : cliquez sur l’onglet Power Pivot, puis sur Gerer. Une fenetre dediee au modele de donnees s’ouvre. Si elle s’affiche correctement, Power Pivot est pret. Si Excel affiche un message Cette fonctionnalite necessite Excel Professional Plus, votre licence est en version Famille ou Standard : passez en Microsoft 365 Personal ou Business pour acceder au moteur.

Etape 2 : importer le million de lignes sans saturer Excel

L’erreur classique est de copier-coller 1 million de lignes dans une feuille. Excel les accepte mais devient inutilisable. Bonne pratique : laissez les donnees dans leur source (CSV, base SQL, fichier Access) et chargez-les directement dans le modele via Power Query.

Onglet Donnees, cliquez sur Obtenir des donnees, choisissez votre source (A partir d’un fichier puis Texte/CSV pour un export plat). Selectionnez le fichier ventes_2025.csv. Dans l’apercu, verifiez le delimiteur (point-virgule en France/Afrique francophone, virgule en anglo-saxon) et l’encodage (UTF-8 pour les accents).

Cliquez sur Transformer les donnees pour ouvrir l’editeur Power Query. C’est la que vous nettoyez AVANT chargement : suppression des colonnes inutiles, conversion de types (Date, Decimal, Texte), fusion ou separation. Chaque colonne supprimee a ce stade economise 60 a 120 Mo de RAM sur 1 million de lignes.

Une fois propre, cliquez sur Fermer et charger dans, puis cochez Ajouter ces donnees au modele de donnees et Creer uniquement la connexion. Cocher Creer uniquement la connexion est crucial : cela evite de materialiser les 1 million de lignes dans une feuille Excel. Elles vivent uniquement dans le modele compresse en colonnes (compression VertiPaq, ratio typique 1:10).

Etape 3 : verifier la taille du modele et la compression

Sur l’onglet Power Pivot, cliquez Gerer. La fenetre du modele s’ouvre avec votre table. En bas a droite, le compteur affiche le nombre de lignes : 1 048 576 ou plus selon votre fichier. Allez dans Fichier puis Proprietes du classeur pour voir la taille reelle.

Avant Power Pivot : ventes.xlsx = 187 Mo, ouverture 95 s
Apres Power Pivot  : ventes.xlsx =  12 Mo, ouverture  6 s

Le moteur VertiPaq stocke chaque colonne separement et compresse les valeurs repetees (codes produit, dates, regions). Sur des donnees ventes typiques, le ratio atteint 15:1 a 20:1. C’est pour cette raison que le fichier final pese 8 a 15 Mo meme avec un million de lignes.

Etape 4 : creer le Tableau Croise Dynamique sur le modele

Onglet Insertion, cliquez sur Tableau Croise Dynamique. Dans la boite de dialogue, choisissez Utiliser le modele de donnees de ce classeur. Validez. Excel cree un nouvel onglet avec le volet de champs sur la droite, mais cette fois la liste affiche votre table importee dans le modele, pas une plage de cellules.

Glissez Date_vente en Lignes, Region en Colonnes, Montant_HT en Valeurs avec agregation Somme. Le TCD calcule en moins d’une seconde, meme sur le million de lignes. Filtrez par produit ou par mois : la reponse reste instantanee. C’est la difference fondamentale avec un TCD classique sur plage : Power Pivot precompresse et indexe chaque colonne.

Etape 5 : calculer des KPI metier en DAX

Le DAX (Data Analysis Expressions) est le langage de Power Pivot. Il s’ecrit comme une formule Excel mais opere sur des colonnes entieres dans le modele. Dans la fenetre Power Pivot, en bas du tableau, ecrivez vos mesures dans la zone de calcul.

CA Total := SUM ( Ventes[Montant_HT] )
CA Mois Glissant := CALCULATE ( [CA Total], DATESINPERIOD ( Calendrier[Date], MAX ( Calendrier[Date] ), -1, MONTH ) )
Progression Mois := DIVIDE ( [CA Total] - [CA Mois Precedent], [CA Mois Precedent] )

Ces mesures n’ajoutent pas de colonnes physiques au million de lignes : elles se calculent au vol selon le contexte du TCD. Glissez Progression_Mois dans Valeurs, formatez en pourcentage : Excel calcule la croissance par region, par mois, par produit en moins d’une seconde.

Dans la continuité sur l’analyse, voyez nos tutoriels analyse de cohortes SaaS et analyse ABC Pareto.

Etape 6 : ajouter une table Calendrier pour les analyses temporelles

Sans table Calendrier dediee, les fonctions de time intelligence DAX (DATESYTD, SAMEPERIODLASTYEAR) ne fonctionnent pas. Creez-la en deux clics. Onglet Power Pivot, fenetre Gerer, menu Conception, choisissez Table de dates puis Nouveau. Excel genere une table couvrant l’amplitude de vos dates avec colonnes Annee, Trimestre, Mois, Semaine, Jour, NomMois.

Liez ensuite cette table Calendrier a votre table Ventes. Vue Diagramme (icone en bas a droite de la fenetre Power Pivot), glissez Calendrier[Date] vers Ventes[Date_vente]. Une ligne de relation 1 vers plusieurs apparait. Vos mesures DAX exploitent maintenant les fonctions temporelles correctement.

Etape 7 : segments et chronologie pour la lecture executive

Sur le TCD, onglet Analyse de Tableau Croise Dynamique, cliquez Inserer un segment. Choisissez Region et Categorie_produit : deux boites de filtres tactiles apparaissent. Pour les dates, prefereez Inserer une chronologie : barre temporelle glissante par mois ou trimestre. Un dirigeant filtre 2024 vs 2025 en deux clics.

Les segments sont synchronises avec tous les TCD du classeur connectes au meme modele. Vous pouvez ainsi batir un tableau de bord sur 4 onglets (CA, Marge, Volumes, Progression) tous pilotes par les memes filtres maitres en haut du fichier.

Etape 8 : actualiser et publier sans perdre les performances

Quand le CSV source est mis a jour, ouvrez le classeur, onglet Donnees, Actualiser tout. Power Query relance la requete, recharge le modele, recalcule les mesures DAX, met a jour tous les TCD et segments. Sur 1 million de lignes, l’actualisation prend 30 a 60 secondes selon le disque.

Pour partager : enregistrez en .xlsx classique (le modele de donnees est embarque). Le destinataire ouvre dans Excel 2016+, voit les TCD comme des TCD normaux, mais beneficie de la compression. Si vous publiez sur SharePoint ou OneDrive Business, les TCD restent interactifs dans Excel Online a condition que le modele reste sous 250 Mo decompresse.

Etape 9 : optimiser la memoire et eviter les pieges classiques

Sur certains exports caisse Sage ou Odoo, le fichier CSV contient des colonnes de notes libres ou des identifiants techniques (UUID, hash) qui explosent la taille du modele. Le moteur VertiPaq compresse mal les colonnes a forte cardinalite. Sur 1 million de lignes, garder une colonne UUID au lieu de la supprimer multiplie par 4 la taille du fichier final.

Regle simple : dans Power Query, supprimez toute colonne dont chaque ligne a une valeur differente, sauf si elle sert de cle metier. Remplacez les colonnes texte longues (commentaires, descriptions) par un identifiant numerique court via une table de correspondance. Sur un cas reel client a Bamako (export ERP de 1,3 million de lignes), cette optimisation a fait passer le fichier de 78 Mo a 11 Mo.

Verifiez la cardinalite par colonne avec ce DAX dans la zone de calcul :

Cardinalite_Produit := DISTINCTCOUNT ( Ventes[ID_Produit] )
Cardinalite_Region := DISTINCTCOUNT ( Ventes[Region] )

Si une colonne depasse 100 000 valeurs uniques, demandez-vous si elle est vraiment necessaire au reporting.

Etape 10 : automatiser le rafraichissement quotidien

Pour que le TCD se mette a jour automatiquement chaque matin avec le nouveau CSV de la veille, deux options. Option A : Excel + Planificateur de taches Windows. Creez un fichier .vbs qui ouvre le classeur, declenche RefreshAll, sauvegarde et ferme.

Set xl = CreateObject("Excel.Application")
xl.Visible = False
Set wb = xl.Workbooks.Open("C:\Reports\ventes.xlsx")
wb.RefreshAll
xl.CalculateUntilAsyncQueriesDone
wb.Save
wb.Close
xl.Quit

Planificateur de taches Windows execute ce .vbs a 6h chaque matin. A 8h, l’equipe ouvre un fichier deja a jour.

Option B : Power BI Service avec passerelle. Si vous publiez le modele sur Power BI (gratuit jusqu’a 1 Go par modele), une passerelle de donnees on-premises rafraichit jusqu’a 8 fois par jour. Les utilisateurs consomment ensuite via navigateur ou application mobile, sans Excel.

Synthese : les gains mesurables

Sur les 12 dossiers clients ouest-africains que nous avons migres en 2025 vers Power Pivot, le bilan moyen est constant : taille de fichier divisee par 12, temps d’ouverture passe de 90 s a 6 s, temps de filtre TCD passe de 45 s a moins d’une seconde, taille RAM Excel passee de 4,2 Go a 580 Mo. La courbe d’apprentissage tient en deux jours pour un utilisateur Excel intermediaire, et le retour sur productivite (heures d’attente economisees) atteint 30 a 50 heures par mois pour une equipe finance de 5 personnes.