How to create direct links to files or folders in Excel with Hyperlink

Do you need to quickly access files, folders, or specific areas within an Excel workbook? You've probably found yourself needing to create shortcuts within your spreadsheets to quickly navigate to documents, folders, or even websites, saving you unnecessary clicks and searches. Well, in Excel, this is possible and extremely versatile thanks to the HYPERLINK function.

In this article we are going to explain to you in a detailed, practical and to the point way, how you can Create direct links to files or folders in Excel with the Hyperlink functionYou'll learn how to navigate between documents, link sheets, access folders, automate the creation of linked file lists, and many other possibilities that will save you time and efficiency in your daily work. You'll see that, no matter your level, after reading this article, you'll be able to create links as complex or simple as you need.

What is a hyperlink and what is it used for in Excel?

Un hyperlink It is an element (text, image or cell) that, when clicked on, takes you to another place: it can be another sheet or cell in your workbook, a file on your computer or network, a folder, a web page, or even an emailIn Excel, hyperlinks are extremely useful because they allow you to quickly navigate between different sources of information and organize your data efficiently.

When you click a hyperlink in Excel, the destination will open in your web browser, video player, or web viewer. PDF or in the folder on your PC, depending on the type of file or link. This makes your spreadsheets become much more dynamic and interactive.

Where can hyperlinks be used in Excel?

• Go to files or web pages on any network, intranet or Internet.
• Access files that do not yet exist but what do you plan to create.
• Send emails from a cell.
• Launch downloads or file transfers by FTP or HTTP.
• Link to specific places within a single book or to other leaves.
• Create folder shortcuts to open them from Excel.

As long as the pointer becomes a hand When you hover over the linked text or image, you'll know you have a hyperlink ready to use.

How does a URL work within a HYPERLINK?

When creating a hyperlink using Excel's HYPERLINK function, you need to specify the destination using a Url addressThis address can take different forms:

• absolute URL: Includes protocol (http, ftp, file), network address, full path, and file name. Example: file://servidor/carpeta/archivo.xlsx
• Relative URL: Omits part of the information, for example, just the file name or a partial path. Excel will use the missing data based on the location in the open workbook.

This is very practical because if you move your files or folders, relative links will continue to work as long as their relative position in the Excel workbook hasn't changed.

The HYPERLINK function: Syntax and basic usage

The function HIPERVINCULO In Excel, it is used to create links from a cell. Its syntax is:

=HYPERLINK(location, [display_name])

• location: This is the path, web address, file address, or folder address you want to go to.
• display name: (Optional) This is the text that will be displayed as a link in the cell.

If you omit the second argument, it will be displayed the management itself as text in the cell.

Practical examples of HYPERLINK in Excel

Let's look at some examples to make it easier for you to understand:

• Link to an Excel file on the web

=HYPERLINK("\\SERVER\Resources\Report.xlsx", "Open report")

This opens a file named Report.xlsx saved in a shared network folder.

• Link to a web page

=HYPERLINK("https://example.com/download", "Download the file here")

Clicking will open the browser to the specified address.

• Link to a specific cell in another sheet or workbook

=HYPERLINK("[Book.xlsx]Sheet2!A1", "Go to Sheet2 - A1")

This takes you directly to cell A1 on Sheet2 in the workbook “Book.xlsx”.

• Link to a local folder

=HYPERLINK("C:\\Users\\MyDocuments\\Folder", "Open Folder")

Open the folder in File Explorer Windows.

• Link using the contents of another cell (dynamic)

=HYPERLINK($Z$1)

If you have an address in cell Z1, you can reference it to create dynamic links.

Create lists of hyperlinks to files automatically

If you have a folder with hundreds or thousands of files and you want to create a list of hyperlinks for each one in Excel, here is a very useful technique with the help of Notepad and a simple macro:

