In many situations, people can be very frustrated when something goes wrong. Especially if it is something that usually works without any issues.
One of those things is hyperlinks in Excel. We will show how to fix the issues with hyperlinks in Excel in the text below.
Fix for: Name Change
Hyperlinks in Excel can link to a file, website, or to a certain cell in another worksheet in existing or in another workbook.
To see all the options, we can simply right-click on any cell in our Excel and then click on the Link option:
When we click on this icon, this is the window that we see:
We see that we have options to insert hyperlinks to an existing file or web page, place them in the document, e-mail address, or we can create a new document.
For our exercise, we will link an existing file that is located in the current folder (in a folder where our Excel file is also located) called Hyperlink.docx. We will then click OK.
Now we have a hyperlink linked to the text in cell A2 („Here“):
When we hover over this text, we will see the location of our hyperlink. When we click on it, our word file will be opened.
The first problem that we could encounter is that someone changes the name of our word file. Let us say that we changed its name from Hyperlink.docx to Hyperlink1.docx:
When we get back to our workbook and click on the hyperlink, we will get the following error message:
What we need to do is edit our hyperlink. To do this, we will again right-click on the cell where the hyperlink is located and click Edit hyperlink:
We will choose the right file and click OK:
Fix for: Hash Sign
The second problem you can face is that you will be unable to open the file if it has the hash or “#” sign in the name. If we change the name of our file to be Hyperlink1#.docx, we will still be able to open up the file. We will also edit the hyperlink in the Excel file, but will still have the same error message, i.e. “Cannot open the specified file”.
This is the hyperlink that we cannot open:
We need to rename our file name again to be Hyperlink1.docx, not to have a hash sign. Once we do, we will not have the error message again.
Fix for: Shutdown
Another issue that you can face is when our system has a sudden shutdown, or we are forced to close Excel files due to some error. We will be unable to open our links in this situation.
Luckily, Excel has an option to update the hyperlinks every time the workbook is saved. For this to run smoothly, you could probably first enable the auto-saving option.
To ensure our links are updated on saving, we need to go to the File tab >> Options >> Advanced.
Once there, we will scroll to the General tab and click on the Web Options.
When we click on it, a new window appears. On this window, we need to go to the Files tab and select the option Update Links on save.
We click OK and this option is saved for future use.