10 trucs indispensables pour optimiser un fichier Excel

Le 19 novembre 2020, je vous ai présenté un webinaire intitulé “10 trucs indispensables pour optimiser un fichier Excel“. En accompagnement de ce webinaire, je partage ici avec vous la liste de ces 10 trucs indispensables qui ont été vus et supportés par des exemples concrets pendant le webinaire. Je rappelle que pour être en mesure d’écouter l’enregistrement du webinaire en différé, vous devez être membre VIP du CFO masqué.

Mais d’abord, sachez qu’au mois de septembre 2020, Microsoft a procédé à de nombreuses améliorations au niveau de la performance d’Excel. Voici une vidéo qui résume l’essentiel de ces améliorations.

Document d’accompagnement

Voici le document qui accompagne ce webinaire.

Truc no.1 – Utiliser les bons outils

Plusieurs usagers d’Excel sont vite à blâmer la solution quand des problèmes surviennent alors que bien souvent, ce n’est pas le logiciel, le problème, mais plutôt l’environnement dans lequel il est utilisé (ou bien l’utilisateur lui-même 😉). Lorsque des problèmes de performance sont rencontrés, il est donc important de vérifier d’abord des éléments comme les suivants :

• Version Excel (par exemple, le même fichier enregistré sous Excel 2007 sera beaucoup plus volumineux que s’il est enregistré sous Excel 2010)
• Nombre de processeurs (plus de processeurs permettra un traitement plus rapide)
• Windows vs Mac (Excel devrait toujours être utilisé sous Windows… point final)
• 32 bits vs 64 bits (vous devriez toujours opter pour la version 64 bits lorsque possible)
• Un ordinateur avec une bonne capacité au niveau de la mémoire vive (RAM) et un SSD (Solid State Drive) sera toujours plus performant

Certaines entreprises utilisent encore une version 32 bits d’Excel car elles doivent utiliser d’anciens (voire très anciens) compléments (add-ins) qui ne peuvent être installés que dans des versions 32 bits. Je recommande fortement à ces entreprises d’envisager tout ce qui est possible pour retirer ces compléments ou les mettre à jour. Il est fort à parier que désormais, Power Query pourrait aisément remplacer ces compléments, permettant ainsi à l’entreprise de passer à la version 64 bits d’Office, en plus de bénéficier des nombreux avantages de Power Query au niveau performance.

Truc no.2 – Désactiver les fonctionnalités qui consomment le plus de mémoire vive

Au-delà de la quincaillerie, il y a plusieurs fonctionnalités qui consomment de la mémoire dans Excel et qu’il est possible de désactiver.

• Désactiver l’option de remplissage instantané
• Désactiver les options de mots de passe
• Désactiver les antivirus
• Désactiver tous les compléments (add-ins) ou ouvrir Excel en mode sans échec (safe mode)
• Utiliser les paramètres régionaux English USA

La page suivante est une page officielle de Microsoft, qui partage une liste de fonctionnalités à désactiver pour augmenter la performance d’un fichier Excel.

Truc no.3 – Réduire la taille du fichier au maximum

Un des facteurs qui affecte le plus la performance d’un fichier Excel est sans aucun doute sa taille. Selon les versions d’Excel, il existe de nombreuses façons de réduire la taille d’un fichier. À ce sujet, nous avions déjà partagé avec vous un article intitulé 8 trucs pour réduire la taille d’un fichier Excel.

Voici un résumé de cet article et quelques compléments d’information :

• Longueur des champs texte (réduire au maximum les caractères dans les noms de cellules, de plages et d’onglets ainsi que les formules faisant référence à plusieurs onglets)
• Dernière cellule d’un onglet (s’assurer que ce soit réellement la dernière cellule active)
• Références circulaires avec itération (réduire voire éliminer complètement)
• Quantité de formules complexes (réduire au maximum)
• Items excédentaires (s’assurer de tout effacer ce qui n’est pas utilisé)
• Formats de nombres et règles de mise en forme conditionnelles (réduire au maximum)
• Validations de données (réduire au maximum)

Lors de notre webinaire, nous verrons notamment :

• Comment s’assurer que la dernière cellule active soit réellement la dernière cellule utilisée
• Comment résoudre les références circulaires sans forcer le nombre d’itérations
• Comment se débarrasser des items excédentaires
• Comment nettoyer les mises en forme excédentaires
• Comment cibler et retirer, au besoin, les validations de données non nécessaires

Truc no.4 – Réduire ou optimiser les formules conditionnelles

Les formules conditionnelles comme les SOMME.SI.ENS et les NB.SI.ENS par exemple, ou encore les fonctions SI imbriquées, tellement appréciées des utilisateurs, peuvent avoir un impact considérable sur la performance d’un fichier puisqu’Excel doit réaliser de nombreux tests avant de pouvoir afficher une valeur et cela, dans chacune des cellules affectées.

