In this Excel Training video, we’ll show you how to locate external links, such as references to other workbooks, within Microsoft Excel. First, we demonstrate how to access the Edit Links tool on Windows, Mac, and Web. Then we explore the practical application of the Find tool, which can be used to find any link, including those containing the “.xlsx” extension. Lastly, the bonus tip will show you how to use Conditional Formatting to identify and manage external links within your Excel workbooks.
Resources
Example of a correctly formatted external link:
='https://optionbox-my.sharepoint.com/personal/john_optionbox_co_uk/Documents/[Names.xlsx]Sheet1'!$A$1- Ensure that the filename and extension are in square brackets [filename.xlsx]
- Change Sheet1 to the name of the sheet you’re linking to.
- !$A$1 refers to the cell you’re linking to. If you were linking to cell B2 you would use !$B$2
Formula to highlight external links in conditional formatting:
=ISNUMBER(SEARCH(".xlsx", FORMULATEXT(A1)))