While working with a large database we sometimes may need to link other workbooks to the database to get data. But in many situations, the link can be broken or the location of the workbook may be replaced. To face these types of problems we can edit links to get our job done. Today in this article, we will discuss some of the methods to edit links in Excel
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Edit Links in Excel (3 Methods)
When you copy data from a workbook and paste it to another workbook via a link, then the source workbook is linked to the main workbook. Here we will discuss three different approaches to edit the link.
1. Updating Values of Links in Excel
Consider a situation, where we have some sales information of a worker. We have to input this data into a new workbook. To do that, copy the data of the worker.
Now go to the new workbook and instead of normal paste, do Paste Link to create a link between these two workbooks.
Click on the icon and the link is created.
Now let’s say a new update has come and we need to update the values in the source workbook.
Now we have to make sure that the main workbook is also updated automatically. To do that, go to Data, in the Connection Group, click on Edit Links.
A new window appears with different editing criteria. Here, click on Update Values to update your main workbook. Click on Close to proceed.
Data→ Edit Links→ Update Values
Now we can see that our data in the main workbook is automatically updated.
2. Changing Source of Links in Excel
Let’s consider a situation where we have already created a link between two workbooks but the source workbook is misplaced from its current location. There is also some update in the values of the source workbook in its new location. We need to change the source link by editing the link in the main workbook.
To solve this problem, go to Data and click on Edit Links, when the new window appears, click on Change Source.
Data→Edit Links→ Change Source
Now go to the new location where the source workbook is located.
Select the workbook and click Ok to update the source.
Now we can see that our dataset is updated according to the source workbook.
3. Breaking Links of Links in Excel
In the following example, we have a new source workbook where some data are given of a salesman. Copy this data to the main workbook by creating a link between two workbooks.
Now in this procedure, our task is to break the links between the workbooks. To do that, go to Data, click on Edit Links to open the Edit Link Window.
Now select the source on which you want to apply Break Link. After selecting, click on the Break Link option.
A warning window is appears telling that once you break the link, this action cannot be undone. Click on Break Links to proceed.
Now we can see that the link is broken and there is no information about the link in the Edit Link Window.
To check that the break-link is working or not, change some value in the source workbook.
And go the main workbook and we can see that the values aren’t updated. So our job is done here.
Things to Remember
➤ Edit Link will only activate when you create a link between a workbook to another workbook.
➤ Because breaking links is an action that cannot be undone, it is wise to keep a backup copy of your workbook first.
Creating links and editing it can make your work easy and comfortable when the database is scattered and huge. We hope this article proves useful to you. If You have any confusion or suggestion, you are most welcome to let us know through the comment section.