So your formula may look like something as shown below:

Note that the part highlighted in yellow is the external link (also called external reference). This part of the formula tells Excel to go to this workbook (Score.xlsx) and refer to the specified cell in the workbook. The benefit of having an external link in your formula is that you can automatically update it when the data in the linked workbook changes. However, the drawback is that you always need to have that linked workbook available. If you delete the linked workbook file, change its name, or change its folder location, the data would not update. If you’re working with a workbook that contains externals links and you have to share it with colleagues/clients, it’s better to remove these external links. However, if you have a lot of formulas, doing this manually can drive you crazy.

Here are a couple of techniques you can use to quickly find external links in Excel:

Using Find and Replace. Using Edit Links Option.

Let’s see how each of these techniques work.

Cells with external links contain the name of the workbook to which it links. This would mean that the reference would have the file name with .xlsx/.xls/.xlsm/.xlb extension. We can use this to find all the external links. Here are the steps to find external links in Excel using Find and Replace:

Select all the cells. Go to the Home tab –> Editing –> Find & Select –> Find. In the Find and Replace dialog box, enter .xl in the ‘Find what’ field. Click on Find All.

This will find and show all the cells that have external links in it.

Now you can select all these cells (select the first record, hold the Shift key and then select the last record) and convert the formulas to values.

Excel has this inbuilt tool that will find all the externals references. Here are the steps to find external links using Edit Links Option:

Go to the Data Tab. In the Connections group, click on Edit Links. It opens the Edit Links dialog box will list all the workbooks that are being referenced. Click on Break Links to convert all linked cells to values.

Be aware that once you break the links, you can undo it. As a best practice, create a backup before doing this.

Sometimes, you may find and remove all the external links, but still get a prompt as shown below:

Don’t go crazy and start cursing Excel. So if you’re getting the update links prompt, also check the following for external links:

Named Ranges Conditional Formatting Data Validation Shapes Chart Titles

Using ‘Find and Replace’ or ‘Edit Links’ as shown above would not identify external links in these above-mentioned features. Here are the steps to find external links in these places:

Named Ranges: Go to the Formula tab and click on Name Manager. It will show you all the named ranges in the workbook. You can check the ‘Refers to’ column to spot external references. Conditional Formatting: The only way an external link can land up in Conditional Formatting is through the custom formula. Go to the Home tab –> Conditional Formatting –> Manage Rules. In the Conditional Formatting Rules Manager, check the formulas for external links. Data Validation: It is possible that the data validation drop down list refers to a named range that in turn has external links. Checking Named Ranges should take care of this issue as well. Shapes: If you’re using shapes that are linked to cells, check these for external links. Here is a quick way to go through all the shapes: Press the F5 key. It will open the Go to Dialog Box. Click on Special. In the Go To Special dialog box, select Objects. Click OK. This would select all the shapes. Now you can use the Tab key to cycle through these. Chart Titles: Select the chart title and check in the formula bar if it refers to an external link.

You can learn more about External Links from these tutorials:

Finding External Links in Excel – Contextures Blog. Finding External Links – Microsoft Excel Support.

There is also an add-in available to find external links in Excel. Click here to learn more and download the add-in.

How to Lock Cells in Excel. Find and Remove Hyperlinks in Excel. Creating Dynamic Hyperlinks. Using FIND function in Excel. How to Reference Another Sheet or Workbook in Excel How to Find Circular Reference in Excel

As you suggested to use of find and searching for .xls or other file extension would be nice idea. But I am trying to copy all these links in search result… and I am not able to do it.. how do I do it…?? Please suggest. Thanks