Create an Index Worksheet using Excel Hyperlinks


Have you ever worked in a workbook with only a few worksheets…say three or four sheets? Navigating to the correct sheet is fast and easy, and you spend very little time getting to the spot you need to be to do your work.

Have you ever worked in a workbook with more sheets? Say, 10, 20 , 100 or even 200 sheets? In these large workbooks, it takes a long time to get to the right sheet, doesn’t it? You waste time just trying to get to the right spot so that you can begin your work.

In these large workbooks, it may be useful to have a worksheet up front named Index or something similar. The index sheet simply has a hyperlink to each sheet in the workbook, and each sheet has a link back to the Index.  This means that any sheet is only two clicks away.  This technique can dramatically speed up your work, making you more productive, since you spend more of your time working and less time just getting to the right spot.

If you have never played with the hyperlink feature of Excel, it is a marvelous feature with many different applications. This post focuses on one narrow application, the Index worksheet. Other applications of this feature include digital reporting, creating a collection of related electronic files, documenting the source of numbers, and making it easier for users to navigate within workbooks.

The screenshot below illustrates the Index worksheet idea:

From the Index sheet, any worksheet is just a click away. Similar hyperlinks that return to the Index sheet make any sheet only two clicks away from any other sheet (one click to the Index, and one click to the other sheet). I like the way that the sheets can be organized as well. In the above screenshot, I organized them by function, input sheets, support sheets, and report sheets. However, they could just as easily be organized by year, quarter, department, region, or anything else that makes sense.

Adding a hyperlink is easy. Simply select the cell, and then Insert > Hyperlink. This brings up the Insert Hyperlink dialog box, pictured below.


To set up a link to another sheet or named reference within the workbook, simply click Place in This Document from the Link to panel. (To set up a link to another workbook, folder, or webpage, click Existing File or Web Page instead.) The existing cell value should appear in the Text to display field, but you can change it if desired. Then, pick the destination worksheet and optionally enter a specific cell or range reference, and click OK. You should see that Excel changed the font color to blue and added an underline. This is now a clickable link that should take you to the desired destination.

Additional Notes

It is important to note that with Excel, there seem to be about 3 different ways to accomplish any task. An alternative approach is to use the right-click shortcut. You can right-click the sheet navigation toolbar (little back and next arrows to the left of the sheet tabs) and then click the desired sheet to jump to it. In Excel 2010 and earlier, this shortcut was great when there were only a few worksheets because it displayed the first 15 sheets. Two limitations of this technique were (a) only the first 15 sheets were displayed in the pop up and (b) you couldn’t navigate to the sheet by typing the sheet’s name with your keyboard. Starting with Excel 2013 things have changed, and now the right-click shortcut opens the Activate dialog box, which includes all sheets and allows you to navigate to a specific sheet by typing the name with your keyboard. This was a great 2013 enhancement! Thank you Microsoft!

In addition to the hyperlink feature, Excel offers a hyperlink worksheet function. Using the function rather than the feature affords more flexibility and portability, since you can get creative with the link location and use values stored in cells to define the full path and file name. I’ve created a macro that can assist in generating a hyperlink for each worksheet in the active workbook, and it uses the hyperlink function.  Running the macro cycles through the active workbook and creates one hyperlink back to each worksheet. Feel free to check it out if you think it can save you some time.

Create Index With Hyperlinks

The ability to set up hyperlinks inside of an Excel worksheet that point to other cells, ranges, worksheets, workbooks, web pages, or any other digital file has been handy for me other the years, and I hope you find it as useful as I have.  Excel rules!



This article was written by jefflenning


  1. Ramji

    Thank you for the example – 1 question: The code generates all the tabs in the Spreadsheet:
    * Hidden and Open tabs.
    * Clicking on the hidden tabs in the generated list does not do anything
    * Is it possible to display only open tabs?

    Again, Thanks.

    Ramji –

    1. jefflenning Post author


      Yes, the macro can easily be edited to exclude hidden sheets. All we need to do is add a related test to see if the sheet is Visible. If the sheet is Visible, then, add the hyperlink. Otherwise, skip the sheet and move to the next one. Here is the updated code for reference:

      For Each s In Sheets
      If s.Visible = True Then
      ActiveCell.Formula = “=Hyperlink(“”#'” & s.Name & “‘!A1″”,””” & s.Name & “””)”
      ActiveCell.Offset(1, 0).Activate
      End If
      Next s

      Hope this helps!


  2. Rebekah

    Great instructions and easy to follow.
    Just wondering how to get my Index to hyperlink to graphs that are their own Tab. Can you help with this?

    1. jefflenning Post author

      To my knowledge, Excel doesn’t currently support setting up a hyperlink to a chart sheet. Possible workarounds to this limitation would be to embed your chart into an ordinary worksheet (rather than using a chartsheet) or set up a VBA macro. In practice, my personal preference is to embed the chart into an ordinary worksheet, however, there are some excellent VBA samples online if that is a direction you’d like to explore.
      Hope it helps!

      1. Rebekah

        Thanks Jeff,
        I found that a macro was the best option for my particular needs. This thread has alowed me to develop a monster of a sheet that is now being rolled out not just where I work but around the company.
        Thanks for getting me thingking.

        1. jefflenning Post author

          Rebekah – That is a great update…thanks for sharing! And, congratulations on your monster workbook…way to go…sounds awesome!!


  3. Aparajith

    Thank you Jeff for this post. It really helped.

    1. jefflenning Post author

      Welcome :-)

  4. Kimberly

    This was a wonderfully simple and easy to use solution. Thanks for the post!

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.