While working with big projects in Excel, we often have to enter links from external sources to various points in our workbook. To reduce complexity, it is a wise idea to remove the external links after certain updates have occurred.
Today I will be showing how to remove external links from your workbook in Excel.
Download Practice Workbook
How to Find External Links in Excel
Before showing how to remove external links from your Excel workbook, I would like to show you how can find all the external links in your workbook.
- To find the external links, go to the DATA>Edit Links tool in Excel Toolbar, under the section connections.
- Click on Edit Links. You will get a dialogue box containing all the external links in your workbook.
This is the most traditional way to look for the external links in your workbook.
But obviously, there are other ways in which you accomplish the task more sophisticatedly.
To learn more ways to find all the external links in your workbook, visit this article.
How to Remove External Links in Excel
1. Removing External Links from Cells
- To remove external links from the cells of your worksheet, go to the DATA>Edit Links tool in your Excel Toolbar under the section Connections.
- Click on Edit Links. You will get a dialogue box containing all the external links.
- Now select the link that you want to remove, and then click on Break Link.
- You will be shown a warning message from Microsoft Excel. Click on Break Link.
- Repeat this process for all the links that you want to remove.
- if you want to remove all the links together, press Ctrl on your keyboard and select all the links. Or press Ctrl + A. Then press Break Link.
- In this way, you can remove external links from the cells of your worksheet.
2. Removing External Links from Named Ranges
There may be external links associated with the named ranges of your workbook. To remove those:
- Go to the FORMULAS>Name Manager tool in your Excel toolbar.
- Click on Name Manager. You will get a window containing all the Named Ranges of your workbook.
- Look at the Refers to option of each Named Range. It contains the source link of the Range.
- Now if you want to remove any link, it is easy. Select the link and click on the option Delete.
- The link will be removed. To remove all the links together, press Ctrl on your keyboard and select all the links. Or press Ctrl + A. Then press Delete.
- Finally, close the window after you remove your desired links.
3. Removing External Links from Pivot Tables
There may be external links associated with the Pivot Tables of your worksheet. To remove that:
- Select any cell in the Pivot Table and go to the PIVOTTABLE TOOLS> ANALYZE>Change Data Source Option.
- Click on Change Data Source. You will get a dialogue box called Change PivotTable Data Source. There, in the Table/Range box, you will get the link to the data of your pivot table.
- Now if you want to remove it, just clear the box and then click OK. The external link from the pivot table will be removed.
4. Removing External Links from Objects
If you have any Object in your Excel workbook with external links, to remove that:
- Go to the Home>Find & Select>Go to Special menu in Excel Toolbar.
- Click on Go to Special. You will get the Go to Special dialogue box. Select Objects. Then click OK.
- All the objects in the workbook will be selected. Move your mouse over each one. The external links with each object will be shown in the formula bar.
- Now, to remove the link, go to the formula bar and clear the formula.
- Then click Enter. Do this for all the objects.
- In this way, you can remove external links from the objects of your Excel Workbook.
Using these methods, you can remove external links from your Excel workbook from all points. Do you know any other method? Or do you have any questions? Feel free to ask us.