Afficher les formules dans Excel, feuille par feuille ou en liste

Bascule une feuille en mode formules d’un raccourci, écris la formule d’une cellule à côté de son résultat avec FORMULETEXTE, ou relève d’un clic celles de tout le classeur.

Module1 (Code)

Public Sub Lister_formules_classeur()
'VIDE une feuille qui porterait déjà le nom « Carte des formules » pour y écrire
'son rapport ; son contenu ne revient pas par Ctrl+Z. Aucune formule du classeur
'n'est touchée.
'Dresse la carte des formules : une ligne par formule, avec sa feuille, son
'adresse, son texte et le résultat affiché. De quoi reprendre un classeur reçu
'sans cliquer cellule par cellule, et repérer la colonne où une formule a été
'écrasée par une valeur en dur.
'Attend : rien, la macro balaie toutes les feuilles du classeur actif.
'Modifie : la seule feuille « Carte des formules ». Ses colonnes Formule et
'          Résultat sont au format texte, sinon Excel recalculerait ce qu'elle
'          est censée montrer.
    Dim classeur As Workbook
    Dim feuille As Worksheet
    Dim cible As Worksheet
    Dim base As Range
    Dim contenu As Variant
    Dim valeurs As Variant
    Dim lignes() As String
    Dim sortie() As String
    Dim formule As String
    Dim lettres As String
    Dim nbLignes As Long
    Dim numeroColonne As Long
    Dim reste As Long
    Dim l As Long
    Dim c As Long
    Dim i As Long
    Dim j As Long
 
    On Error GoTo Echec
 
    Set classeur = ActiveWorkbook
    If classeur Is Nothing Then
        Application.ScreenUpdating = True
        Signaler "Lister_formules_classeur", "aucun classeur ouvert."
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
 
    For Each feuille In classeur.Worksheets
        'Excel ne distingue pas les majuscules dans un nom d'onglet, et refuse même
        'deux feuilles qui ne diffèrent que par la casse. Comparer avec « = » faisait
        'donc ajouter une seconde feuille, qu'Excel refusait ensuite de nommer
        '(erreur 1004), en laissant une feuille vide derrière elle.
        If StrComp(feuille.Name, "Carte des formules", vbTextCompare) = 0 Then Set cible = feuille
    Next feuille
    If cible Is Nothing Then
        Set cible = classeur.Worksheets.Add(After:=classeur.Worksheets(classeur.Worksheets.Count))
        cible.Name = "Carte des formules"
    Else
        cible.Cells.Clear
    End If
 
    ReDim lignes(1 To 4, 1 To 256)
    For Each feuille In classeur.Worksheets
        If Not feuille Is cible Then
            If Application.WorksheetFunction.CountA(feuille.Cells) > 0 Then
                Set base = feuille.UsedRange
                'Une plage d'une seule cellule ne rend pas un tableau.
                If base.Cells.Count = 1 Then Set base = base.Resize(2, 2)
                'Formules et résultats sont lus en deux fois pour toute la
                'feuille, plutôt qu'un aller-retour vers Excel par formule.
                contenu = base.Formula
                valeurs = base.Value
                For l = 1 To UBound(contenu, 1)
                    For c = 1 To UBound(contenu, 2)
                        'CStr refuse une valeur d'erreur, qui ne porte de toute
                        'façon aucune formule.
                        formule = ""
                        If Not IsError(contenu(l, c)) Then formule = CStr(contenu(l, c))
                        If Left$(formule, 1) = "=" Then
                            nbLignes = nbLignes + 1
                            'ReDim Preserve ne sait agrandir que la dernière
                            'dimension : le nombre de lignes est donc en second.
                            If nbLignes > UBound(lignes, 2) Then
                                ReDim Preserve lignes(1 To 4, 1 To UBound(lignes, 2) * 2)
                            End If
                            'Adresse calculée sans repasser par Excel.
                            numeroColonne = base.Column + c - 1
                            lettres = ""
                            Do While numeroColonne > 0
                                reste = (numeroColonne - 1) Mod 26
                                lettres = Chr$(65 + reste) & lettres
                                numeroColonne = (numeroColonne - 1) \ 26
                            Loop
                            lignes(1, nbLignes) = feuille.Name
                            lignes(2, nbLignes) = lettres & (base.Row + l - 1)
                            lignes(3, nbLignes) = Left$(formule, 500)
                            If IsError(valeurs(l, c)) Then
                                'Seul ce cas repasse par Excel : CStr refuse une
                                'valeur d'erreur.
                                lignes(4, nbLignes) = base.Cells(l, c).Text
                            Else
                                lignes(4, nbLignes) = Left$(CStr(valeurs(l, c)), 255)
                            End If
                        End If
                    Next c
                Next l
            End If
        End If
    Next feuille
 
    cible.Range("A1").Value = "Feuille"
    cible.Range("B1").Value = "Adresse"
