{"id":32963,"date":"2024-09-18T19:58:11","date_gmt":"2024-09-18T19:58:11","guid":{"rendered":"https:\/\/excelevate.co\/wp\/?p=32963"},"modified":"2024-10-11T02:33:35","modified_gmt":"2024-10-11T02:33:35","slug":"5-ways-to-make-an-excel-table-of-contents","status":"publish","type":"post","link":"https:\/\/excelevate.co\/wp\/2024\/09\/18\/5-ways-to-make-an-excel-table-of-contents\/","title":{"rendered":"5 Ways to Make an Excel Table of Contents"},"content":{"rendered":"

<\/p>[et_pb_section fb_built=\u201d1\u2033 admin_label=\u201dsection\u201d _builder_version=\u201d4.16\u2033 custom_padding=\u201d0px||0px|||\u201d global_colors_info=\u201d{}\u201d][et_pb_row admin_label=\u201drow\u201d _builder_version=\u201d4.16\u2033 background_size=\u201dinitial\u201d background_position=\u201dtop_left\u201d background_repeat=\u201drepeat\u201d custom_margin=\u201d||0px\u201d custom_padding=\u201d0|0px|0|0px|false|false\u201d global_colors_info=\u201d{}\u201d][et_pb_column type=\u201d4_4\u2033 _builder_version=\u201d4.16\u2033 custom_padding=\u201d|||\u201d global_colors_info=\u201d{}\u201d custom_padding__hover=\u201d|||\u201d][dipl_modal modal_id=\u201ddiv_toc_pic_1\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_1\u2033 modal_title=\u201dManually-built TOC\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d][\/dipl_modal][dipl_modal modal_id=\u201ddiv_toc_pic_2\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_2\u2033 modal_title=\u201dFormula-driven TOC\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]\n

<\/p>\n

[\/dipl_modal][dipl_modal modal_id=\u201ddiv_toc_pic_3\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_3\u2033 modal_title=\u201dNavigation Pane TOC\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d][\/dipl_modal][dipl_modal modal_id=\u201ddiv_toc_pic_4\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_4\u2033 modal_title=\u201dMacro-driven TOC (list)\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d][\/dipl_modal][dipl_modal modal_id=\u201ddiv_toc_pic_5\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_5\u2033 modal_title=\u201dMacro-driven TOC (gallery)\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d][\/dipl_modal][dipl_modal modal_id=\u201ddiv_toc_pic_6\u2033 trigger_element_type=\u201delement_class\u201d trigger_element_class=\u201dimg_toc_pic_6\u2033 modal_title=\u201dInserting an in-workbook link\u201d _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d][\/dipl_modal][et_pb_text _builder_version=\u201d4.24.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Overview<\/strong><\/h3>\n

If your Excel files have a lot of sheets, you know how cumbersome they can be to navigate.\u00a0 You may also not be sure what you\u2019re getting yourself into!\u00a0 One of the first things I do when I inherit a new Excel file to manage is add a table of contents (\u201cTOC\u201d).\u00a0 It\u2019s an approach I\u2019ve used for over 20 years!\u00a0 Great news \u2013 there are more options than ever for quickly adding a super-useful TOC.<\/p>\n

In this article, we\u2019ll look at five different approaches to building an Excel TOC.\u00a0 Make sure to download the example files below to see these options in action!<\/p>\n

[\/et_pb_text][et_pb_signup provider=\u201dmailerlite\u201d mailerlite_list=\u201dXLEV8_WP_API|85744827291928411\u2033 first_name_field=\u201doff\u201d last_name_field=\u201doff\u201d success_action=\u201dredirect\u201d success_redirect_url=\u201dhttps:\/\/excelevate.co\/wp\/wp-content\/uploads\/2024\/09\/Excel-TOC_Example-Files_2024-09-18.zip\u201d success_redirect_query=\u201doff|off|off|off|off\u201d title=\u201dDownload Example Files\u201d button_text=\u201dGet Example File\u201d description=\u201d<\/p>\n

Want a copy of the examples discussed in this article and video? Sign up for our email newsletter to access this great template and lots of other resources and insights!<\/p>\n

\u201d footer_content=\u201d<\/p>\n

We won\u2019t pass your details on to anyone else. By clicking the sign up button, you agree to our <\/span>Terms of Use<\/a> and <\/span>Privacy Policy.<\/a><\/p>\n

\u201d _builder_version=\u201d4.27.2\u2033 _module_preset=\u201ddefault\u201d form_field_text_color=\u201d#000000\u2033 background_color=\u201dRGBA(255,255,255,0)\u201d background_layout=\u201dlight\u201d border_width_all=\u201d3px\u201d border_color_all=\u201d#000000\u2033 border_width_all_fields=\u201d1px\u201d border_color_all_fields=\u201d#000000\u2033 saved_tabs=\u201dall\u201d global_colors_info=\u201d{}\u201d][\/et_pb_signup][et_pb_text _builder_version=\u201d4.27.0\u2033 custom_margin=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Create manually<\/strong><\/h3>\n

[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Why<\/strong><\/p>\n

It may not be the most efficient approach, but there\u2019s nothing stopping you from manually building your TOC (\ud83d\uddbc\ufe0f example screenshot<\/span>).\u00a0 It gives you the flexibility to format the list however you want and include just the sheets you care to reference.\u00a0 There may be ancillary sheets you don\u2019t want to clutter up your list, and you can just exclude them.\u00a0 You can also include a quick note or define the purpose of the sheets.<\/p>\n

Why Not<\/strong><\/p>\n

It could take a while to build.\u00a0 And it\u2019s easy to make errors and forget one or more sheets.<\/p>\n

How<\/strong><\/p>\n

Simply list out the sheets you want to include in your list.\u00a0 If you want to add a hyperlink, right-click the cell, then go to Link > Insert Link.\u00a0 In the prompt, select \u201cPlace in this Document\u201d and type the cell reference and then select a sheet in the large box at the bottom (see this \ud83d\uddbc\ufe0f<\/span> screenshot for the Insert Link prompt for an in-file link).<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 custom_margin=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Use formulas<\/strong><\/h3>\n

[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Why<\/strong><\/p>\n

Using formulas is a slightly quicker way to build a TOC, especially the hyperlinks.\u00a0 This gives you flexibility and adds some efficiency, as you can fill your formula down for each sheet you want to include in your TOC.<\/p>\n

Note that as of the date of this article, there is no way to easily build formulas to get a list of all sheets in a workbook without using VBA.\u00a0 While you could reference each sheet individually to build your list, that\u2019s not ideal.\u00a0 To see the VBA approach with a user-defined function (\u201cUDF\u201d) that lists all sheets in the workbook, grab a copy of the example files using the form at the bottom or top of this article.<\/p>\n

Why Not<\/strong><\/p>\n

It\u2019s not totally automated nor dynamic (reflecting added\/deleted sheets).\u00a0 While there\u2019s some copy and paste to speed it up, there are other ways to build a TOC faster.<\/p>\n

How<\/strong><\/p>\n

Use a UDF or manually build a list of sheets.\u00a0 Then use the HYPERLINK function to create a formula-based link and fill that formula down for each sheet (see this \ud83d\uddbc\ufe0f<\/span> screenshot for the result).\u00a0 The formula I like to use for this is:
=HYPERLINK(\u201c#'\u201d&A3&\u201d\u2018!A1\u2033,\u201dLink\u201d)<\/p>\n

A3 is the cell containing the sheet name.\u00a0 \u201cA1\u201d is the cell to which the link will direct you in that sheet.\u00a0 \u201cLink\u201d is the text displayed \u2013 feel free to change this to whatever you\u2019d like.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 custom_margin=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

Use the navigation pane<\/strong><\/h3>\n

[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]Why<\/strong><\/p>\n

The navigation pane is a relatively new feature to Excel, and you might have missed it.\u00a0 Located in the middle of the View ribbon tab menu, it opens a persistent pane on the right side of your Excel window (see a \ud83d\uddbc\ufe0f<\/span> screenshot).\u00a0 It shows a hierarchy of sheets that can be expanded to show ranges, tables, and charts within each sheet.\u00a0 You can even narrow what is displayed by searching all these elements with the box at the top.\u00a0 Two key benefits of the navigation pane are 1) it\u2019s already available and 2) it updates automatically as you add\/delete sheets, ranges, etc.<\/p>\n

Why Not<\/strong><\/p>\n

You might be thinking the navigation pane is the perfect TOC option, and I\u2019d say it\u2019s pretty close.\u00a0 The only downsides I\u2019ve found with it so far are:<\/p>\n

    \n
  1. Not many people know about it yet.<\/li>\n
  2. It\u2019s very mouse-driven, so keyboard shortcut fans might not love that.<\/li>\n
  3. It\u2019s not very flexible on formatting (though does it need to be?) and can often seem a little cluttered with all the non-sheet items.<\/li>\n<\/ol>\n

    How<\/strong><\/p>\n

    Simply navigate to the View ribbon tab and click Navigation (near the middle). This will toggle the persistent navigation pane that is displayed on the right side of the window. It displays sheets in a hierarchical view, where the sheets contain ranges, tables, and charts. You can search through all of these items with the search box at the top.[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 custom_margin=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

    Use a macro (list)<\/strong><\/h3>\n

    [\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]Why<\/strong><\/p>\n

    If you like the idea of a list of sheets with links but don\u2019t want to spend time to manually build it, a macro is for you!\u00a0 I\u2019ve used this approach for over 20 years and refined it over time.\u00a0 It\u2019s extremely quick not just to build, but to use.\u00a0 It works well with keyboard shortcuts (i.e. finding sheets, following the hyperlinks, and jumping back again).\u00a0 I\u2019ve refined the macro I use to do three key things:<\/p>\n

      \n
    1. It prompts the user for the cell reference for each sheet (with cell A1 as the default).<\/li>\n
    2. Hidden sheets are displayed as hidden rows in the TOC by default.<\/li>\n
    3. The cell colors for each sheet match the tab colors at the bottom.<\/li>\n<\/ol>\n

      Why Not<\/strong><\/p>\n

      This requires the use of macros.\u00a0 For an approach like this that is not file-specific, I highly recommend including this in an add-in where the macros are always available no matter what file you have open.<\/p>\n

      How<\/strong><\/p>\n

      Simply run the macro to add a TOC sheet at the beginning of the workbook (here\u2019s a \ud83d\uddbc\ufe0f<\/span> screenshot).\u00a0 It will show every sheet in the file with a hyperlink to each of them.\u00a0 If you add or delete any sheets, simply run it again. To see a basic example of this macro at work (including the VBA code), grab a copy of the example files using the form at the bottom or top of this article.\u00a0 If you want this and hundreds of macros always available to use, check out the XLEV8 Excel Add-in<\/a>.[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 custom_margin=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

      Use a macro (gallery)<\/strong><\/h3>\n

      [\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]Why<\/strong><\/p>\n

      Are you more of a visual person and prefer thumbnails instead of a list (or maybe you like both)?\u00a0 Then maybe the gallery macro approach is for you.\u00a0 This is very similar to the list approach above, but shows a gallery of thumbnails that link to each of the sheets in the workbook.<\/p>\n

      Why Not<\/strong><\/p>\n

      This approach also requires the use of macros.\u00a0 For an approach like this that is not file-specific, I highly recommend including this in an add-in where the macros are always available no matter what file you have open.<\/p>\n

      How<\/strong><\/p>\n

      Simply run the macro to add a TOC Gallery sheet at the beginning of the workbook (here\u2019s a \ud83d\uddbc\ufe0f<\/span> screenshot).\u00a0 It will show every sheet in the file as a thumbnail that links to the sheet.\u00a0 If you add or delete any sheets, simply run it again. This macro is also included in the XLEV8 Excel Add-in<\/a>.[\/et_pb_text][et_pb_text _builder_version=\u201d4.27.0\u2033 _module_preset=\u201ddefault\u201d custom_padding=\u201d||0px|||\u201d global_colors_info=\u201d{}\u201d]<\/p>\n

      Macros that automate the TOC<\/strong><\/h3>\n

      There are several macros I\u2019ve used (some mentioned above) that help automate the process of building and using the TOC.\u00a0 Here are a few of them:<\/p>\n