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.
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.
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.
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!
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.