Find and break links in Excel

Posted July 22, 2022

Linking from one worksheet to another is a fantastic way to connect data from one source to another, and a great way to avoid having to enter the same data in multiple places.

However, there are times when you need to break links in Excel, and this can create the challenge of having to locate and identify exactly what the link is prior to breaking it.

Unless you know where the links are located you can find yourself wasting time searching your document trying to find and remove them.

In this blog I cover how to:

Find links in Excel

Most of the time we work in files that are familiar, so we are aware of the placement of links throughout the workbook.

However, if you weren't the original creator of the workbook you may find yourself exploring the file to find exactly where the links are located.

Although Excel lets us know that a file contains links, it doesn’t share with us exactly where the links are.

For example, when you open a workbook containing links you may see a warning message, ‘Automatic update of links has been disabled’.

Excel spreadsheet with Security Warning 'Automatic update of links has been disabled' and an Enable Content button next to it

Or the Microsoft Excel warning message ‘This workbook contains links to one or more external sources that could be unsafe’.

Microsoft Excel dialogue box with Warning saying 'This workbook contains links to one or more external sources that could be unsafe'

If you are unsure of exactly what is linked to this file you may be hesitant to click the Enable or Update button before you know just what it is you are updating.
Once you have located a link you can easily break the link. But the first step is finding it.

Links can reside in many places in Excel. For example, a link can be created inside the following:

  • A formula within a cell, e.g. a VLOOKUP or XLOOKUP function
  • Named ranges
  • Charts
  • Hidden worksheets

Once you have located the link you will need to use different methods to break the link, depending on the link type.

Find links in Excel cells using Show Formulas

The Show Formulas feature in Excel displays the formula in each cell instead of the resulting value. This option quickly uncovers cells containing links to other worksheets within the same file, and links to worksheets in external files.

To quickly locate linked cells from the Formulas tab, in the Formula Auditing group, click Show Formulas.

All cells containing formulas will now display the formula instead of the value, allowing you to easily identify those cells that are linked to other worksheets and/or external links to other sources.

Sales Report spreadsheet with formula '='[Sales Targets.xlsx]Jan'!$B$4 in cell B4 and an orange circle around it

Once you have located the link, you can now take steps to break the link.

How to find external links in Excel

To locate a link to an external file, use Find.

The following steps are a better option than using Show Formulas if you are searching a large workbook or in a workbook that has many formulas.

1. To quickly find links to external files, press CTRL + F to display the Find dialog box.

2. In the Find what box type .xls

Note: .xls is the file extension used for Microsoft Excel files. External links include the filename of the linked document. Therefore using .xls means you will find links to any external files using a .xls or .xlsx extension. Searching for .xl will include additional Excel file types.

3. If you would like to search just this worksheet, leave the Within option as Sheet. To search the entire workbook set this to Workbook. If you can’t see these options click Options first.

4. Make sure the Look in option is set to Formulas.

5. Click Find All and all cells containing references to external files will be displayed. 

Find and Replace dialogue box, in the Find tab with .xls in the Find what box, Workbook in the Within box, Formulas in the Look in box and mouse over the Find All button

6. Click the cell reference in the search results list to quickly jump to the linked cell.

Find all and Find next options with mouse pointing to $C$4

Once you have located the link or links, you can now take steps to break the links.

Note: Finding links using Show Formulas is an excellent option for smaller worksheets. Using Find to find external links is a great option when you are trying to find links in larger more complex files

Breaking links in Excel

I’ve listed below two different methods for breaking links. Each are great to use depending on your requirements.

The first method, Break links using Paste Values allows you to break links for a range of cells or a single worksheet. It doesn’t remove all links within the entire workbook. It also allows you to do an Undo should you make a mistake. 

The second method, Break external links in Excel quickly removes all links within the entire workbook and it does not allow an Undo should you make a mistake. Tread carefully when you use this option. It may pay to save a backup copy of the file using File > Save As prior to breaking the link. 

Break links using Paste Values

When you need to remove the link from a range of cells but keep the cell’s value instead of the link formula, use Paste Values.

The Paste Values option helps you remove linked formulas and converted them to their current values.

1. Select the cells containing the linked data. BTW, you can select the entire worksheet if you want (CTRL + A). If you aren’t sure where the links are within the file, see Finding Links in Excel above.

2. Copy the data.

