Create a PivotTable to analyze data in multiple tables Excel 2013
If your business stores its important data in relational databases, chances are you analyze and report on that data on a regular basis. When your data happens to be relational, you can build a PivotTable like this in minutes:
What’s different about this PivotTable? Notice how the Field List shows a collection of tables, each one containing fields that you can combine in a single PivotTable to slice your data in multiple ways. No manual formatting or data preparation is necessary. If you have relational data, you can immediately build a PivotTable based on related tables as soon as you import the data.
How do you get multiple tables into a PivotTable Field List? There are two ways. First, when you import from a relational database, you can import multiple tables simultaneously. Alternatively, you can also import tables individually from the same or different data sources, add them to a Data Model in Excel, create relationships, and then use that Data Model to drive a PivotTable.
Let’s take a look at how you would import multiple tables from SQL Server.
- Make sure you know the server name, database name, and which credentials to use when connecting to SQL Server. Your database administrator can provide the necessary information.
- Click Data > Get External Data > From Other Sources > From SQL Server.
- In Server Name, enter the network computer name of the computer that runs SQL Server.
- In Log on credentials, click Use Windows Authentication if you are connecting as yourself. Otherwise, enter the username and password provided by the database administrator.
- In Select Database and Table, choose the database, and then click Enable selection of multiple tables.
- Manually choose the tables you want to work with. Alternatively, pick one or two, and then click Select Related Tables to auto-select tables that are related to those you selected.
- If Import relationships between selected tables is checked, keep it that way to allow Excel to recreate equivalent table relationships in the workbook.
- Click Finish.
- In the Import Data dialog box, choose PivotTable Report.
- Click OK to start the import and populate the Field List.
- Notice that the Field List contains multiple tables. 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 chart by dragging fields from any table to the VALUES, ROWS, or COLUMNS area.
- 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.
- 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.
- To use other relational databases, such as Oracle, you might need to install additional client software. Check with your database administrator to find out if this is required.
- You can import multiple tables from Access. See Tutorial: PivotTable data analysis using a Data Model in Excel 2013 for details.
Other ways to analyze multiple tables
Relational databases are not the only data source that lets you work with multiple tables in a PivotTable Field List. You can use arbitrary tables throughout your workbook, or import data feeds that you then integrate with other tabular data in your workbook. To make all this unrelated data work together, you’ll need to add each table to a Data Model, and then create relationships between the tables using matching lookup values.
- Add worksheet data to a Data Model using a linked table
- Create a relationship between two tables
- Create relationships in Diagram View
Start a new PivotTable using tables in a Data Model
Perhaps you’ve related several tables, creating a Data Model in the process, and are now ready to use this data in your analysis. Here is how you start a new PivotTable or PivotChart using the Data Model in your workbook.
- Click any cell on the worksheet.
- Click Insert > PivotTable.
- In the Create PivotTable dialog box, under Choose the data that you want to analyze, click Use an external data source.
- Click Choose Connection.
- On the Tables tab, in This Workbook Data Model, select Tables in Workbook Data Model.
- Click Open, and then click OK to show a Field List containing all of the tables in the model.
More about PivotTables and Data Models
- Create a Data Model in Excel
- Get data using the PowerPivot add-in
- Use the Field List to arrange fields in a PivotTable
- Create a PivotTable to analyze worksheet data
- Create a PivotTable to analyze external data
- Change the source data range for a PivotTable
- Update data in a PivotTable
- Delete a PivotTable