Pourquoi un fichier Excel est lent et trop lourd ? Comment faire ?

Une succession de copier coller sur des cellules qui ont des mises en forme conditionnelle peut rapidement créer de trop nombreuses instructions qui ralentissent Excel

La solution est la suivante : supprimer les mises en forme conditionnelle sur la feuille.

Accueil / Mise en forme conditionnelle / Gérer les règles et Dans la liste déroulante, choisir : Cette feuille de calcul, Supprimer toutes les règles

Des formes ont été insérées dans la feuille de calcul par des copier/coller intempestif.

L’ensemble des formes doivent être supprimer pour qu’Excel retrouve une vitesse normale. Il faut lancer le code suivant sur la feuille de calcul

Sub SuppressionFormee()

Dim sh As Shape

Application.ScreenUpdating = False

For Each sh In ActiveSheet.Shapes

sh.Delete

Next sh

 Application.ScreenUpdating = true

End Sub

L’utilisation des formules matricielles peuvent ralentir Excel

Dans la mesure du possible les formules matricielles peuvent être remplacées par l’utilisation de formule de type SOMME.SI.ENS

Le code VBA peut être accélérer en insérant le code VBA en début de module.

Le code suivant permet d’insérer un commentaire dans la barre d’état (en bas à gauche de la fenêtre).

Cette programmation VBA permet d’arrêter le rafraichissement de l’écran, désactive tous les événements et annule le calcul automatique d’Excel.

    With Application
        .StatusBar = "Imporation et calculs"
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

Une fois que le code VBA est exécuté, il faut remettre en place l’ensemble des fonctionnalités désactivées, en plaçant le code suivant en fin de procédure.

With Application
        .StatusBar = ""
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
End With

Certaines formules, qui sont désignées comme volatiles, sont recalculées à chaque fois qu’une modification est apportée au classeur. Les formules concernées sont INDIRECT, MAINTENANT, AUJOURDHUI, DECALER, ALEA.ENTRE.BORNES.

Un nombre trop élevé de ces fonctions volatiles peut entrainer un ralentissement d’Excel car à chaque calcul, il y a une modification de toutes les cellules contenant ces formules ainsi que toutes les cellules dépendantes. Si Excel est lent, vérifiez l’impact des ces formules sont la performance de votre fichier.

Si les formules sont compliquées, il est possible de désactiver les calculs lors de la saisie ou de la modification du fichier. Aucun calcul ne sera réalisé lorsque vous aurez annulé le calcul automatique. Les cellules ne seront pas recalculées. Il faudra à la fin des modification remettre en service le calcul automatique.

Pour désactiver le calcul automatique :

Onglet Formules / Calcul / Options de calcul / Manuel

Pour réactiver le calcul automatique :

Onglet Formules / Calcul / Options de calcul / Automatique

Lors de la construction des formules, il est souvent plus rapide de faire référence à une ligne entière ou la colonne entière plutôt qu’aux seules cellules concernées par la sélection. Si cette pratique ne perturbe pas les performance de votre fichier, il ne faut pas s’en priver. Par contre si vous fichier est ralenti, il faut modifier vos formules pour n’utiliser que les cellules utiles.

Si le fichier contient de très nombreuses colonnes sur lesquelles un filtre est appliqué, le temps de traitement peut être considérablement augmenté.
Il faut donc appliquer ou retirer le filtre selon :

• saisie de données : retirer le filtre
• analyse : appliquer le filtre

Autrement via la commande classique d’Excel Données/Filtre, vous pouvez appliquer et retirer le filtre. Il est important de sélectionner le tableau complet quand le filtre est appliqué. Pour une sélection rapide du tableau un raccourci clavier permet de sélectionner automatiquement tout le tableau :

• • se position dans le tableau
  • puis cliquer en même temps sur CTRL et A

Il est possible via un jeu de macros d’appliquer et de retirer le filtre.