5 Ways to Make an Excel Table of Contents

Overview

If your Excel files have a lot of sheets, you know how cumbersome they can be to navigate.  You may also not be sure what you’re getting yourself into!  One of the first things I do when I inherit a new Excel file to manage is add a table of contents (“TOC”).  It’s an approach I’ve used for over 20 years!  Great news – there are more options than ever for quickly adding a super-useful TOC.

In this article, we’ll look at five different approaches to building an Excel TOC.  Make sure to download the example files below to see these options in action!

Create manually

Why

It may not be the most efficient approach, but there’s nothing stopping you from manually building your TOC (🖼️ example screenshot).  It gives you the flexibility to format the list however you want and include just the sheets you care to reference.  There may be ancillary sheets you don’t want to clutter up your list, and you can just exclude them.  You can also include a quick note or define the purpose of the sheets.

Why Not

It could take a while to build.  And it’s easy to make errors and forget one or more sheets.

How

Simply list out the sheets you want to include in your list.  If you want to add a hyperlink, right-click the cell, then go to Link > Insert Link.  In the prompt, select “Place in this Document” and type the cell reference and then select a sheet in the large box at the bottom (see this 🖼️ screenshot for the Insert Link prompt for an in-file link).

Use formulas

Why

Using formulas is a slightly quicker way to build a TOC, especially the hyperlinks.  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.

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.  While you could reference each sheet individually to build your list, that’s not ideal.  To see the VBA approach with a user-defined function (“UDF”) 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.

Why Not

It’s not totally automated nor dynamic (reflecting added/deleted sheets).  While there’s some copy and paste to speed it up, there are other ways to build a TOC faster.

How

Use a UDF or manually build a list of sheets.  Then use the HYPERLINK function to create a formula-based link and fill that formula down for each sheet (see this 🖼️ screenshot for the result).  The formula I like to use for this is:
=HYPERLINK(“#'”&A3&”‘!A1″,”Link”)

A3 is the cell containing the sheet name.  “A1” is the cell to which the link will direct you in that sheet.  “Link” is the text displayed – feel free to change this to whatever you’d like.

Use the navigation pane

The navigation pane is a relatively new feature to Excel, and you might have missed it.  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 🖼️ screenshot).  It shows a hierarchy of sheets that can be expanded to show ranges, tables, and charts within each sheet.  You can even narrow what is displayed by searching all these elements with the box at the top.  Two key benefits of the navigation pane are 1) it’s already available and 2) it updates automatically as you add/delete sheets, ranges, etc.

Why Not

You might be thinking the navigation pane is the perfect TOC option, and I’d say it’s pretty close.  The only downsides I’ve found with it so far are:

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

How

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.

Use a macro (list)

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

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

Why Not

This requires the use of macros.  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.

How

Simply run the macro to add a TOC sheet at the beginning of the workbook (here’s a 🖼️ screenshot).  It will show every sheet in the file with a hyperlink to each of them.  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.  If you want this and hundreds of macros always available to use, check out the XLEV8 Excel Add-in.

Use a macro (gallery)

Are you more of a visual person and prefer thumbnails instead of a list (or maybe you like both)?  Then maybe the gallery macro approach is for you.  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.

Why Not

This approach also requires the use of macros.  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.

How

Simply run the macro to add a TOC Gallery sheet at the beginning of the workbook (here’s a 🖼️ screenshot).  It will show every sheet in the file as a thumbnail that links to the sheet.  If you add or delete any sheets, simply run it again. This macro is also included in the XLEV8 Excel Add-in.

Macros that automate the TOC

There are several macros I’ve used (some mentioned above) that help automate the process of building and using the TOC.  Here are a few of them:

• Sheet Action Picker – This has several helpful sheet-based macros, including the list and gallery TOC builders covered above, as well as jumping to the first sheet (where the TOC likely is located) and a sheet auto-complete search box.
• Formula Picker – This is helpful for all kinds of formulas – it lets you save your favorites and search for them with an auto-complete box.
• Follow Hyperlink – This mimics the clicking of a cell that contains a hyperlink, whether it’s to another place in the file or an external file/website.  Map it to a keyboard shortcut and you don’t need to click with the mouse!

Summary

The more you use Excel, the more you’ll likely accumulate files with a ton of sheets.  We spend a LOT of time navigating them!  But a TOC can be a major source of time savings.  Whether you take a TOC approach discussed in this article or your own unique approach, it’s a great way to save time and make navigation easier for you and your colleagues.

What’s your favorite TOC approach?  Has it changed over time?  Let us know in the comments below!