How do I find the external data in an Excel workbook that appears to have none?

Solution 1:

Links to external data can exist in

• formulas (visible through Data --> Edit Links)
• named ranges
• macros called by buttons and shapes
• conditional formatting
• pivot tables linked to external sources

Bill Manville (MS MVP) has created a little tool to find them all. Download: http://www.manville.org.uk/software/findlink.htm

It does not find conditional formatting links, though, so you may need to check these manually.

Solution 2:

Somebody posted an answer stating that they thought pivot tables were causing this message even when there is no external data. By the time I verified for myself that this was correct, that answer had been deleted.

Here's the comment I had intended to leave on their post:

The template in question does have a pivot table. I have tried deleting the pivot table (and then everything else in the worksheet with the pivot table) and the problem persists. When I delete the entire worksheet that held the pivot table then the problem goes away. Then when I insert a few numbers in cells and a new pivot table on those cells, the problem re-appears. So yes, it does appear that dialog is incorrectly triggered by having a pivot table in your template.

Anyway, my thanks to whoever pointed this out.

Solution 3:

I had a similar problem in a template. I solved it by deleting the sheet which had, at one point in time, had a pivot table on it.

Solution 4:

Pivot tables can cause this problem and even cleaning all fields and resetting all the formattings does not undo this problem (bug) and the error still comes at each save until the sheet is removed from the workbook.

The best way to remove it is by temporarily moving the sheet to a new workbook via right-click on the sheet-> Move or Copy ... To book: (new book) There you delete the pivot table and move it back and then you can save the template again without the bug and without the need to delete the whole sheet.

(I would have written my contribution as a comment, if my reputation allowed it.)