Le classeur Excel qui met 45 secondes à calculer : autopsie d’un carnage

Bonjour à tous,

Vous pouvez lire tout le texte ci-dessous (partiellement générée par une IA), ou alors passer votre fichier - sans données confidentielles - dans une IA pour en faire une analyse en profondeur. En ce qui concerne la vitesse, je recommandes aussi l'outil absolument incontournable qu'est FastExcel. Évidemment, la lecture ci-dessous vous donnera pleins de clefs de compréhension de l'analyse.

Il y a des fichiers Excel qui donnent le ton dès l’ouverture.

Vous double-cliquez.
Excel prend son temps.
La barre d’état affiche un calcul interminable.
Et là, quelqu’un vous dit très calmement :

“Oui, il est un peu lent, mais il marche.”

Non.

Un fichier qui met 45 secondes à recalculer n’est pas “un peu lent”.

C’est un fichier qui a accumulé, au fil du temps, suffisamment de complexité pour devenir difficile à utiliser, à maintenir et à faire évoluer.

Et depuis, tout le monde a fini par s’y habituer.

Et bien sûr, moi le premier, je participe parfois à la création de ce genre de fichiers, car certaines situations exigent un équilibre entre la création d'une dette technique et la finalisation d'un fichier fonctionnel, reflétant avec haute fidélité une situation industrielle et surtout qui va apporter beaucoup aux utilisateurs.

Alors aujourd’hui, je vous propose une petite autopsie.

Le genre de fichier qu’on récupère parce que plus personne ne veut vraiment y toucher, mais que tout le monde continue d’utiliser parce qu’il produit “les chiffres”.

D’ailleurs, si vous êtes déjà confronté à ce type de situation, vous pouvez relire cet article : Quatre conseils pour des fichiers Excel très lents.

Le symptôme

Le diagnostic de départ est simple :

ouverture lente
recalcul interminable
filtres qui manquent de réactivité
copier-coller pénible
enregistrement trop long
parfois même un gel temporaire de l’application

Et quand on demande ce qu’il y a dans le fichier, on entend souvent les mêmes phrases :

“Il a été enrichi au fil du temps”
“Plusieurs personnes ont travaillé dessus”
“On a ajouté quelques automatisations”
“Avant, il allait plus vite”
“On ne veut surtout rien casser”

Traduction :

personne ne sait vraiment comment ce fichier tient encore debout.

Si vous récupérez un classeur de ce type, une bonne entrée en matière consiste aussi à relire : 7 règles d’or pour appréhender un fichier Excel complexe.

Le profil du suspect

Le fichier en question, en général, a ce genre de pedigree :

18 onglets
250 000 formules
des mises en forme conditionnelles recopiées très loin “au cas où”
des RECHERCHEV partout
des formules volatiles disséminées un peu partout
des SI imbriqués difficiles à relire
des tableaux qui n’en sont pas vraiment
des colonnes entières référencées sans nécessité
des copier-coller de morceaux historiques jamais nettoyés
parfois une macro censée “optimiser” le tout

Et bien sûr, avec des onglets aux noms très rassurants comme :

Feuil1
Base finale
Base finale V2
Base finale V2 bis
Synthèse OK
Synthèse OK 2
Archive ancienne pas toucher

Rien qu’avec ça, on comprend qu’on n’est pas face à un simple classeur.

On est face à un système devenu difficile à piloter.

Première cause : les formules partout, sans réflexion

Le premier point qu’on observe dans ces fichiers, c’est l’accumulation.

Une formule fonctionne.
Alors on la recopie.
Puis on la recopie encore.
Puis on l’étend à toute la colonne.
Puis à tout le tableau.
Puis à d’autres onglets.
Puis on ajoute une autre couche par-dessus.

Et un jour, sans que personne ne sache vraiment quand, le fichier passe de “pratique” à “lourd à maintenir”.

Le vrai problème, ce n’est pas qu’il y ait beaucoup de formules.

Le vrai problème, c’est qu’il y a beaucoup trop de formules qui recalculent des choses inutilement.

On retrouve alors très souvent :

le même calcul répété plusieurs fois dans le même onglet
des recherches refaites encore et encore au lieu d’être mutualisées
des SI imbriqués très longs pour éviter de structurer la logique
des tests inutiles copiés à l’infini
des références à des colonnes entières quand une référence dynamique (voir SUPPR.PLAGE) suffirait.

Et là, forcément, Excel souffre.

Pas parce qu’il est mauvais.

Parce qu’on lui demande de refaire trop de travail.

Pour corriger ça, il y a plusieurs pistes :

remplacer certains anciens réflexes par RECHERCHEX
utiliser de vrais objets tableau avec Les tableaux structurés dans Excel
et, quand une formule refait plusieurs fois le même calcul, regarder des approches mieux structurées comme dans Dépivotez un tableau avec une seule formule (Office 365)

