8 trucs pour réduire la taille d’un fichier Excel

Publié le 16 septembre 2013
par Sophie Marchand M.Sc.

Il arrive fréquemment que des clients me fassent auditer leurs fichiers Excel. Très souvent, ces fichiers sont extrêmement volumineux. Ils sont donc non seulement lourds à manipuler mais également très instables. Comment faire donc pour réduire la taille d’un fichier Excel? C’est à cette question que je vais répondre dans l’article suivant.

 

Réduire taille fichier Excel

 

1. Version Excel

D’abord, il faut savoir que la version Excel aura un impact sur la taille de votre fichier. Faites le test. Si vous sauvegardez un fichier sous Excel 2003 et que vous sauvegardez ensuite le même fichier sous Excel 2007, vous verrez sa taille diminuer de moitié, voire même plus encore.

 

2. Longueur des champs texte

Si vous utilisez des noms de cellules, des noms de champs, des noms d’onglets et des noms de fichiers très longs, vous allez assurément alourdir votre fichier inutilement. Pensez plutôt à utiliser des abréviations claires. Vous observerez une différence nette. Par exemple, la formule suivante est simplement l’addition de deux cellules… Vous voyez qu’elle utilise beaucoup trop de caractères pour une formule aussi simple…

 

Trop de caractères inutiles dans cette formule Excel

 

 3. Dernière cellule d’un onglet

Il arrive qu’en travaillant dans Excel, on élabore et on supprime ensuite certaines sections. Utilisez la touche CTRL+End (ou Fin en français) pour voir quelle est la dernière cellule active dans chaque onglet. Souvent, vous remarquerez que votre dernière cellule réellement utilisée est V50 et que votre dernière cellule active est CD30000. Ceci alourdira incontestablement votre fichier. Pensez donc à réduire toutes les “extras” lignes et colonnes.

 

4. Références circulaires

Les références circulaires sont sans contredit des éléments d’instabilité dans un fichier Excel. La plupart du temps, lorsque vous avez des références circulaires, c’est réellement que vous avez effectué un mauvais calcul (une cellule qui réfère à elle-même dans un calcul). D’autres fois, la référence circulaire est souhaitée. Il est par contre toujours possible de contourner les références circulaires par calculs algébriques. Tentez donc d’éviter les références circulaires.

 

5. Quantité de formules complexes

Lorsque vous utilisez des formules complexes, comme par exemple RechercheV/Vlookup, RechercheH/Hlookup, Index/Index, Equiv/Match, Decaler/Offset, somme.si.ens/sumifs, et que vous appliquez ces formules sur un très grand nombre de lignes, vous alourdirez beaucoup votre fichier Excel. Si cette situation est inévitable, pensez à utiliser des macros (qui s’assureront d’effectuer les bons calculs et de copier coller ensuite les résultats en valeurs) ou encore à utiliser des outils solutions comme Power Query et Power Pivot, qui compressent les données en colonne et réduisent de façon spectaculaire les fichiers.

 

6. Cache

Si votre fichier Excel est excessivement lourd, il est possible que ce soit lié aussi à des problèmes de cache. Par exemple, si vous avez une base de données et que vous créez 10 tableaux croisés dynamiques à partir de cette base de données, si vous recréez chaque fois le tableau croisé dynamique à partir de la base de données, vous allez remplir la cache et alourdir votre fichier. Pour contourner ce problème, il faudrait plutôt copier le premier tableau croisé dynamique créé 9 fois et ajuster ensuite les paramètres de chacun selon ce que l’on veut présenter. (note : dans les versions récentes d’Excel, les tableaux croisés dynamiques conçus à partir d’une même base de données partagent maintenant la même cache).

 

7. Items excédentaires

Pensez à utiliser la fonction F5 pour retracer et supprimer tous les items non nécessaires (objets, commentaires, formats, etc.). Vérifiez notamment les formats conditionnels. J’ai audité des fichiers Excel où le format conditionnel avait été très malmené! Je ne sais trop comment mais une même cellule pouvait se retrouver avec 20 fois le même format conditionnel (appliqués les uns par-dessus les autres). Une fois le nettoyage effectué, le fichier roulait comme sur des roulettes! Pensez également à désactiver tous les compléments (add-ins).

 

