How to Find External Links in Microsoft Excel Workbooks

Finding links and external references in a Microsoft Excel workbook isn’t as easy as you’d expect. To find all workbook links, you’ll need to search in the formulas, defined names, objects, chart titles, and chart data series.

In this tutorial, we’ll explain how to find external links in Excel quickly and easily.

How to Find External Links in Excel Cells

The first way to find external links is by searching within cell formulas. To do so, you need to use the Excel Find feature:

  1. Open your Excel worksheet and press Ctrl + F to open the Find and Replace dialog box.
  2. Select Options.
  3. In the Find what box, type “.xl” to capture all Excel file extensions.
  4. Under Within, select Workbook.
  5. Under Look in, choose Formulas.
  6. Select Find All. The Find and Replace window will display a list of all external links found within formulas inside your workbook.

Note: If you’ve linked your spreadsheet to an external workbook, the link name will display the workbook’s filename along with its .xl file extension. This should be either .xlsm, .xls, or .xlsx.

How to Find External Links in Defined Names

Defined names are used to name ranges and cells so that they’re easier to reference in formulas and data validation drop-down lists. These lists and formulas often reference external sources of data, so it’s worth searching them for external links:

  1. Select the Formulas tab and then press Name Manager.
  2. For each entry in the list, check the Refers To column. Links to external workbooks should be surrounded by square brackets, making them easy to find.

How to Find External Links in Objects

It’s possible to link objects like text boxes, shapes, and WordArt to external Excel files. To find external links within objects:

  1. Open your Excel spreadsheet.
  2. Press the Home tab.
  3. Under Formats, choose Find & Select and then Go to Special.
  1. In the Go to Special dialog box, tick the circle next to Objects and press OK.
  1. Check the first object for external links, then press Tab to move to the next object. Continue until you’ve checked all objects for links to external data sources.

Note: To check each object for links, select the object and refer to the formula bar. Alternatively, hover over the object to see where the external link goes.

How to Find External Links in Excel Charts

To find any external links used in chart titles or chart data series:

  1. Open the Excel sheet that contains the chart.
  2. Select the chart title and check the formula bar for a reference to another file.
  1. Select the data series and check the formula bar for external hyperlinks.

How to Find External Links in PivotTables

Occasionally, Excel users will create PivotTables that reference outside data. To check for external links in your PivotTables:

  1. Select a cell within your PivotTable.
  2. Under the PivotTable Analyze tab, select Change Data Source.
  1. Check whether the source file next to “Table/Range” links to your current workbook or another workbook.

How to Break External Links in Excel

To break links in Microsoft Excel:

  1. Select the Data tab.
  2. Under the Queries & Connections group, select the Edit Links button. If the Edit Links option is gray, it means there are no links in your workbook.
  1. When the Edit Links dialog box opens, choose the link you want to break, then select Break Link.

Note: This method will only help you break links that are present in your cells. Links in PivotTables, Charts, and elsewhere require a different method. Read our in-depth guide on how to break links in Microsoft Excel. If they won’t break, find out why.

Cleaning Up Your Excel Workbook

Broken links can be a nightmare, preventing your workbook from functioning correctly. Worse, Excel doesn’t make it easy to quickly scan, break, and replace links — you have to check each possible location manually or create complicated VBA code macros to do it for you. But with this guide, you should be able to find external links and clean your workbook up in no time.



Source : How to Find External Links in Microsoft Excel Workbooks

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more