480,000.00 in a cell with a callout saying "Select and Copy" and the formula ='[Sales Targets.xlsx]Jan'!$B$4 in the formula bar

3. Leave the data selected and then right-click and select Paste Values to paste right over the original cell or range you copied.

480,000.00 in a cell is selected with 480000 in the formula bar and a call out pointing to it saying "The linked formula in now replaced with a value"

Break external links in Excel

When you need to remove all links to an external file use the steps below.

Remember this option cannot be undone so it may pay to create a backup copy of your file before you proceed.

1. From the Data tab, in the Connections group, click on Edit Links.

Data tab with mouse pointing to Edit Links button and a description saying "View all of the other files this spreadsheet is linked to so that you can update or remove the links"

2. The Edit Links dialog box displays a list of all files that are currently linked to this file. Select the file that you would like to disconnect.

Edit Links dialogue box with Sales Tagrets.xlsx under Source

3. Click Break Link to break the link to the source workbook.

4. Excel will display a prompt reminding you that if you continue the action cannot be undone. Click Break Links to continue.

Microsoft Excel Warning saying "Breaking links permanently converts formulas and external references to their existing values. Because this cannot be undone, you may want to save a version of this file with a new name. Are you sure you want to break links?"

5. The link will now be broken. Click Close. All cells that previously held link formulas will now be shown as values.

Find additional links in a worksheet

All too often you can find Excel is still telling you there are existing external links after you think you have located and removed all links within the workbook.

This is because links can be hidden in many different places within the workbook file.

I’ve outlined below the best places to check and steps on how to break the link once found.

Find links in Excel Charts

There are times when a chart has been created using links to external data.

To check this, click the data series in the chart and then check the Formula Bar for external references.

In the example below the data series segments of the pie chart have been selected and a link to an external file is displayed in the Formula Bar.

Excel Pie Chart with April in 35% (blue), May in 30% (orange) and June in 35% (grey).

Links can also be attached to the Chart Title so be sure to check this too.

Excel Pie Chart with April in 35% (blue), May in 30% (orange) and June in 35% (grey). The pie chart is titles "Used Car Sales"

Break links in Excel Charts

If you need to break a link to the Chart Title, just overtype the Title to replace the formula with text.

If you would like to keep the chart but break the link to the data series do the following:

1. Select the chart.

2. Copy the chart.

3. Paste the chart as a picture.

Paste options with mouse hovering over Picture (U) option

4. Delete the original (linked) chart.

Find links in Excel Name Manager

If a link to an external file has been defined in a Named range, the linked name can remain in your document, even after you have updated or deleted formulas using the Named range

To check for external links inside of Named ranges:

1. On the Formulas tab, in the Defined Names group, click Name Manager.

Formulas tab with mouse hovering over the Name Manager option

2. The Name Manager dialog box will be displayed. Click the Named range that you want to change.

Name Manager dialogue box with option called Target being selected. It has a value of 480,000.00, Refers to ='[Sales Targets.xlsx]Jan'!$B$4 and the Scope is workbook

3. Excel will ask if you are sure you want to delete the named range. Click OK.

Microsoft Excel warning saying "Are you sure you want to delete the name Target?" and the mouse hovering over OK

4. Click Close.

Note: if you delete a Named range, any existing formulas that reference the Named range will display an error. You might like to press Ctrl + F to open the Find dialog box and type the named range name in the Find what box. Be sure to set the Look in option to Formulas. This will locate any formulas using the named range. You can then avoid errors by updating the formula and removing the named range reference before it is deleted.

Find links in Excel hidden worksheets

Links can sometimes be inside hidden worksheets.

1. To check if your file has hidden worksheets, right-click any sheet tab. If the Unhide option is available there are hidden worksheets in the file.

2. Select Unhide.

The options Hide, Unhide and Select All Sheets by the worksheets tab with the mouse hovering over the Unhide option

3. Select the worksheet you would like to unhide and then click OK.

Unhide dialogue box with two options in Unhide one or more sheets: March and Feb, with Feb selected

Now you can find and break links.

To Sum Up

Unfortunately, there is no quick way to find all links within an Excel file. Links can be located in many different places, and you need to deal with each link type in a different way.

This blog covers the most places that links are commonly found.

Adding the Find Links in Excel and Break Links in Excel tools to your ‘Excel-toolbox’ will save you a lot of time.

Was this blog helpful? Let us know in the Comments below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus today!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>