10

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.

Improve this question
3
  • Click on Data and then Connections. I don't see Edit Links at all. I see a window with a top and bottom half to the window. In the top half I see Name/Description fields (along with Add/Remove/Properties/refresh/Manage Sets buttons) and the name of the external references below. In the bottom half, it has "Locations where connections are used". In my case, these are all just leftover references from stuff copy and pasted, for data which isn't in the spreadsheet any longer (some not for a long time). Do you see anything in here?
    – Dallas
    Jan 6, 2014 at 22:13
  • Note that the "Connections" button in the "Data" ribbon is part of a group of buttons also called "Connections". That's what I was referring to above.
    – Anon Gordon
    Jan 6, 2014 at 22:19
  • And when I click the "Connections" dialog as you describe, it opens a "Workbook Connections" dialog which is empty.
    – Anon Gordon
    Jan 6, 2014 at 22:20

6 Answers 6

Reset to default
6

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.

Improve this answer
3
  • That's a cool tool, but it found no links, and no links that reference ".x" or ".X". Nothing obvious in the conditional formatting either.
    – Anon Gordon
    Jan 7, 2014 at 2:20
  • 1
    Note, it looks like you need to close every instance of Excel before Bill's tool will show up after you install it. Took me a minute to realize that.
    – Jared
    Feb 8, 2017 at 15:05
  • I reviewed a file of my client. FindLink helped me to identify external links in conditional formatting (as I did not have these external files, I could not see them in the conditional formatting), thank you very much!
    – SoftTimur
    Oct 9, 2019 at 21:37
4

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.

Improve this answer
1
  • 1
    This is old, but I just ran into it. A) Thanks for pointing this out, and B) it's not necessarily External data, it's actually the Pivot Cache that it's wanting to clear, so the message is legitimate, but, in true Microsoft fashion, it's not really clear. If you click Yes to the message, your workbook size on disk will be smaller because the cache(s) are thrown out, but it will take longer to open the WB because Excel will rebuild each and every one as it opens, then refresh all the pivots.
    – FreeMan
    Aug 18, 2015 at 14:05
2

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.

Improve this answer
1
  • 1
    Not sure why you were voted down, but this is the only way I have found to solve the problem if it is a spurious message caused by a pivot table (which it usually is in my case).
    – Anon Gordon
    Mar 31, 2015 at 19:55
1

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.)

Improve this answer
1

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.

Improve this answer
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .