Help, I Am Lost in this Massive Excel File! A Short Guide to Hyperlinks in Excel

Have you ever opened an Excel file and seen multiple tabs each containing multiple schedules? Did you start working through the file and find yourself frustrated going back and forth between different schedules and / or tabs? In this article, we will address these issues and provide some helpful ways to navigate through a long complex file in a seamless manner. Specifically, we will show you: (i) how to insert hyperlinks (ii) some tips and tricks to make hyperlinks more user-friendly, and (iii) a sample table of contents we build into many of our consulting models.

Benefits of Using Hyperlinks

The primary benefit of using hyperlinks in an Excel file is to allow a user, through the simple click of a mouse, to navigate between different locations within a file. Hyperlinks also serve as a communication tool since the text displayed as a hyperlink provides a description of the area where a user will be navigating.

How to Insert Hyperlinks

Step 1: Locate the cell where you would like the hyperlink to appear.

Step 2: Once the cell is selected, open the hyperlink dialogue box using the keyboard (Ctrl + K).

Steps 3-7 all occur within the dialogue box (see figure below).

Step 3: On the left-hand side under the “Link to” area, click on “Place in this document”.

Step 4: In the “Text to display:” section, type in the text you would like to display in the cell. In the example below we would like to display “Go to Assumptions”.

Step 5: In the “Type the cell reference” section, type the cell reference you would like to be navigated to after clicking the hyperlink. Note: Excel defaults to cell A1. We have kept it at the default A1 in the example below.

Step 6: In the “Select a place in this document” section, choose the location (i.e. tab) where you would like to be navigated to after clicking the hyperlink. Note: Excel defaults to the current tab if you do not choose a specific tab for the cell reference. In our example, we have three tabs in our Excel file and have chosen the Assumptions tab.

Following the above steps, a similar dialogue box to the one below should appear on your screen.

Once you click OK, the hyperlink will appear in the selected cell and if you hover over the cell you should see the cursor change from a “plus” sign to a hand sign (see below). In our example, clicking this cell takes you to Cell A1 on the Assumptions tab.

Making Hyperlinks User-friendly

The first thing you might notice in the figure above is the text “Book1-Assumptions!A1…” that appears when you hover over the hyperlink. Excel is providing the location of where the hyperlink will be taking you. At Training The Street, one of our guiding principles is to make financial models as user-friendly and aesthetically pleasing as possible. Wouldn’t it be nice to be able to change the wording of this text to something more intuitive like “Go to Assumptions”? To do this you would need to navigate back to our dialogue box (Ctrl + K) and click on the Screen Tip button. In the Screen tip dialogue box you can then type in the text you want to appear (see below). Press OK. Now, when you hover over the hyperlink you will have resolved the above issue.

As you can see, when you hover over the hyperlink the text that appears is much more relevant to the contents of the model.