How to Create a Pivot Table from Multiple Worksheets

With a pivot table, you summarize your data within a few seconds. You can create reports, analyze data, and easily share with others.

That's the beauty of pivot tables.

But here is a twist:

Normally when you create a pivot table you can select source data from a single table in a worksheet as Excel doesn't allow you to refer to different worksheets.

But sometimes, it happens that we need to use the source data from multiple worksheets to create a pivot table.

So today, I'd like to share with you simple steps to use multiple worksheets in a pivot table.

The Problem!

Assume that you want to analyze the sales data of your company and you pull out the yearly data for the last 4 years.

This is how you get the data dump in Excel.

Please note, the data in all the 4 sheets is different but the structure is exactly the same i.e. same number of columns and heading.

Now, to analyze this data, you need to make a single pivot table report from these multiple sheets.

Some of the common methods are:

1. Manually copy and paste data from each sheet and make one single data set on a new sheet.
2. Use a VBA code to automatically consolidate data from multiple sheets.
3. Or you can, consolidate multiple worksheets using into a single worksheet by using Excel's consolidate option.

But the thing is, these methods require coding, copy-pasting, or, are repetitive.

The Solution

Here I am going to discuss a new method using Microsoft query which is dynamic, robust and simple.

Trust me you’ll love it!

By using Microsoft query you can create a pivot table from multiple worksheets.

Download this data file to follow along.

Steps To Create a Pivot Table from Multiple Worksheets

Here we have simple steps which you can follow and before that, please download this file from here to follow along.

• First of all, select all the data on each sheet and name them.
  • 2005 data named as – Year2005
  • 2006 data named as – Year2006
  • 2007 data named as – Year2007
  • 2007 data named as – Year2007
  • After that, apply data tables to all the data in fours worksheets.
    • Select any cell in the data range.
    • Use the Ctrl + T to convert the data on each sheet it into a table.
    • Make sure “My table has headers” is checked each time.
    • Repeat this for all 4 years (sheets).
    • Let’s start consolidating this data on a new sheet (shortcut to add a new sheet : Shift + F11).
    • In the Data Tab, click on ‘From Other Sources’ -> Choose ‘From Microsoft Query’.
    • In the choose data source box:
      • Click on Excel Files and then press OK.
      • Select the path of your excel file and then select your file click on OK.
      • The named ranges will appear in the ‘Query Wizard- Choose Columns’ box.
      • In the Query Wizard:
        • Drag each named range (using the arrow button) into the ‘Columns in your query area’.
        • Click on Next – Forget about the error.
        • And click on OK
        • Now, we have all the data in our query editor the only thing remaining is to combine the data from all the named ranges. To do that click on the little SQL button.
        • In the SQL box, delete all the text and write a new query and click OK.

        Select * from Year2005
        Union all
        Select * from Year2006
        Union all
        Select * from Year2007
        Union all
        Select * from Year2008

        • Now, the table that appears on the screen has the data from all the 4 sheets.
        • All we need to do is go to File Tab and import that table into Excel.
        • In the end, import the data back to excel as a pivot table.
          • From the File Menu -> click on Return Data to Microsoft Excel.
          • You can see that in total (from all 4 sheets) we have 592 records.
          • Also if you add more data to any of the 4 sheets, the pivot table will update as soon as you refresh it.

          NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

          13 thoughts on “How to Create a Pivot Table from Multiple Worksheets”

          Hi, I have created a pivot which is created by power query and I tried to create multiple worksheets from the filter values but it shows an error but the code runs fine in the normal pivot, could you please help me to find the solution. Reply

          Hi Puneet,
          Thanks for sharing this great piece of work. There is no file link to download for practice.
          Can you please help? Reply

          Thank you for sharing, worked for me with tweak to SQL query, my solution below which selects just specific columns (using my tab & column names) One other point – seems Excel saves a fixed filename in the query string, so you can’t easily rename your file without updating the query string under Data > Connections > Properties (of existing query) Example SQL query:
          SELECT CoinsSheet.Date, CoinsSheet.Name, CoinsSheet.Amount, CoinsSheet.Project FROM CoinsSheet CoinsSheet
          Union All
          SELECT DonationsSheet.Date, DonationsSheet.Name, DonationsSheet.Amount, DonationsSheet.Project FROM DonationsSheet DonationsSheet
          Union All
          SELECT PledgesSheet.Date, PledgesSheet.Name, PledgesSheet.Amount, PledgesSheet.Project FROM PledgesSheet PledgesSheet Reply

          Hi, thanks for posting this – it has worked really well for my expenses log in the most part. However, I’m running into a problem whereby some of the key fields in my report are returning as [blank] – even though there is an entry in there on the source table. Do you know any reason why this might be the case? I’m trying to combine 13 separate worksheets from the same workbook. They all have the same number of columns and headings. Some of these are only 60 rows, whilst some are around 800 rows. I don’t think this should cause the problem though should it? Reply