Find External Links in Excel Workbooks

External links in Excel are references to cells in other workbooks, often used to pull in data from a shared source. They can break when the source file moves or is renamed, causing errors like #REF! or prompts to update links. This guide shows you how to locate every external link in a workbook—including those hidden in formulas, defined names, charts, and conditional formatting—so you can update or remove them.

What You’ll Need

• A Windows or Mac computer with Microsoft Excel (2016 or later recommended; steps are similar for 2013 and 365).
• The workbook you want to inspect, saved on your computer or network drive.
• If the workbook contains links to other files, those files may need to be accessible to avoid prompts.

Step-by-Step

1. Open the workbook in Excel. If you see a prompt about updating links, choose “Don’t Update� for now—you can still find the links.
2. Use the built-in link checker (fastest method):
   1. Go to the Data tab.
   2. In the Queries & Connections group, click Edit Links (on Mac, it’s under Data > Edit Links).
   3. A dialog box lists all external workbooks referenced in formulas. This is the most reliable way to see them at a glance.
3. Find links in formulas (if the dialog is empty but you suspect links):
   1. Press Ctrl+F (Windows) or Cmd+F (Mac) to open Find & Replace.
   2. Click Options to expand the search options.
   3. In the Find what box, type [ (the opening square bracket). External references always contain a bracket, like =[Book1.xlsx]Sheet1!A1.
   4. Set Within to Workbook, Look in to Formulas.
   5. Click Find All. Excel lists every cell with a formula containing [. Review each to identify the external link.
4. Check defined names (named ranges can hide links):
   1. Go to the Formulas tab and click Name Manager.
   2. Look through the list for any Refers to values that contain a [ (e.g., =Book1.xlsx!Sheet1!$A$1).
   3. If you find one, note the name and the external reference. You can edit or delete it here.
5. Inspect charts and shapes (they can have linked data):
   1. Right-click a chart and choose Select Data. In the dialog, look at the Chart data range or series values—if they contain a [, that’s an external link.
   2. For shapes or text boxes with formulas, click the shape and look at the formula bar. If it shows an external reference, note it.
6. Check conditional formatting rules (less common but possible):
   1. Go to Home > Conditional Formatting > Manage Rules.
   2. Select This Worksheet from the dropdown, then review each rule’s formula for a [.
7. Use a manual search for all possible sources (if the above don’t find anything but you still get link prompts):
   1. Save a backup copy of the workbook.
   2. In the original workbook, press Ctrl+F and search for .xls (without the brackets) in Formulas. This catches references like ='C:\Folder\File.xlsx'!A1.
   3. Also search for ! in formulas—though this will match many normal formulas, so look for ones that include a file path or filename.
8. Document your findings in a separate sheet or text file. List the cell, the type (formula, name, chart, etc.), and the external file path. This helps you decide what to update or remove.

Common Mistakes

• Only checking formulas—links can hide in defined names, charts, and conditional formatting. The Edit Links dialog only shows formula links, so you might miss others.
• Searching for “.xlsxâ€� with the dot—external references sometimes omit the file extension if the link is to a defined name. Searching for [ is more reliable.
• Forgetting hidden sheets—a link might be on a very hidden sheet. Unhide all sheets first (right-click a sheet tab > Unhide) before searching.
• Assuming Edit Links shows everything—it only shows links in formulas, not in names or charts. Use multiple methods.

Tips

• Use the “Find Allâ€� results to jump directly to each cell—click an entry in the list and Excel selects the cell.
• Break links carefully—if you use Edit Links > Break Links, Excel converts external references to their current values. Make sure you have a backup first.
• Keep a list of external sources—if you deliberately use links, document them in a README sheet so future users know what they are.
• Check for links in array formulas—some array formulas may not show up in a simple find. Press Ctrl+~ to show formulas, then visually scan for brackets.
• Use a third-party add-in if you have many workbooks—tools like “FindLinkâ€� can scan multiple files, but they may cost money. The manual method is sufficient for one workbook.

Troubleshooting

• Edit Links is grayed out—This happens if the workbook is shared or protected. Unshare the workbook (Review > Share Workbook > uncheck) and unprotect sheets/workbook before trying again.
• Find and Replace doesn’t find anything—Try searching for .xls without the dot, or search in “Valuesâ€� as well as “Formulas.â€� Also ensure you’ve set “Withinâ€� to “Workbookâ€� and “Look inâ€� to “Formulas.â€�
• Link prompt appears but no links found—The link might be in a defined name or chart. Check those locations. Also, the prompt might come from a macro (VBA) that references another file—open the VBA editor (Alt+F11) and search for “Workbooksâ€� or “.xlsâ€� in the code.
• External file is missing—If you can’t update the link, you can either break it (replacing with current values) or edit the link to point to a new location. In Edit Links, click “Change Sourceâ€� and browse to the new file.
• Links in conditional formatting not found—Conditional formatting formulas are not shown in the regular Find dialog. You must open Manage Rules and inspect each rule manually.

Frequently Asked Questions

Q: Can I find all external links at once?
A: The Edit Links dialog lists all formula-based links in one place. For links in names, charts, or conditional formatting, you’ll need to check each area separately.

Q: Why does Excel show a link prompt even though I don’t see any links?
A: The link might be in a defined name, a chart, or a macro. Also, check if the workbook contains hidden sheets with links. Use the methods above to search thoroughly.

Q: What does “Break Links� do?
A: It replaces external references with the current values from those sources. For example, =[Book1.xlsx]Sheet1!A1 becomes the number that was in that cell. This is permanent, so save a backup first.

Q: Can I update a link to a new file location?
A: Yes. In Edit Links, select the link and click “Change Source,� then browse to the new file. Excel will update all references to that file.

Q: How do I find links in VBA macros?
A: Press Alt+F11 to open the VBA editor, then press Ctrl+F to search for “Workbooksâ€� or “.xlsâ€� in the code. Look for lines like Workbooks("Source.xlsx").Sheets(1).Range("A1").

Q: Are there any free tools to find links?
A: Some add-ins are free, but the built-in methods are usually sufficient. You can also use a macro to scan all cells, but that requires VBA knowledge.

Related

• How to Make a FaceTime Call from iPhone to Android
• Schedule an Email on Mac: 5 Ways That Work
• CPU: How to Fix PUBG No Sound Issues?