Microsoft Excel is an integral part of any professional environment, reliable for budgeting, income tax details, and other kinds of data requiring tabular form.
Add a couple of formulas, macros, and you have a friendly, capable database-based information at your disposal.
Frequently we try to add links to specific files, locations located on particular servers, and partitions on a computer.
We do that to call them for reference for a report, statistical data, etc., to enunciate a point because displaying the entire data set is not feasible and unnecessary in most of the cases.
Reasons for the Error
When you implement a bunch of hyperlinks into the sheets containing your reports, sometimes the hyperlinks fail to work, and therefore the reports look out of place.
Changes in hyperlinked file names can be a common reason behind the error. Saving the files twice at once may add a prefix, such as "(1)" to the file name.
Computers shut down abruptly while the workbook was open. It can send the data and formatting into a jiffy.
The hyperlinks are not configured correcting and may be missing the allowed operators, symbols.
The file in question has been misplaced, moved to a place with an address different from the one given.
Before you read the below solutions, you can also learn the best methods to solve the issue 'Excel cannot open the file because of file format'.
Solution #1- Match the hyperlinked file names
Have you renamed the targeted files recently? Or have it modified by itself due to file reshuffling.
Change in the targeted files can break the hyperlinks, therefore making them ineffective.
We agree that manually changing links is a tedious task. Still, it can be streamlined using free clipboard tools such as Ditto, which makes manually copy-pasting easier that you would imagine.
Solution #2- Update Links on Save
When the computer shutdowns abruptly, your worksheets can suffer from data loss or corruption. Hyperlinks to the files may not get saved either.
To make things easier, Excel ships with an option to update your hyperlinks on every save.
Note: We are working with Microsoft Excel 2013. The places of settings may differ in other versions of MS Office.
Open your workbook and head to Files > Options.
The options window will appear on the screen, consisting of several settings that can be configured according to user preference.
Head to the Advanced tab of the Excel options.
Scroll down to the General section inside the Advanced tab. Find and click on the web options.
Head to the Files tab in the web options. There you will find a tickbox, beside which the text says, "Update links on save." Check the box and click on Ok to save and apply the changes to the settings.
From this point forward, every time you save the workbook, the changes to the hyperlinks pointing to specific files will be updated and saved automatically.
Solution #3 - Use the Correct Hyperlink Syntax
Microsoft Excel's HYPERLINK function works on a predetermined set of the syntax that should be free from any kind of errors and ambiguity.
There are hundreds of guides on Excel syntax, but you can start with Microsoft's official guide on hyperlinks functions.
Solution #4 - Add File's location to Trusted Locations
Excel keeps a small number of locations in its trusted locations from where the worksheet program prefers to open a file via the Hyperlink.
As a result, we will add a new entrant to the Trusted locations list in our attempt to solve the issue at hand.
Go to Options from the File menu. Excel options window will appear on the screen as we did earlier.
Head to the Trust Center Settings from the Trust Center tab.
At the bottom of the page, click on the option that says Add New Location option.
The wizard of Microsoft Trusted Location would appear on the screen. Add the location of your choice that you wish to add for future references.
In any case, if you want to include the subfolders as well, then check the box that says Subfolders of this location are also trusted. It will consist of all the folders in the location that is marked as trusted.
Solution #5 - Provide support for Opening the File
Files of different types, formats required for opening various kinds of applications on your computer. For instance, opening a PDF document will need an application capable of opening PDFs on your computer.
If you cannot find a particular file/document on your computer, then it will not open in excel either. It relies on third-party applications to provide support for files of varying formats.
First of all, let's find out the format of the file.
Go to the properties of a file from the right-click menu.
Alternatively, you can also double-click while holding the alt key to open the properties.
There you will find the type of the file and its extension. You may have a program installed already which can open the respective file.
Right-click on the file and head to the Open With menu to find the right program to open it.
When you cannot find the application that can open it, you can use this free tool to find the appropriate software for opening it.
Search for the file type, extension, and it will suggest programs, applications for opening it.
Dealing with an MS Excel error is a tough nut to crack, especially when it is as rare as we discussed.
The key to avoiding the "cannot open the specified..." error is to ensure proper usage of the allowed syntax in the workbook.
When opening files located on a server, make sure to make an exception in the firewall.