…

Excel pour Windows, prévue aussi pour Mac
Ctrl+Z n’annule pas ce qu’elle fait

Rien ne distingue à l’écran un total calculé d’un nombre tapé à la main, puisque les deux affichent le même chiffre. Le mode Afficher les formules lève le doute sur une feuille, FORMULETEXTE sur une cellule. Pour garder la liste des formules de tout un classeur, notre macro VBA l’écrit dans une feuille.

Comment faire apparaître toutes les formules d’une feuille Excel

Pour faire apparaître toutes les formules d’une feuille, bascule-la en mode Afficher les formules, depuis l’onglet Formules du ruban ou au clavier. Chaque cellule montre alors le calcul qu’elle contient au lieu de son résultat, sur toute la feuille d’un coup, et un second passage ramène les chiffres. Rien n’est modifié dans le classeur, puisqu’il ne s’agit que d’un réglage d’affichage.

Toute la page suit le même classeur, celui d’un suivi de ventes par région. Son onglet Ventes porte sept lignes dont la colonne Total devrait être calculée, et son onglet Bilan en tire deux indicateurs. À l’écran, les sept totaux se ressemblent, et c’est bien le problème.

	A	B	C	D
1	Région	T1	T2	Total
2	Nord	38400	41200	79600
3	Sud	45700	43900	89600
4	Est	41200	39800	92000
5	Ouest	36900	38400	75300
6	Centre	36500	40100	85000
7	Île-de-France	52300	49800	102100
8	Outre-mer	28900	31300	71000

	A	B
1	Indicateur	Valeur
2	Total annuel	594600
3	Meilleure région	102100

Basculer une feuille avec le bouton Afficher les formules

Le bouton Afficher les formules se trouve dans l’onglet Formules, au bout du groupe Vérification des formules, celui qui porte aussi Repérer les antécédents et Repérer les dépendants. C’est un interrupteur, qui rebascule au clic suivant, et son libellé ne s’affiche qu’en grande largeur, une fenêtre étroite le réduisant à sa seule icône.

01
Clique sur l’onglet de la feuille à examiner, ici Ventes, car le mode ne vaut que pour la feuille affichée.
02
Ouvre l’onglet Formules du ruban, puis clique sur Afficher les formules dans le groupe Vérification des formules.
Afficher les formules est la première des trois petites icônes de la colonne de droite, celle du fx barré.
03
Clique de nouveau sur le même bouton pour revenir aux résultats.

La colonne Total de la feuille Ventes se lit alors d’un coup d’œil, sans cliquer dans une seule cellule. Les quatre totaux calculés montrent leur SOMME, et les trois autres restent des nombres nus, en D4, D6 et D8. Ce sont ces trois-là qui ne se recalculeront plus jamais, quoi qu’il arrive aux chiffres des deux trimestres.

Feuille Ventes en mode Afficher les formules, où trois totaux tapés en dur apparaissent au milieu d’une colonne de SOMME. — En mode formules, la colonne Total trahit les trois nombres saisis à la main au milieu des SOMME.

Afficher les formules avec le raccourci clavier

Le raccourci fait la même bascule sans quitter la feuille des yeux. Appuie sur Ctrl et sur la touche placée à gauche du 1, juste au-dessus de Tab, celle qui porte le ² sur un clavier français et l’accent grave sur les autres. L’aide de Microsoft l’écrit d’ailleurs Ctrl et accent grave, et précise qu’elle se trouve dans le coin supérieur gauche du clavier.