Ce faisant, lorsque vous utilisez des fonctions conditionnelles dans Excel, il faut s’assurer de :

• Placer les conditions dans un ordre optimal, de façon à accélérer le temps requis par Excel pour effectuer les tests
• Remplacer les fonctions conditionnelles par d’autres fonctions lorsque possible

Lors de notre webinaire, nous verrons précisément comment décider de l’ordre optimal des tests logiques dans une formule conditionnelle et nous donnerons des exemples de formules conditionnelles pouvant être remplacées par d’autres formules utilisant des fonctions comme la fonction MAX ou les fonctions INDEX/EQUIV, par exemple.

Truc no.5 – Utiliser les fonctions volatiles avec parcimonie

Les fonctions volatiles peuvent entraîner des problèmes de performance car elles augmentent le nombre de formules qui doivent être recalculées à chaque actualisation du fichier. Utilisées en quantité restreinte, l’impact n’est pas perceptible. Mais utilisées dans de nombreuses cellules, l’impact peut être considérable.

Lors de notre webinaire, nous verrons notamment comment :

• Remplacer la fonction volatile DECALER par la fonction INDEX
• Remplacer la fonction INDIRECT par la fonction CHOISIR

Truc no.6 –  Utiliser les tableaux pour fournir des plages de données à analyser

Si vous ne connaissez pas encore l’utilité des données “mises sous forme de tableaux”, il vous manque une pièce essentielle pour travailler efficacement dans Excel. Pour une mise à jour sur ce que sont les tableaux et comment les utiliser, je vous invite à lire l’article : Découvrez la magie des tableaux Excel. Les tableaux vous permettront notamment de créer des formules basées sur des plages de données qui seront toujours efficaces (bon nombre de lignes et bon nombre de colonnes) et ajustées de façon dynamique.

Lors de notre webinaire, nous verrons comment utiliser une fonction de recherche basée sur des plages de données, présentées sous forme de tableaux.

Truc no.7  – Utiliser les fonctions de recherche adéquatement

Pour que vos fonctions de recherche soient performantes, il faut respecter certaines pratiques et concevoir les fichiers selon une structure optimale, tel qu’illustré dans notre article Excel : Utiliser les fonctions de recherche adéquatement. À noter qu’il existe maintenant la fonction RECHERCHEX, qui pourrait vous intéresser.

Lors de notre webinaire, nous repasserons ensemble les pratiques à respecter pour améliorer l’utilisation de vos fonctions de recherche.

Truc no.8 – Utiliser les caractères de remplacement pour alléger vos formules

Que ce soit pour vos fonctions de recherche (comme RECHERCHEV) ou vos fonctions conditionnelles (comme SOMME.SI.ENS), pensez à insérer des caractères de remplacement afin d’alléger la structure de vos fonctions. Par exemple :

• Voyez comment optimiser vos formules de recherche avec les caractères de remplacement
• Voyez comment optimiser vos formules conditionnelles avec les caractères de remplacement

Truc no.9 – Utiliser Power Query toutes les fois que c’est possible

Utiliser Power Query vous aidera à tous les niveaux, y compris du point de vue performance. Power Query permet d’importer les données d’autres fichiers, de les compresser et d’en garder une copie en cache, ce qui en réduit significativement la taille. Au passage, Power Query permet aussi de transformer les données.

Au final, utiliser Power Query permettra de régler les problèmes de performance en lien avec :

• La taille des fichiers
• Les fichiers liés
• Les transformations complexes / formules lourdes
• Le traitement de données dont la volumétrie dépasse les capacités d’une feuille Excel
• Et plus encore

Lors de ce webinaire, je vous présenterai un exemple d’une solution Excel avec et sans l’usage de Power Query et vous montrerai les impacts positifs d’utiliser Power Query. À mon avis, en 2020, TOUS les usagers d’Excel devraient utiliser Power Query.

Truc no.10 – Utiliser Power Pivot lorsque requis

Finalement, les utilisateurs de Power Query voudront sans doute également profiter de la puissance de Power Pivot, qui permettra notamment de régler les problèmes de performance liés à des volumétries importantes de données à analyser et des formules complexes entre les différentes sources de données (remplacer notamment des RECHERCHEV et des SOMME.SI.ENS), en plus de donner beaucoup plus de souplesse au niveau de l’information présentée dans les tableaux croisés dynamiques.

Lors de ce webinaire, nous examinerons comment Power Pivot peut optimiser l’usage d’un fichier Excel.

Écoutez notre webinaire en différé

Formation complémentaire

Afin d’approfondir vos connaissances avec les meilleures pratiques d’affaires dans la construction d’un fichier Excel, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1).

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.