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
- Find links in Excel using Show Formulas
- How to find external links in Excel
- Break links in Excel
- Break links using Paste Values
- Break external links in Excel
- Find additional links in a worksheet
- Find links in Excel Charts
- Break links in Excel Charts
- Find links in Excel Name Manager
- Find links in Excel hidden worksheets
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’.
Or the Microsoft Excel warning message ‘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
- 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.
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.
6. Click the cell reference in the search results list to quickly jump to the linked cell.
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.
3. Leave the data selected and then right-click and select Paste Values to paste right over the original cell or range you copied.
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.
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.
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.
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.
Links can also be attached to the Chart Title so be sure to check this too.
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.
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.
2. The Name Manager dialog box will be displayed. Click the Named range that you want to change.
3. Excel will ask if you are sure you want to delete the named range. Click 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.
3. Select the worksheet you would like to unhide and then click OK.
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.
Was this blog helpful? Let us know in the Comments below.