Appuie une seconde fois sur les mêmes touches pour retrouver les résultats. C’est aussi la réponse quand une feuille reçue affiche ses formules sans qu’on comprenne pourquoi, le raccourci ayant été frappé par mégarde, un cas que détaille la fiche du dictionnaire sur afficher les formules.

Ce que le mode change dans la feuille, et ce qu’il ne touche pas

Le mode ne change que l’affichage, jamais les calculs, et ce que tu vois revient exactement comme avant au second appui. Deux choses bougent pourtant à l’écran le temps de la bascule. Les colonnes s’élargissent d’abord, pour laisser voir chaque formule en entier plutôt qu’un résultat court. Les valeurs, ensuite, perdent leur format, si bien qu’une date réapparaît sous la forme du numéro de série qu’Excel manipule en coulisse.

Le réglage appartient à chaque feuille et non au classeur, si bien qu’activé sur Ventes il laisse Bilan afficher ses résultats, dans la même fenêtre et sous le même ruban.
Il part avec le fichier, puisqu’un classeur enregistré en mode formules rouvre sur ses formules, feuille par feuille, sur ton poste comme chez celui à qui tu l’envoies.
Il ne dépend d’aucune protection, à la différence des formules qu’on masque par la case Masquée de Format de cellule, lesquelles disparaissent jusque dans la barre de formule une fois la feuille protégée.
Il ne montre que les formules écrites dans les cellules, et laisse donc invisibles les calculs d’une mise en forme conditionnelle, d’une règle de validation ou d’un nom défini.

Imprimer les formules d’une feuille Excel

Une impression ordinaire donne les résultats, jamais les formules, comme le rappelle l’aide de Microsoft. Pour garder une trace papier des calculs, bascule d’abord la feuille en mode formules, puis imprime comme d’habitude. C’est le moyen le plus simple de faire relire la mécanique d’un tableau loin de l’écran, ou de la joindre à un dossier.

01
Bascule la feuille en mode formules, au bouton ou au raccourci.
02
Ouvre le menu Fichier puis Imprimer, ou appuie sur Ctrl+P, pour arriver à l’aperçu avant impression.
03
Règle la mise à l’échelle si les colonnes sont trop étroites pour les formules, plus longues à lire que les résultats qu’elles remplacent.
04
Lance l’impression, puis reviens aux résultats avec le même raccourci.

Il faut donc recommencer feuille par feuille, puisque le mode ne vaut que pour celle qui est affichée. Sur un classeur de douze onglets, l’impression demande douze bascules, et c’est là que la feuille produite par la macro, plus bas, devient plus commode à imprimer que les douze feuilles elles-mêmes.

Afficher la formule d’une seule cellule avec FORMULETEXTE

Quand tu veux voir une formule sans basculer toute la feuille, la fonction FORMULETEXTE la renvoie sous forme de texte, dans la cellule de ton choix. Les résultats restent affichés partout ailleurs, et la formule s’écrit à côté d’eux, ce qui permet de comparer les deux d’un même regard.

fx Formule

=FORMULETEXTE(D2)

Écrite en F2, elle renvoie le texte =SOMME(B2:C2), la formule qui calcule le total du Nord.

Recopiée sur toute la colonne, elle trahit les totaux tapés à la main d’une autre façon. Une cellule sans formule ne lui donne rien à écrire, et la fonction renvoie alors l’erreur #N/A, en F4, F6 et F8. Ces trois erreurs sont le signal qu’on cherche, et la variante ci-dessous les remplace par une phrase lisible.

fx Formule

=SIERREUR(FORMULETEXTE(D4);"tapé à la main")

En F4, elle écrit tapé à la main au lieu de #N/A, et garde la formule partout où il y en a une.

Trouver les cellules qui contiennent une formule

Afficher les formules les montre à l’écran, mais ne les sélectionne pas pour autant. Pour agir sur elles, les colorer ou les compter, deux outils d’Excel les attrapent d’un coup, et ils ne couvrent pas la même chose. La fenêtre Atteindre travaille sur la feuille active, alors que Rechercher tout sait balayer le classeur entier.

Sélectionner les formules avec Atteindre

