How-To

How to Find and Remove External Links in Excel

find and remove external links in excel

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.
    excel 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.
    excel edit links
  4. You will see a list of all of the documents that are being referenced externally.
    excel linked cells
  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.
    excel break links
  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.
    excel edit links gray

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.
    excel find
  2. Select Go To Special.
    excel go to special
  3. At the bottom of the window, click Objects.
    excel find 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.
    excel shape external link
  6. Hover over the object to see if the object is linked to any specific file.
    excel linked file
  7. If an object does link to a file, right-click it and select Remove Link.
    excel 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.
    excel chart external link
  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.
    excel pivottable analyze tab
  3. Select Change Data Source.
    excel change data source
  4. Examine the Table/Range field for any external links which you can remove if necessary.
    excel data source

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.
    excel formulas menu
  2. In the Defined Names section, click Name Manager.
    excel name manager
  3. Look in the Refers To column for any external links.
    excel named range
  4. If you want to remove a named range that contains external links, click Delete.
    excel delete named range

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.
    excel find and replace
  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.
    excel search square bracket
  3. Alternatively, you can search for .XL since any linked spreadsheet will include one of the file formats .XLS, .XLSX, or .XLSM.
    excel find xl
  4. Choose to search within the entire workbook or the current sheet.
    excel find within sheet
  5. Set the Look In value to Formulas.
    excel find in formulas
  6. Click Find All.
    excel find all
  7. If any results are returned, you can select one to be taken to that cell.
    excel search results
  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.

1 Comment

1 Comment

  1. jamies

    April 27, 2023 at 1:57 pm

    I suspect the article needs some tweaking –
    Hidden sheets are also something to be looked at – probably needing to be unhidden
    And then there are veryhidden sheets where the sheets do not even show in the select a sheet list (bottom left corner of #Excel window)

    I usually start looking by creating a copy of the workbook,
    then right click on a worksheet to see if unhide is not grey’d out , and unhide as needed.
    then select all the sheets (select the first, shift and select the last in the tabs along the bottom of the window)
    and use find for the files named in the links list
    FindAll is a helpful option, expand the window, and use alt+prtscr to capture the panel’s window then paste that into a new .rtf file, or temporarily into a new email
    Select just a single sheet as indicated by that list –
    (with all the sheets selected, any change can be applied to the indicated cell of all the selected sheets – and at this stage you would just be looking to see what can just be set to the “Value” rather than needing to be set to something differennt – maybe a new sheet with the variable values indicated in it, and the external links from other sheets set to data cells in that new ‘linksToGet’ worksheet.
    So go through the links creating data value cells in the workbook and commentary etc, for the external links that were found!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top