Deuxième cause : les fonctions volatiles, ce petit enfer discret

Alors là, on entre dans une catégorie que beaucoup sous-estiment.

Certaines fonctions recalculent beaucoup plus souvent et beaucoup plus largement que ce que les utilisateurs imaginent.

Le problème, ce n’est pas seulement leur existence.

Le problème, c’est leur multiplication.

Une fonction volatile de temps en temps, ce n’est pas forcément dramatique, encore que.

Deux cents, quatre cents, huit cents, combinées à d’autres calculs lourds, là, cela commence à coûter cher.

Et comme souvent, on les retrouve dans des fichiers qui ont grandi sans vraie gouvernance.

Au départ, quelqu’un met un petit calcul “temporaire”.
Puis un autre.
Puis encore un.
Et quelques mois plus tard, tout le classeur devient sensible au moindre changement.

Troisième cause : la mise en forme conditionnelle en mode surcharge

La mise en forme conditionnelle est très utile.

Entre de bonnes mains.

Mais mal gérée, elle peut devenir une vraie source de lenteur.

Dans les classeurs lents, on retrouve souvent :

des dizaines de règles empilées
des règles dupliquées
des couches de surlignage qui se superposent inutilement

Et beaucoup de gens pensent encore que “ce n’est que de la mise en forme”.

Non.

Quand elle est mal gérée, ce n’est pas “que de la mise en forme”.

C’est une partie du problème.

Vous pouvez d’ailleurs renvoyer ici vers Quatre conseils pour des fichiers Excel très lents ainsi que vers Les tableaux structurés dans Excel.

Quatrième cause : les références mal dimensionnées

Quand je vois une formule qui travaille sur une colonne entière alors qu’il n’y a que 15 000 lignes de données, je sais déjà qu’on a privilégié la facilité au détriment de l’efficacité.

Oui, c’est pratique.
Oui, cela évite de réfléchir à la taille des plages.
Oui, cela “marche”.

Mais dans un gros fichier, ce confort finit par coûter cher.

Même logique avec :

les plages mal bornées
les tableaux bricolés à la main
les zones fantômes
les objets qui croient encore qu’il y a des données très loin en bas de la feuille

Et évidemment, personne ne nettoie cela.

Parce que cela ne se voit pas.

Jusqu’au jour où le fichier devient lourd.

Ici, le meilleur renvoi interne est probablement Les tableaux structurés dans Excel. Vous pouvez aussi figer la référence à une colonne d’un tableau pour encourager des références structurées plus propres.

Cinquième cause : les données sales traitées comme si elles étaient propres

Très grand classique.

Le fichier met 45 secondes à calculer non pas parce qu’il est “complexe”, mais parce qu’il compense en permanence une base bancale.

Par exemple :

dates en texte
nombres en texte
espaces invisibles
libellés incohérents
doublons
cellules fusionnées
trous dans la structure
colonnes fourre-tout

Du coup, au lieu de travailler sur une base propre, le classeur passe son temps à corriger le désordre.

Et comme cette correction est faite avec des couches de formules ajoutées au fil des années, on se retrouve avec un système où Excel passe plus de temps à corriger la matière première qu’à produire le résultat.

Là, vous pouvez proposer plusieurs portes de sorties :

Sixième cause : la logique métier dissoute dans les formules

C’est probablement l’erreur la plus coûteuse à long terme.

La logique métier ne devrait pas être éparpillée en petits morceaux dans 14 colonnes cachées, trois onglets techniques et une formule de 1 200 caractères dont personne n’ose modifier une parenthèse.

Et pourtant, c’est exactement ce qu’on voit.

On n’a pas construit un modèle.

On a empilé des réactions.

Résultat :

impossible de relire
impossible d’auditer
impossible d’optimiser proprement
impossible de savoir ce qui sert encore

Un classeur lent est souvent un classeur où la logique n’a jamais été pensée comme un système.

Elle a juste été ajoutée couche après couche.

Septième cause : la macro “sauveuse” qui ne sauve pas grand-chose

On me dit souvent :

“Mais il y a une macro d’optimisation.”

Très bien.

On ouvre l’éditeur.
On regarde.
Et on trouve généralement l’un des cas suivants :

Cas n°1

Une macro qui désactive l’écran, passe le calcul en manuel, fait quelques opérations, puis remet tout à la fin.

Ce n’est pas une optimisation.

C’est souvent un simple contournement.

Cas n°2

Une macro qui parcourt cellule par cellule ce qui aurait dû être traité autrement.

Là, on n’a pas amélioré le problème.

On a ajouté une couche supplémentaire.

Cas n°3

Une macro historique que plus personne ne comprend, mais que tout le monde continue d’exécuter.