La fenêtre Atteindre sélectionne d’un coup toutes les cellules de la feuille qui portent une formule, et elles seules. Sur Ventes, elle prend D2, D3, D5 et D7, en laissant les trois totaux tapés en dur de côté. Une couleur de fond posée là-dessus marque les vrais calculs pour de bon, même une fois le mode formules refermé.

01
Clique n’importe où dans la feuille, sans rien sélectionner de particulier.
02
Appuie sur F5 ou sur Ctrl+T pour ouvrir la fenêtre Atteindre, puis clique sur son bouton Cellules…
03
Dans la fenêtre Sélectionner les cellules, choisis Formules, laisse ses quatre cases cochées, puis valide par OK.
La fenêtre s’ouvre ici sur Notes. Formules se trouve en dessous, avec ses quatre cases qui trient les calculs selon ce qu’ils renvoient.
04
Pose une couleur de fond sur la sélection, depuis l’onglet Accueil, pour garder la trace des cellules calculées.

Sur une feuille qui ne calcule rien, Excel refuse la sélection et répond « Pas de cellules correspondantes. » sans rien changer à ce qui était sélectionné. Ce message est une réponse en soi sur un fichier reçu, puisqu’il dit que toute la feuille est saisie à la main.

C’est aussi le point de départ pour protéger ces cellules, puisque la même sélection sert ensuite à les verrouiller, geste que détaille la page sur la manière de verrouiller une cellule Excel sans bloquer les cases de saisie.

Rechercher le signe égal dans tout le classeur

Rechercher et remplacer sait, lui, dépasser les limites de la feuille active. Réglé sur le classeur et sur les formules, il cherche le signe égal et rend la liste des cellules qui en contiennent un, avec leur feuille et leur adresse. C’est le seul outil natif qui embrasse le classeur entier, et sa liste se lit sans rien modifier.

01
Appuie sur Ctrl+F, puis clique sur Options pour déplier la fenêtre.
02
Tape le signe égal dans le champ Rechercher, choisis Classeur dans la liste Dans, et Formules dans la liste Regarder dans.
Réglée ici sur la feuille, la liste Dans doit passer à Classeur pour que la recherche dépasse l’onglet affiché.
03
Clique sur Rechercher tout, et la liste s’ouvre au bas de la fenêtre, avec une ligne par cellule trouvée.

Un clic sur une ligne de la liste saute directement à la cellule qu’elle désigne, sans que la fenêtre se referme. Cette liste a pourtant le défaut de ne vivre que le temps de la recherche, puisqu’elle disparaît avec la fenêtre et ne laisse rien dans le classeur. Un audit demande souvent l’inverse, une trace qu’on garde, qu’on annote et qu’on transmet, et c’est exactement ce qu’écrit la macro plus bas.

Elle ramène aussi les cellules qui portent un texte commençant par le signe égal, qu’il ait été tapé dans une cellule au format Texte ou précédé d’une apostrophe, à côté des vraies formules. Ce sont précisément les cellules qui affichent leur formule au lieu d’un résultat, et les voir remonter dans la liste vaut mieux que de les chercher une à une.

Choisir la méthode selon ce que tu veux en garder

Méthode	Portée	Ce qu’elle donne	Ce qui reste après
Mode Afficher les formules	La feuille affichée	Les formules à la place des résultats	Rien, un second appui revient en arrière
FORMULETEXTE	Une cellule par formule	La formule en texte, à côté du résultat	Une colonne de formules à effacer
Atteindre, Cellules, Formules	La feuille affichée	Les cellules calculées, sélectionnées	La couleur que tu poses dessus
Rechercher tout sur le signe égal	Le classeur entier	Une liste dans une fenêtre	Rien, la liste se ferme avec la fenêtre
Macro de cette page	Le classeur entier	Une feuille de quatre colonnes	La feuille Carte des formules

Le choix se joue sur deux questions, et la première est celle de la portée, puisqu’il s’agit de regarder une feuille ou de faire le tour d’un classeur reçu. La seconde est celle de la trace, selon que tu veuilles garder l’inventaire ou seulement jeter un œil. Pour ce coup d’œil sur une feuille, le raccourci suffit et ne laisse rien derrière lui. Pour un classeur entier dont il faut rendre compte, la macro écrit un inventaire qui se trie, se filtre et s’imprime.

