Learn Excel, Pivot Tables & Charts, Power Query

How to make a pivot table when you have data in multiple sheets [Tutorial]

Last updated on September 26, 2023

Chandoo

Recently I had to create a Pivot report from monthly data. But there is a twist. The data is spread across multiple sheets, one for each month. Let me explain how I built the pivot for that scenario.

Step 1: Load Monthly Data File to Power Query

We can use Power Query and automatically combine all the individual sheets to one big table. For this, you need a monthly workbook that has one tab (sheet) per month. If you need sample data, check out this file.

Create a “NEW” Excel workbook. We will use this file to combine all the monthly worksheets.
Go to Data Ribbon and click on Get Data > From File > From Excel

Select your monthly workbook with all the individual sheets
In the navigator screen, select any one sheet and click on “Transform” to go to Power Query Editor.

Step 2: Combine & Clean-up Data

Once you are in Power Query Editor (PQE), we can quickly combine all monthly sheets and clean-up the data.

Using the “Query Settings” panel on the right, delete all the steps except “Source” step. You can use the ❌ mark next to the step to delete the step.

In the query view, expand the “Data” column so we can get all the monthly data in one big table.

But the data is not clean...!

When you “Expand” the data column, we get all the data, but a ton of problems too. Here is a summary of the key issues.

Incorrect headers
Repeated header rows (one per month)
Incorrect data types (dates, numbers treated as Alpha-numeric by Power Query)
Unwanted columns
Null values or blank rows
Something else?!? 😱

Fortunately, Power Query can solve all these problems quite easily.

Set first row as headers using Home Ribbon > First Row as Headers option
To fix nulls & repeated header rows, use filter on a column like “Sales Person” and filter away the null & title words. See this demo:

Right click on the columns and use “Change Type” to set the correct data type.
Using Home ribbon > Choose Columns select the columns you want.

Step 3: Load the Data to Excel

Once all the clean-up is done, rename your query to something like “Combined data” and use the Close & Load button in Home ribbon to load this data to Excel.

Pro Tip

Use the "Load to" options to load the data just to your data model instead of Excel grid. This is helpful if you just want to make pivots and don't need to "see" the data.

Step 4: Create Pivot Table(s)

Now that all the data is in one place, you can create a pivot report easily in Excel. Just select the “combined data” table and use Insert > Pivot Table to add the reports you want.

Step 5: How to update when you have "NEW" data?

Come next month, you may have another sheet in the “monthly file”. Just save the file and open the consolidated workbook (with all your pivots) and use the “Data” ribbon > Refresh All button to refresh the queries. You may need to press this twice to update the pivots too.

VIDEO: How to create a pivot table from multiple sheets of data?

Check out this video tutorial to understand how to make a pivot table from multiple sheets of data. (see it on my channel)

📂 Sample File

Download the sample data file (monthly data) to practice this concept on your computer.

What to do when you have an error? ⛔

While this trick works most of the time, you can still get some errors. Use below tips to debug / fix the problems.

Save & Close the Monthly file: The refresh / update process may throw errors if your file is not closed. So, save and close the workbook.
Monthly tabs should be consistently formatted: The headers and number of columns in your monthly file should match across tabs. If you have different headers or number of columns, then the combined file may have errors or incorrect values.
Missing Data: If you notice that some data (a month for ex.) is missing in your consolidated file or pivots, try refreshing the file a few times. If that doesn’t work, go to “Query” ribbon and “Edit” the query. Once you are in PQ Editor, backtrack and locate the step that might be causing the problem.
Any other problem? Leave a comment with the issue you faced so I can offer some guidance.