If you are having trouble hyperlinking one excel spreadsheet to a specific location in another, I have an answer. Although I stopped focusing on Microsoft Office services when I moved into website development, I still use Excel every day and so do many of my clients. To help everyone, here is a solution to the most recent problem I encountered.
The “Hyperlink” Function is Broken
If the two files you want to link are in the same folder, the usual solutions will probably work fine. This article is about what to do when the file is in a different folder and the hyperlink is not working.
A common way to link two files is to go the
Insert tab and click
Hyperlink (or the keyboard shortcut: Ctrl+K), but as far as I can tell, there is no way to choose a particular sheet or cell in the file you are linking to. In that case, use the method below.
How to Hyperlink to a Particular Cell
The following works when the two files are in the same folder. If not, you can start out the same way and then insert the path name so the link will actually work.
- Open the spreadsheet you want to link to and then go back to the first spreadsheet and click on the cell where you want to place the link.
- From the Excel ribbon, click the
Lookup & Reference -> Hyperlink. In the dialog box that opens, click the button to the right of
- A smaller dialog box will open. With that still showing, switch to the spreadsheet you want to link to and click on the cell in the sheet that you want to go to, then press the Enter key or click the button to the right of the field that got filled in when you clicked the cell.
- The first dialog box should reappear, with the
Link_locationfield filled in.
- Type the name that you want to see for the link in the
- Click OK.
- Note the formula that appears when the cell you just entered is selected. (If you do not see the Formula Bar, go to the
Viewtab and click on the check box next to
Formula Bar.) It will look something like this:
=HYPERLINK('[your-file.xlsx]Desired Sheet'!$D$152,"Display Name")
"your-file.xlsx” will be the actual name of your file, “
Desired Sheet” will be the name of your sheet, “$D$152” will be the absolute reference to the cell you want showing on that sheet, and “
Display Name” will be whatever you typed into the
Now Fix the Hyperlink
Did you notice that there is no path name included in the hyperlink? If it is there, you must be using a newer version of Excel than I am, and the link may actually work. If not, you will need to copy the path name into the link. The generic format is like this:
=HYPERLINK("[Full Path To The File]'Sheet Name'!Cell Reference","Friendly Name")
Be careful that every bracket, single quote, double quote, apostrophe and comma is in the right place. Note that the first set of double quotes enclose everything up to the first comma, including the sheet name and the cell address. For our hypothetical example, the correct result would be:
[C:\Documents\Your Subfolderyour-file.xlsx]'Desired Sheet'!$D$152","Display Name")
If you still have any problem with this issue, feel free to leave a comment below and I will see if I can help you fix it!
Website problems, WordPress, and accessibility issues are my specialties now. Got one of those? Contact me!