C’est précisément le genre de situation qui mérite un audit.

Si vous voulez proposer un exemple concret de “fausse bonne idée” côté VBA, le lien parfait ici est : Pourquoi est-ce si lent de protéger une feuille avec Excel VBA ?.

L’autopsie sérieuse : comment je démonte le problème

Quand je récupère ce genre de fichier, je ne commence jamais par “optimiser”.

Je commence par observer.

Parce qu’un classeur lent, ce n’est pas un problème unique.
C’est un ensemble de problèmes qui se nourrissent les uns les autres.

Ma démarche ressemble généralement à ça.

1) Identifier où le temps part vraiment

Pas “où on croit”.
Où il part réellement.

ouverture
recalcul
filtres
TCD
macro
actualisation
enregistrement

On veut localiser la douleur.

L'outil le plus utile que j'ai jamais trouvé est celui-ci : https://www.decisionmodels.com/FastExcelV4.htm

2) Repérer les onglets suspects

Très vite, il y a des zones qui méritent l’attention. FastExcel peut en trouver certains, mais vous pouvez aussi chercher ces indices:

onglets techniques très volumineux
zones de formules massives
règles de MFC trop nombreuses
feuilles avec colonnes cachées en grand nombre
tableaux bricolés qui ont perdu leur structure

3) Vérifier la structure des données

Si la base est sale, l’optimisation en bout de chaîne est souvent une perte de temps.

4) Regarder les répétitions absurdes

Quand le même calcul est refait partout, il y a du travail à rationaliser.

5) Séparer le structurel du cosmétique

Parfois, 80 % de la lenteur vient d’un petit nombre de choix techniques très coûteux.

Et parfois, il faut accepter une vérité désagréable :

le fichier n’est pas à optimiser, il est à repenser.

Ce qu’on fait concrètement pour récupérer des secondes

Alors non, il n’y a pas de bouton magique “réparer le fichier compliqué”.

Mais il y a des gains très réels quand on arrête les choix les plus coûteux.

Typiquement, les gros leviers sont :

Réduire les calculs inutiles

Un calcul utile, très bien.
Le même calcul répété partout, non.

Stopper les références excessives

Travailler sur ce qui existe vraiment.
Pas sur un million de lignes théoriques.

Nettoyer la mise en forme conditionnelle

Souvent, il y a déjà là un gisement de performances.

Repenser la logique

Au lieu d’empiler, structurer.

Déporter certaines étapes

Tout ce qui n’a pas à être recalculé en permanence ne doit pas forcément rester sous forme de formules vivantes.

Nettoyer les déchets

Objets inutiles, formats fantômes, onglets obsolètes, duplications inutiles.

Et surtout :

arrêter de confondre “on a toujours fait comme ça” avec “c’est une bonne idée”

Le moment où il faut être honnête

Il y a un moment où il faut arrêter le bricolage.

Certains classeurs ne sont pas lents parce qu’ils sont “gros”.

Ils sont lents parce qu’ils sont mal conçus.

Et dans ces cas-là, on peut toujours gagner un peu de temps avec quelques ajustements.

Mais si l’architecture est mauvaise, on ne transforme pas un système fragile en système robuste avec quelques retouches de surface.

Il faut parfois revoir une partie du raisonnement.

C’est moins spectaculaire que de lancer une “macro d’optimisation ultime”.
Mais c’est généralement beaucoup plus efficace.

Ce que ce type de fichier raconte vraiment

Un classeur à 45 secondes de recalcul raconte presque toujours la même histoire :

personne n’a pensé à la totalité de la structure dès le départ - parfois et même souvent, c'est d'ailleurs impossible
tout le monde a ajouté sa couche
personne n’a nettoyé la dette technique
on a privilégié le “ça marche aujourd’hui” au “ça tiendra demain”
on a laissé la complexité s’installer tranquillement
l'ajout de données au fur et à mesure a ralenti le système

Et un jour, tout le monde trouve normal d’attendre 45 secondes entre deux actions.

C’est ça, le vrai problème.

Le problème n’est pas seulement technique.

Le problème, c’est l’habituation à l’inefficacité.

Ma conclusion

Si votre classeur met 45 secondes à recalculer, ne cherchez pas d’abord l’astuce miracle.

Cherchez le mécanisme qui a conduit à cette situation.

Parce qu’un fichier lent n’est presque jamais victime d’un seul problème.
C’est un empilement de mauvaises décisions devenues invisibles à force d’habitude.

Donc avant de demander :

“Comment accélérer ce fichier ?”

posez plutôt la vraie question :

“Qu’est-ce qu’on a laissé se dégrader pendant des mois pour en arriver là ?”

Et là, en général, l’autopsie devient tout de suite beaucoup plus intéressante.