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.

Navigation Pane

Some versions of Excel include a Navigation Pane, which is an alternative to creating links to specific sheets or references of interest manually. You can quickly determine if your version of Excel has the Navigation Pane feature by heading to View > Navigation. When you toggle it on, you will see a list of sheets and many additional objects. Clicking on one will navigate you there immediately.

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!

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

11 Comments

  1. Ramji on September 25, 2014 at 2:12 am

    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 –

    • jefflenning on October 2, 2014 at 9:43 am

      Ramji,

      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!

      Thanks,
      Jeff

  2. Rebekah on October 26, 2014 at 12:30 pm

    Hi,
    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?

    • jefflenning on October 29, 2014 at 2:10 pm

      Rebekah,
      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!
      Thanks
      Jeff

      • Rebekah on October 29, 2014 at 2:45 pm

        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.

        • jefflenning on October 30, 2014 at 1:24 pm

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

          Thanks
          Jeff

  3. Aparajith on June 15, 2015 at 12:01 pm

    Thank you Jeff for this post. It really helped.

    • jefflenning on June 19, 2015 at 12:54 pm

      Welcome 🙂

  4. Kimberly on December 18, 2015 at 5:19 pm

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

  5. Heather on October 8, 2020 at 1:02 pm

    This is great; it just helped me index a workbook (which was created prior to me coming onto the project) with over 800 sheets!

    Question though; I’d like the index sheet to also pull data from a couple of fields from each corresponding sheet, and I’m having a lot of trouble sorting that out. Is there a reasonably simple formula for that, when the range is over 800 sheets?

  6. Pablo on March 31, 2022 at 8:40 am

    thanks to you, now I have a complete index with all the diferent spreadsheets I have to handle for work.

Leave a Comment