Accounting professionals spend much of their time working in Microsoft Excel. If you're handling accounting for your organization, you'd likely appreciate any shortcuts or tricks to make your time in worksheets more efficient and to make your reporting more valuable. Dr. Wayne Winston, Becker CPE instructor and Excel expert, has written several articles providing tips and tricks for Microsoft Excel. This one will cover how to create links in Excel. Hyperlinks in Excel are a great way to connect information across workbooks, or include external references in your Excel worksheet. Dr. Winston will detail how to hyperlink in Excel, linking to web pages and references within a single workbook.
How to insert hyperlink in Excel
When navigating the web, we have all clicked on blue hyperlinks that send us to another web page. Can you hyperlink in Excel? Absolutely! In this article, we will show you how to insert links in Excel to connect your workbook to external web pages or other locations within your current workbook. For this tutorial, we'll work in the file Hyperlinkswithfunctions.xlsx.
Microsoft Excel has created a web page dedicated to using Excel's great Flash Fill feature. To create a link in Excel in cell B9 of the worksheet Web Links, use the following instructions.
- Copy the URL and navigate to cell B9 of the worksheet.
- 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.
Figure 1: Insert hyperlink in Excel
As shown in Figure 2, clicking on the link in cell B9 will send you to the Flash Fill Help web page.
Figure 2: Hyperlink in Excel to connect to a web page
You can also hyperlink in Excel via the Hyperlink function. In D3:D5 we entered three URLs. 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 links in Excel that connect 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.
Figure 3: Create link in Excel to Cell D10 of Worksheet February
Now clicking on the link shown in Figure 4 sends you to cell D10 of worksheet February.
Figure 4: Hyperlinks in Excel 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.
Learning to hyperlink in Excel can be a useful tool as you tell the financial story of your organization. It connects the dots between different figures within your workbook, but can also point to other online resources.
Ready to learn another useful Microsoft Excel tool? Here’s how to use Excel’s Stock Data Types.
