If our Excel worksheet contains a link to data from another worksheet that has been changed to another location, every time we try to open our workbook in Excel we will get an error message informing us that some links cannot be updated. This means that if we save external data in our worksheet, it is currently not available.
What are the links and reasons causing the problem
Excel has a tool called Links or External References that will allow us to copy all the data from one sheet and paste it into another, both within the same book and different books. This link means that, if the data of the source sheet changes, this change also affects and is carried out in the destination sheet. It is important to clarify that this function is different from that of copy and paste or hyperlinks, since no updated linking process is carried out in these ways. In this way, every time the source content of the link is updated, it will also be carried out in the book where we have linked it.
When working with links, a warning message may appear indicating that it is not possible to update some of the links in our workbook at a certain time. If this happens we can continue without updating or editing the links that we create may be wrong. This can happen for a number of reasons, either because the source file no longer exists, the worksheet was protected, or the location of the source file has been changed.
Ways to fix error updating links in Excel
Next, we are going to see different actions that we can take to try to end the problems when updating links in Microsoft Excel.
Find external references to fix the problem
In our book, all cells that have links or external references have the name of the book they are linking to, so the cell will contain the extension of the book. This is something we can use to find cells and repair them to fix the update issue.
To do this in the Home tab, click on the button «Search and select», and click on «Search». In the text field we must enter * .xl * and click on the Search all button. In the box at the bottom it will show us all the cells that contain that link. Once the link that gives us an error is found, we can delete or modify it in order to solve it.
Disable the error message when updating links
In our Excel workbook we may make external links to other workbooks. In this case, when we try to open our Excel workbook with external links, it will show us a warning message. To correct the error message we can disable it within Excel Options.
Click on the File tab and then on the Options button. In the new window that will open, click on Advanced in the left panel. Later, in the panel on the right, in the General section, we uncheck the Consult box when updating automatic links. Finally, click on OK to close the window. From now on, when we open a workbook, the links will automatically update without receiving any error messages.
Control update of links
By creating links with other textbooks we can control whether they are updated or not. For this, both the book from where we create the link and the book where we link must be accessible at the time of opening. Once both workbooks are opened on the same computer, the link is automatically updated. In case we do not have the origin open, a message will appear asking if we want to update the data.
To update the links, within the “Data” tab, we must click on the “Update all” button.
Unlock a protected spreadsheet
Sometimes we can find that the problem to update links in Excel can happen when we have the worksheet protected. If we have established a password that protects our sheet, we can try to eliminate it to see if it solves the problem.
To do this, it will only be necessary to right-click on the “Sheet” tab, at the bottom left. This will make a contextual menu appear where we must choose the option of «Unprotect sheet«. In the event that our spreadsheet is password protected, we must enter the correct password and click Apply. In the event that it is not locked by any password, clicking on Unprotect Sheet will unlock the protected sheet automatically.
Alternatively we can also unlock a protected spreadsheet from the “Review” tab that we find in the upper right part of Excel. From here, in the “Protect” section, click on the “Unprotect sheet” button.
Repair broken links
Before making this option it is convenient that let’s keep a backup from our Excel workbook. Later we open the Excel workbook that shows us an error with the links and click on the “Data” tab. Here we will see that the “Edit Links” command is not available if our book does not contain any links.
In the “Source file” box we must select the broken file that we want to correct and click on “Change source”. Now we only have to find the destination of a file with which we have linked data, we choose a new source file and click on Change source.
Disable DDE (Dynamic Data Exchange)
The dynamic data exchange protocol DDE It was designed by Office to be able to exchange data between the different applications that compose it, for example, between PowerPoint and Excel, Word and Excel, etc. When we try to open a document with this protocol, a warning window will appear indicating if we want to update the data of the files that have been linked.
If we want to deactivate it, we can click on the “File” and “Options” tab. In the new window in the Advanced section, we look in the “General” section and check the box “Skip other applications using Dynamic Data Exchange (DDE)” in Excel.
