A hyperlink is not only for navigation between internet pages! Just like with websites, hyperlinks can be very useful in Excel too. You can even create hyperlinks to sheets or references in an Excel workbook as well. For example, you can generate a hyperlink to the folder by using formulas if you need to access your workbook quickly in your project. In this article, we are going to show you how to create a hyperlink to the current workbook folder in Excel.
Formula
* <cell reference in the workbook>: This can be any cell or reference within the same workbook
** “Friendly Text” is optional. You can omit this if you want.
How it works
HYPERLINK
First of all, the HYPERLINK function creates and displays an active hyperlink without any extra steps. You need to provide the link path itself. Alternatively, you can enter a friendly name to be displayed instead of the path link.
Sample: HYPERLINK(“www.google.com”, “Google”)
The tricky part is to supply the link location the HYPERLINK function. The correct location allows us to create a hyperlink to the current workbook folder. To achieve this, we will introduce the CELL, FIND and LEFT functions.
CELL
The CELL function can return several metadata information about the referenced workbook. It needs information type and a reference as arguments. Using the CELL function with “filename” argument returns the full path of referenced workbook. Remember to supply a reference from your workbook. For instance, a simple A1 reference will do.
Sample: CELL(“filename”, A1)
Since the CELL function returns the folder path along with file and worksheet name, you need to parse the folder name. The FIND and LEFT functions can help us in this step.
FIND and LEFT
The FIND function can detect and return a string’s position in another cell. The LEFT function parses a sub-string from a specified string, starting from the left-most character.
As shown in the following example, the file name starts after a “[“ character.
Thus, we can use the FIND function to detect “[“ character’s position and use it in the LEFT function. Do not skip to add a subtraction by 2 is needed to exclude “\” and “[“ characters at the end of the folder path.
Finally, you can combine all functions to create the formula for creating a hyperlink to the current workbook folder in Excel.