Lister les formules de tout un classeur avec une macro

Notre macro Lister_formules_classeur parcourt toutes les feuilles du classeur actif et écrit une ligne par formule trouvée dans une feuille qu’elle crée à la fin, nommée Carte des formules. Elle t’y amène en terminant, et la barre d’état annonce le nombre de lignes écrites. Aucune formule du classeur n’est modifiée. C’est l’inventaire que le mode d’affichage ne sait pas produire, parce qu’il ne dépasse jamais la feuille affichée.

	A	B	C	D
1	Feuille	Adresse	Formule	Résultat affiché
2	Donnees	B2	=A2*2	2
3	Donnees	B3	=A3*2	4
4	Donnees	B4	=A4*2	6
5	Donnees	B5	=A5*2	8
6	Donnees	B6	=A6*2	10
7	Donnees	B7	=A7*2	12
8	Donnees	B8	=A8*2	14

Lire les quatre colonnes de la Carte des formules

La feuille produite tient en quatre colonnes, et chaque ligne désigne une cellule qui calcule. Feuille et Adresse disent où elle se trouve, Formule reprend son texte, et Résultat affiché donne ce qu’elle vaut au moment du relevé. Comme la feuille est une feuille ordinaire, tu la tries par colonne, tu la filtres sur un nom d’onglet ou sur un bout de formule, et tu l’imprimes.

La lecture des deux dernières colonnes demande pourtant une précaution chacune. La colonne Formule reprend la formule telle qu’Excel la range dans le fichier, avec les noms anglais des fonctions, si bien qu’un SOMME y apparaît en SUM. La colonne Résultat affiché, elle, donne la valeur brute et non la mise en forme, donc un pourcentage de 25 % s’y lit 0,25 et un montant sans son symbole.

Repérer un total tapé à la place d’une formule

C’est en regardant ce qui manque que la carte est la plus utile. Sur le classeur de cette page, elle compte six lignes, quatre pour l’onglet Ventes et deux pour l’onglet Bilan. Les adresses D4, D6 et D8 n’y figurent pas, et c’est la preuve que ces trois totaux ont été tapés à la main au lieu d’être calculés.

Un tri sur la colonne Adresse fait sauter le trou aux yeux, puisque les adresses d’une même colonne se suivent. Sur un fichier reçu, c’est le premier geste à faire avant de se fier au moindre total, d’autant que ces trois nombres resteront figés même le jour où les ventes d’un trimestre seront corrigées.

Installer la macro qui relève les formules

Ouvre l’éditeur VBA avec Alt+F11 et colle le code dans un module neuf (menu Insertion, Module). Reviens ensuite au classeur à inventorier et lance Lister_formules_classeur avec Alt+F8.

Voir les étapes détaillées pour installer une macro

Connaître les limites de la macro avant de la lancer

Elle vide sans retour une feuille qui porterait déjà le nom Carte des formules, quelle que soit la casse de ce nom, et Ctrl+Z ne ramène pas son contenu. Renomme-la avant de relancer la macro si tu tiens à garder un relevé précédent.
Elle relève ce qui commence par le signe égal dans la zone utilisée de chaque feuille, donc aussi un texte commençant par ce signe, tapé dans une cellule au format Texte ou précédé d’une apostrophe. C’est voulu, et c’est ce que fait déjà la recherche du signe égal plus haut, puisque ces cellules-là sont justement celles qui affichent une formule au lieu d’un résultat.
Elle ne voit que les formules écrites dans les cellules, et laisse donc hors de la carte celles d’une mise en forme conditionnelle, d’une règle de validation ou d’un nom défini, tout comme les feuilles de graphique.
Les feuilles masquées sont relevées comme les autres, ce qui est le but sur un fichier reçu, mais surprend quand on comptait les lignes de la carte.
Une formule est coupée à 500 caractères dans la colonne Formule, et un résultat à 255 dans la colonne Résultat affiché.
La carte est une photo prise à l’instant du lancement, qui ne suit donc pas les formules ajoutées ni effacées ensuite, et c’est un nouveau lancement qui la met à jour.

D’autres macros utiles

Verrouiller des cellules sans bloquer la saisie

