I an using Excel 2010. I have a template that, as far as I know, contains no external data sources:
- I cannot find any cell that refers to an external source.
- In the "Data" ribbon "Connections" tab the "Edit Links" option is greyed out.
- If I select "Existing Connections" it says "No connections found" for this workbook.
However, every time I save the template, a dialog pops up stating:
This workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened?
If I select "No", then the users of the templates are also prompted about external data, but if I select "Yes" I worry that Excel may be doing something I do not intend.
How can I find out what external data Excel thinks it is accessing?
Thanks.
36 Answers
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:
It does not find conditional formatting links, though, so you may need to check these manually.
3Somebody 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.
1I 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.
1Pivot 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.)
I was able to find the problem in a round-about way that might help someone else. I didn't have any pivot tables in my document. I decided to open the spreadsheet from a new computer and when I did that, I got a message that said the system couldn't update links and there was an option to look at the external link. When I did that, I found where the external link existed in my document and was able to fix it. That might work for you if you have many worksheets with many formulas like I did.
One other possibility:
In an excel 365 file that had several pivot tables and was getting the warning of external data connections, I created a blank worksheet and deleted all other worksheets except the blank one, saved and reopened the workbook and still got the warning. I found the source in Data-->Queries and Connections to be a mysterious Table1 with the query (Select * from Table1). I could not edit the query, as that created an error "Could not find Excel table named Table1". But deleting Table1 in the Queries and Connections form was nevertheless possible and it fixed the issue.
