Software

Use index sheets to quickly navigate in an Excel workbook

Navigating through dozens of sheet name tabs in Microsoft Excel to find the one you need can be quite time consuming. Cut your navigation time in half by creating index sheets that contain a list of hyperlinks to every worksheet in the workbook.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

When working with large workbooks in Microsoft Excel, you can spend a good deal of time navigating through dozens of sheet name tabs to find the one you need. One method for making this task easier is to build an index sheet that contains hyperlinks to every worksheet in your workbook.

To create the index, follow these steps:

  1. Insert a new worksheet at the beginning of your workbook and rename it Index.
  2. Right-click on the sheet tab and select View Code.
  3. Enter the following code in Listing A.
  4. Press [Alt][Q] and save the workbook.

The next time you open the workbook, the Index sheet will contain a list of hyperlinks to each worksheet in the workbook. Each worksheet also contains a hyperlink back to the Index sheet. In this example, the link back to the Index is contained in cell H1, but it can be in any cell that is blank for all worksheets.

Editor's Picks