How to get EXTERNAL links in Excel (Windows, Mac, & Web)


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)))
Need help? This is a free service for Business Tech Planet readers. Pop in your problem below and we'll be in touch soon!

John Gleave

John Gleave has been a researcher, content writer, and senior editor at Business Tech Planet since 2022. John was formerly a data analyst and web designer with expertise in several programming languages, such as JavaScript, JQuery, PHP, CSS, SQL, and more! With a passion for writing and technology, he has now focused his skills on crafting tech guides for BTP. You can connect with John on LinkedIn.

Recent Posts