1. 1. Generates the list of files with CMD: Open the Start menu, type 'cmd' and press Enter. Navigate to the desired folder with commands on the table:
      • cd \ to go to the root disk
      • cd folder to enter a folder
   2. In the destination directory, type dir to display all files.
   3. Enlarges the screen buffer (right-click the title bar — Properties — Layout) to see the entire list. Adjust the height and width as needed.
   4. Copy the entire list: Click on the top left corner of the CMD, select 'Edit' — 'Select All' and then 'Edit' — 'Copy'.
   5. Paste it into Notepad to remove any extraneous formatting and select only the file names.
   6. Copy again from notepad and paste it into Excel, for example in the column A from row 4.
   7. Use a formula to extract only the file name if the listing has more information, for example in column B:

=MISS(A4, 37, 255)

1. 1. In cell A2, paste the full path of the folder ending in a backslash (\).
   2. By concatenating the path with the name of each file in column C, you get the full path:

=CONCATENATE($A$2, B4)

1. 1. With this data you can create hyperlinks with the function:

=HYPERLINK(C4, B4)

1. 1. To do it in a more visual and automatic way, you can use a simple macro:

Sub HyperLinksInCells() For Each Cell In Range(ActiveCell, ActiveCell.End(xlDown)) ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:=Cell.Value Next Cell End Sub

1. Select the starting cell, run the macro, and see how Excel creates hyperlinks for all the files listed.

This way, you'll have a list of files from which you can directly open any of them with a single click.

Create hyperlinks from the Excel interface (without formulas)

Another very practical option, especially for less advanced users, is use the Insert > Hyperlink menu from the Excel ribbon itself:

1. Select the cell in which you want to create the link.
2. Go to the tab Insert and choose Hyperlink.
3. A window will open where you can choose:
   • Existing file or web page: Find and select the file, folder, or web page.
   • Place of this document: Creates a link to another specific sheet or cell.
   • E-mail: allows you to create links to send new emails.
4. (Optional) Change the text that will be displayed as the link.
5. Click accept and your link will be ready to use.

Also, if you want to customize the pop-up text (the information that appears when you hover over it), you can click on 'ScreenTip' and type in your preferred text.

Advanced examples and tips

• Link to a named range or cell:

=HYPERLINK("[Book.xlsx]'Sheet1'!TotalDept", "See Department Total")

• Link to bookmarks in documents Word or PowerPoint:

=HYPERLINK("http://example.com/Report.docx#Bookmark", "Go to bookmark in Word")

• Completely customized links:

You can use CONCATENATE combinations or dynamic functions to create variable links based on your needs. This is very useful in dashboards and automated reports.

Differences between absolute and relative paths in Excel

The absolute paths They are fixed and require all the information, from the protocol to the file name. This ensures that the link will work from any location. relative paths, on the other hand, are flexible and adapt if files or the workbook are moved together. By default, Excel creates links relative to the location of the active workbook, but you can change the base address if needed (in the workbook properties or from the 'Insert Hyperlink' box).

Link to other advanced applications and resources

With Excel hyperlinks you can not only open files, it is also possible launch apps if you have the path to the executable, open shared folders on the network with UNC paths (\\servidor\carpeta), or even initiate file transfers via FTP using the appropriate protocol in the address.

For example:

=HYPERLINK("ftp://user:password@server/path/file.txt", "Download FTP file")

Solutions to common problems

• If links to local files don't work, check that the path is correct and use double slashes (\\) in UNC paths.
• If you open your spreadsheet on another computer, absolute links may fail if the folder doesn't exist there. Use relative paths or network paths accessible from any computer.
• To update multiple links at once, manage the destinations from a referenced cell and use absolute references in the formula.
• If you need the link to display specific information, use the second parameter of the HYPERLINK function.

Adding hyperlinks in Excel helps organize large amounts of data, facilitating navigation and efficiently connecting information. Mastering this feature will give you an advantage in managing your documents, improving your productivity and professionalism.