fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

One Pivot Table from Multiple Sheets of Data

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

Load "Monthly Excel" file to Power Query

  • 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.

Select any one sheet and "Transform" to enter the PQE

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.
Delete steps in Power Query
  • In the query view, expand the “Data” column so we can get all the monthly data in one big table.

expand monthly worksheets to one table

But the data is not clean...!

Data Quality Issues with Combined Sheet Data - Power Query

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.

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

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

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

Removing repeated header rows & null values

 

  1. Right click on the columns and use “Change Type” to set the correct data type.
  2. 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.

Close & Load the data to Excel grid

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.

Creating a pivot report from consolidated monthly data

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.

Refresh all pivots and queries - Excel

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.

Learn More 👩‍💻

💥 Complete Excel + Pivot Tables + Power Query course from Chandoo

If you want to learn all the necessary Excel functionalities and how to apply them for work in one place, check out my Excel School program

Trusted by more than 20,000 people around the world, Excel School is the #1 course to help you slay Excel for good. 

Click here to know more and secure your spot.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply