{"id":3581,"date":"2023-04-12T15:39:33","date_gmt":"2023-04-12T15:39:33","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/"},"modified":"2026-01-15T18:00:15","modified_gmt":"2026-01-15T18:00:15","slug":"taming-the-many-tabbed-excel-workbook","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/","title":{"rendered":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs?"},"content":{"rendered":"

Have you ever opened an Excel workbook and been surprised by the number of sheets it contains?\u00a0\u00a0 Perhaps you are looking for something, or don\u2019t know where to begin?\u00a0\u00a0 This article shares a great trick in Excel if you need to see at-a-glance what all the tabs are in a workbook. \u00a0You can build your own built-in \u201cTable of Contents\u201d and optionally add helpful information as well.<\/p>\n

In Operations or in Financial Planning & Analysis (FP&A) too often we can encounter large overwhelming Excel workbooks (despite those New Year\u2019s Resolutions to put Excel on a diet!).\u00a0 Examples abound, such as in gathering business requirements for a project, building new templates that replace old Excel reports, or trying to trace formulas that involve hidden sheets.\u00a0 All those tabs can be daunting to navigate even for users who work with them on a regular basis.\u00a0 This technique has so many benefits, especially if you have newly hired team-members or for anyone who is new to the data and how it is organized.<\/p>\n

Full Disclosure: this trick uses an old \u201cExcel 4 macro\u201d function which still happens to work in Excel versions through Excel 365.\u00a0 The function is a quicker option than coding a macro using VBA.\u00a0 Microsoft may stop supporting this (and other Excel 4 functions) in the future.<\/em><\/p>\n

Not only is this tip quick to implement in any workbook, but it is also non-invasive.\u00a0 It won\u2019t impact existing data.\u00a0 There is no need to touch or unhide\/hide any of the other tabs when creating this instant list.\u00a0 Once you have generated the initial list of tabs, the results stay put whether or not the macro is saved into the workbook (as a *.xlsm).\u00a0\u00a0 It is easy to take that initial output to make a more useful and formatted \u201cTable of Contents.\u201d \u00a0The Example below is shown from Excel 365, but the steps should be similar in recent individual-license versions.<\/p>\n

Steps:<\/p>\n

    \n
  1. Open the large workbook & insert a new worksheet in the workbook (preferably at the beginning \u2013 but it can be anywhere) If there happen to be many hidden sheets, you don\u2019t need to bother \u201cfinding\u201d the beginning sheet.<\/li>\n
  2. Label the new sheet tab as \u201cTOC\u201d or whatever you prefer<\/li>\n<\/ol>\n

    \"\"<\/p>\n

      \n
    1. Navigate in the Excel top menu to the Formulas ribbon<\/li>\n<\/ol>\n

      \"\"<\/p>\n

        \n
      1. Select the Name Manager feature<\/li>\n<\/ol>\n

        \"\"<\/p>\n

        5. Click on the New button and type in a name (this example uses \u201cLISTER\u201d); leave the Scope as Workbook; the Comment is Optional input; and in the Refers To field, type in this formula =TRANSPOSE(REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””))<\/p>\n

        \"\"<\/p>\n

          \n
        1. Close the Name Manager box and go back in the newly added worksheet. Choose a cell (\u201cA1\u201d here in the example in sheet \u201cTOC\u201d) and type in the following formula\u00a0\u00a0\u00a0 =LISTER
          \n(or = whatever Name you used)<\/li>\n<\/ol>\n

          \"\"<\/p>\n

          7. Once you hit Enter, that formula will populate cells vertically, with the set of cells showing in order the names of all the tabs of the workbook, first (top) to last (bottom). If there are hidden tabs that occur before the new TOC tab, the TOC name will be listed after them.<\/p>\n

          \"\"<\/p>\n

          8. An option at this point is for you to add really useful information to your Table of Contents such as referring to each listed sheet name to make a hyperlink within the workbook and adding comments or color-coding to help explain what sheets do.<\/p>\n

          9. Notes on saving the workbook:<\/p>\n

          a. When you save the workbook with your Table of Contents, if you want the named function to stay \u201clive\u201d, remember to Save-As a macro-enabled workbook.<\/p>\n

          \"\"<\/p>\n

          This ensures a future benefit where after any other new tabs are added to the workbook or tab names change or old tabs removed, the horizontal <\/em>list can be refreshed directly at the cell with the formula (such as A1 with \u201c=LISTER\u201d) by recalculating that cell.<\/p>\n

          As a saving reminder, Excel shows a message similar to this<\/p>\n

          \"\"<\/p>\n

          b. Otherwise, if you must keep the workbook as a *.xlsx file, then the best approach is to copy the formula calculated list of tabs as a Copy-Paste Special-Values. This way the results will stay in place as a static list.\u00a0 In an *.xlsx, the Name Manager will no longer show the function, and the cell having the function formula will not be able to calculate\/will show an error.\u00a0 Clear out the stale formula to remove the error.<\/p>\n

          \"\"<\/p>\n

            \n
          1. For those of you out there who are curious about coding VBA as a macro to list out tabs, the Microsoft.com 365 Blog includes an easy to follow How-To post.<\/li>\n<\/ol>\n

            Whichever path you take, building a Table of Contents should help you gain some X-Ray vision into those massive workbooks that we know are out there!<\/p>\n","protected":false},"excerpt":{"rendered":"

            Have you ever opened an Excel workbook and been surprised by the number of sheets it contains?\u00a0\u00a0 Perhaps you are looking for something, or don\u2019t know where to begin?\u00a0\u00a0 This article shares a great trick in Excel if you need to see at-a-glance what all the tabs are in a workbook. \u00a0You can build your…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[27],"tags":[34],"class_list":["post-3581","post","type-post","status-publish","format-standard","hentry","category-quebit-value","tag-data-management"],"acf":[],"yoast_head":"\nHow Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT\" \/>\n<meta property=\"og:description\" content=\"Have you ever opened an Excel workbook and been surprised by the number of sheets it contains?\u00a0\u00a0 Perhaps you are looking for something, or don\u2019t know where to begin?\u00a0\u00a0 This article shares a great trick in Excel if you need to see at-a-glance what all the tabs are in a workbook. \u00a0You can build your…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:39:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-01-15T18:00:15+00:00\" \/>\n<meta name=\"author\" content=\"agoddard\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"agoddard\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs?\",\"datePublished\":\"2023-04-12T15:39:33+00:00\",\"dateModified\":\"2026-01-15T18:00:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/\"},\"wordCount\":799,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/09\\\/T1-min.jpg\",\"keywords\":[\"Data Management\"],\"articleSection\":[\"QueBIT Value\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/\",\"name\":\"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/09\\\/T1-min.jpg\",\"datePublished\":\"2023-04-12T15:39:33+00:00\",\"dateModified\":\"2026-01-15T18:00:15+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#primaryimage\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/09\\\/T1-min.jpg\",\"contentUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/09\\\/T1-min.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/taming-the-many-tabbed-excel-workbook\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\",\"name\":\"agoddard\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"caption\":\"agoddard\"},\"sameAs\":[\"https:\\\/\\\/quebit.com\\\/askquebit\"],\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/author\\\/agoddard\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/","og_locale":"en_US","og_type":"article","og_title":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT","og_description":"Have you ever opened an Excel workbook and been surprised by the number of sheets it contains?\u00a0\u00a0 Perhaps you are looking for something, or don\u2019t know where to begin?\u00a0\u00a0 This article shares a great trick in Excel if you need to see at-a-glance what all the tabs are in a workbook. \u00a0You can build your…","og_url":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:39:33+00:00","article_modified_time":"2026-01-15T18:00:15+00:00","author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs?","datePublished":"2023-04-12T15:39:33+00:00","dateModified":"2026-01-15T18:00:15+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/"},"wordCount":799,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/09\/T1-min.jpg","keywords":["Data Management"],"articleSection":["QueBIT Value"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/","url":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/","name":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs? - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/09\/T1-min.jpg","datePublished":"2023-04-12T15:39:33+00:00","dateModified":"2026-01-15T18:00:15+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/09\/T1-min.jpg","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/09\/T1-min.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/taming-the-many-tabbed-excel-workbook\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"How Can I Create a Table of Contents in Excel to Easily Navigate Workbooks with Many Tabs?"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084","name":"agoddard","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","caption":"agoddard"},"sameAs":["https:\/\/quebit.com\/askquebit"],"url":"https:\/\/quebit.com\/askquebit\/author\/agoddard\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3581","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=3581"}],"version-history":[{"count":1,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3581\/revisions"}],"predecessor-version":[{"id":4901,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3581\/revisions\/4901"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}