How-To

How to Find and Remove External Links in Excel

By Adam Davidson

Published April 26, 2023

Outdated or broken external links can cause issues with your Excel spreadsheets. Learn how to find and remove external links in Excel in this guide.

Microsoft Excel is a powerful spreadsheet application that’s packed with useful features. One of those useful features is the ability to reference data that’s outside of the spreadsheet itself. Excel allows you to add references to data in other spreadsheets and use that data as if it’s part of your current spreadsheet.

Sometimes, however, these external links can cause issues. They may be out of date, be broken, or have other issues. You may also not want to include external references if you’re sending a file to someone else who doesn’t have access to the files that they reference.

Getting rid of external references from your Excel spreadsheets is usually fairly straightforward, but you may find that there are some stubborn ones that are harder to get rid of. If you want to know how to find and remove external links in Excel, follow the steps below.

How to Break External Links in Excel

Excel has a feature that helps you to find external links and gives you the option to break those links if you no longer need them. Whilst this is very useful, it doesn’t always find all of the links if your document, particularly if links are somewhere other than in a cell.

However, this is definitely a good place to start if you want to find and remove your Excel external links.

To break external links in Excel:

1. Open the Excel workbook containing your external links.
2. Click the Data tab.
   
3. In the Queries & Connections section of the ribbon, click Edit Links. If this button is grayed out, it means that Excel cannot find any external links.
   
4. You will see a list of all of the documents that are being referenced externally.
   
5. Click on a link you want to break. To select multiple links, hold down Ctrl as you select each link, or press Ctrl+A to select them all.
6. Click Break Link. This will convert the contents of any cells that reference external files to their current values.
   
7. If you’re sure you want to remove the links, click Break Links to confirm.
   
8. Your selected links should now be broken. If you chose to break them all, the Edit Links button should now be grayed out, indicating that Excel can find no more external links in your document.
   

How to Find and Remove External Links in Objects

Unfortunately, the method above may not remove all of the external links in your spreadsheet. There may still be some lurking in other places. If this is the case, you can find and remove them manually.

One place that you may find some is in Excel objects, such as shapes or text boxes.

To find and remove external links in Excel objects:

1. In the Home tab, click the Find & Select icon.
   
2. Select Go To Special.
   
3. At the bottom of the window, click Objects.
   
4. Click OK and any objects in your spreadsheet will be selected. To move through the objects, press Tab.
5. For each object, look in the formula bar for any references to external worksheets. If you find any, you can delete these external references directly in the formula bar.
   
6. Hover over the object to see if the object is linked to any specific file.
   
7. If an object does link to a file, right-click it and select Remove Link.
   

How to Find and Remove External Links in Charts

External links can also be found in your Excel charts. There’s no simple way to search for these links in charts, so you’ll need to work through your charts manually.

To find and remove links in Excel charts:

1. Click on different parts of your chart. There may be external links in the chart data series, or even in the chart title.
2. When you click on each part of your chart, look for any external links in the formula bar. If you find any, you can delete or replace them.
   
3. If your chart includes several data series, you’ll need to click each one in turn to check it.

How to Find and Remove External Links in Pivot Tables

Pivot tables are another place where external links may be hiding. Once again, you’ll need to examine each pivot table in your spreadsheet; there’s no simple way to find search them all at once.

To find external links in Excel pivot tables:

1. Click somewhere within the pivot table.
2. Click the PivotTable Analyze tab.
   
3. Select Change Data Source.
   
4. Examine the Table/Range field for any external links which you can remove if necessary.
   

How to Find and Remove External Links in Named Ranges

A clever way to quickly refer to multiple cells in Excel is to create named ranges. These named ranges can be referenced in formulas or even just to quickly navigate to the appropriate cells. Sometimes these named ranges may refer to cells outside of your current spreadsheet. In other words, they contain external links.

To find external links in named ranges in Excel:

1. Click the Formulas tab.
   
2. In the Defined Names section, click Name Manager.
   
   
3. Look in the Refers To column for any external links.
   
4. If you want to remove a named range that contains external links, click Delete.
   

How to Find and Remove External Links in Cells

You can also search for external links in cells manually using Excel’s Find and Replace tool. This is worth trying if you’ve tried all of the above, but there are still some stubborn external links hiding in your spreadsheet.

To find external links in Excel cells:

1. In the Home tab, click Find & Select > Find or press Ctrl+F.
   
2. You have a few options of what to enter in the Find What You can enter a square bracket ([) since external links are always given in square brackets. This will find any other square brackets in your spreadsheet, however.
   
3. Alternatively, you can search for .XL since any linked spreadsheet will include one of the file formats .XLS, .XLSX, or .XLSM.
   
4. Choose to search within the entire workbook or the current sheet.
   
5. Set the Look In value to Formulas.
   
6. Click Find All.
   
7. If any results are returned, you can select one to be taken to that cell.
   
8. To select multiple cells, hold Ctrl as you select each link.
9. To select all of the cells in the results, press Ctrl+A.
10. You can now remove the cells containing external links if you so wish.

Excel at Excel

Learning how to find and remove external links in Excel ensures that you can remove all the unnecessary or dead links from your spreadsheet. It also means that if you send the spreadsheet to someone else, you won’t have any issues with missing references.

There are plenty of other Excel skills that you can learn to take your spreadsheets to the next level. For example, you can learn how to count cells containing text in Excel. You can learn how to enable or disable macros in Excel. You can even learn how to create a random number generator.