Create a Table of Contents in Excel

In an Excel file with lots of worksheets, how do you help users navigate through the workbook? Here are a few of the methods I’ve used. I’m sure you’ve found your own creative ways to deal with the problem, and I’d be interested in hearing about them.

Create a Workbook Menu

You can use programming to create a special menu with a list of sheets. Add the menu to the menu bar when the workbook opens, and remove the menu when the workbook closes. John Walkenbach has a nice example of an Excel menu maker.

Add a Drop Down List of Sheets

Another option is to install an add-in that lists all the sheets in the active workbook, in a toolbar dropdown list. Dave Peterson created a Navigation Toolbar for Excel 2003.

Ron de Bruin adapted the code to create a Navigation Command for Excel 2007.

NavToolbarSelect

Create List of Hyperlinks

A non-programming option is to create a list of sheets on a worksheet, then change each sheet name into a hyperlink.

HyperlinkClick

  1. Click on a cell that contains a sheet name.
  2. On Excel’s Ribbon, click the Insert tab (In Excel 2003, click the Insert Menu.)
  3. Click Hyperlink, to open the Insert Hyperlink dialog box.
  4. In the Link To list, click on Place in This Document
  5. In the list of places in the document, click on a sheet name, then click OK.

It will take you a few minutes to set up all the hyperlinks in a large Excel workbook, but they’ll make it easy to navigate through the sheets.
If you have a copy of the Power Utility Pak you can use it to create a table of contents, with either a list of hyperlinks or a set of navigation button.

PUPTOC

Based on sample code from Sam and Andrew in the reader comments below, I’ve posted an Excel Table of Contents workbook in which you can create a popup list or or a list with sheet hyperlinks.
______________________