8 façons de réduire la taille du fichier Excel (qui fonctionnent réellement)

Les classeurs Excel sont connus pour devenir douloureusement lents et lourds à mesure que davantage de données/formules/images y sont ajoutées.

Parfois, il est facile de se débarrasser de la graisse supplémentaire et de contrôler la taille du fichier Excel, et parfois, ce n'est pas le cas.

Et comme Excel n'est pas un outil Web, la plupart d'entre nous travaillons et collaborons en envoyant des fichiers Excel par e-mail. Et un gros fichier Excel pesant 50 Mo peut être difficile à partager avec vos collègues/clients.

Dans mon dernier travail, nous utilisions le client de messagerie Lotus Notes, qui est très lent, et envoyer des fichiers Excel lourds par-dessus était une torture.

Bien qu'il n'y ait pas de moyen magique de réduire la taille d'un classeur Excel, il existe quelques techniques que vous pouvez utiliser pour réduire la graisse.

Remarque : L'impact de ces techniques pour réduire la taille du fichier Excel dépendra de vos données. Pour certains, cela peut aider à réduire la taille de 50% et pour certains, cela peut finir par réduire la taille de 5%.

Conseils pour réduire la taille du fichier Excel

Vous trouverez ci-dessous les techniques que vous pouvez utiliser pour réduire la taille du fichier de votre classeur Excel.

Pour chaque astuce, j'ai fait des tests et j'ai ajouté des instantanés pour vous montrer l'impact sur la taille du fichier. Les résultats que vous obtiendrez varieront en fonction de vos données et de vos fichiers.

En fonction de votre classeur, vous pouvez utiliser un ou plusieurs de ces conseils.

Supprimer les feuilles de calcul/données inutiles

Les meilleures solutions sont souvent les plus simples.

Et cette technique relève davantage du bon sens et moins de la magie d'Excel.

Si vous avez un classeur Excel qui contient des feuilles de calcul qui ne sont pas nécessaires (et ne sont utilisées dans aucune formule), voyez si vous pouvez vous en débarrasser.

Cela a une corrélation directe avec la taille du fichier Excel. Ainsi, dès que vous supprimez certaines des feuilles de calcul et enregistrez le classeur, vous verrez instantanément une taille de fichier réduite.

Résultats de test:

J'ai créé un fichier Excel avec 10 feuilles de calcul où chaque feuille de calcul contient 2,6 millions de points de données (26 colonnes avec 100 000 points de données dans chaque colonne).

La taille résultante de ce classeur est de 143 Mo (et l'enregistrement et l'ouverture prennent un certain temps).

Maintenant, quand j'ai supprimé cinq feuilles, la taille a été réduite de moitié (duh).

Comme vous pouvez le voir, les données que vous avez dans votre feuille de calcul ont une corrélation directe avec la taille du fichier.

Convertir au format binaire (XLSB)

Savez-vous que le simple fait d'enregistrer un fichier Excel au format XLSB peut réduire la taille du fichier ?

Oui c'est vrai.

Tout ce que vous avez à faire est d'utiliser le format .xlsb lors de l'enregistrement d'un classeur.

Voici les étapes à suivre :

1. Cliquez sur l'onglet Fichier.
2. Cliquez sur Enregistrer sous.
3. Cliquez sur Parcourir.
4. Dans la boîte de dialogue Enregistrer sous, remplacez le type de fichier par Classeur binaire Excel (.xlsb)

Résultats de test

J'avais un classeur Excel qui prenait 14,6 Mo d'espace.

Après avoir converti le même fichier au format XLSB, j'ai pu réduire la taille à 10,4 Mo (une réduction de taille d'environ 28 %).

Qu'est-ce que XLSB ?

Lorsque vous enregistrez un fichier Excel (.xlsx ou .xlsm), il est enregistré au format XML.

Par exemple, si je change l'extension de fichier du fichier XLSX en ZIP puis l'ouvre, voici ce que j'obtiens.

Et si je vais plus loin dans le dossier « xl », puis dans le dossier des feuilles de calcul, cela me montre les feuilles de calcul de ce classeur (et ces feuilles de calcul sont enregistrées en tant que document XML).

