Créer un sommaire cliquable des onglets d’un classeur Excel

Poser un lien vers un onglet avec Ctrl+K et Emplacement dans ce document

Ctrl+K ouvre la fenêtre Insérer un lien hypertexte, dont la colonne Lier à propose quatre destinations, et c’est Emplacement dans ce document qu’il te faut ici. Elle garde le lien à l’intérieur du fichier au lieu d’envoyer Excel chercher un document sur le disque, et elle liste les feuilles du classeur sous Référence de cellule.

1. 01

   Clique sur le bouton d’ajout de feuille, à droite du dernier onglet, renomme la feuille Sommaire par un double-clic sur son onglet, puis fais-la glisser en tête du classeur.

2. 02

   Sélectionne A2 sur cette feuille et appuie sur Ctrl+K. Le bouton Lien, dans le groupe Liens de l’onglet Insertion, ouvre la même fenêtre.

3. 03

   Dans la colonne Lier à, clique sur Emplacement dans ce document, puis choisis Paris sous Référence de cellule et laisse A1 dans la zone Tapez la référence de la cellule.

   

4. 04

   Écris Paris dans la zone Texte à afficher, en haut de la fenêtre, puis valide par OK. Recommence en A3 pour l’onglet suivant.

La cellule prend alors la couleur des liens, et le clic t’emmène en A1 de Paris. Le bouton Info-bulle, en haut à droite de la fenêtre, ajoute la phrase qui s’affichera au survol. Elle rend service quand un nom d’onglet abrégé, T1 ou CA, ne dit pas à lui seul ce que contient la feuille. Répété sur chaque feuille, ce geste reste court, mais il est à refaire à chaque onglet ajouté, et c’est ce qui le rend pénible sur un classeur qui grossit.

Ajouter un bouton Retour au sommaire sur chaque feuille

Un sommaire se parcourt dans les deux sens, et le chemin du retour se pose exactement de la même façon. Il te faut un lien par feuille, pointé vers la cellule A1 du sommaire. Choisis une cellule libre et toujours la même, pour que le lecteur la retrouve sans avoir à la chercher d’un onglet à l’autre.

1. 01

   Sur la feuille Paris, sélectionne la cellule qui portera le retour, puis appuie sur Ctrl+K.

2. 02

   Choisis Emplacement dans ce document, sélectionne Sommaire sous Référence de cellule, écris Retour au sommaire dans Texte à afficher, puis valide par OK.

3. 03

   Copie cette cellule, puis colle-la au même endroit sur chaque autre feuille, le lien suivant la copie.

Creer_sommaire n’écrit que la feuille Sommaire, et ce retour lui échappe par construction, puisqu’elle écraserait une cellule dans un classeur qu’elle ne connaît pas. Le geste reste donc à faire à la main, mais il ne se fait qu’une fois, et il suit ensuite les feuilles que tu dupliques.

Transformer le sommaire en boutons avec les formes d’Excel

Pour une feuille d’accueil qui ressemble à un menu, les formes remplacent les cellules, et chacune reçoit son lien de la même manière. Dans l’onglet Insertion, le menu Formes pose un rectangle arrondi que tu remplis du nom de l’onglet, puis Ctrl+K lui donne sa destination. Sélectionne plusieurs formes en gardant Ctrl enfoncé pour les aligner d’un seul geste, et le sommaire prend l’allure d’un tableau de bord plutôt que d’une liste.

Cette mise en forme ne change rien au fond, puisque le lien posé reste le même, et elle coûte le même travail à chaque onglet ajouté. Passé une dizaine de feuilles, ce sont les deux méthodes automatiques ci-dessous qui font gagner du temps.

Lister les onglets du classeur avec LIRE.CLASSEUR dans un nom défini

Le nom défini sert de passage obligé, puisqu’il est le seul endroit où Excel accepte encore une fonction macro d’Excel 4.0. Une fois posé, il se comporte comme n’importe quelle plage nommée, et les formules de la feuille peuvent aller y piocher les noms de feuilles un par un.

1. 01

   Dans l’onglet Formules, clique sur Définir un nom, ou ouvre le gestionnaire de noms avec Ctrl+F3 puis choisis Nouveau.

2. 02

   Écris nomF dans la zone Nom, colle la formule ci-dessous dans la zone Fait référence à, puis valide par OK.

Reste à retirer ce préfixe, ce que fait la formule suivante en gardant tout ce qui suit le crochet fermant. Écris-la en A2 de la feuille Sommaire, puis recopie-la vers le bas sur une vingtaine de lignes, largement de quoi couvrir les onglets à venir.

LIGNES($1:2) est le compteur qui avance avec la recopie, puisqu’il vaut 2 en ligne 2, 3 en ligne 3, et ainsi de suite. Il commence à 2 parce que la première feuille du classeur est le sommaire lui-même, qui n’a pas à figurer dans sa propre liste. NBVAL compte les feuilles trouvées, et c’est ce test qui vide les lignes en trop plutôt que de les remplir d’erreurs.

Rendre chaque nom d’onglet cliquable avec LIEN_HYPERTEXTE

LIEN_HYPERTEXTE construit le lien à partir du nom trouvé en colonne A, et le dièse de tête est ce qui compte vraiment. Sans lui, Excel part chercher sur le disque un fichier portant ce nom au lieu de rester dans le classeur ouvert, comme le rappelle la fiche de la fonction. Les apostrophes qui encadrent le nom, elles, servent à Lyon 2026, dont l’espace ferait perdre à Excel la fin du nom.

SUBSTITUE double l’apostrophe de Chiffre d’affaires, et cette précaution n’a rien de cosmétique. Une apostrophe laissée seule au milieu du nom ferme la paire trop tôt, et le lien ne mène alors plus nulle part. Notre macro fait le même remplacement, pour la même raison. La colonne A, qui ne sert plus qu’à alimenter la colonne B, se masque une fois la liste en place.

Enregistrer le classeur au format qui prend en charge les macros

Un nom défini qui porte une fonction macro d’Excel 4.0 ne survit pas à un enregistrement ordinaire, et c’est la règle que suit déjà la fiche de la somme par couleur. Le classeur doit donc partir au format « Classeur Excel (prenant en charge les macros) », à choisir dans la liste des types de la fenêtre Enregistrer sous.

Cette contrainte met les deux méthodes automatiques à égalité, puisque la macro impose le même format. La formule garde pourtant un avantage, celui de relire la liste des feuilles à chaque calcul. La macro, elle, écrit des liens ordinaires, que la personne qui reçoit le classeur peut déplacer, renommer ou supprimer sans rien casser.

Obtenir la feuille Sommaire d’un classeur de sept onglets

La feuille arrive en première position, avec l’en-tête Feuille en gras, puis un lien par onglet visible. Les deux feuilles invisibles ne reçoivent aucun lien, et c’est une ligne grise qui en rend compte. Elle se place sous la liste, séparée du dernier lien par une ligne vide, et dit combien d’onglets ont été laissés de côté.

La colonne A s’élargit à la demande, si bien qu’un nom long comme Chiffre d’affaires reste lisible sans que tu touches à la bordure. Le clic sur un nom amène en A1 de sa feuille. Le sommaire, lui, reste au premier plan une fois la macro finie, pour que tu voies ce qu’elle vient d’écrire.

Régler le nom de la feuille et la cellule d’arrivée des liens

• nomFeuille Le nom de la feuille créée, "Sommaire" dans le code. Une feuille du même nom déjà présente est remplacée, et c’est ce qui rend la macro relançable.
• celluleArrivee La cellule sur laquelle chaque lien arrive, "A1" dans le code. Sur des feuilles dont le tableau commence plus bas, vise la première ligne utile, "A4" par exemple.
• citerLesMasquees False dans le code, et les feuilles masquées sont seulement comptées sous la liste. Mets True pour écrire leur nom à la place de ce décompte, à leur rang dans la liste, en gris et sans lien, puisque la macro ne pose pas de lien vers une feuille qu’Excel n’affiche pas.

Installer la macro qui crée le sommaire

Connaître les limites du sommaire écrit par la macro

• Les liens ne suivent pas un onglet renommé. Rebaptise Nantes en Nantes Sud et le lien du sommaire vise toujours Nantes, parce que sa destination est une chaîne figée et non une référence qu’Excel réécrit. Relance la macro après un renommage comme après un ajout.

• Une feuille Sommaire écrite à la main est remplacée sans confirmation, titre, logo et mise en forme compris, et Ctrl+Z ne la ramène pas. C’est le prix de la relance, et c’est la seule feuille du classeur que la macro touche.

• Les feuilles de graphique ne sont jamais listées, la boucle ne parcourant que les feuilles de calcul. Le reste du sommaire se construit normalement, graphique ou pas.

