Comment trouver et contrôler toutes les formules d'une feuille de calcul Excel
Aujourd’hui, nous nous retrouvons pour un mini-tutoriel dans lequel je vais vous montrer comment afficher rapidement toutes les formules insérées dans une feuille de calcul pour les visionner sans avoir besoin de les afficher ou encore pour les imprimer.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
Partie 1 :
Partie 2 :
Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !
1. Présentation
Par défaut, les différentes cellules d’une feuille de calcul sont affichées pour permettre de visionner le résultat de celles-ci, en tenant compte des paramètres de mise en forme de cellules qui sont appliqués.
Sur le document ci-dessus, nous retrouvons l’ensemble des déplacements réalisés par une personne au cours de l’année 2019, ainsi que le montant des indemnités remboursées par l’entreprise pour chacun de ces déplacements.
Les formules de ce tableau ne sont pas apparentes, seuls les résultats nous sont présentés.
Or, le document est rempli directement par le salarié, puis transmis au service des ressources humaines qui doit mettre en place le remboursement de ces frais.
Il convient donc de pouvoir contrôler l’exhaustivité des résultats avant la mise en paiement.
2. Contrôle de chaque formule
Pour contrôler chacune des formules une à une, il suffit de cliquer sur l’une d’entre elles, puis de visionner le résultat de la formule dans la zone des formules situé au-dessus de la feuille de calcul :
Nous pouvons ensuite répéter cette opération sur l’ensemble des cellules sur lesquelles nous souhaitons effectuer le contrôle.
Cela nous permet ainsi de constater que l’employé a modifié les formules afin d’augmenter le montant des remboursements :
Le règlement de l’entreprise stipule en effet que le montant des indemnités doit être égal à 0,45€ par kilomètre.
Hors, la formule saisie dans la cellule C16 calcul une indemnité de 0,85€ par kilomètre parcouru !
Malheureusement, contrôler chacune des formules d’une feuille de calcul peut demander un temps bien trop important pour que cette solution soit envisageable !
3. Afficher toutes les formules des cellules sélectionnées
Voyons maintenant comment afficher toutes les formules des cellules sélectionnées.
Pour l’exemple, et fort des constatations faites précédemment, nous souhaitons contrôler l’ensembles des montants de remboursement calculés dans le tableau.
Pour cela, nous sélectionnons les cellules correspondantes :
Puis nous allons utiliser une astuce simple, qui consiste simplement à transformer le symbole égal qui se trouve dans ces cellules par un caractère neutre pour Excel (ou de le supprimer complètement).
Pour cela, nous nous rendons dans le menu Accueil du ruban > Rechercher et Remplacer (tout à droite) > Remplacer (nous pouvons également utiliser le raccourci clavier [Ctrl]+[h]) :
Dans la fenêtre qui s’affiche, nous choisissons de remplacer tous les symboles d’égalité (zone Rechercher) par une apostrophe (zone Remplacer par) :
Puis nous validons le remplacement de toutes les occurrences correspondantes en appuyant sur le bouton Remplacer tout.
Excel nous informe alors avoir effectué 15 remplacements :
Les formules sont maintenant clairement visibles, et nous pouvons alors constater que deux remplacements ont été effectués par le salarié :
Pour remettre en place les formules, nous pouvons simplement annuler l’opération en cliquant sur le bouton correspondant dans la barre de lancement rapide (ou utiliser la combinaison [Ctrl]+[Z]) :
4. Afficher et imprimer les formules en 1 clic
Enfin, il reste encore une autre solution qui va permettre d’afficher toutes les formules de la feuille de calcul en un seul clic.
Pour cela, il suffit de se rendre dans le menu Formules et cliquer sur le bouton Afficher les formules (ou utiliser la combinaison [Ctrl]+["], ce qui est encore plus rapide !) :
À ce moment-là, toutes les formules de la feuilles de calcul apparaissent distinctement :
Nous pouvons également constater que :
- La largeur de chaque colonne a été doublée afin de permettre de visionner toutes les formules de la feuille,
- Les nombres sont affichés tels quels, sans la moindre mise en forme (date sous forme de numéro de série, mise en forme des kilométrages non prise en compte,…)
Nous pouvons simplement imprimer la feuille en l’état pour que les formules soient correctement imprimées :
Attention toutefois à adapter la pagination de la feuille pour prendre en compte les changements intervenus sur la largeur des colonnes.
Avant de remettre la feuille de calcul dans son état d’origine, nous pouvons encore constater une irrégularité sur les calculs fournis par le salarié : le total est en effet majoré de +10% !
Régularisons la situation :
Enfin, pour afficher à nouveau les résultats des formules, il suffit de cliquer à nouveau sur le bouton Afficher les formules :
5. La méthode rapide
Maintenant, nous allons découvrir une nouvelle méthode pour identifier facilement toutes les cellules d’une feuille de calcul contenant des formules, en utilisant VBA.
Si vous n’avez aucune connaissance en VBA, pas de problème, pour que ce tutoriel soit accessible à tout le monde, nous allons revenir dans le détail sur aspect de cette dernière.
Mais bien entendu, si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.
Pour simplifier au maximum la création de la macro-commande, nous allons utiliser l’enregistreur de macro.
Il s‘agit d’un outil extrêmement puissant qui va traduire chacune de nos actions en lignes de code VBA !
Pour utiliser l’enregistreur de macro, il suffit de cliquer sur le petit bouton dédié tout en bas à gauche de la fenêtre Excel :
Excel va alors nous afficher une fenêtre dans laquelle nous allons pouvoir donner un nom à la macro :
Ici, nous décidons de l’appeler « selectionnerFormules », puis nous validons en appuyant sur le bouton [OK].
Notez au passage qu’il est possible d’affecter un raccourci clavier à la macro, ou encore d’ajouter une description depuis cette fenêtre.
Lorsque l’enregistrement de la macro est lancé, l’icône de lancement est modifiée pour prendre la forme d’un carré :
Toutes les actions que nous allons effectuer sur Excel (entre maintenant et le moment où nous cliquerons sur ce bouton) vont être automatiquement enregistrées et traduites en lignes VBA.
Pour sélectionner toutes les cellules contenant des formules, nous allons procéder en deux temps :
- Tout d’abord, nous sélectionnons l’ensemble des cellules de la feuille de calcul, en cliquant simplement sur le bouton situé en haut à gauche, à l’intersection des en-têtes de lignes et de colonnes (ou en utilisant le raccourci clavier [Ctrl]+[a]) :
- Ensuite, nous allons réduire la sélection aux seules cellules contenant des formules en nous rendant dans le menu Accueil du ruban > Rechercher et sélectionner > Sélectionner les cellules :
- Dans la fenêtre qui s’affiche, nous choisissons donc de sélectionner les formules, puis nous validons en appuyant sur le bouton [OK] :
Et voilà, toutes les cellules concernées sont maintenant sélectionnées :
Pour finir, maintenant que les cellules sont sélectionnées, nous allons pouvoir les identifier en appliquant par exemple une police particulière que nous n’avons pas l’habitude d’utiliser dans nos fichiers :
Ici, nous sélectionnons par exemple la police Bauhaus 93, dont la particularité est d’avoir une graisse assez importante (comme si nous avions passé le texte en gras).
Nous pourrions choisir un autre type d’identification (changer la couleur de la police, ou de fond, bordures de cellules, …), mais la suppression sera alors un peu plus complexe…
Et voilà, les formules sont maintenant facilement identifiables :
Nous pouvons maintenant arrêter l’enregistrement de la macro, en cliquant sur le petit carré noir.
C’est bien beau, maintenant ces cellules sont identifiables pour un besoin ponctuel, mais une fois le traitement terminé, nous allons vouloir retrouver l’état d’origine de notre feuille.
Et pour cela, nous allons créer une seconde macro-commande :
Une fois l’enregistreur lancé, nous sélectionnons à nouveau toutes les cellules, pour remettre la police par défaut sur l’ensemble du classeur (ici, la police Calibri) :
C’est tout, nous pouvons maintenant arrêter l’enregistrement de la macro-commande !
Nous pouvons ajouter deux boutons sur la feuille de calcul pour lancer rapidement ces macros :
- Pour créer un bouton, nous pouvons insérer une zone de texte (menu Insertion > Zone de texte, tout à droite du menu) :
- Puis nous dessinons la zone sur la feuille de calcul, et nous saisissons un texte :
- Nous pouvons éventuellement mettre le bouton en forme (menu Mise en forme > Style) :
- Puis, nous effectuons un clic droit sur le bouton pour sélectionner Affecter une macro :
- Dans la fenêtre suivante, nous sélectionnons la macro-commande :
Ensuite, il ne reste plus qu’à tester en cliquant sur ces deux boutons :
