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.
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.
- Click on a cell that contains a sheet name.
- On Excel’s Ribbon, click the Insert tab (In Excel 2003, click the Insert Menu.)
- Click Hyperlink, to open the Insert Hyperlink dialog box.
- In the Link To list, click on Place in This Document
- 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.
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.