Contact: 514-605-7112 / info@lecfomasque.com

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

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.

.

Ciseaux

.

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 allourdir votre fichier inutilement. Pensez plutôt à utiliser des abbré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…

.

Cliquez sur l’image pour l’agrandir

Formule longue

.

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 comme PowerPivot (add-in Excel pour construire des cubes de données).

.

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.

.

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!

.

8. Format XLSB

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

.

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

.


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • répondre Pascal PERRIN ,

    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 !

    • répondre Serge Turgeon ,

      Bonjour Mme Marchand,
      Au point 3 lorsque vous dites <> comment fait-on? Est-ce simplement en les supprimant?
      Merci
      Serge Turgeon

      • répondre moncherwatson ,

        Bonjour Serge,

        C’est bien exact. Il s’agit de les supprimer.

        Au plaisir,

        Sophie

        • répondre Charles ,

          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

          • répondre moncherwatson ,

            Bonjour Charles,

            Je ne suis pas certaine de bien saisir votre question. Pourriez-vous être plus précis?

            Merci,

            Sophie

      • répondre lily ,

        très intéressant merci

        • répondre Joanne Bazinet ,

          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

          • répondre Jenjacks KANGA ,

            Merci pour ces infos bien utiles.

            • répondre David & Pascale ,

              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 ;)

              • répondre moncherwatson ,

                Wow, génial!

                Très contente d’avoir pu vous diriger dans la bonne direction!

                Au plaisir,

                Sophie

              • répondre pouetre ,

                Lorsque il y a plusieurs milliers de lignes à supprimer, comment faire pour faire la manip en seule fois?

              • répondre NOEL Frédérique ,

                comment faire ctrl+end ? end je clik sur les trois lettre ou il y a une touche pour faire cela?

                • répondre moncherwatson ,

                  Bonjour Frédérique,

                  Il y a une touche sur votre clavier sur laquelle il devrait être inscrit « end ».

                  Merci,

                  Sophie

                • répondre NOEL Frédérique ,

                  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…

                  • répondre moncherwatson ,

                    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

                  • répondre Seb ,

                    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

                    Écrire un commentaire


                    • *