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

As you work with a PivotTable and other data visualizations over time, you might 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. A column that is grayed out has been hidden from client applications.

    Power Pivot tabbed window

  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.

Note:  Data models are new in Excel 2013. You use them to create PivotTables, PivotCharts, and Power View reports that visualize data from multiple tables. Learn more about data models inCreate a Data Model in Excel.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.