Faisons maintenant la même chose avec un fichier XLSB.

Je change donc d'abord l'extension de fichier en ZIP, puis je l'ouvre et localise la feuille1. Voici ce que vous obtenez :

Vous remarquerez que XLSX et XLSB enregistrent la feuille de calcul dans un format différent.

XLSX/XLSM l'enregistre en tant que fichier XML et XLSB l'enregistre en tant que fichier binaire (.bin).

Et sans devenir trop technique, les fichiers XML sont volumineux par rapport à un fichier binaire.

Ainsi, lorsque vous enregistrez votre classeur Excel au format XLSB, la taille du fichier est réduite. Dans notre exemple ci-dessus, la réduction de taille était d'environ 30 %.

XLSB contre XLSX/XLSM

Lorsqu'il s'agit d'utiliser le format XLSB, la réduction de la taille est un énorme avantage. Mais vous devez être un peu prudent lorsque vous travaillez avec le format XLSB.

Avantages du format XLSB :

1. Il réduit la taille du fichier Excel. Cela peut être utile lorsque vous avez des fichiers volumineux et que vous souhaitez les partager par e-mail. Vous pouvez rapidement réduire la taille en l'enregistrant au format XLSB.
2. Les fichiers XLSB ont tendance à s'ouvrir et à s'enregistrer plus rapidement que les fichiers XML. Sur la base des tests limités que j'ai effectués, les fichiers XLSB s'ouvraient et économisaient 25 à 50 % plus rapidement. Cette différence n'est perceptible que dans les fichiers Excel volumineux. Avec des fichiers plus petits, XLSB et XLSX sont rapides.

Bien que XLSB ait fière allure, il y a quelques raisons pour lesquelles vous devriez vous en tenir aux fichiers XLSX/XLSM :

1. Avec le format XLSB, vous n'avez aucun moyen de savoir s'il a une macro ou non. Cela le rend plus risqué car quelqu'un peut l'utiliser pour exécuter du code malveillant.
2. Les fichiers XLSB sont codés au format de fichier bin propriétaire tandis que XML est un format de fichier lisible à source ouverte. Cela signifie que si vous utilisez des outils tiers avec Excel, il est préférable d'utiliser le format XML.
3. Power Query ne peut pas lire les données d'un fichier XLSB. Donc, si vous utilisez Power Query, il est préférable d'avoir vos données aux formats XLSX/XLSM.

En règle générale, si la taille de votre fichier est petite (moins de 5 Mo), il vaut mieux s'en tenir aux formats XLSX/XLSM.

D'après ce que j'ai entendu des gens et lu sur de nombreux forums, beaucoup de gens préfèrent utiliser XLSB comme format de fichier lorsqu'il s'agit d'utiliser Excel.

Voici un bon article pour comprendre le format XLSB.

Supprimer les formules inutiles

Trop de formules augmenteront la taille de votre fichier de classeur.

Donc, si vous avez un ensemble de données dont vous avez besoin, mais que vous n'avez pas besoin des formules qu'il contient, il est préférable de convertir ces formules en valeurs.

Voici les étapes pour convertir rapidement des formules en valeurs dans Excel :

1. Sélectionnez l'ensemble de la feuille de calcul/de l'ensemble de données.
2. appuie sur le F5 clé.
3. Dans la boîte de dialogue Aller à, cliquez sur "Spécial".
4. Dans la boîte de dialogue Aller à spécial, sélectionnez Formules.
5. Cliquez sur OK.

Les étapes ci-dessus sélectionneraient toutes les cellules contenant une formule.

Utilisez maintenant les étapes ci-dessous pour convertir ces formules en valeurs :

1. Copier les cellules sélectionnées (contrôle + C)
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Presse-papiers, cliquez sur Coller.
4. Cliquez sur l'icône Coller la valeur.

Les étapes ci-dessus convertiraient toutes les cellules avec des formules en valeurs.

Résultats de test

J'ai créé un fichier Excel avec 2,7 millions de points de données (1048576*26), puis j'ai ajouté trois colonnes avec des formules (SUM, COUNT et AVERAGE).

Ensuite, j'ai converti ces formules en valeurs et enregistré le fichier sous un nom différent.

Ci-dessous le résultat :

