by

Creating Hyperlinks In Excel

Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.

When you open the report in the Power BI service, the hyperlinks work there, too. Create a table or matrix hyperlink in Excel Power Pivot. Another way to add hyperlinks to your Power BI tables and matrixes is to create the hyperlinks in the dataset before you import/connect to that dataset from Power BI. This example uses an Excel workbook.

We you could use a filter, but I found another way to go straight to the code you want using a hyperlink.

To be more specific, the HYPERLINK function.

I have written about HYPERLINK before – check out this blog post.

Create hyperlinks in excel multiple

How to Create a Hyperlink to a File in Excel: Step 1: Enter a value under the File column with a file name you want to open from your computer. Here we have entered the name “ Tutorial ”. Step 2: Open Hyperlink and from Insert Hyperlink dialog box click the Current folder and select your file for the hyperlink. (6) Link to create a new file. This type of hyperlink is rarely used in Excel. It typically means that when someone clicks the hyperlink, it will open another new Excel file. Right-click cell B2 and select Hyperlink from the pop-up menu. A new Insert Hyperlink dialog box appears. Click the Create New Document button. Open Excel Workbook -Go to File - Options - Advanced. Scroll down to find the General tab and click on Web Options. Web Options Window pops-up. On Web Options, pop-up, Click on Files and Uncheck the box for “ Update Links on save ”. Click OK button and your option is saved. The steps are also explained in the image below: Uncheck the.

This time I will enter a code in a cell and then use a few functions to do all the work for me. Ultimately they will work together to create a link to the cell that contains the code.

This is a simplified example but it will work for larger tables. It will find the first instance of the code.

The image below is our simple table.

As you can see I have a formatted table named tblCode. I use tbl as a prefix for all my formatted tables. This has advantages I will discuss in a future blog post.

I used Ctrl + T to create the formatted table and then renamed it at the top left in the Design ribbon tab.

The image below is our Search sheet where we create the hyperlinks.

I enter a code in the yellow cells in column A and click the word Link in column B and magically I am teleported to the correct cell on the Data 1 sheet.

I can even press F5 and then press Enter to return to the Search sheet just like a normal hyperlink.

The Magic

The magic happens in the Helper column – column C. The formula in that column creates the exact reference required by the HYPERLINK function.

I have mentioned in a previous blog post that using the HYPERLINK function can be tricky as it requires exact names and addresses for the hyperlink to work.

The formula in cell C2 which has been copied down is

The # symbol at the starts is how you cope with spaces in hyperlinks.

The CELL function is an information function – in this case it returns a text string that describes the cell where the code can be found – perfect for our requirements.

We reference that cell in the CELL function using the good ‘ol INDEX-MATCH combination.

This demonstrates the power of the INDEX function. INDEX can refer to a cell rather than return a value. VLOOKUP for example can only ever return a value from a cell.

The MATCH function returns a number that refers to the position of the code being searched for within the table reference.

The INDEX uses the MATCH result to specify the cell, in this case cell A4.

If you used the above INDEX function by itself in a cell, it would return the code ABCD1236, much like the following VLOOKUP.

But when you use the INDEX with a function that requires a cell reference (like the CELL function) the INDEX will return the cell reference rather than the value in the cell, in this case the cell reference is Data 1!A4.

The formula in cell B2 which has been copied down is straightforward. The formula is

The IFERROR stops an error message displaying in column B when there is an error in column C. The error is column C is caused when either there is nothing in column A or the code in column A isn’t in the list in the Data 1 sheet. I have left that error in column C and would probably hide column C in a working model.

As you can see the HYPERLINK function itself is very simple

Cell C2 did all the work.

You could get rid of the Helper cell and create one long formula in cell B2 like

The file I used can be downloaded at the link below.

Related Posts

Excel spreadsheets are ideal for creating detailed records or storing large amounts of data.

However, with hyperlinks in Excel, you can take this one step further by transforming your spreadsheet into an entire filing system that links to multiple other documents.

Creating

For example, if you have a spreadsheet that keeps track of your expenditure, you could link a scanned-in image of every invoice or receipt that you received to this spreadsheet. Each of these documents could then be opened up by simply clicking on the hyperlink (see below).

How to Create a Hyperlink in Excel

The following steps describe how to create a hyperlink in Excel:

Creating Hyperlinks In Excel
  1. Select the cell that you want to add the hyperlink to;

  2. Open up the 'Insert Hyperlink' dialog box by either:

    • Selecting the Hyperlink option from the 'Insert' tab of the Excel ribbon:

    or

    • Using the mouse to right click on the cell and then selecting Hyperlink from the right-click menu:

    or

    • Using the keyboard shortcut Ctrl + K.

    Any of these three options will open up the Insert Hyperlink dialog box, shown below:

  3. Type the text that you want to display in the Excel worksheet into the Text to display: field at the top of the dialog box, and then use the options in the dialog box to define your link address.

    The available types of hyperlink are listed on the left hand side of the 'Insert Hyperlink' window. These are described, individually, below:

    Hyperlink to an Existing File or Web Page

    The default option in the 'Insert Hyperlink' dialog box is to create a hyperlink to an Existing File or Web Page.

    You can either navigate to the file or web page or you can type the address of the file or web page directly into the Address: field of the dialog box.

    Hyperlink to a Specified Place Within a File

    If the file you link to is an Excel file, you can set the hyperlink to go to a specified place within the file that you link to.

    To do this, first select the file that you want to link to and then:

    1. Click on the Bookmark... button (which is located on the right hand side of the 'Insert Hyperlink' dialog box).

      This causes the 'Select Place in Document' dialog box to open (see rightabove).

    2. The 'Select Place in Document' dialog box gives you the option to select a specific place within a document.

      For example, the above dialog box on the right will link to cell B5 on Sheet2 of the selected Excel workbook.
    3. Once you have specified the location that you want to link to, click OK to close the 'Select Place in Document' dialog box and then click OK within the 'Insert Hyperlink' dialog box.

    Hyperlink to a Place in This Document

    The second option on the left of the 'Insert Hyperlink' dialog box is to Hyperlink to a Place in This Document.

    If you select this option, you will be presented with options that allow you to select a worksheet and specify a cell reference within the current Excel workbook.

    In the example shown above (right), the hyperlink will take the user to cell C3 on Sheet3 of the current Excel workbook.


    Hyperlink to a New Document

    The third option on the left of the 'Insert Hyperlink' dialog box is to create a hyperlink to a New Document. If you select this option, you will be presented with a window that asks you to specify the name and location of the required new document. If you enter these details, and click OK, a new Excel document will be created and hyperlinked to.

    Hyperlink to an E-Mail Address

    The final option on the left of the 'Insert Hyperlink' window is to create a hyperlink to an e-mail address. Selecting this option allows you to specify an e-mail address to link to, either by typing the address directly into the window, or selecting from recently used e-mail addresses.


Further information on creating, editing and deleting hyperlinks in Excel is provided on the Microsoft Office Website.


Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page