How to find links in Excel and break external links

This tutorial provides several methods to locate and manage external links in Microsoft Excel workbooks. Excel's lack of a single, comprehensive link finder necessitates different approaches depending on the link's location (formulas, named ranges, objects, charts, PivotTables).

Finding External Links:

• Cells: Use the "Find and Replace" dialog (Ctrl F). In the "Find what" field, enter ".xl" to search for all common Excel file extensions (.xls, .xlsx, .xlsm). Set "Look in" to "Formulas" and "Within" to either "Workbook" or "Sheet." Click "Find All." The results list shows cells containing external references. You can select individual cells, group results, or select all at once (Ctrl A).

• Named Ranges: Access the "Name Manager" (Formulas tab > Defined Names > Name Manager or Ctrl F3). Examine the "Refers To" column for references enclosed in square brackets, indicating external links like [Source_data.xlsx].

• Objects (Shapes, Text Boxes, etc.): Use "Go To Special" (Home tab > Find & Select > Go to Special or F5 > Special...). Select "Objects," then press Tab to cycle through objects, checking the formula bar for cell references or hovering over the object to reveal file links. Links to entire files, not individual cells, require right-clicking and selecting "Remove link."

• Charts: Check the formula bar for external references in chart titles or data series. For charts with multiple series, use the Format tab > Chart Elements to select individual series.

• PivotTables: Click inside the PivotTable, then go to the PivotTable Analyze tab > Data > Change Data Source. Check the "Table/Range" box for external links.

Managing External Links:

• Enabling Links: Excel displays a security warning when opening workbooks with external links. Click "Enable Content" to allow updates. Subsequent openings prompt whether to update; click "Update" to refresh data.

• Controlling Update Prompts: Adjust settings in Data tab > Queries & Connections > Edit Links > Startup Prompt... to control automatic updates and security alerts. Alternatively, modify Trust Center settings (File > Options > Trust Center > Trust Center Settings > External Content) to enable automatic updates for all workbook links (use caution).

• Breaking Links: Use Data tab > Queries & Connections > Edit Links. Select links and click "Break Link" to replace external references with their current values. Remember that this action is irreversible.

• Listing All Links: The "Edit Links" feature provides a list of linked files, but requires manual inspection. For a more comprehensive list, consider using dynamic arrays (Excel 365) with the LINKS() macro or VBA code (provided in the original text). A third-party add-in like Ultimate Suite offers a one-click solution.

This summarized version maintains the core information while improving readability and conciseness. The image references remain intact.

The above is the detailed content of How to find links in Excel and break external links. For more information, please follow other related articles on the PHP Chinese website!