Verrouille les cellules à protéger sans bloquer les cases que tes collègues remplissent, par les gestes d’Excel ou d’un clic avec notre macro gratuite.

Tu as peut-être encore une question

Le symptôme a deux origines qu’un seul coup d’œil sépare, selon que la feuille entière montre ses formules ou qu’une cellule s’y entête toute seule, la première venant du mode Afficher les formules resté allumé et la seconde d’une cellule passée au format Texte. Le raccourci coupe le mode, et la fiche du dictionnaire sur afficher les formules détaille la seconde origine.

La barre de formule se rallume depuis l’onglet Affichage du ruban, où la case Barre de formule, dans le groupe Afficher, la fait revenir à sa place habituelle, juste sous le ruban et au-dessus de la feuille, avec la zone de nom à sa gauche et le bouton fx à son entrée. C’est un tout autre réglage que le mode de cette page, puisqu’il ne concerne qu’une bande de l’écran et non le contenu des cellules, comme l’explique la fiche barre de formule.

Les formules dites masquées sont celles dont la case Masquée a été cochée dans Format de cellule, sous l’onglet Protection, avant que la feuille ne soit protégée, si bien qu’elles n’apparaissent plus du tout dans la barre de formule quand on clique sur leur cellule. Il faut donc ôter la protection depuis l’onglet Révision, puis décocher cette case, un geste que détaille la page sur la façon de verrouiller une cellule Excel.

Le même geste fait l’aller et le retour, qu’il s’agisse du bouton Afficher les formules de l’onglet Formules ou du raccourci clavier, et l’aide de Microsoft le dit dans ces termes, il suffit de sélectionner le bouton une seconde fois pour revenir à l’affichage standard des résultats. Il n’existe donc pas de commande séparée pour désactiver l’affichage des formules, et une feuille dont les résultats sont revenus n’a rien perdu de ce qu’elle contenait.

Excel sur le web ne propose pas le mode Afficher les formules, et l’aide de Microsoft le dit sans détour, puisque la barre de formule y montre bien la formule de la cellule sélectionnée pendant que toutes les cellules de la feuille, elles, continuent d’afficher leurs seuls résultats. Pour voir les formules à même les cellules et les imprimer, il faut ouvrir le classeur dans l’application de bureau, par la commande du même nom, en haut du ruban.

Pour aller plus loin

Tout ce dont tu as besoin pour faire tourner tes macros et progresser sur Excel se trouve ici.

Télécharge notre cours Excel complet en PDF

Télécharger

Ton cours Excel gratuit

3 chapitres pour apprendre les bases et découvrir l'automatisation.

Voir le cours

Afficher les formules dans Excel, feuille par feuille ou en liste

Comment faire apparaître toutes les formules d’une feuille Excel

Basculer une feuille avec le bouton Afficher les formules

Afficher les formules avec le raccourci clavier

Ce que le mode change dans la feuille, et ce qu’il ne touche pas

Imprimer les formules d’une feuille Excel

Afficher la formule d’une seule cellule avec FORMULETEXTE

Trouver les cellules qui contiennent une formule

Sélectionner les formules avec Atteindre

Rechercher le signe égal dans tout le classeur

Choisir la méthode selon ce que tu veux en garder

Lister les formules de tout un classeur avec une macro

Lire les quatre colonnes de la Carte des formules

Repérer un total tapé à la place d’une formule

Installer la macro qui relève les formules

Connaître les limites de la macro avant de la lancer

D’autres macros utiles

Verrouiller des cellules sans bloquer la saisie

Tu as peut-être encore une question

Pourquoi Excel affiche la formule au lieu du résultat ?

Comment faire apparaître la barre de formule dans Excel ?

Comment afficher les formules masquées sur Excel ?

Comment revenir aux résultats après avoir affiché les formules ?

Comment afficher les formules dans Excel sur le web ?

Pour aller plus loin

Télécharge notre cours Excel complet en PDF

Ton cours Excel gratuit

Entraîne-toi sur nos 63 exercices corrigés

Les macros Excel et le VBA expliqués simplement

Activer les macros quand Excel les bloque

Décris ta tâche, on écrit la macro

Besoin d’aide sur Excel ?