Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

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.

four multiple worksheets to create a pivot table

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

    Hi Chandeep / Puneet,

    Thanks a lot. This is really nice.
    With this, we can create a pivot from multiple tables in the same workbook.

    Can we create a pivot table from multiple tables, each lying in a different workbook using Excel Query ?

    Regards,
    Raman Girdhar.

    • Puneet Gogia

      Hey Raman,

      Power query is best solution for this thing.

      I’ll try to write a post on that soon.

      • Raman Girdhar

        Hi Puneet, Thanks.
        waiting for the same.

      • Raman Girdhar

        Thanks Puneet. It’s been 2 months, I have been looking forward for the same. Can you let me know by when can we expect this ?

        • Puneet Gogia

          Hey Raman, I’m writing something about that, will publish soon. Which Excel version you have so that I can assist you bit on this?

  • Yoni Livne

    Hi,
    After applying all steps (almost), i changed the text on the SQL but got a messeage: “syntax error. incomplite query caluse