• Sur un classeur dont la structure est protégée, la macro s’arrête avant de créer quoi que ce soit et te le dit, au lieu de laisser Excel interrompre le code sur une erreur.

• Elle ne travaille que sur le classeur au premier plan et n’écrit rien dans les autres feuilles, donc aucun bouton de retour ne se pose tout seul.

Ouvrir la liste des onglets par un clic droit sur les flèches de défilement

À gauche de la barre d’onglets, deux petites flèches font défiler les onglets un à un, et un clic droit dessus ouvre une liste des feuilles du classeur, dans laquelle tu choisis la tienne. Cette fenêtre s’appelle Activer, et sa liste est intitulée Feuilles du classeur. C’est le geste le plus rapide quand la barre du bas ne montre plus que les premiers onglets, et il ne laisse rien derrière lui.

Parcourir les feuilles dans le panneau de navigation de Microsoft 365

Microsoft 365 ajoute un panneau latéral qui liste les feuilles du classeur et offre une recherche pour les retrouver. Il s’ouvre depuis le ruban, par un bouton nommé Navigation, reste affiché pendant que tu travailles, et convient donc à un classeur très fourni où l’on saute sans arrêt d’une feuille à l’autre. Comme il appartient à l’application et non au fichier, la personne à qui tu envoies le classeur ne le retrouve que si sa propre version d’Excel le propose.

Sauter sur un onglet en tapant son adresse dans la zone de nom

La zone de nom, à gauche de la barre de formule, accepte une adresse complète et pas seulement une référence de la feuille courante. Tape Bilan!A1 puis Entrée, et Excel ouvre la feuille Bilan sur sa cellule A1 sans passer par la barre d’onglets.

Le geste ne marche que sur une feuille visible, Excel n’allant pas sur un onglet qu’il n’affiche pas. Il ne garde rien non plus, contrairement à un lien posé dans une cellule, et c’est pour cela qu’il dépanne sans remplacer un sommaire.

Garder ou perdre les liens du sommaire dans le PDF

Un classeur exporté en PDF perd la navigation de son sommaire, et les deux façons de poser les liens la perdent chacune à sa manière. Un lien posé par Ctrl+K ne laisse aucune zone cliquable dans le fichier produit. Un lien construit par LIEN_HYPERTEXTE y arrive, lui, sous la forme de son adresse brute, celle qui ne désigne rien pour un lecteur de PDF. Dans les deux cas, le document final ne contient aucune destination interne vers laquelle sauter.

Ce n’est pas un réglage d’export à retrouver, Excel n’écrit tout simplement aucune destination interne dans le fichier produit, et rien ne se rattrape depuis la feuille. Si le classeur doit circuler en PDF, le sommaire sert de table des matières imprimée, et les numéros de page reprennent le rôle du clic.

Compter les pages de chaque feuille avant d’imprimer le sommaire

Le numéro de page de chaque feuille ne se lit dans aucune cellule, mais VBA sait le calculer, en additionnant les pages des feuilles qui précèdent. Microsoft publie un exemple de sommaire bâti sur ce principe. La macro ci-dessous fait la même chose en colonne B, sur la feuille Sommaire que tu viens de créer.

Sub Numeroter_pages_du_sommaire()
    Dim feuille As Worksheet
    Dim sommaire As Worksheet
    Dim ligne As Long
    Dim page As Long
    Set sommaire = ActiveWorkbook.Worksheets("Sommaire")
    sommaire.Range("B1").Value = "Page"
    ligne = 2
    page = 1
    For Each feuille In ActiveWorkbook.Worksheets
        If Not feuille Is sommaire Then
            If feuille.Visible = xlSheetVisible Then
                sommaire.Cells(ligne, 2).Value = page
                page = page + feuille.PageSetup.Pages.Count
                ligne = ligne + 1
            End If
        End If
    Next feuille
End Sub

Chaque appel demande à Excel de calculer les sauts de page de la feuille concernée, ce qui coûte une fraction de seconde par onglet. C’est pour cette raison que Creer_sommaire ne s’en charge pas d’office, et que ce comptage reste une macro à part, à lancer une fois la mise en page arrêtée.

Elle part du principe que la feuille Sommaire ne s’imprime pas, et décale sinon tous les numéros d’une page. Elle suit aussi les seules feuilles visibles, ligne après ligne, si bien qu’elle se désaligne quand citerLesMasquees vaut True et que des noms gris s’intercalent dans la liste.