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.
As shown in Figure 2, clicking on the link in cell B9 will send you to the Flash Fill Help web page.
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.
Now clicking on the link shown in Figure 4 sends you to cell D10 of worksheet February.
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 worksheet March, 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.
Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including Marketing Analytics, Data Analysis and Decision Making, Operations Research, Practical Management Science, Excel 2019 Data Analysis and Business Modeling, and Mathletics. Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.