In this Excel training video, we’ll guide you through the essential process of identifying external links, such as links or references to other workbooks. Managing external links is crucial for maintaining data integrity and ensuring accurate calculations in complex Excel projects. We’ll explore three distinct methods to streamline this task, catering to various user preferences and scenarios.
Edit Links tool
Firstly, we’ll demonstrate the utilization of the Edit Links tool, a powerful feature within Excel that provides a comprehensive overview of all external links associated with your workbook. This tool not only reveals the connections but also allows for efficient management and updating, ensuring that your data remains current and error-free.
Find tool
Additionally, we’ll delve into the application of the Find tool, showcasing how you can quickly locate external links within your spreadsheet. This method is particularly useful for users seeking a straightforward and precise way to identify and navigate through links embedded in their Excel workbooks.
Conditional Formatting
Finally, we’ll show you how to use Conditional Formatting to visually highlight cells containing external links, offering a visual cue that aids in quick identification and enhances the overall clarity of your Excel project.
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)))