Excel – How to find the external data in an Excel workbook that appears to have none

external datamicrosoft excel

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.

Best Answer

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.

Related Solutions

Excel – How to update automatically and continuously cell values in the master workbook, while the source workbooks are edited from different computers

Nice formatting of your question!

There is no way to turn that on, but there are ways to get it to do it automatically. Enable the developer ribbon if you haven't already (if you have full Excel) and in that ribbon click "Visual Basic"

You want to stick ActiveWorkbook.RefreshAll somewhere. Maybe by editing 'ThisWorkbook' and adding the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveWorkbook.RefreshAll
End Sub

so that it happens every time you switch tabs?

Or maybe by editing the master sheet's VBA code and adding ActiveWorkbook.RefreshAll somewhere in there, like in the Worksheet's Worksheet_Activate or Worksheet_SelectionChange events?

Once you get that working you could also try going to https://stackoverflow.com/questions/211715/how-to-make-a-macro-which-executes-periodically-in-excel to have it periodically update every n seconds instead.

Excel – How to rectify Excel error “We couldn’t get the data from table in the workbook…”

Additional detail to @meggie's response above. I suspect this has to do with corrupt Data Model connections.

Pay attention to the error message – it will list the name of the broken connection in quotes
Data -> Queries and Connections to open the sidebar
Click on Connections tab
Hover over each item and look for one that matches the name in the error message. Delete.

Best Answer

Related Solutions

Excel – How to update automatically and continuously cell values in the master workbook, while the source workbooks are edited from different computers

Excel – How to rectify Excel error “We couldn’t get the data from table in the workbook…”

Related Question