Le fichier sans formules est d'environ 8% de moins (et notez que je n'avais que 3 colonnes de formules).

Si vous avez beaucoup de formules, cela peut augmenter la taille.

REMARQUE: Les formules volatiles peuvent vraiment gonfler la taille de votre fichier. Dans le cadre du test, lorsque j'ai remplacé la formule SUM par une formule volatile (OFFSET), cela a conduit à un saut dans la taille du fichier (de 186 Mo à 198 Mo). Donc, si vous avez des formules volatiles que vous pouvez remplacer par des formules non volatiles, ou que vous pouvez les convertir en valeurs, cela vous aidera à réduire la taille du fichier.

Compresser les images (avant et après le téléchargement)

Si vous travaillez avec des fichiers Excel contenant des images, vous devez optimiser ces images et vous assurer que leur taille est inférieure.

Le problème avec les images est que lorsque vous les compressez, il y a également une réduction de la qualité, ce que vous ne souhaitez peut-être pas.

Lorsque vous travaillez avec des images, le meilleur moyen de réduire la taille de votre fichier Excel consiste à compresser les images avant de les insérer dans Excel.

Un bon outil en ligne pour le faire est TinyPNG (ou vous pouvez utiliser Photoshop ou tout autre outil similaire).

C'est un outil gratuit qui peut réduire la taille d'une image jusqu'à 90 % tout en conservant une qualité d'image décente.

Vous trouverez ci-dessous la capture d'écran où j'ai pu compresser une image d'une taille de 3,9 Mo à 331 Ko avec une perte de qualité minimale.

Une fois que vous avez téléchargé ces images compressées dans Excel, vous pouvez les compresser davantage à l'aide de l'option intégrée dans Excel.

Voici les étapes pour compresser une image dans Excel :

1. Sélectionnez l'image que vous souhaitez compresser.
2. Cliquez sur l'onglet Format des outils d'image. Il s'agit d'un onglet contextuel qui n'apparaît que lorsque vous cliquez sur une image.
3. Dans le groupe Ajuster, cliquez sur l'option « Compresser l'image ».
4. Dans la boîte de dialogue Compresser les images :
   • Désélectionnez « Appliquer uniquement à cette image » si vous souhaitez compresser toutes les images du classeur.
   • Sélectionnez E-mail (96 ppp) pour une compression maximale.
5. Cliquez sur OK.
6. Enregistrez le fichier.

Cela compressera toutes les images et vous aidera à réduire la taille du fichier de votre classeur Excel.

Résultats de test:

J'ai utilisé l'option compresser l'image dans Excel en utilisant une image.

Ci-dessous les résultats :

En utilisant l'option de compression d'image, la taille du fichier a été réduite de 445 Ko à 43 Ko (environ 90 % de réduction de la taille du fichier).

Cela dépendra aussi de l'image. Une image déjà compressée peut ne pas entraîner une énorme réduction de taille.

Supprimer le cache de pivot pour économiser de l'espace

Si vous travaillez avec des tableaux croisés dynamiques et que vous avez du mal avec des fichiers Excel volumineux, il peut être utile de connaître le cache Pivot.

Lorsque vous créez un tableau croisé dynamique à l'aide d'un ensemble de données, Excel crée automatiquement le cache croisé dynamique.

Le cache de pivot est un objet qui contient un réplica de la source de données.

Bien que vous ne puissiez pas le voir, il fait partie du classeur et est connecté au tableau croisé dynamique. Lorsque vous apportez des modifications au tableau croisé dynamique, il n'utilise pas la source de données, mais plutôt le cache croisé dynamique.

Alors que vous pensez être directement lié aux données sources, en réalité, vous accédez au cache pivot (et non aux données sources) lorsque vous effectuez des modifications dans le tableau croisé dynamique.

Maintenant que vous créez une réplique de l'ensemble de données, cela prend de l'espace.

Ainsi dès que vous créez un tableau croisé dynamique, vous remarquerez que la taille du fichier Excel augmente.

Dans l'exemple ci-dessus, dès que j'ajoute le tableau croisé dynamique, la taille du fichier augmente de deux fois.

