In this article, we will learn to hyperlink multiple PDF files in Excel. Sometimes, we need to add different kinds of files to our Excel worksheet for various purposes. In Excel, we can link the address of multiple PDF files to specific texts or to specific cells. So, today, we will demonstrate 3 methods. In these methods, we will show how to link multiple PDF files using the HYPERLINK Function and also without using it.
Excel HYPERLINK Function Introduction
The HYPERLINK Function creates a shortcut in a specific cell. If you click on the shortcut, it will open the linked document. The document can be stored on the hard drive of the computer, a network server, or on the internet.
- Syntax
HYPERLINK(link_location,[friendly_name])
- Arguments
link_location: It is the compulsory argument of the function. Here, we define the cell that contains the address link.
[friendly_name]: This is the second argument of the function and it is optional. We put the text that should be displayed in the cell using the double quotation sign.
For example, you want to hyperlink a file in Cell B4. If Cell A2 contains the link location and you need ‘Click here to download’ to be displayed, then, you need to type the formula below in Cell B4:
=HYPERLINK(A2,”Click here to download”)How to Hyperlink Multiple PDF Files in Excel: 3 Ways
To explain these methods, we will use a dataset that contains information about the Department and Appointment Letters of some employees. In the Appointment Letter column, we will hyperlink the PDF of the letter to all employees.
1. Hyperlink Multiple PDF Files Using Excel Insert Tab
In the first method, we will use the Insert tab from the ribbon to hyperlink multiple PDF files in Excel. This method is very effective when you need to hyperlink a small number of PDF files.
Let’s follow the steps below to learn this method.
STEPS:
- First of all, select the cell where you want to link the PDF. We have selected Cell D5 here.
- Secondly, go to the Insert tab and select Link. It will open a drop-down menu.
- Select Insert Link from the drop-down menu.
- Alternatively, you can also do it from the context menu. For that, you need to right-click on Cell D5.
- Then, select Link from the context menu.
- After that, the Insert Hyperlink window will appear.
- Select the file that contains the PDF files using the drop-down icon.
- Now, select the appointment letter of Mike as Row 5 contains information about Mike. You have to select the respective PDF file for each employee.
- Click OK to proceed.
- After clicking OK, you will see that Cell D5 has been linked successfully.
- Finally, do the same for the rest of the cells to hyperlink multiple PDF files in excel.
Read More: How to Activate Multiple Hyperlinks in Excel
2. Use of Command Prompt to Hyperlink Multiple PDF Files in Excel
There is another way to hyperlink multiple PDF files in excel very easily. In this method, we will use the command prompt window and also implement the HYPERLINK Function. Here, we will use the same dataset. But, we will keep the Appointment Letter column empty.
Let’s pay attention to the steps below to know more.
STEPS:
- In the first place, go to the file that contains the PDF files.
- The order of the PDF files should be the same as the order of the employees. This means Letter 1 must contain the appointment letter of Mike. In this way, Letter 2, Letter 3, Letter 4 & Letter 5 should contain the appointment letters of Tom, Paul, Josh & Carry respectively.
- Secondly, type ‘cmd’ in the Address bar like the picture below.
- Then, hit Enter to open the command prompt window.
- Thirdly, type the below command in the window:
dir/b/s/0|clip- And press Enter.
- After that, go to your excel sheet and select a cell. Here, we have selected Cell B11.
- Now, press Ctrl + V to paste the address of the PDF files.
- After pasting the addresses, select Cell D5 and type the formula:
=HYPERLINK(B11,"Download")
- Hit Enter to see the result like the picture below.
Here, this formula links the address of Cell B11 and defines the friendly name as Download.
- Finally, drag down the Fill Handle to hyperlink the rest of the cells.
- If you click on the Download, it will show a message. You need to click OK to open the PDF file.
Read More: How to Hyperlink Multiple Cells in Excel
3. Simple Excel Formulas to Hyperlink Multiple PDF Files
In the last method, we will use the HYPERLINK Function to link multiple PDF files. Here, we will create a drop-down list first and then, apply some simple formulas. This technique is very effective for large datasets. Here, we will use the dataset of Method-1.
Let’s observe the steps below to learn this method.
STEPS:
- At the start, go to a new sheet and create a structure like the below image.
- Then, select the cell that will contain the drop-down list. Here, we have selected Cell C4.
- Select, select the Data Validation option from the Data tab.
- Now, select List in the Allow field of the Data Validation window.
- Then, select the Source field.
- After that, go to the sheet that contains the name of the employees and select the names. The name of the employees should be unique.
- Click OK to proceed.
- Instantly, you will see a drop-down list in Cell C4.
- Next, we need to hyperlink the PDF files. The name of the PDF files should be the same as the name of the employees.
- To do so, open the file that contains the PDF files.
- Click on the right side of the Address bar.
- Then, press Ctrl + C to copy the address.
- After that, select Cell B7 of the sheet and press Ctrl + V to paste it.
- Again, select Cell B8 and type the formula:
=B7&"\"&C4&".pdf"
Here, this formula is creating link locations for the respective PDF files.
- Press Enter to see results like the below screenshot.
- Furthermore, select Cell C5 and type the formula:
=HYPERLINK(B8,"Download")
- Finally, hit Enter and choose a name from the drop-down list to see results like the below.
Here, the HYPERLINK function creates a shortcut for the link stored in Cell B8 and defines Download as the friendly name.
Read More: Excel Hyperlink with Shortcut Key
Things to Remember
There are certain things we need to remember if we use the above methods.
- Method-1 is very effective for a small number of PDF But not handy for large datasets. Use Method-2 & Method-3 instead.
- In Method-2, the PDF files should be arranged in the same order as the order of the employees. Otherwise, it will hyperlink the wrong PDF file.
- In Method-3, the name of the PDF files must be the same as the name of the employees.
Download Practice Book
Download the practice book here.
Conclusion
We have demonstrated 3 easy and quick methods to hyperlink multiple PDF files in Excel. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
