It would be not easy to keep a record of all the external references in your workbook. As an unmanageable thing, you will not be able to find external links in a spreadsheet. Microsoft Excel though has many features to help you with different functions; however, you still will not find any built-in feature that can assist you in finding links to external sources.
When a tool does not have some built-in features, we have to find alternatives for this. Similarly, we have managed to find some useful hacks that can help you in finding external links in a spreadsheet.
Though manually finding external links would be a tiring thing, that’s why you have to be clever enough to find alternatives and this is what we are going to explain in this post.
What are External Links?
Do you know Excel tends to make a link to another workbook when you make formulas and submit them to a data point in that workbook?
Well, this is what we call external links in a workbook and it could be tricky at times to find them. The formula, in the end, would be like this:
In the image given above, you can see the yellow area that shows the external link or reference. The highlighted part of the workbook commands Excel to direct toward other workbooks and open the specified cell. Simply you can update the formula by using this external link.
Just as this feature has benefits, it also comes with some drawbacks. And one drawback is that you always have to have that linked workbook with you. You will not be able to update the formula if the linked workbook is renamed, or deleted, or the folder location is changed.
Before sharing any workbook containing external links with others, you must remove these links. And in case, if you have many links in the workbook, it would be quite challenging.
Let’s dive in to find some hacks for this function:
Finding External Links with Find Function
Using the find function may not be the best option; however, it can still provide an answer to your query. Mainly, this method shows that Excel includes external references in long brackets “[]”. When all these brackets in a workbook are found, it clearly means that you will get a list of external links easily.
- Go to the Excel sheet that contains the external links.
- Now, press the CTRL + F keys from the keyboard, open the “Find”, and replace the dialog box.
- Enter the string “[*]” in the Find field without quotes. Using this string represents that the results will include within long brackets.
- Select Formulas in the ‘Look in’ dropdown and press the “Find All” button.
- The resultant will be a set of external links used in the sheet.
Find External Links in Excel using Report and Replace
This approach uses Report and Replace function that let you find external links in a workbook. For this, follow the steps given below:
- Press CTRL + F from the keyboard and a dialog box Find and Replace will appear.
- Select Options from the lower right side of the dialog box.
- Enter “*.xl*” in the field Find What. This extension is useful for other workbooks such as *.xl* or *.xlsx
- Open the workbook from the Within box.
- Open the Formulas tab from Look in the box.
- Choose the Find All option.
- After this step, all the external links will appear in front of you.
Find External Links using the Edit Links Option
The Edit links option is a built-in feature of Excel that can help you find all the external links. You just have to follow these steps carefully:
- Open the Data Tab.
- Click on Edit Links from the Connections group. A dialog box Edit Links will open up and list all the linked workbooks.
- Click on the Break Links option to change all referenced cells to values.
Before breaking links, make sure you have a backup of your data for safety reasons. Continue exploring Excel for new features and share knowledge with others.
