Pourquoi un fichier Excel est très lent ? Comment faire ?

Suppression des mises en forme conditionnelles

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

Suppression des formes

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

Remplacement des formules matricielles

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

Accélération du code VBA – des macros

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

Vérifier les fonctions volatiles

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.

Formules compliquées : désactiver le calcul

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

Evitez d’utiliser une colonne ou ligne entière comme référence

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.

Supprimer les filtres, si trop de colonnes filtrées

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 (retrouver la liste des raccourcis Excel)

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

La formation Excel est un moyen de régler les problème de lenteur de vos fichiers. Nous réalisions des formations sur mesure à partir de vos propres fichiers : en présentiel dans votre entreprise ou par VisioFormation.

Si vous avez solutionné vos lenteurs Excel via ces recommandations ou si vous souhaitez partagé d’autres solutions pour que d’autres utilisateurs en bénéficient, veuillez laisser un commentaire sur cet article ou partager le.