Accounting

Dr. Winston's Excel Tip: How to Use Hyperlinks in Microsoft Excel

10 min read
how-to-use-hyperlinks-in-microsoft-excel-listing-image

When navigating the web, we have all clicked on blue hyperlinks that send us to another web page. In this article, we will show you how to use hyperlinks in Excel to create links in your workbook to web pages or locations in your current workbook. Our work is in the file Hyperlinkswithfunctions.xlsx.

This web page contains help on Excel’s great Flash Fill feature. To create a link to this web page in cell B9 of the worksheet Web Links, first copy the URL and navigate to cell B9 of the worksheet. Then, from the Insert tab, click the Link icon and then choose Insert Link. As shown in Figure 1, we selected “Existing File or Web Page” and pasted the URL in the Address portion of the Insert Hyperlink dialog box. In the “Text to display” section of the dialog box, we entered the “friendly text” that we want displayed in our hyperlink.

how-to-use-hyperlinks-in-microsoft-excel-body-image-01

Figure 1: Creating a Hyperlink to a Web Page

As shown in Figure 2, clicking on the link in cell B9 will send you to the Flash Fill Help web page.

how-to-use-hyperlinks-in-microsoft-excel-body-image-02

Figure 2: Hyperlinks to Web Pages

You can also create hyperlinks to web pages via the Hyperlink function. In D3:D5 we entered three URL’s. In C3:C5 we entered the “friendly text” which will key the hyperlink. Copying from B3 to B4:B5 the formula =HYPERLINK(D3,C3) places a link to the websites in Column D, that is keyed by clicking on the friendly text in Column C.

In the worksheet March,we show how to create hyperlinks to locations in a worksheet. Suppose you want to place in cell F10 a link to cell D10 in the worksheet February. After placing your cursor in cell F10 of the worksheet March, click “Link and Insert Link” from the Insert tab fill in the dialog box as shown in Figure 3.

how-to-use-hyperlinks-in-microsoft-excel-body-image-03

Figure 3: Hyperlink to Cell D10 of Worksheet February

Now clicking on the link shown in Figure 4 sends you to cell D10 of worksheet February.

how-to-use-hyperlinks-in-microsoft-excel-body-image-04

Figure 4: Hyperlinks to Locations in Same Worksheet

The Hyperlink function can be used in conjunction with the copy command to easily create multiple hyperlinks. As shown in Figure 4, we want to create a hyperlink to cell D10 of each worksheet. In F5:F7 we enter the worksheet names, in G5:G7 we enter the cells we want the hyperlink to take us to, and in E5:E7 we enter the friendly text that will key each hyperlink. The syntax to generate the hyperlinks is very tricky! Copying from H5 to H6:H7 the formula =HYPERLINK(“#”&”‘” & F5 & “‘!” & G5,E5) generates the desired hyperlinks in H5:H7. The # sign, ‘, ampersands, and ! must be entered as shown in Figure 4.

Finally, in cell G14 of the worksheetMarch, we used the formula=HYPERLINK(“#Data”,”Data”) to create a hyperlink with the friendly text Data to the range name Data, located in D16:F17 of the January worksheet.

Ready to learn another new Microsoft Excel skill? Here’s how to use Excel’s Stock Data Types.