How to Find External Links in an Excel Workbook

Excel asks whether you want to update links to another workbook. You click through the prompt, open Edit Links to see what it means, and the list is empty or shows a file nobody recognises. The prompt comes back every single time you open the model.

External links hide in more places than the Edit Links dialog reports. Here is the full sweep, in the order worth doing it.

1. Start with Edit Links

Data > Queries & Connections > Edit Links. This lists workbook-level links and lets you update, change the source, or break them. Breaking a link converts the formulas that use it to their current values, which cannot be undone, so save a copy first.

If the dialog is greyed out entirely, there are no workbook-level links, and whatever is triggering the prompt is one of the cases below.

2. Search the formulas

An external reference always contains the source file name in square brackets, so the bracket is the thing to search for. Press Ctrl + F, set Within to Workbook and Look in to Formulas, then search for:

[

Find All gives you every cell with a link and the sheet it sits on. Searching for .xl works too and catches slightly different cases. This is the step that finds the vast majority of them.

3. Defined names

This is the most common hiding place, and Edit Links does not surface it clearly. A named range can point at another workbook, and if nothing on any sheet uses that name any more, the link stays in the file and keeps triggering the prompt.

Formulas > Name Manager. Sort by the Refers To column and look for anything containing a bracket or a file path. Delete names you do not use. Unused names accumulate quietly in any model that has been copied between files a few times.

4. Chart series

A chart can point directly at a range in another workbook without any cell on the sheet doing so. Click the chart, then Chart Design > Select Data, and read the series formulas. A path in there is a link the formula search will never find.

5. Data validation and conditional formatting

Both can reference another workbook. Neither shows up in a formula search, because the reference lives in the rule rather than in a cell.

For validation, select the range and check Data > Data Validation. For conditional formatting, Home > Conditional Formatting > Manage Rules, with the scope set to This Worksheet, then read the rule formulas. Worth checking each sheet, since rules are per-sheet.

6. Hidden and very hidden sheets

A hidden sheet holds links like any other. Right-click any tab and choose Unhide to see the ordinary hidden ones.

Sheets set to very hidden do not appear in that dialog at all. They can only be revealed through the VBA editor: Alt + F11, select the sheet in the Project pane, and set its Visible property back to -1 - xlSheetVisible. Models that have been passed between teams often carry one or two.

A note on links that are not actually broken

Not every link that looks broken is. A path pointing at SharePoint or OneDrive resolves fine when you are signed in and looks dead when you are not, and a network path behaves the same way off the VPN. Breaking those because a checker called them broken converts working formulas to stale values, which is a real way to damage a model while trying to tidy it.

Doing the whole sweep at once

The steps above are six separate places to look, per sheet. ExcelSurge's workbook audit covers them in one pass: broken links, unused and broken defined names, hidden and very hidden sheets, and data connections, each reported with the cells or names involved. Network and cloud paths are treated as reachable rather than flagged as broken, so you are not talked into breaking links that work.

If you are running this check before a model leaves your hands, the ten-minute model check covers what else belongs in the same pass.