Link It

Back in the old days, when I used workpapers that were made with, you know, paper, I used a red pencil to create cross-references that made it easy to navigate to related documents. I  remember feeling very organized because every document was indexed and part of a collection.

Now, things are different. I get annoyed when I need to print something. And, when I do, let’s say to sign it, I immediately scan and shred it. In this digital world, I don’t have any red pencils in my office and I use hyperlinks to create cross-references. Even though I don’t perform audits or attest engagements, I use Excel hyperlinks to make it easy to navigate to documents related to the things I do work on.

In this article, I’ll demonstrate how to use Excel hyperlinks to cross-reference related electronic documents, regardless of file type. Related documents can be just about any digital file—such as scanned PDFs, QuickBooks company data files, Excel workbooks or Word documents.

Excel Hyperlink

When you use your web browser, you encounter hyperlinks on web pages all the time. You see some type of friendly label, such as “Click Here,” and when you click it,you’re navigated to the underlying destination. Excel hyperlinks work the same way. You create a friendly label and define the underlying destination. When you click the hyperlink, you’re navigated to the destination. Excel hyperlinks don’t retrieve values from the destination, or somehow connect or link the cells. An Excel hyperlink simply helps navigate, just like its web-based counterpart.

Excel provides two ways to create a hyperlink: the hyperlink feature and the hyperlink function. Let’s begin with the hyperlink feature.

Hyperlink Feature

To create a hyperlink in the active cell using the hyperlink feature, click the following Ribbon icon:

• Insert > Hyperlink

This will display the Insert Hyperlink dialog. (Figure 1)

Figure 1

We define the two key components of a hyperlink, the friendly label and the destination, using the dialog. The friendly label goes into the “Text to display” field. The destination address is set with the help of the four “Link to” buttons. For example, if you’d like to create a hyperlink to another file, regardless of file type, use the “Existing File or Web Page” button, and then browse to the destination file. When a user clicks the hyperlink, the destination file is opened with the related application. You can use the “Bookmark” button to select a specific worksheet or cell if the destination is an Excel file.

If you’d like to create a hyperlink to another place within the active workbook, such as to another worksheet or cell, use the “Place in This Document” button and then browse to the desired internal destination.

Notes:

• To edit or remove a link, right-click on the hyperlink cell and select the “Edit Hyperlink” or “Remove Hyperlink” menu item as desired.
• Excel doesn’t continuously monitor hyperlinked destination files, so, if you move, rename or delete the file, Excel won’t update the hyperlink. If the destination file isn’t found at the hyperlinked location, Excel will display an error alert.
• If you need to move the related files to another location, such as from the C: drive to the P: drive, you can update the hyperlink base path property. The hyperlink base is prepended to partial path destinations, and can be set on the Summary tab of the Advanced Document Properties dialog (File —> Info —> Properties —> Advanced Document Properties).
• You can create a hyperlink to a folder as well as a specific file by browsing to and selecting the folder.

Hyperlink Function

You can also create a hyperlink using the HYPERLINK worksheet function, which has two arguments, as follows:

=HYPERLINK(link_location,[friendly_name])

Where:

• link_location is the destination; and
• friendly_name is the friendly label, if omitted the link_location is displayed.

Each argument can be expressed as a text string, a cell reference, an Excel function or an expression. For example, we could create a destination using a file name such as “Statement.pdf.” Excel would look for this file in the same folder as the workbook that contains the hyperlink.

If the destination was stored in a different folder, we could specify the full path, such as “P:\Files\Statement.pdf.” Or, if the file name was stored in a cell, we could simply use the cell reference, such as A1.

We can store the hyperlink base in a cell rather than in the Advanced Document Properties dialog and use concatenation to combine it with the file name. For example, if the base path “P:\Files\” was stored in A1, our link location argument could be the expression A1&“Statement.pdf.”

Since hyperlinks are stored in cells, you can place and configure them in various ways, for example, to the right of the related values. (Figure 2)

Figure 2

When a user clicks on the hyperlink, the related document is opened.

If you’re like me, you’re glad there is a convenient Excel alternative to the red pencil we threw away years ago. And remember, Excel rules!