How to create PivotTable from multiple tables on different sheets?

Question

I am trying to create one Pivot Table to summarize all my data for the year. Each month has a separate sheet. When I try to consolidate the data, I get the error message that the source is not valid.

I have tried using Power Query, but that does not let me keep my Pivot Table in the same worksheet with my other data. Is there a way to create this Pivot Table without exporting data from a different worksheet?

Answer 1

To create a PivotTable from multiple tables located on different sheets in Excel, you can use the following methods:

1. Using Power Query: Although you mentioned that Power Query does not allow you to keep the PivotTable in the same worksheet, it is still a powerful tool for consolidating data from multiple sheets. You can load the consolidated data into a new worksheet and then create a PivotTable from that data. Here’s how:
   • Go to the Data tab and select Get Data > From Other Sources > Blank Query.
   • Use the Advanced Editor to combine your data from different sheets into one table.
   • Load the combined data into a new worksheet.
   • Create your PivotTable from this new data.
2. Using the Data Model: If you have Excel 2013 or later, you can use the Data Model feature to create relationships between tables from different sheets. Here’s how:
   • Load each of your monthly tables into the Data Model by selecting them and choosing Add to Data Model when creating a PivotTable.
   • Once all tables are added, you can create relationships between them based on common fields.
   • Create a PivotTable from the Data Model, which allows you to summarize data from multiple tables.
3. Using Consolidate Feature: You can also use the Consolidate feature in Excel:
   • Go to the Data tab and select Consolidate.
   • Choose the function you want to use (e.g., Sum, Count) and select the ranges from each sheet.
   • This will create a summary table that you can then use to create a PivotTable.

If you encounter an error stating that the source is not valid, ensure that the ranges you are selecting are correctly defined and that there are no blank rows or columns in your data.

---

References:

• Export to an Excel PivotTable
• About the PowerPivot Model Object in Excel

Answer 2

The first step is to get all data from all the sheets using Power Query. There are various ways and it depends how your data is prepared / organized in each sheet.

Let us assume in each sheet you have a table and the data is in there.
https://support.office.com/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664

Data \ Get Data \ From File \ From Workbook \ Choose your workbook \ Import

In the new window simply choose the file and click Transform data, so Power Query opens and shows all the data in your file.

If you file contains the data in tables, you can filter the Kind column to get the tables only.

Select the Data column and the Name column, right-click and remove other columns.

Expand the data column and you have all the data you need in one table.

Close and load it into a sheet or the data model.
https://www.youtube.com/watch?v=fFvJ3JtITyc

Next step create a Pivot table from that source, done.

If you need further help I need to see your sample file.

Andreas,