Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Use multiple tables to create a PivotTable

PivotTables are great for analyzing and reporting on your data. And when your data happens to be relational—meaning it's stored in separate tables you can bring together on common values—you can build a PivotTable.

PivotTable containing multiple tables The Field List for multiple tables

What’s different about this PivotTable? Notice how the Field List on the right shows not just one but a collection of tables. Each of these tables contain fields you can combine in a single PivotTable to slice your data in multiple ways. No manual formatting or data preparation is necessary. You can immediately build a PivotTable based on related tables as soon as you import the data. 

Here are the three basic steps to get multiple tables into the PivotTable Field List:

Step one: import related tables from a database

Import from a relational database, like Microsoft SQL Server, Oracle, or Access. You can import multiple tables at the same time:

Step two: add fields to the PivotTable

Notice that the Field List contains multiple tables.

PivotTable Fields list

These are all of the tables that you selected during import. You can expand and collapse each table to view its fields. As long as the tables are related, you can create your PivotTable by dragging fields from any table to the VALUES, ROWS, or COLUMNS areas. You can:

  • Drag numeric fields to the VALUES area. For example, if you are using an Adventure Works sample database, you might drag SalesAmount from the FactInternetSales table.

  • Drag date or territory fields to the ROWS or COLUMNS area to analyze sales by date or territory.

Step three: optionally create relationships

Sometimes you need to create a relationship between two tables before you can use them in a PivotTable. If you get a message indicating a relationship is needed, click Create to get started.

Create button appears when relationship is needed

Note    Data Models are not supported on Excel for Mac.

Relational databases are not the only data source that lets you work with multiple tables in a PivotTable Field List. You can use tables in your workbook or import data feeds that you then integrate with other tables of data in your workbook. For more information, see Import multiple tables from other data sources.

To make all this unrelated data work together, you’ll need to add each table to the Data Model, and then create relationships between the tables using matching field values. For more information, see Add worksheet data to a Data Model using a linked table, Create a relationship between two tables, and Create relationships in Diagram View.

After you’ve created a Data Model, you can use this data in your analysis. Here's how you build a new PivotTable or PivotChart using the Data Model in your workbook.

  1. Click any cell on the worksheet.

  2. Select Insert, and then the down arrow under PivotTable.

    Insert PivotTable dropdown showing "from Power BI" option.

  3. Select From External Data Source.

    PivotTable from External Source

  4. Select Choose Connection.

  5. On the Tables tab, in This Workbook Data Model, select Tables in Workbook Data Model.

    Tables in the Data Model

  6. Click Open, and then click OK to show a Field List containing all the tables in the Data Model.

See Also

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×