Maintenant, il existe deux façons de réduire la taille du fichier lorsque vous travaillez avec des tableaux croisés dynamiques.

1. Conserver les données sources et supprimer le cache de pivot
2. Conserver le cache de pivot et supprimer les données sources

Regardons ces deux techniques.

Conserver les données sources et supprimer le cache de pivot

Lorsque vous avez terminé avec le tableau croisé dynamique et que vous l'enregistrez, vous pouvez forcer Excel à enregistrer uniquement les données source et le tableau croisé dynamique résultant, et non le cache croisé dynamique.

Cela rendra votre fichier Excel plus léger et vous aurez un moyen de recréer le cache Pivot lorsque vous en aurez besoin la prochaine fois.

Vous trouverez ci-dessous la différence de taille lorsque vous enregistrez un fichier Excel avec et sans Pivot Cache.

Voici les étapes pour NE PAS enregistrez le cache de pivot :

1. Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
2. Cliquez sur l'onglet Analyser.
3. Cliquez sur l'icône « Options » du tableau croisé dynamique.
4. Dans la boîte de dialogue Options du tableau croisé dynamique, cliquez sur l'onglet Données.
5. Dans les options Données du tableau croisé dynamique, décocher « Enregistrer les données source avec le fichier ». Cette option garantit que le cache de pivot n'est pas enregistré lorsque vous enregistrez le classeur (et aide à réduire la taille du fichier Excel).
6. Cochez l'option - « Actualiser les données lors de l'ouverture du fichier ». Cette option garantit que dès que vous ouvrez le classeur, le tableau croisé dynamique est actualisé et le cache croisé dynamique est généré automatiquement. Si vous ne cochez pas cette option, vous devrez actualiser le tableau croisé dynamique manuellement pour générer le cache croisé dynamique.
7. Cliquez sur OK.

Maintenant, lorsque vous enregistrez le fichier avec le tableau croisé dynamique, vous remarquerez une taille de fichier réduite.

Conserver le cache de pivot et supprimer les données sources

Une autre façon de réduire la taille du fichier Excel consiste à supprimer les données source d'un tableau croisé dynamique et à conserver uniquement le cache croisé dynamique.

Cela fonctionne comme un cache croisé dynamique est une image miroir de vos données et vous n'accédez pas aux données source lorsque vous apportez des modifications au tableau croisé dynamique, à la place, vous utilisez le cache croisé dynamique.

Vous trouverez ci-dessous les résultats lorsque j'ai enregistré le fichier Excel sans les données source.

Pour ce faire, supprimez simplement les données (soit en supprimant les données, soit la feuille de calcul contenant les données).

Lorsque vous souhaitez récupérer les données source, double-cliquez sur la cellule "Grand total" du tableau croisé dynamique et il générera instantanément l'ensemble des données dans une nouvelle feuille de calcul.

Compresser le fichier (ZIP IT)

C'est quelque chose que vous pouvez faire en dehors de l'application Excel.

Lorsque vous compressez un fichier Excel, vous constaterez immédiatement une réduction de taille d'environ 10 à 15 %.

Vous pouvez ensuite partager ce fichier compressé par e-mail, et la personne qui le reçoit peut décompresser le fichier et utiliser le classeur Excel.

Voici les étapes pour compresser un fichier Excel (pour Windows) :

1. Faites un clic droit sur le fichier Excel
2. Passez votre curseur sur l'option Envoyer à.
3. Cliquez sur l'option "Dossier compressé (zippé)".

Cela créera un fichier compressé pour le classeur Excel sélectionné.

Vous remarquerez également une réduction de la taille du fichier (comme indiqué ci-dessous).

Résultats de test

Avec cette technique, j'ai pu réduire la taille du fichier du classeur Excel de 8%.

Cette technique fonctionne mieux pour les fichiers compris entre 20 et 50 Mo. À mesure que la taille du fichier augmente, le pourcentage de réduction n'est pas très élevé.

Supprimer le formatage des données

La mise en forme des données, telle que l'application d'une couleur d'arrière-plan, l'ajout de bordures ou la modification du style de police, peut augmenter la taille du fichier.

Bien que, sur la base de mes tests, j'ai remarqué que cela n'a pas un impact énorme.