8. Format XLSB

Dans les cas extrêmes, vous pouvez également penser à enregistrer vos fichiers Excel volumineux dans le mode binaire XLSB. Personnellement, je n’ai jamais eu à me rendre jusque-là mais ça demeure une option.

 

Au-delà de la taille du fichier, viser la performance !

Au-delà de la taille du fichier lui-même, vous voudrez sans doute travailler avec des fichiers performants. Pour cela, je vous invite à prendre connaissance de :

 

Et vous, que faites-vous pour réduire la taille de vos fichiers Excel?

 

Formation complémentaire

Afin d’approfondir vos connaissances avec Excel, dont celles touchant aux meilleures pratiques d’affaires, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

55 réflexions sur “8 trucs pour réduire la taille d’un fichier Excel”

  1. Je fais presque tout cela également !

    J’ajouterais que je déconseille de formater les cellules en sélectionnant toute la ligne ou toute la colonne ce qui évite de formater le maximum de lignes et de colonnes possibles avec Excel et de se retrouver au point 3 !…

    Je formate toutes les cellules de la feuille voire du classeur avec la même police et la même taille … Eh oui !…
    Je privilégie des noms d’onglets courts (2 ou 3 lettres suffisent! ) pour les formules ce qui revient au point 2 ! Et en plus les formules sont plus intelligibles…

    Je dimensionne automatiquement et au plus juste mes tableaux croisés dynamiques en agrandissant mes bases de données entre la premiere ligne et l’entête des colonnes, c’est très fiable en plus.

    Par contre j’évite les macros à tout prix, car leur maintenance est parfois délicate et leur portabilité assez faible d’une version ou d’un environnement à l’autre. De plus, la plupart des macros ignorent une fonction souvent déjà présente dans Excel.

    Voila comment “j’ai la ligne” avec Excel !

    1. Bien sur que les macros gonflent un classeur surtout si elles ont été écrites avec l’enregistreur de macro…
      Et puis cela ne sert à rien d’automatiser des traitements qui peuvent être fait manuellement 😉

      1. Bonjour,

        Je ne suis pas sûr d’avoir compris cette méthode, j’ai un onglet de base que je copie et rajoute à chaque nouvelle entrée (fiche client).

        Cet onglet de base doit être volumineux car à chaque ajout de nouvelle fiche, mon Excel s’allourdit…j’en suis à 13 MO aujourd’hui (11 hier!)

        Merci

  2. Bonjour,
    J’utilise des noms (Onglet Formules/Gestionnaire de noms) pour identifier des plages de cellules ou des tableaux. Ça me permet de garder des onglets avec des noms un peu plus parlant et simplifie le libellé de mes formules.

    Certaines données que je reçois sont des valeurs numériques qui correspondent à des textes (choices 0= USA, 1=Europe, etc.). Avant, j’importais la valeur numérique et j’utilisais recherchev (vlookup) pour faire la correspondance avec le texte. Maintenant, je travaille un peu plus les query d’extraction et j’importe directement la valeur en texte.

    Je révise annuellement mes plus gros fichiers. Je reçois souvent des demandes pour avoir des informations plus détaillées ou particulières. Je fais un tableau croisé supplémentaire, une ou deux formules de plus. Finalement, cette information n’est plus réutilisée, mais le tableau reste dans le fichier. Il faut s’assurer de ne pas avoir de colonnes inutiles, d’onglets inutilisés. Ménage annuel!
    Joanne

  3. Merci pour l’astuce du ctrl+end ; Nous venons de gagner 1 bon méga sur un fichier qui vient de passer à 43Ko. Assurément une bonne opération, merci encore 😉

  4. Bonjour ,

    J’ai enfin trouvé la touche “fin” et je me pose la question comment faire pour maintenant que je sais que ma dernière cellule active est la ligne “1048576” alors que je n’utilise que 20 lignes…
    Comment remonté à la 20 ligne…

    1. Bonjour Frédérique,

      Vous devez sélectionner toutes les lignes excédentaires et les supprimer et recommencer la même chose avec les colonnes.

      Ensuite, vous devez enregistrer le fichier.

      La dernière cellule active deviendra la dernière cellule utilisée.

      Au plaisir,

      Sophie

  5. Bonjour,

    Pour ma part, en complément du point 3, j’utilise une astuce (peut être plus nécessaire sur les nouvelles versions) qui permet de demander à Excel de compacter au maximum le fichier.
    Se mettre sur la cellule A1 et sauvegarder. Vous verrez notamment les ascenseurs se redimensionner à la taille réellement utilisée sur la feuille.

    Je confirme que les mises en formes conditionnelles prennent un volume ENORME. Un de mes stagiaires vient d’en faire l’expérience ce jour en réduisant un périmètre de plages vides formatés sur plusieurs onglet. Passage de 11Mo à 400 ko !

    A bientôt

    Sbéastien

  6. Bonjour,
    on bon moyen d’alléger les fichiers est de ne pas trop utiliser les couleurs en surlignage. Ces dernières font peser les fichiers plus lourds.
    Une autre technique vise à figer les formules en faisant un copier-coller format et valeurs, quitte à ne conserver que la première ligne du tableau sous forme de formule si on veut absolument la conserver. Après avoir figé les cellules en copier-coller, il est important de supprimer les cellules qui semblent vides, mais ne le sont pas : elles contiennent un espace qui prend de la place.

    bon courage à tous

  7. un copié-collé depuis une page web (html) m’a fait passer de 76 kO à 5.41Mo (350 lignes sur 7 colonnes seulement)

    j’ai copié la totalité du contenu de la feuille en question et collé les valeurs seulement dans une autre feuille, puis supprimé la feuille contenant le html

  8. Merci pour ces astuces, que je garde sous le coude.
    malheureusement cela n’a pas corrigé mon cas;
    mis à part [CTRL]+[HOME], [CTRL]+[SHIFT]+[END], [CTRL]+C, [CTRL]+N, [CTRL]+V, [CTRL]+S
    vraiment étrange :/

  9. Simplement enregistrer sous format binaire (.SLXB) réduit la taille de moitié rapidement.
    Question 1 : y a t-il de quoi qu’on perd ou ne pourra plus faire une fois le fichier sous format binaire ?
    Question 2: À partir d’un fichier sous format binaire, est-il possible de recréer un .xslx ? ou autre ?
    Merci

  10. Bonjour,
    Mon problème est :
    J’ai un fichier qui résulte d’une extraction d’un grand livre via sage 1000, le fichier fait plus de 600 mille lignes et ne peut être enregistré que sous le format csv pour l’extraction.
    A l’ouverture du fichier, Excel me fait déjà un malaise… j’enregistre directement sous le format xlsb, cela n’est pas suffisant. Je vérifie quelle est la dernière cellule active (juste au cas où), je renomme au plus court l’onglet créé pas le système…
    Cependant, je n’ai même pas encore fait une seule formule que déjà je sens mon fichier sur le point d’exploser. Je réduis les colonnes (3 colonnes « Débit », « Crédit », « montant » passe à 1 colonne « montant » après formule pour obtenir les montants signés), fichier toujours aussi lourd.
    Le fait est que je dois, sur ce fichier faire quelques formules plutôt lourdes avec une recherchev, des somme.si.ens, et ajouter un onglet supplémentaire, et aussi insérer un croisé dynamique….
    Je n’ai pas de solution, je ne peux simplement pas travailler sur mon fichier de base.
    Comment faire ?

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour Claire,

      Il semble bien que votre situation soit typique d’une application des Power Tools d’Excel (Power Query et Power Pivot). Ces outils vous permettraient de réduire considérablement la taille de votre fichier, de faire les transformations et traitements nécessaires et de connecter la ou les tables résultantes sur des tableaux croisés dynamiques.

      Au sujet de ces outils, je vous invite à d’abord lire l’article suivant À télécharger: Présentation et vidéo du webinaire Power Query, Power Pivot et cie puis à chercher sur notre site les nombreux autres articles qui parlent de Power Query ou de Power Pivot.

      Francis

  11. Bonjour,

    merci pour cet article, fort intéressant.
    pourriez-vous me dire si les formules type “liredonnéesTCD”, sont-ils aussi lourdes ques des “recherchev”?
    je vous remercie.
    Ania

  12. Tout d’abord pour essayer d’apporter des éléments de réponses essayez de borné les fonctions recherchev ou index.equiv, somme.si au périmètre de recherche de valeur. Pour le reste de ce que je connais ça à déjà été abordé sauf le fait de Supprimer les couleurs ou utiliser des couleurs de base ou du gris.

  13. La solution rapide via VBA est la suivante…
    ” if you want to restrict the scrolling of an Excel worksheet to a specific range, use the worksheet.scrollarea property. For more on it see Excel VBA help.

    An example that only shows a1:s150 and limits scrolling as well as a way to undo the limits:

    Sub hideExtra()
    With ActiveSheet
    .Range(.Range(“t1”), .Cells(1, .Columns.Count)).EntireColumn.Hidden = True
    .Range(.Range(“a151”), .Cells(.Rows.Count, 1)).EntireRow.Hidden = True
    .ScrollArea = “a1:s150”
    End With
    End Sub
    Sub showAll()
    With ActiveSheet.Cells
    .EntireColumn.Hidden = False
    .EntireRow.Hidden = False
    .Parent.ScrollArea = “”
    End With
    End Sub

    Macro fournie par:
    Tushar Mehta (Technology and Operations Consulting)
    http://www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

  14. Salut,

    Le gestionnaire de noms, s’il est malmené (ex. par de $ et couverture de toute les cellules d’une colonne) peut multiplier x fois la taille de votre fichier Excel. Je viens d’en faire l’expérience, je suis passé de 98Mo à 28Mo en corrigeant la couverture des noms de colonnes de mon fichier.

    Bien à vous.

  15. J’ai fait ce que vous préconisez et notamment le point 3.
    Après enregistrement fermeture et réouverture du fichier le sélecteur se place toujours (Ctrl + end) dans la cellule H65536. Pourtant j’ai supprimé tout ce qui pouvait se trouver de la cellule H1 à la cellule IV65536 et, cerise sur le gâteau, le fichier est ………….+ gros d’1 kilo Octet……?????? !!!!!!!!!

  16. Je travaille avec Excel sur de très très gros fichiers avec beaucoup d’onglets. Je travaille à distance sur différents PC. Je constate et je pense que cela est normal que plus la machine est puissante et plus vite vont les traitements. Je suis passé de 4 G de ram a 8G de ram et la ça marche nickel.

  17. Ping : Temp2 | Pearltrees

  18. Pour le point 3, dans ma feuille, “la dernière cellule active” est dans la colonne WZO alors que j’utilise actuellement que jusque la colonne AU. J’ai beau supprimé des colonnes ou effacer le contenu, il n’y a rien à faire.
    Doit on refaire la feuille avec copier coller des cellules utilisées ; est-ce le gain de taille est suffisamment important pour toutes les modifications que cela entraîne ? (remplacement des références à la feuille, TCD déjà fait avec la feuille, etc)

  19. Bonjour, merci pour votre réponse rapide.
    En fait, la suppression des colonnes dans la feuille ne marchait pas, mais j’ai copié collé les colonnes utilisées dans une feuille vierge, et après avoir supprimé tout, recopier tout le contenu de cette nouvelle feuille dans la feuille d’origine. Et après l’enregistrement, ça a marché. Sans doute que cela peut aider certains dans mon cas.

  20. Bonjour à tous,
    j’ai suivi à la lettre vos conseils, mais mon fichier fait toujours le même poids (194 Mo).
    Il s’agit d’un tableau de 135727 lignes et de 448 colonnes. Toutes les formules de calcul ont été supprimer par un copier/coller valeur. Pas de macro. Pas de tableau croisé dynamique. Pas de graphique … en résumé que des données.
    Mon fichier est sauvegarder avec l’extension “xlsx”, j’ai essayé avec “xlsb”, il diminue à 110Mo, mais c’est pas suffisant.
    Avez-vous quelque chose à me proposer pour m’aider ?
    Merci par avance pour votre aide.
    Bonne journée
    Laurent

  21. Bonjour , perso j’ai un fichier bourré de macros , creation des onglets suivant le nb de jours au mois, insertions de 10230 en formules matricielles, et optimisé pour un poid de 287 Ko. meme le fichier complèté au max il ne fait que 1089 Ko, certe je n’ai que 30 lignes par 15 colonnes dans chaque onglets mais avec 5 colonnes avec formules, et 6 colonnes avec listes deroulantes…néamoins merci pour vos conseils…
    Puls

  22. bonjour, avez-vous des astuces particulières concernant le poids des images insérées dans un .xlsx ? j’ai le cas d’images de quelques centaines de Ko qui donnent un .xlsx de 5Mo quand on les y insère… je n’ai pas de pistes de recherche (.xlsx généré en automatique via script)
    d’avance, merci. Hervé (FRANCE)

  23. Hervé,

    Votre demande contient un grand nombre de défis!

    Premièrement, chaque image dans chaque feuille fait partie d’une collection de ”Shapes” dans cette feuille. Il est toutefois possible de boucler au sein des feuilles et des ”shapes”.

    Cela étant dit, il n’y a pas de façon directe d’appeler la compression d’images dans VBA pour Excel. À la place, une fois l’image sélectionnée, vous devez utiliser une série de commandes du type

    Sub CompressPict()
    Dim octl As CommandBarControl
    ‘ Vous sélectionnez toutes les images de la feuille active
    ActiveSheet.Pictures.Select

    With Selection
    Set octl = Application.CommandBars.FindControl(ID:=6382)
    Application.SendKeys “%e~”
    Application.SendKeys “%a~”
    octl.Execute
    End With

    End Sub

    Ce genre de commandes peut fonctionner dans une version d’Excel et pas dans une autre… Les commandes SendKeys sont souvent volatiles et ne donnent pas toujours les résultats attendus…

    Sur mon ordi, cela définit les images à la résolution par défaut, laquelle peut être modifiée dans les options avancées d’Excel.

    C’est le mieux que je peux vous donner…

    Francis

    En conclusion, il vous serait sans doute plus facile de limiter la taille des images en amont, avant la création des fichiers Excel…

    Cela ne vous aide sans doute pas beaucoup…

    Francis

  24. Bonjour,
    Pour ma part j’ai trouvé un très bon moyen d’alléger certains classeurs : j’exporte les onglets (un par un) dans d’autres fichiers puis je les réimporte.
    Ça n’est pas toujours efficace mais en général ça contribue bien à réduire les fichiers.

  25. Bonjour,

    Pour ma part, j’ai “découvert” (?) une astuce pour rendre rapide un fichier Excel trop lourd…

    Je m’explique: Afin d’aider des amis peu versés en bureautique, en profession libérale, qui listaient à la main leurs dépenses/recettes mensuelles sur de grandes feuilles, puis les totalisaient avec une calculatrice (!) je leur ai confectionné un classeur Excel reproduisant fidèlement cette feuille, en répétant bien sûr le travail sur les 12 mois de l’année, plus une 13° feuille récap.

    Très facile.

    Seulement voilà: le fichier vierge d’écritures affichait un poids de 22 Mo, et ne voulait pas s’ouvrir… ou tout du moins après une attente interminable, Excel plantant souvent.

    Bref, impraticable. Beaucoup de recherches sur internet, vaines, pour trouver une solution.

    Jusqu’à ce que, par intuition, j’ouvre le dit classeur avec Libre Office… et là, miracle !

    Libre Office m’envoie d’abord plusieurs message d’alerte, et me propose des modifications,et je clique OK partout. Le fichier s’ouvre, puis je le sauvegarde au format Excel…

    Résultat: le fichier d’origine est passé de 22 Mo à … 282 ko ! 🤗

    Rapide comme l’éclair, toutes les formules et mises en forme préservées…

    Très bonne journée

    NB: Je suppose que Libre Office, en ouvrant un fichier qui n’est pas à son format .odt, recherche et enlève d’éventuelles “scories”…
    Cela dit, 13 feuilles sans données dans un classeur à 22 Mo, il y avait visiblement un problème… d’autant que mes tableaux de bord (quand j’étais en activité…) représentaient, pour un classeur d’un trentaine de feuilles liées, plusieurs milliers de lignes, des formules partout, et graphiques compris, une taille de 2,6 Mo…
    J’ai donc utilisé LibreOffice par hasard, et par nécessité, puisque Excel ne voulait pas ouvrir ce gigantesque classeur de 22 Mo…
    Le résultat est épatant, la taille d’origine est diminuée d’un facteur 80….

  26. bonjour, j’ai mon fichier Excel qui explose et quand je lis vos reco, je pense que c’est du au fait que j’ai bcp de table Pivot et que je ne suis pas toujours repartie d’un copier/coller d’une table existante. Parfois je repars du fichier source. Ma question: comment puis je savori quelles tables pivot posent probleme? j’aimerais evviter ed devoir refaire tout mon fichier de zero.. 😉 Merci!

    1. Bonjour Sandrine,

      L’article que vous consultez date de 2013. Si vous utilisez une version récente d’Excel, vous ne devriez pas avoir de problèmes de caches. Mais pour savoir combien vous avez de caches, allez simplement dans l’éditeur de codes VBA (en cliquant sur ALT+F11) et insérez le code suivant ?ActiveWorkbook.PivotCaches.Count dans la fenêtre d’exécution (affichage / fenêtre d’exécution).

      Si vous avez plus d’une cache et que vous voulez améliorer la performance de votre fichier, vous pourriez aller dans les options du tableau croisé dynamique, dans la section données, décocher Enregistrer les données sources avec le fichier et cocher Actualiser les données lors de l’ouverture du fichier.

      Au plaisir,

      Sophie

  27. Waow +
    Merci pour le point n° 3 :
    Le Crl + End :
    ligne 1048576
    Je devrai etre à … 300
    Suppression de 1048276 lignes
    Le fichierr est passé de 11 Mo a 149Kb 🙂
    Grands Mercis !!!

  28. Comment dire ? Vous êtes une magicienne !!
    En suivant vos conseils point par point mon fichier est passé de 15 182 Ko à 1 715 Ko
    Merci Merci Merci !!

  29. Bonjour et merci pour vos conseils. Bien que votre mail date de 2013, il reste d’actualité en 2022…

    Grâce à vos conseils (le 3 en particulier), j’ai pu débloquer un utilisateur dont le fichier pesait de 270 Mo et est passé à 400Ko.

  30. Bonjour,
    Je créé aujourd’hui un fichier Excel pour la gestion de mes stocks en tachant d’automatiser au maximum mes calculs avec des mises en forme conditionnelles. Je ne pensais pas faire quelque chose de si lourd, mais j’ai jouté des cases a cocher avec des mises en formes conditionnelles si VRAI.
    Seulement, mon document n’étant pas encore tout à fait au point (je souhaiterais ajouter une mise en forme conditionnelles permettant d’ajouter la valeur liée à ma case à cocher dans mes valeurs totales tout en excluant les FAUX. ) Je continue de travailler dessus jusqu’a ce que toutes mes cases disparaissent ! comment puis-je prévenir ce problème ?
    Bien à vous,

  31. Bonjour,
    De mon côté, j’ai résolu le problème en faisant un copier de tout le contenu dans un nouveau fichier et tout s’est bien passé. J’ai réduit le poids de presque moitié.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut