Find out which data sources are used in a workbook data model

If you're working with data that's been added to an Excel data model, then sometimes, you may lose track of which tables and data sources were added to the data model.

Here are a few easy steps you can follow to determine exactly what data exists in the model:

1. In Excel, click Power Pivot > Manage to open the Power Pivot window.

2. View the tabs in the Power Pivot window.

   Each tab contains a table in your model. Columns in each table appear as fields in a PivotTable Field List. Any column that is grayed-out has been hidden from client applications.

   

3. To view the origin of the table, click Table Properties.

   If Table Properties is grayed out and the tab contains a link icon indicating a linked table, the data originates from a sheet in the workbook rather than an external data source.

   For all other types of data, the Edit Table Properties dialog shows the connection name and query used to retrieve the data. Make a note of the connection name, and then use Connection Manager in Excel to determine the network resource and database used in the connection:

   1. In Excel, click Data > Connections.

   2. Select the connection used to populate the table in your model.

   3. Click Properties > Definition to view the connection string.