Vous trouverez ci-dessous les tailles de deux classeurs Excel, l'un avec 2,6 millions de points de données (dans une feuille de calcul) et l'autre avec les mêmes données mais la mise en forme qui lui est appliquée (couleur de la cellule, bordure et en italique).

Résultats de test

Vous pouvez voir que la différence entre les deux fichiers n'est pas si énorme (bien que cela ait un impact lorsque vous avez beaucoup de données).

Le fichier avec les données fait 14,6 Mo et le fichier avec les données et le formatage fait 14,9 Mo.

Donc, si vous souhaitez réduire la taille du fichier de votre classeur Excel, vous pouvez rechercher un formatage dont vous n'avez pas besoin. Le supprimer peut vous faire économiser de l'espace disque.

Voici les étapes pour supprimer instantanément tout le formatage, tout en conservant les données intactes :

1. Sélectionnez l'ensemble de données complet (dont vous souhaitez supprimer la mise en forme).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Édition, cliquez sur « Effacer ».
4. Cliquez sur « Effacer les formats ».
5. Enregistrez le classeur.

Supprimer la mise en forme conditionnelle

Tout comme le formatage normal des données, le formatage conditionnel augmente également la taille du fichier.

Cependant, sur la base de mes tests, j'ai remarqué que la différence est moindre (sauf si vous avez plusieurs règles appliquées à plusieurs feuilles avec d'énormes ensembles de données).

Néanmoins, c'est une bonne idée de supprimer la mise en forme conditionnelle de tout ensemble de données où vous n'en avez pas besoin.

Outre la taille, il existe une autre bonne raison de supprimer la mise en forme conditionnelle : la VITESSE.

La mise en forme conditionnelle est volatile et chaque fois qu'il y a un changement dans la feuille de calcul, elle recalcule. Cela peut conduire à un classeur plus lent.

Résultats de test

J'ai créé un fichier avec 27,2 millions de points de données et lui ai appliqué 5 règles de mise en forme conditionnelle.

La différence de taille semble n'être que de quelques Ko.

Lire aussi: Conseils pour créer rapidement des feuilles de calcul Excel lentes

Astuces pour booster les performances d'Excel

Bien que les méthodes ci-dessus vous aident à réduire la taille du fichier Excel, voici quelques conseils supplémentaires pour améliorer les performances des classeurs Excel :

1. Évitez d'utiliser des formules volatiles. En plus d'augmenter la taille du fichier, les formules volatiles ralentissent également vos classeurs. Des exemples de fonctions volatiles incluent RAND, AUJOURD'HUI, OFFSET, INDIRECT, etc.
2. Si vous avez besoin de créer des résumés d'un grand ensemble de données, au lieu d'utiliser des formules, voyez si vous pouvez utiliser un tableau croisé dynamique à la place. Un tableau croisé dynamique est rapide et très intuitif par rapport aux formules.
3. Si vous extrayez des données de plusieurs sources ou combinez des données de plusieurs fichiers, utilisez Power Query pour obtenir et transformer les données. Vous pouvez enregistrer les données en tant que connexion Power Query au lieu de les conserver dans la feuille de calcul. Vous pouvez facilement créer un tableau croisé dynamique à l'aide des connexions créées à l'aide de Power Query.
4. Convertissez vos données tabulaires en un tableau Excel et utilisez le tableau Excel dans les formules. Cela accélère les performances de votre classeur et les formules créées à l'aide de références structurées de tableau Excel sont plus faciles à gérer (car elles sont plus intuitives).
5. Évitez de faire référence à une ligne ou une colonne entière dans une formule. Cela aidera vos formules à être plus efficaces.
6. Si vous avez beaucoup de formules dans votre classeur, désactivez le calcul automatique et passez au calcul manuel. En faisant cela, chaque fois qu'il y a un changement dans les feuilles de calcul, les formules ne seraient pas recalculées. Vous pouvez effectuer cette modification en accédant à l'onglet Formule et en modifiant les « Options de calcul ».

J'espère que vous trouverez la méthode et les conseils de ce tutoriel utiles.

Que pourrais-je ajouter à ce tutoriel pour le rendre plus utile ? S'il vous plaît laissez-moi savoir dans la section des commentaires.