(How to find) external links in Excel
An external link (or an external reference) in Excel is a reference to a cell, a range or a defined name in another workbook. It’s a great way to populate your spreadsheet.
In this article we’ll first explain how you can create a link to another workbook in Excel and then we’ll help you find any external references in your workbook when you are not sure where exactly you have used them.
Link to a cell in another workbook
To create a link to a cell in another spreadsheet, we start by opening both workbooks: the one that will contain the external link (the destination) and the one that contains the data that we want to refer to (the source).
Example
In this example we have a workbook that contains the revenue of fruit per month (a different tab for each month): “fruit-rev-21” and another workbook in which we want to sum up the revenue for the entire year: “tot-rev-21”. We’d like to populate the latter with data from the former.
We’ll start in our destination workbook, with the total revenue of fruits in January, by selecting cell ‘B2’ and entering ‘=’
Next, we’ll focus on the source workbook (‘fruit-rev-2021’) and we’ll select cell ‘D8’ that contains the total revenue on the sheet that holds the January data:
Now all you have to do is press ENTER to create the link and display the value in the destination file (‘tot-rev-2021’).
Link to cell range in another workbook
To create a link to a cell range in another workbook, we follow almost the exact same steps, but this time we’ll select the destination cell ‘B3’ (fruit revenue February) and enter: ‘=SUM(‘
Like before, we go back to the source workbook, but this time we’ll select the February tab and the cell range ‘D2:D7’
Once again we’ll press ENTER and cell ‘B3’ in our destination workbook will display the result of the sum of the cell range from the other spreadsheet (‘fruit-rev-2021’):
Link to external named range
The method to create an external link to an object with a defined name is similar to the methods explained above. It all depends on the nature of your named object.
We’ll continue populating the ‘tot-rev-2021’ sheet. In our source workbook (‘fruit-rev-2021’) we have two named ranges. The first is the cell that contains the total revenue of fruits in March (‘tot_rev_fruit_mar_21’) and the second is the range that contains all totals per fruit in April (‘tot_rev_per_fruit_apr_21’).
Example 1: Link to a cell with a defined name in another workbook
In our destination workbook (‘tot-rev-2021’) we select cell ‘B4’ and enter: ‘=’
Then we go back to our source workbook (‘fruit-rev-2021’) and press ‘F3’ to call on the list of available named ranges.
After selecting the relevant named range in the ‘Paste Name‘ pop-up, hit ‘OK’ and the defined name will appear in your destination workbook. Press ENTER to publish its value.
Example 2: Link to a named range in another workbook
In our destination workbook (‘tot-rev-2021’) we select cell ‘B5’ for the revenue of April and enter: ‘=SUM(‘
Then we switch to our source workbook (‘fruit-rev-2021’) and press ‘F3’ again for the pop-up with the list of defined names in this spreadsheet.
N.B. It’s not necessary to select the sheet that contains the named range. You can select it from the dialogue box anywhere in your spreadsheet!
Now select the relevant name and press ‘OK’ for the named range to appear in your destination workbook. Hit ENTER to publish the value.
Pay attention to the nature of the linked name
If you’re linking to a named range (or a cell range for that matter) and you don’t include it in a formula (you only type ‘=’), your destination workbook will publish the entire range. See example below.
How to find external links in your spreadsheet
Now we’ll change the perspective. Let’s say you’re working with our destination sheet (‘tot-rev-2021’), but you created it a while ago, or you didn’t even create it yourself and you want to know if it contains any external references.
If you go to Data > Edit links… you will get an overview of the source files that your workbook is linking to. In our case, this is only one file: ‘fruit-rev-2021.xlsx’
So now we know which sources are used in our file, but still we don’t know WHERE in our file the link or links to these sources are located. Unfortunately, Excel itself can’t answer this question for you. PerfectXL Explore can. Whether the link is located within a simple cell or a very complex formula, PerfectXL Explore will find it and take you there.